SQL: "ORDER BY" skips NULL value for indexed properties

[gramian]

ArcadeDB Server v25.10.1-SNAPSHOT (build 303868455cdd9ab4a7e30a1d71ae3e4985e03747/1761120719567/main)

Running on Linux 6.14.0-33-generic - OpenJDK 64-Bit Server VM 21.0.8

Given a type with an indexed property, the number of results varies if ordered or not.

CREATE DOCUMENT TYPE doc;
CREATE PROPERTY doc.num LONG;
CREATE INDEX ON doc (num) NOTUNIQUE;
INSERT INTO doc SET num = 1;
INSERT INTO doc SET num = 2;
INSERT INTO doc;

SELECT FROM doc -- returns 3 records

SELECT FROM doc ORDER BY num -- returns 2 records

Is this behavior expected? If so, I would document it.

Given the default NULL_STRATEGY of SKIP, it seems ORDER BY uses the index, but does not prepend or append all records with a NULL property.

[lvca]

LSM indexes don't support indexing of NULL values. This is something we should do. We could have a new null strategy INDEX:

  public enum NULL_STRATEGY {ERROR, SKIP, INDEX}

And then index the null value as the lowest value.

[lvca]

This might be strictly connected to #2453.

[gramian]

Does not work for me yet:

CREATE INDEX ON doc (num) NOT_UNIQUE NULL_STRATEGY INDEX

errors:

Exception on parsing the following SQL command: CREATE INDEX ON doc (num) NOTUNIQUE NULL_STRATEGY INDEX
com.arcadedb.exception.CommandSQLParsingException: Encountered "index" "INDEX" at line 1, column 51.
Was expecting one of:
    "skip" ...
    "error" ...
    
	at com.arcadedb.query.sql.parser.StatementCache.throwParsingException(StatementCache.java:127)
	at com.arcadedb.query.sql.parser.StatementCache.parse(StatementCache.java:117)
	at com.arcadedb.query.sql.parser.StatementCache.get(StatementCache.java:71)
	at com.arcadedb.query.sql.SQLQueryEngine.parse(SQLQueryEngine.java:219)
	at com.arcadedb.query.sql.SQLQueryEngine.command(SQLQueryEngine.java:115)
	at com.arcadedb.database.LocalDatabase.command(LocalDatabase.java:1379)
	at com.arcadedb.console.Console.executeSQL(Console.java:632)
	at com.arcadedb.console.Console.execute(Console.java:293)
	at com.arcadedb.console.Console.parse(Console.java:787)
	at com.arcadedb.console.Console.interactiveMode(Console.java:149)
	at com.arcadedb.console.Console.execute(Console.java:206)
	at com.arcadedb.console.Console.main(Console.java:166)
Caused by: com.arcadedb.query.sql.parser.ParseException: Encountered "index" "INDEX" at line 1, column 51.
Was expecting one of:
    "skip" ...
    "error" ...
    
	at com.arcadedb.query.sql.parser.SqlParser.generateParseException(SqlParser.java:27011)
	at com.arcadedb.query.sql.parser.SqlParser.jj_consume_token(SqlParser.java:26853)
	at com.arcadedb.query.sql.parser.SqlParser.CreateIndexStatement(SqlParser.java:15285)
	at com.arcadedb.query.sql.parser.SqlParser.StatementInternal(SqlParser.java:1423)
	at com.arcadedb.query.sql.parser.SqlParser.Statement(SqlParser.java:1082)
	at com.arcadedb.query.sql.parser.SqlParser.Parse(SqlParser.java:255)
	at com.arcadedb.query.sql.parser.StatementCache.parse(StatementCache.java:111)
	... 10 more