MM/dd/yyyy date ranges are not normalized for system date fields, so suggested modDate queries silently return nothing

Problem Statement

MM/dd/yyyy date ranges are not normalized when the date field is a system field, so a query that dotCMS itself suggests returns no results.

The Show Query dialog in Content Search (Content → Search → Search dropdown → Show Query) documents:

+modDate:[12/15/2006 TO 02/03/2010]

Copy that into the Query Tool and it silently returns nothing. The same range against a client-added content-type date field (news.expire:[12/15/2006 TO 02/03/2010]) works correctly. The format is therefore half-supported: valid for custom date fields, broken for system date fields.

This is a parser bug, not a documentation bug. The suggested query text is reasonable and should keep working verbatim — the fix belongs in the query normalization so that MM/dd/yyyy behaves identically regardless of whether the field is a system field (modDate, creationDate, …) or a date field added by the client.

Impact: Anyone who builds a date-filtered content query from the built-in query help — including Support, who hands these queries to customers. The failure is silent: no error, just an empty result set, so it reads as "no matching content" rather than "malformed query". Surfaced while helping a customer build a modDate cutoff query for a content cleanup (FD #38919).

Root cause

In LuceneQueryDateTimeFormatter.findAndReplaceQueryDates():

1. The MM/dd/yyyy → MM/dd/yyyy 00:00:00 TO MM/dd/yyyy 23:59:59 padding for a clause starting with moddate: sits inside the for (Field field : dateFields) loop. dateFields is only populated from clauses shaped contentTypeVar.fieldVar: — so when modDate is the only date clause in the query, dateFields is empty and the branch never executes. This is also why the check is a hardcoded clause.startsWith("moddate:") rather than a general system-date-field concept.
2. The generic fallback passes near the end of the method are field-agnostic, which is the right shape — but their value regexes are too narrow: \[([0-9]*) (TO) ([0-9]*)\] and \[([a-z0-9]*) (TO) ([a-z0-9]*)\]. Neither matches a value containing / or a space-separated time component.
3. The final elasticsearch#2980 workaround then rewrites / → \/, so Elasticsearch receives +moddate:[12\/15\/2006 TO 02\/03\/2010] against a date-mapped field.

The supporting machinery already exists and is not the problem: LUCENE_DATE_TIME_FORMAT_PATTERNS already enumerates every accepted input form (MM/dd/yyyy, MM/dd/yyyy HH:mm:ss, MM/dd/yyyy hh:mm:ssa, yyyyMMdd, yyyyMMddHHmmss, and the time-only variants), and replaceDateTimeFormatInClause() already tries them in order and converts to the ES-accepted format.

Suggested approach: widen the value pattern in the field-agnostic fallback pass (item 2) to cover the non-digit date shapes already listed in LUCENE_DATE_TIME_FORMAT_PATTERNS, and apply the 00:00:00 / 23:59:59 boundary padding there too. Because that pass runs before the slash-escaping step, a normalized value has no slashes left to escape and item 3 resolves itself. That keeps the fix in one field-agnostic place instead of extending the hardcoded moddate: special case into a system-field allowlist — the pattern that produced this gap in the first place. Implementer's call, but a per-field-name list should be avoided.

There is currently no test coverage anywhere for findAndReplaceQueryDates.

Affected files

File	Detail
dotCMS/src/main/java/com/dotcms/content/elasticsearch/business/LuceneQueryDateTimeFormatter.java	the normalization gap — primary fix site
dotCMS/src/main/java/com/dotcms/content/elasticsearch/business/ESContentFactoryImpl.java:1581,1589	callers (translateQuery → findAndReplaceQueryDates)
dotCMS/src/main/webapp/WEB-INF/messages/Language.properties:2597	message.contentlet.hint3 — the suggested example that must work as-is
dotCMS/src/main/webapp/html/portlet/ext/contentlet/view_contentlets_js_inc.jsp:2811-2814	renders hint2/hint3/hint4/hint6 into the Show Query dialog

No documentation change is required by this ticket. The existing help text is the specification: +modDate:[12/15/2006 TO 02/03/2010] must work exactly as printed. (Optional cosmetic follow-up, explicitly not a blocker: the example's 2006–2010 dates are stale and could be refreshed to something like +modDate:[20270101 TO 20301231], and viewcontentlets.message.datetime.hint at Language.properties:5287 could lead with the yyyyMMddHHmmss form. Neither is needed for this fix to be complete.)

Steps to Reproduce

1. Log into dotAdmin and go to Content → Search.
2. Set Type to any content type (e.g. File) and pick a Site or Folder.
3. Open the Search split-button dropdown and choose Show Query.
4. In the dialog, read the Date Ranges help block below Ordered by — it shows Example 1: +modDate:[12/15/2006 TO 02/03/2010].
5. Take the Lucene query offered at the top of the same dialog and append that date range, so modDate is the only date clause:

   +contentType:FileAsset +languageId:1 +deleted:false +working:true +variant:default +modDate:[12/15/2006 TO 02/03/2010]
   

6. Paste it into Dev Tools → Query Tool and run it.

Expected: the content modified in that window — the same result set as the yyyyMMdd equivalent.

Actual: zero results, no error.

Controls that confirm the diagnosis:

Query	Result today
+modDate:[12/15/2006 TO 02/03/2010]	✗ empty
+modDate:[20061215 TO 20100203]	✓ correct results
+news.expire:[12/15/2006 TO 02/03/2010] (client-added date field)	✓ correct results

Acceptance Criteria

• A query whose only date clause is a system date field in MM/dd/yyyy form returns the same result set as its yyyyMMdd equivalent — verified for modDate, creationDate, versionTs, and wfModDate, plus any other system field indexed as a date (pubdate, expdate, sysPublishDate).
• The same holds for a client-added content-type date field (myType.myDateField:[12/15/2006 TO 02/03/2010]) — both on its own and combined in one query with a system date field clause.
• Every input form already listed in LUCENE_DATE_TIME_FORMAT_PATTERNS is normalized for system date fields, not just MM/dd/yyyy: yyyyMMdd, yyyyMMddHHmmss, MM/dd/yyyy HH:mm:ss, MM/dd/yyyy hh:mm:ssa (and the hh:mm a / hh:mma spacing variants).
• A MM/dd/yyyy range with no time component still receives the 00:00:00 (from) and 23:59:59 (to) boundary padding, so the range is inclusive of both end dates.
• The / → \/ escaping no longer corrupts a date range: after normalization no slashes remain in a date value, and the escaping still applies to non-date parts of the query that need it.
• The example printed in the Show Query dialog works verbatim. Pasting +modDate:[12/15/2006 TO 02/03/2010] into the Query Tool returns results without any change to the help text.
• No regression: existing queries using yyyyMMdd / yyyyMMddHHmmss ranges, and existing content-type date-field queries, behave exactly as they do today. The Query Tool's own shipped example (+contentType:fileAsset +deleted:false +modDate:[20250101 TO 20991231]) still works.
• Edge cases: open-ended and wildcard ranges ([20230101 TO *], [* TO 20230101]) continue to work; a malformed or unparseable date value fails loudly or is passed through unchanged rather than silently producing an empty result set.
• Unit tests are added for LuceneQueryDateTimeFormatter.findAndReplaceQueryDates() — currently untested — covering each supported input format, for a system date field alone, a content-type date field alone, and both combined.

dotCMS Version

Reported by a customer on 25.07.10 LTS. The code paths above were inspected on a current main checkout and are unchanged, so this affects main as well as LTS.

Severity

Medium - Some functionality impacted

Links

• Freshdesk ticket #38919
• elasticsearch#2980 — the slash-escaping workaround referenced in the code

Start in dotCMS/src/main/java/com/dotcms/content/elasticsearch/business/LuceneQueryDateTimeFormatter.java and inspect findAndReplaceQueryDates(), its date patterns, and the callers in ESContentFactoryImpl.java at the referenced lines. Add unit coverage for system and content-type date ranges across the listed formats, including boundary padding and wildcard cases. Done means the Show Query modDate example and existing yyyyMMdd queries normalize without regressions.