ORDER BY not fetching form end of records when index is descendant

[hafedh-trimeche]

Hello, Executing this SQL does not fetch records from the end even the index is descendant. select * from AccessLog where (CAST(DATETIME AS VARCHAR(24)) BETWEEN '2024-09-01 00:00:00.000' AND '2024-10-11 23:59:58.999') ORDER BY 'DATETIME DESC' ROWS 200

CREATE TABLE ACCESSLOG
(
  HEAD_RECORD_AUTOINC bigint,
  HEAD_RECORD_INDICATOR bigint,
  CN varchar(256),
  SUBJECT bigint,
  SESSIONID bigint,
  DATETIME timestamp,
  LOGACTION integer,
  ACTIONOBJECT blob sub_type 1,
  DETAIL blob sub_type 1,
  SEARCHINDEX varchar(256)
);

CREATE DESCENDING INDEX ACCESSLOG_127695072413535 ON ACCESSLOG (SESSIONID);
CREATE DESCENDING INDEX ACCESSLOG_162510553703360 ON ACCESSLOG (SUBJECT);
CREATE DESCENDING INDEX ACCESSLOG_177212822539329 ON ACCESSLOG (DATETIME);
CREATE UNIQUE DESCENDING INDEX ACCESSLOG_184407378811076 ON ACCESSLOG (HEAD_RECORD_AUTOINC);
CREATE DESCENDING INDEX ACCESSLOG_24247933457952 ON ACCESSLOG (SEARCHINDEX);
CREATE DESCENDING INDEX ACCESSLOG_46823003101201 ON ACCESSLOG (CN);
CREATE DESCENDING INDEX ACCESSLOG_81097265135624 ON ACCESSLOG (LOGACTION);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON ACCESSLOG TO  SYSDBA WITH GRANT OPTION GRANTED BY SYSDBA;

Best regards.

[aafemt]

ORDER BY 'DATETIME DESC'

Are you sure about this part? You are sorting by string literal i.e. constant.

[hafedh-trimeche]

ORDER BY DATETIME DESC (without a quote) doesn't work! 12 Rows

With quote the result is: 200 Rows

[asfernandes]

What Firebird version?

[hafedh-trimeche]

Firebird-5.0.2.1499-0-00c0049-windows-x64

[asfernandes]

Can you try the same in ISQL? (I have no idea if the tool you're using are modifying the query to make pagination).

Can you also try a "traditional" (and much more optimized version) of the query in this tool and in ISQL?

select *
    from AccessLog
    where DATETIME BETWEEN timestamp '2024-09-01 00:00:00.000' AND timestamp  '2024-10-11 23:59:58.999'
    ORDER BY DATETIME DESC
    ROWS 200

[hafedh-trimeche]

I'm using FlameRobin 0.9.11

Preparing statement: select *
    from AccessLog
    where DATETIME BETWEEN timestamp '2024-09-01 00:00:00.000' AND timestamp  '2024-10-11 23:59:58.999')
    ORDER BY DATETIME DESC
    ROWS 200

Error: *** IBPP::SQLException ***
Context: Statement::Prepare( select *
    from AccessLog
    where DATETIME BETWEEN timestamp '2024-09-01 00:00:00.000' AND timestamp  '2024-10-11 23:59:58.999')
    ORDER BY DATETIME DESC
    ROWS 200
 )
Message: isc_dsql_prepare failed

SQL Message : -104
Invalid token

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 3, column 104
)

Total execution time: 0.002s

[hafedh-trimeche]

The SQL should be: select * from AccessLog where DATETIME BETWEEN timestamp '2024-09-01 00:00:00.000' AND timestamp '2024-10-11 23:59:58.999' ORDER BY DATETIME DESC ROWS 200 But gives wrong result!

[asfernandes]

What' s wrong? Please create complete test case, with data.

[hafedh-trimeche]

Please download this backup: https://mega.nz/file/KxIiyLiJ#j40V-NFbgnuDZJUlrfvggszsGnVoSMF1ILwHN7e0KJ8 SYSDBA / masterkey

[asfernandes]

I'm not able to reproduce any problem you mentioned with latest v5 source tree.

[hafedh-trimeche]

Sorry, Order By quoted string would caused the problem!

[hafedh-trimeche]

Backup and Restore database solved the problem.

[hafedh-trimeche]

Which would caused the problem?

[asfernandes]

Maybe some index corruption, which may have happened even due to hardware or disk problem.

[hafedh-trimeche]

Thanks

[hafedh-trimeche]

Please note that this request outputs this result: select * from AccessLog ORDER BY HEAD_RECORD_AUTOINC DESC ROWS 200

2024-09-11 09:51:39 and 2024-09-11 09:50:12 are included into the result.

but using this Datetime one: select * from AccessLog where (LOGACTION BETWEEN 0 AND 13) AND (DATETIME BETWEEN CAST('2024-09-10 00:00:00.000' AS TIMESTAMP) AND CAST('2024-09-11 23:59:59.999' AS TIMESTAMP)) ORDER BY DATETIME DESC ROWS 200

the first 7 rows are excluded from the result even DATETIME is inside the range.

[asfernandes]

This data is not the database you sent. It does not make sense to test things in corrupted database. If you can generate new database or backup that reproduce a problem, reopen this.