You are viewing a plain text version of this content. The canonical link for it is here.
Posted to oak-commits@jackrabbit.apache.org by th...@apache.org on 2026/10/06 07:53:55 UTC

(jackrabbit-oak) branch OAK-12344 updated: OAK-12344: Align ORDER BY placement of missing and invalid values in Lucene and Elastic with the query engine (#3188)

This is an automated email from the ASF dual-hosted git repository.

thomasmueller pushed a commit to branch OAK-12344
in repository https://gitbox.apache.org/repos/asf/jackrabbit-oak.git


The following commit(s) were added to refs/heads/OAK-12344 by this push:
     new 4fbf9c0188 OAK-12344: Align ORDER BY placement of missing and invalid values in Lucene and Elastic with the query engine (#3188)
4fbf9c0188 is described below

commit 4fbf9c018858b0e69ff1fcce4ea8b2c5eae51d8f
Author: Anton Hosgood <ah...@icloud.com>
AuthorDate: Tue Oct 6 09:53:49 2026 +0200

    OAK-12344: Align ORDER BY placement of missing and invalid values in Lucene and Elastic with the query engine (#3188)
    
    * OAK-12344: Align ORDER BY placement of missing and invalid values in Lucene and Elastic with the query engine
    
    * OAK-12344: Set Elastic missing sort order explicitly in both directions
    
    * OAK-12344: Restore Lucene test for ORDER BY-only query on non-date values
    
    * OAK-12344: Move FT_OAK-12344 to FulltextIndex
    
    ---------
    
    Co-authored-by: Anton Hosgood <ah...@adobe.com>
---
 oak-doc/src/site/markdown/query/lucene.md          |  24 +++-
 .../plugins/index/lucene/LuceneDocumentMaker.java  |   2 +-
 .../index/lucene/LuceneIndexProviderService.java   |   3 +
 .../plugins/index/lucene/LucenePropertyIndex.java  |  13 +-
 .../plugins/index/lucene/LuceneOrderByTest.java    |  81 +++--------
 .../index/lucene/LucenePropertyIndexTest.java      |  19 +++
 .../index/elastic/ElasticIndexProviderService.java |   3 +
 .../index/elastic/query/ElasticRequestHandler.java |  11 +-
 .../plugins/index/elastic/ElasticOrderByTest.java  |  54 +++-----
 .../elastic/ElasticRegexPropertyIndexTest.java     |   2 +-
 .../index/search/spi/query/FulltextIndex.java      |  10 ++
 .../oak/plugins/index/OrderByCommonTest.java       | 150 +++++++++++++++++++--
 12 files changed, 255 insertions(+), 117 deletions(-)

diff --git a/oak-doc/src/site/markdown/query/lucene.md b/oak-doc/src/site/markdown/query/lucene.md
index b96f506640..ce7cf383c4 100644
--- a/oak-doc/src/site/markdown/query/lucene.md
+++ b/oak-doc/src/site/markdown/query/lucene.md
@@ -483,8 +483,18 @@ ordered
     jcr:content/@jcr:lastModified`
 
   Refer to [Lucene based Sorting][OAK-2196] for more details. Note that this is
-  only supported for single value property. Enabling this on multi value property
-  would cause indexing to fail.
+  only supported for single value property. For a multi value property, the value is
+  ignored for sorting (a warning is logged).
+
+  Nodes without a sortable value (the property is absent, or its value can't be converted to the
+  declared `type`) sort first in ascending and last in descending order. In union queries (e.g. XPath
+  queries with `or` conditions), values that can't be converted may be ordered differently, as the
+  query engine then compares the stored values.
+
+  Note: `ordered` alone only allows sorting, not selecting nodes. If the `order by` is the only condition
+  the index can evaluate (e.g. `select * from [nt:base] where isdescendantnode('/content') order by [date]`
+  on an index without `evaluatePathRestrictions`), the query returns no results. Set `propertyIndex=true`
+  (or `notNullCheckEnabled=true`) on the property, or add a condition the index can evaluate.
 
   Ordering is supported on properties, and on functions. To order on the name of the node,
   use the following query and index definition:
@@ -504,11 +514,11 @@ type
   type is not used consistently across various nodes then it would recommended
   to specify the type explicitly.
 
-  Note: with `type=Date`, values that are not valid ISO-8601 dates are not indexed as dates, so
-  `order by` on the property gives the wrong order, or no results if the query relies on that
-  property to select nodes. For inconsistent values, leave `type` unset or use `type=String`;
-  string values sort lexicographically, which matches date order only for uniformly formatted
-  timestamps (same precision and zone, e.g. UTC `Z`).
+  Note: with `type=Date`, values that are not valid ISO-8601 dates have no sortable value (see
+  [ordered](#ordered)). Also, unless `notNullCheckEnabled=true` is set, nodes whose value is not stored
+  as a date (e.g. as a string) are not returned if the `order by` is the only condition the index can
+  evaluate. To sort them, leave `type` unset or use `type=String`; strings sort lexicographically, which
+  matches date order only for uniformly formatted timestamps (same precision and zone, e.g. UTC `Z`).
 
   For binary properties, you do not need to index the property separately.
   Binary properties are automatically added to the fulltext index (but only there),
diff --git a/oak-lucene/src/main/java/org/apache/jackrabbit/oak/plugins/index/lucene/LuceneDocumentMaker.java b/oak-lucene/src/main/java/org/apache/jackrabbit/oak/plugins/index/lucene/LuceneDocumentMaker.java
index 1882e156c4..4fbd06cb4c 100644
--- a/oak-lucene/src/main/java/org/apache/jackrabbit/oak/plugins/index/lucene/LuceneDocumentMaker.java
+++ b/oak-lucene/src/main/java/org/apache/jackrabbit/oak/plugins/index/lucene/LuceneDocumentMaker.java
@@ -78,7 +78,7 @@ public class LuceneDocumentMaker extends FulltextDocumentMaker<Document> {
 
     private static final String ORDERED_CONVERT_WARN =
             "[{}] Ignoring ordered value for property {} (type {}): not convertible to the declared "
-            + "type {} at {}. ORDER BY may return incorrect or no results - leave type unset or use type=String.";
+            + "type {} at {}. The value is not usable for ORDER BY on this property - leave type unset or use type=String.";
 
     /**
      * Feature toggle for OAK-12372: ignore facet properties with category path longer than 8191 characters to
diff --git a/oak-lucene/src/main/java/org/apache/jackrabbit/oak/plugins/index/lucene/LuceneIndexProviderService.java b/oak-lucene/src/main/java/org/apache/jackrabbit/oak/plugins/index/lucene/LuceneIndexProviderService.java
index c34595438b..b3e6572f9b 100644
--- a/oak-lucene/src/main/java/org/apache/jackrabbit/oak/plugins/index/lucene/LuceneIndexProviderService.java
+++ b/oak-lucene/src/main/java/org/apache/jackrabbit/oak/plugins/index/lucene/LuceneIndexProviderService.java
@@ -404,6 +404,9 @@ public class LuceneIndexProviderService {
         oakRegs.add(whiteboard.register(FeatureToggle.class,
                 new FeatureToggle(FulltextIndexEditor.FT_OAK_12365, FulltextIndexEditor.FT_OAK_12365_DISABLE),
                 emptyMap()));
+        oakRegs.add(whiteboard.register(FeatureToggle.class,
+                new FeatureToggle(FulltextIndex.FT_OAK_12344, FulltextIndex.FT_OAK_12344_DISABLE),
+                emptyMap()));
         initializeIndexDir(bundleContext, config);
         initializeExtractedTextCache(bundleContext, config, statisticsProvider);
         tracker = createTracker(bundleContext, config);
diff --git a/oak-lucene/src/main/java/org/apache/jackrabbit/oak/plugins/index/lucene/LucenePropertyIndex.java b/oak-lucene/src/main/java/org/apache/jackrabbit/oak/plugins/index/lucene/LucenePropertyIndex.java
index 340a3f877c..d8616028de 100644
--- a/oak-lucene/src/main/java/org/apache/jackrabbit/oak/plugins/index/lucene/LucenePropertyIndex.java
+++ b/oak-lucene/src/main/java/org/apache/jackrabbit/oak/plugins/index/lucene/LucenePropertyIndex.java
@@ -826,7 +826,18 @@ public class LucenePropertyIndex extends FulltextIndex {
             boolean reverse = oe.getOrder() != OrderEntry.Order.ASCENDING;
             String propName = oe.getPropertyName();
             propName = FieldNames.createDocValFieldName(propName);
-            fieldsList.add(new SortField(propName, toLuceneSortType(oe, pd), reverse));
+            SortField.Type sortType = toLuceneSortType(oe, pd);
+            SortField sortField = new SortField(propName, sortType, reverse);
+            // like the query engine, sort documents without a value first when ascending and last when
+            // descending (string sorts already do so by default)
+            if (!FulltextIndex.FT_OAK_12344_DISABLE.get()) {
+                if (sortType == SortField.Type.LONG) {
+                    sortField.setMissingValue(Long.MIN_VALUE);
+                } else if (sortType == SortField.Type.DOUBLE) {
+                    sortField.setMissingValue(Double.NEGATIVE_INFINITY);
+                }
+            }
+            fieldsList.add(sortField);
         }
 
         if (fieldsList.isEmpty()) {
diff --git a/oak-lucene/src/test/java/org/apache/jackrabbit/oak/plugins/index/lucene/LuceneOrderByTest.java b/oak-lucene/src/test/java/org/apache/jackrabbit/oak/plugins/index/lucene/LuceneOrderByTest.java
index 346ccd56fd..47aeaa9649 100644
--- a/oak-lucene/src/test/java/org/apache/jackrabbit/oak/plugins/index/lucene/LuceneOrderByTest.java
+++ b/oak-lucene/src/test/java/org/apache/jackrabbit/oak/plugins/index/lucene/LuceneOrderByTest.java
@@ -17,22 +17,23 @@
 package org.apache.jackrabbit.oak.plugins.index.lucene;
 
 import org.apache.jackrabbit.oak.api.ContentRepository;
-import org.apache.jackrabbit.oak.api.Tree;
 import org.apache.jackrabbit.oak.plugins.index.LuceneIndexOptions;
 import org.apache.jackrabbit.oak.plugins.index.OrderByCommonTest;
-import org.apache.jackrabbit.oak.plugins.index.search.util.IndexDefinitionBuilder;
+import org.apache.jackrabbit.oak.plugins.index.search.spi.query.FulltextIndex;
+import org.junit.After;
 import org.junit.Ignore;
 import org.junit.Rule;
 import org.junit.Test;
 import org.junit.rules.TemporaryFolder;
 
-import javax.jcr.PropertyType;
 import java.io.File;
+import java.time.LocalDate;
 import java.util.List;
-import java.util.UUID;
 import java.util.concurrent.ExecutorService;
 import java.util.concurrent.Executors;
 
+import static org.junit.Assert.assertTrue;
+
 public class LuceneOrderByTest extends OrderByCommonTest {
 
     private ExecutorService executorService = Executors.newFixedThreadPool(2);
@@ -60,70 +61,28 @@ public class LuceneOrderByTest extends OrderByCommonTest {
         super.orderByMultiProperties();
     }
 
-    /**
-     * OAK-12343: type=Date on an ordered property requires every value to be a valid date. A value
-     * that is not (here "not-a-date") is dropped from the sort field and sorts as the epoch, so
-     * ORDER BY returns the wrong order. Lucene-specific (Elastic sorts such values last), so it
-     * lives here rather than in OrderByCommonTest. Workaround: declare the property as type=String
-     * (see query/lucene.md).
-     *
-     * TODO(OAK-12344): asserts current Lucene behaviour; update the expected order here if Lucene
-     *  is aligned with Elastic.
-     */
-    @Test
-    public void orderByNonDateValuesWithDateTypeIsIncorrect() throws Exception {
-        IndexDefinitionBuilder builder = createIndexDefinitionBuilder();
-        builder.evaluatePathRestrictions();
-        IndexDefinitionBuilder.IndexRule rule = builder.indexRule("nt:base");
-        rule.property("foo").propertyIndex();
-        rule.property("dt").propertyIndex().type(PropertyType.TYPENAME_DATE).ordered();
-        setIndex(UUID.randomUUID().toString(), createIndex(builder, false, "foo", "dt"));
-
-        Tree test = root.getTree("/").addChild("test");
-        Tree a = test.addChild("a");
-        a.setProperty("foo", "bar");
-        a.setProperty("dt", "2023-06-05T16:59:48.119Z");
-        Tree b = test.addChild("b");
-        b.setProperty("foo", "bar");
-        b.setProperty("dt", "2013-06-21T08:30:48.119Z");
-        Tree c = test.addChild("c");
-        c.setProperty("foo", "bar");
-        c.setProperty("dt", "not-a-date");
-        root.commit();
+    @After
+    public void resetToggle() {
+        FulltextIndex.FT_OAK_12344_DISABLE.set(false);
+    }
 
-        // "not-a-date" is dropped from the sort field and sorts as the epoch (first, ascending).
-        assertEventually(() -> assertOrderedQuery(
-                "select [jcr:path] from [nt:base] where foo = 'bar' order by [dt]",
-                List.of("/test/c", "/test/b", "/test/a")));
+    @Test
+    public void ft_oak_12344_toggleShouldBeRemoved() {
+        assertTrue("Feature toggle " + FulltextIndex.FT_OAK_12344 + " is overdue for removal",
+                LocalDate.now().isBefore(LocalDate.of(2027, 10, 31)));
     }
 
     /**
-     * OAK-12343: worse symptom - no results at all. With type=Date and no path evaluation, ORDER BY
-     * is served by a numeric range on the property. The stored values aren't valid ISO-8601 dates,
-     * so none can be indexed as a number and the range matches nothing. Lucene-specific (Elastic
-     * returns the rows).
-     * Workaround: declare the property as type=String (see query/lucene.md).
-     *
-     * TODO(OAK-12344): asserts current Lucene behaviour; update the expectation here if Lucene is
-     *  aligned with Elastic.
+     * Legacy behaviour, kept when FT_OAK-12344 is disabled: a value that is not a date sorts as the epoch, so
+     * after dates before 1970.
      */
     @Test
-    public void orderByNonDateStringValuesWithDateTypeReturnsNoResults() throws Exception {
-        // no evaluatePathRestrictions, so the numeric range on the date property drives the query
-        IndexDefinitionBuilder builder = createIndexDefinitionBuilder();
-        builder.indexRule("nt:base").property("dt").propertyIndex().type(PropertyType.TYPENAME_DATE).ordered();
-        setIndex(UUID.randomUUID().toString(), createIndex(builder, false, "dt"));
-
-        Tree test = root.getTree("/").addChild("test");
-        test.addChild("a").setProperty("dt", "Mon Feb 24 2025 10:22:32 GMT+0000");
-        test.addChild("b").setProperty("dt", "Wed Jan 15 2025 09:00:00 GMT+0000");
-        test.addChild("c").setProperty("dt", "Fri Mar 07 2025 18:30:00 GMT+0000");
-        root.commit();
-
-        // option(traversal fail) forces the index to serve the query, so this asserts index behaviour
+    public void orderByNonDateValuesWithDateTypeSortAsEpochLegacy() throws Exception {
+        FulltextIndex.FT_OAK_12344_DISABLE.set(true);
+        createMixedDateContent();
         assertEventually(() -> assertOrderedQuery(
-                "select [jcr:path] from [nt:base] where isdescendantnode('/test') order by [dt] desc option(traversal fail)",
-                List.of()));
+                "select [jcr:path] from [nt:base] where foo = 'bar' order by [dt]",
+                List.of("/test/a", "/test/c", "/test/b")));
     }
 
 }
diff --git a/oak-lucene/src/test/java/org/apache/jackrabbit/oak/plugins/index/lucene/LucenePropertyIndexTest.java b/oak-lucene/src/test/java/org/apache/jackrabbit/oak/plugins/index/lucene/LucenePropertyIndexTest.java
index e4aba564a8..9c52beb829 100644
--- a/oak-lucene/src/test/java/org/apache/jackrabbit/oak/plugins/index/lucene/LucenePropertyIndexTest.java
+++ b/oak-lucene/src/test/java/org/apache/jackrabbit/oak/plugins/index/lucene/LucenePropertyIndexTest.java
@@ -1230,6 +1230,25 @@ public class LucenePropertyIndexTest extends AbstractQueryTest {
                 .toList(IterableUtils.chainedIterable(getSortedPaths(tuples, OrderDirection.DESC), List.of("/test/n0"))));
     }
 
+    @Test
+    public void orderByOnlyWithNonDateStringValuesWithDateTypeReturnsNoResults() throws Exception {
+        // no evaluatePathRestrictions, so the existence query on the date property drives the query
+        IndexDefinitionBuilder idxb = new LuceneIndexDefinitionBuilder().noAsync();
+        idxb.indexRule("nt:base").property("dt").propertyIndex().type(PropertyType.TYPENAME_DATE).ordered();
+        idxb.build(root.getTree("/").getChild("oak:index").addChild("test1"));
+        root.commit();
+
+        Tree test = root.getTree("/").addChild("test");
+        test.addChild("a").setProperty("dt", "Mon Feb 24 2025 10:22:32 GMT+0000");
+        test.addChild("b").setProperty("dt", "Wed Jan 15 2025 09:00:00 GMT+0000");
+        test.addChild("c").setProperty("dt", "Fri Mar 07 2025 18:30:00 GMT+0000");
+        root.commit();
+
+        // TODO OAK-12434 these nodes should be returned
+        assertOrderedQuery("select [jcr:path] from [nt:base] where isdescendantnode('/test') order by [dt] desc option(traversal fail)",
+                List.of(), SQL2, true);
+    }
+
     @Test
     public void sortQueriesWithStringAndLong() throws Exception {
         Tree idx = createIndex("test1", Set.of("foo", "bar", "baz"));
diff --git a/oak-search-elastic/src/main/java/org/apache/jackrabbit/oak/plugins/index/elastic/ElasticIndexProviderService.java b/oak-search-elastic/src/main/java/org/apache/jackrabbit/oak/plugins/index/elastic/ElasticIndexProviderService.java
index e7a75ad01b..e51431ecab 100644
--- a/oak-search-elastic/src/main/java/org/apache/jackrabbit/oak/plugins/index/elastic/ElasticIndexProviderService.java
+++ b/oak-search-elastic/src/main/java/org/apache/jackrabbit/oak/plugins/index/elastic/ElasticIndexProviderService.java
@@ -258,6 +258,9 @@ public class ElasticIndexProviderService {
         oakRegs.add(whiteboard.register(FeatureToggle.class,
                 new FeatureToggle(ElasticFeatureToggles.FT_OAK_12415, ElasticFeatureToggles.FT_OAK_12415_ENABLE),
                 emptyMap()));
+        oakRegs.add(whiteboard.register(FeatureToggle.class,
+                new FeatureToggle(FulltextIndex.FT_OAK_12344, FulltextIndex.FT_OAK_12344_DISABLE),
+                emptyMap()));
         if (System.getProperty(QueryEngineSettings.OAK_INFERENCE_ENABLED) != null) {
             this.isInferenceEnabled = Boolean.parseBoolean(System.getProperty(QueryEngineSettings.OAK_INFERENCE_ENABLED));
         } else {
diff --git a/oak-search-elastic/src/main/java/org/apache/jackrabbit/oak/plugins/index/elastic/query/ElasticRequestHandler.java b/oak-search-elastic/src/main/java/org/apache/jackrabbit/oak/plugins/index/elastic/query/ElasticRequestHandler.java
index cfae30fd6a..6988fe1194 100644
--- a/oak-search-elastic/src/main/java/org/apache/jackrabbit/oak/plugins/index/elastic/query/ElasticRequestHandler.java
+++ b/oak-search-elastic/src/main/java/org/apache/jackrabbit/oak/plugins/index/elastic/query/ElasticRequestHandler.java
@@ -387,10 +387,15 @@ public class ElasticRequestHandler {
                 LOG.warn("Unable to sort by {} for index {}", sortPropertyName, elasticIndexDefinition.getIndexName());
                 continue;
             }
+            boolean ascending = QueryIndex.OrderEntry.Order.ASCENDING.equals(o.getOrder());
+            // like the query engine, sort documents without a value first when ascending and last when descending
+            boolean setMissing = !JCR_PATH.equals(sortPropertyName) && !JCR_SCORE.equals(sortPropertyName)
+                    && !FulltextIndex.FT_OAK_12344_DISABLE.get();
             SortOptions order = SortOptions.of(so -> so
-                    .field(f -> f
-                            .field(fieldName)
-                            .order(QueryIndex.OrderEntry.Order.ASCENDING.equals(o.getOrder()) ? SortOrder.Asc : SortOrder.Desc)));
+                    .field(f -> {
+                        f.field(fieldName).order(ascending ? SortOrder.Asc : SortOrder.Desc);
+                        return setMissing ? f.missing(ascending ? "_first" : "_last") : f;
+                    }));
             list.add(order);
         }
 
diff --git a/oak-search-elastic/src/test/java/org/apache/jackrabbit/oak/plugins/index/elastic/ElasticOrderByTest.java b/oak-search-elastic/src/test/java/org/apache/jackrabbit/oak/plugins/index/elastic/ElasticOrderByTest.java
index a7e880bf8a..c814d921ff 100644
--- a/oak-search-elastic/src/test/java/org/apache/jackrabbit/oak/plugins/index/elastic/ElasticOrderByTest.java
+++ b/oak-search-elastic/src/test/java/org/apache/jackrabbit/oak/plugins/index/elastic/ElasticOrderByTest.java
@@ -17,15 +17,16 @@
 package org.apache.jackrabbit.oak.plugins.index.elastic;
 
 import org.apache.jackrabbit.oak.api.ContentRepository;
-import org.apache.jackrabbit.oak.api.Tree;
 import org.apache.jackrabbit.oak.plugins.index.OrderByCommonTest;
-import org.apache.jackrabbit.oak.plugins.index.search.util.IndexDefinitionBuilder;
+import org.apache.jackrabbit.oak.plugins.index.search.spi.query.FulltextIndex;
+import org.junit.After;
 import org.junit.ClassRule;
 import org.junit.Test;
 
-import javax.jcr.PropertyType;
+import java.time.LocalDate;
 import java.util.List;
-import java.util.UUID;
+
+import static org.junit.Assert.assertTrue;
 
 public class ElasticOrderByTest extends OrderByCommonTest {
 
@@ -47,39 +48,28 @@ public class ElasticOrderByTest extends OrderByCommonTest {
         setTraversalEnabled(false);
     }
 
+    @After
+    public void resetToggle() {
+        FulltextIndex.FT_OAK_12344_DISABLE.set(false);
+    }
+
+    @Test
+    public void ft_oak_12344_toggleShouldBeRemoved() {
+        assertTrue("Feature toggle " + FulltextIndex.FT_OAK_12344 + " is overdue for removal",
+                LocalDate.now().isBefore(LocalDate.of(2027, 10, 31)));
+    }
+
     /**
-     * OAK-12343: Elastic counterpart to the Lucene type=Date limitation. Elastic sorts missing
-     * (non-date) values last instead of as the epoch, so ORDER BY returns the valid dates in order
-     * with the non-date value last - it does not mis-order or drop rows the way Lucene does
-     * (LuceneOrderByTest#orderByNonDateValuesWithDateTypeIsIncorrect).
-     *
-     * TODO(OAK-12344): if Lucene is aligned with this, this becomes the shared expectation.
+     * Legacy behaviour, kept when FT_OAK-12344 is disabled: a value that is not a date sorts last also in
+     * ascending order (the Elasticsearch default for missing values).
      */
     @Test
-    public void orderByNonDateValuesWithDateTypeSortsMissingLast() throws Exception {
-        IndexDefinitionBuilder builder = createIndexDefinitionBuilder();
-        builder.evaluatePathRestrictions();
-        IndexDefinitionBuilder.IndexRule rule = builder.indexRule("nt:base");
-        rule.property("foo").propertyIndex();
-        rule.property("dt").propertyIndex().type(PropertyType.TYPENAME_DATE).ordered();
-        setIndex(UUID.randomUUID().toString(), createIndex(builder, false, "foo", "dt"));
-
-        Tree test = root.getTree("/").addChild("test");
-        Tree a = test.addChild("a");
-        a.setProperty("foo", "bar");
-        a.setProperty("dt", "2023-06-05T16:59:48.119Z");
-        Tree b = test.addChild("b");
-        b.setProperty("foo", "bar");
-        b.setProperty("dt", "2013-06-21T08:30:48.119Z");
-        Tree c = test.addChild("c");
-        c.setProperty("foo", "bar");
-        c.setProperty("dt", "not-a-date");
-        root.commit();
-
-        // valid dates order chronologically; the non-date value sorts last (missing=_last).
+    public void orderByNonDateValuesWithDateTypeSortLastLegacy() throws Exception {
+        FulltextIndex.FT_OAK_12344_DISABLE.set(true);
+        createMixedDateContent();
         assertEventually(() -> assertOrderedQuery(
                 "select [jcr:path] from [nt:base] where foo = 'bar' order by [dt]",
-                List.of("/test/b", "/test/a", "/test/c")));
+                List.of("/test/a", "/test/b", "/test/c")));
     }
 
 }
diff --git a/oak-search-elastic/src/test/java/org/apache/jackrabbit/oak/plugins/index/elastic/ElasticRegexPropertyIndexTest.java b/oak-search-elastic/src/test/java/org/apache/jackrabbit/oak/plugins/index/elastic/ElasticRegexPropertyIndexTest.java
index 9daf0c0f9e..1e772e3203 100644
--- a/oak-search-elastic/src/test/java/org/apache/jackrabbit/oak/plugins/index/elastic/ElasticRegexPropertyIndexTest.java
+++ b/oak-search-elastic/src/test/java/org/apache/jackrabbit/oak/plugins/index/elastic/ElasticRegexPropertyIndexTest.java
@@ -101,7 +101,7 @@ public class ElasticRegexPropertyIndexTest extends ElasticAbstractQueryTest {
             assertThat(explain, containsString("\"sort\":[{\"flat:" +
                     ElasticIndexUtils.fieldName("allProperties") + "." +
                     ElasticIndexUtils.fieldName("propd") +
-                    "\":{\"order\":\"asc\"}},{\":path\":{\"order\":\"asc\"}}]"));
+                    "\":{\"missing\":\"_first\",\"order\":\"asc\"}},{\":path\":{\"order\":\"asc\"}}]"));
             assertThat(explain, containsString("sortOrder: [{ propertyName : propd, propertyType : UNDEFINED, order : ASCENDING }]"));
             assertQuery(propaOrderQuery, List.of("/test/f", "/test/e"));
         });
diff --git a/oak-search/src/main/java/org/apache/jackrabbit/oak/plugins/index/search/spi/query/FulltextIndex.java b/oak-search/src/main/java/org/apache/jackrabbit/oak/plugins/index/search/spi/query/FulltextIndex.java
index bf796fae34..9cdf17a842 100644
--- a/oak-search/src/main/java/org/apache/jackrabbit/oak/plugins/index/search/spi/query/FulltextIndex.java
+++ b/oak-search/src/main/java/org/apache/jackrabbit/oak/plugins/index/search/spi/query/FulltextIndex.java
@@ -60,6 +60,7 @@ import java.util.List;
 import java.util.Map;
 import java.util.Objects;
 import java.util.Set;
+import java.util.concurrent.atomic.AtomicBoolean;
 import java.util.function.Predicate;
 
 import static org.apache.jackrabbit.oak.plugins.index.IndexConstants.TYPE_PROPERTY_NAME;
@@ -86,6 +87,15 @@ public abstract class FulltextIndex implements AdvancedQueryIndex, QueryIndex, N
 
     public static final String FT_FILTER_GLOBALLY_SUPERSEDED = "FT_OAK-12146";
 
+    /**
+     * Feature toggle for OAK-12344: in property sorts, documents without a sortable value (absent, or not
+     * convertible to the declared type) sort first in ascending and last in descending order, as in the query
+     * engine. Enabled by default (bug fix); flipping the toggle sets {@link #FT_OAK_12344_DISABLE} to {@code true}
+     * and restores the legacy behavior (Lucene: such numeric and date values sort as 0; Elastic: missing last).
+     */
+    public static final String FT_OAK_12344 = "FT_OAK-12344";
+    public static final AtomicBoolean FT_OAK_12344_DISABLE = new AtomicBoolean(false);
+
     @Nullable private Feature filterGloballySupersededFeature;
 
     public void setFilterGloballySupersededFeature(@Nullable Feature feature) {
diff --git a/oak-search/src/test/java/org/apache/jackrabbit/oak/plugins/index/OrderByCommonTest.java b/oak-search/src/test/java/org/apache/jackrabbit/oak/plugins/index/OrderByCommonTest.java
index 0d105df8e0..d9f3c15ef5 100644
--- a/oak-search/src/test/java/org/apache/jackrabbit/oak/plugins/index/OrderByCommonTest.java
+++ b/oak-search/src/test/java/org/apache/jackrabbit/oak/plugins/index/OrderByCommonTest.java
@@ -516,23 +516,151 @@ public abstract class OrderByCommonTest extends AbstractQueryTest {
         return row.getValue("plan").getValue(Type.STRING);
     }
 
-    protected void assertOrderedQuery(String sql, List<String> paths) {
-        List<String> result = executeQuery(sql, AbstractQueryTest.SQL2, true, true);
-        assertEquals(paths, result);
+    @Test
+    public void orderByNonDateValuesWithDateTypeSortAsMissing() throws Exception {
+        createMixedDateContent();
+        assertEventually(() -> assertOrderedQuery(
+                "select [jcr:path] from [nt:base] where foo = 'bar' order by [dt]",
+                List.of("/test/c", "/test/a", "/test/b")));
+        assertEventually(() -> assertOrderedQuery(
+                "select [jcr:path] from [nt:base] where foo = 'bar' order by [dt] desc",
+                List.of("/test/b", "/test/a", "/test/c")));
+    }
+
+    @Test
+    public void orderByDateSortsAbsentPropertyFirstAscending() throws Exception {
+        createMixedDateContent();
+        root.getTree("/test/c").removeProperty("dt");
+        root.commit();
+        assertEventually(() -> assertOrderedQuery(
+                "select [jcr:path] from [nt:base] where foo = 'bar' order by [dt]",
+                List.of("/test/c", "/test/a", "/test/b")));
+        assertEventually(() -> assertOrderedQuery(
+                "select [jcr:path] from [nt:base] where foo = 'bar' order by [dt] desc",
+                List.of("/test/b", "/test/a", "/test/c")));
+    }
+
+    @Test
+    public void orderByStringSortsAbsentPropertyFirstAscending() throws Exception {
+        IndexDefinitionBuilder builder = indexOptions.createIndexDefinitionBuilder();
+        builder.evaluatePathRestrictions();
+        IndexDefinitionBuilder.IndexRule rule = builder.indexRule("nt:base");
+        rule.property("foo").propertyIndex();
+        rule.property("s").propertyIndex().type(PropertyType.TYPENAME_STRING).ordered();
+        indexOptions.setIndex(root, UUID.randomUUID().toString(), indexOptions.createIndex(builder, false, "foo", "s"));
+
+        Tree test = root.getTree("/").addChild("test");
+        Tree a = test.addChild("a");
+        a.setProperty("foo", "bar");
+        a.setProperty("s", "y");
+        Tree b = test.addChild("b");
+        b.setProperty("foo", "bar");
+        b.setProperty("s", "x");
+        test.addChild("c").setProperty("foo", "bar");
+        root.commit();
+
+        assertEventually(() -> assertOrderedQuery(
+                "select [jcr:path] from [nt:base] where foo = 'bar' order by [s]",
+                List.of("/test/c", "/test/b", "/test/a")));
+        assertEventually(() -> assertOrderedQuery(
+                "select [jcr:path] from [nt:base] where foo = 'bar' order by [s] desc",
+                List.of("/test/a", "/test/b", "/test/c")));
+    }
+
+    @Test
+    public void orderByMultiplePropertiesSortsMissingFirstAscending() throws Exception {
+        IndexDefinitionBuilder builder = indexOptions.createIndexDefinitionBuilder();
+        builder.evaluatePathRestrictions();
+        IndexDefinitionBuilder.IndexRule rule = builder.indexRule("nt:base");
+        rule.property("foo").propertyIndex();
+        rule.property("a").propertyIndex().type(PropertyType.TYPENAME_LONG).ordered();
+        rule.property("b").propertyIndex().type(PropertyType.TYPENAME_DATE).ordered();
+        indexOptions.setIndex(root, UUID.randomUUID().toString(), indexOptions.createIndex(builder, false, "foo", "a", "b"));
+
+        Tree test = root.getTree("/").addChild("test");
+        Tree n1 = test.addChild("n1");
+        n1.setProperty("foo", "bar");
+        n1.setProperty("a", 1L);
+        n1.setProperty("b", "2023-06-05T16:59:48.119Z");
+        Tree n2 = test.addChild("n2");
+        n2.setProperty("foo", "bar");
+        n2.setProperty("a", 1L);
+        n2.setProperty("b", "not-a-date");
+        Tree n3 = test.addChild("n3");
+        n3.setProperty("foo", "bar");
+        n3.setProperty("a", 1L);
+        n3.setProperty("b", "2013-06-21T08:30:48.119Z");
+        Tree n4 = test.addChild("n4");
+        n4.setProperty("foo", "bar");
+        n4.setProperty("a", 2L);
+        root.commit();
+
+        assertEventually(() -> assertOrderedQuery(
+                "select [jcr:path] from [nt:base] where foo = 'bar' order by [a], [b]",
+                List.of("/test/n2", "/test/n3", "/test/n1", "/test/n4")));
+        assertEventually(() -> assertOrderedQuery(
+                "select [jcr:path] from [nt:base] where foo = 'bar' order by [a], [b] desc",
+                List.of("/test/n1", "/test/n3", "/test/n2", "/test/n4")));
     }
 
-    // Passthroughs so subclasses in other packages can build indexes: the IndexOptions methods are
-    // protected and only reachable from this package.
-    protected IndexDefinitionBuilder createIndexDefinitionBuilder() {
-        return indexOptions.createIndexDefinitionBuilder();
+    @Test
+    public void orderByOnUnionSortsMissingLikeQueryEngine() throws Exception {
+        IndexDefinitionBuilder builder = indexOptions.createIndexDefinitionBuilder();
+        builder.evaluatePathRestrictions();
+        IndexDefinitionBuilder.IndexRule rule = builder.indexRule("nt:base");
+        rule.property("foo").propertyIndex();
+        rule.property("n").propertyIndex().type(PropertyType.TYPENAME_DOUBLE).ordered();
+        indexOptions.setIndex(root, UUID.randomUUID().toString(), indexOptions.createIndex(builder, false));
+
+        Tree test = root.getTree("/").addChild("test");
+        // negative values, so that missing values sorting as 0 (the legacy Lucene behaviour) would be detected
+        Object[][] nodes = {{"a", "bar", -2.0}, {"c", "bar", 1.0}, {"e", "bar", null}, {"b", "baz", -1.0}, {"d", "baz", 2.0}};
+        for (Object[] n : nodes) {
+            Tree t = test.addChild((String) n[0]);
+            t.setProperty("foo", (String) n[1]);
+            if (n[2] != null) {
+                t.setProperty("n", (Double) n[2]);
+            }
+        }
+        root.commit();
+
+        String union = "select [jcr:path] from [nt:base] where foo = 'bar' and isdescendantnode('/test') "
+                + "union select [jcr:path] from [nt:base] where foo = 'baz' and isdescendantnode('/test') order by [n]";
+        assertEventually(() -> assertOrderedQuery(union, List.of("/test/e", "/test/a", "/test/b", "/test/c", "/test/d")));
+        assertEventually(() -> assertOrderedQuery(union + " desc", List.of("/test/d", "/test/c", "/test/b", "/test/a", "/test/e")));
+
+        // the query engine sorts the same way when no index is used
+        setTraversalEnabled(true);
+        String engineSorted = "select [jcr:path] from [nt:base] where isdescendantnode('/test') order by [n]";
+        assertOrderedQuery(engineSorted + " option(index tag none)", List.of("/test/e", "/test/a", "/test/b", "/test/c", "/test/d"));
+        assertOrderedQuery(engineSorted + " desc option(index tag none)", List.of("/test/d", "/test/c", "/test/b", "/test/a", "/test/e"));
     }
 
-    protected IndexDefinitionBuilder createIndex(IndexDefinitionBuilder builder, boolean isAsync, String... propNames) {
-        return indexOptions.createIndex(builder, isAsync, propNames);
+    // a: before 1970, b: after 1970, c: not a date
+    protected void createMixedDateContent() throws Exception {
+        IndexDefinitionBuilder builder = indexOptions.createIndexDefinitionBuilder();
+        builder.evaluatePathRestrictions();
+        IndexDefinitionBuilder.IndexRule rule = builder.indexRule("nt:base");
+        rule.property("foo").propertyIndex();
+        rule.property("dt").propertyIndex().type(PropertyType.TYPENAME_DATE).ordered();
+        indexOptions.setIndex(root, UUID.randomUUID().toString(), indexOptions.createIndex(builder, false, "foo", "dt"));
+
+        Tree test = root.getTree("/").addChild("test");
+        Tree a = test.addChild("a");
+        a.setProperty("foo", "bar");
+        a.setProperty("dt", "1960-06-05T16:59:48.119Z");
+        Tree b = test.addChild("b");
+        b.setProperty("foo", "bar");
+        b.setProperty("dt", "2020-06-21T08:30:48.119Z");
+        Tree c = test.addChild("c");
+        c.setProperty("foo", "bar");
+        c.setProperty("dt", "not-a-date");
+        root.commit();
     }
 
-    protected Tree setIndex(String name, IndexDefinitionBuilder builder) {
-        return indexOptions.setIndex(root, name, builder);
+    protected void assertOrderedQuery(String sql, List<String> paths) {
+        List<String> result = executeQuery(sql, AbstractQueryTest.SQL2, true, true);
+        assertEquals(paths, result);
     }
 
 }