SELECT with comparison between indexed DATETIME_MICROS values does not work as expected #809
Description
I think there is still a problem with comparison operators.
If a LocalDateTime property is indexed, the <= and >= comparison operators don't work as expected.
If it's not indexed, the comparison operators work as expected.
I have created two versions of the same test case.
In both versions, I create a document type with two DATETIME_MICROS properties, called start and stop.
I insert two records:
- record 1: start = '2022-11-27T21:55:23', stop = '2022-11-29T00:23:22'
- record 2: start = '2022-03-18T21:55:23', stop = '2022-03-20T00:23:21'
In both cases I then select the records that satisfy the following inequality expression: start <= '2022-03-19T00:26:24.404379' AND stop >= '2022-03-19T00:28:26.525650'. The select should return record 2.
When there is no index, the correct record (record 2) is returned.
When there is an index on (start, stop), the wrong record (record 1) is returned.
This is the output of the testLocalDateTimeComparisonWithoutIndex test case:
sql String: INSERT INTO Product SET start = ?, stop = ?, parameters = [2022-11-27T21:55:23, 2022-11-29T00:23:22]
result: #1:0@Product[?]
sql String: INSERT INTO Product SET start = ?, stop = ?, parameters = [2022-03-18T21:55:23, 2022-03-20T00:23:21]
result: #2:0@Product[?]
sql String: SELECT start, stop FROM Product WHERE start <= ? AND stop >= ? ORDER BY start DESC, stop DESC LIMIT 1, parameters = [2022-03-19T00:26:24.404379, 2022-03-19T00:28:26.525650]
start = 2022-03-18T21:55:23, stop = 2022-03-20T00:23:21
and this is the output of the testLocalDateTimeComparisonWithIndex test case:
sql String: INSERT INTO Product SET start = ?, stop = ?, parameters = [2022-11-27T21:55:23, 2022-11-29T00:23:22]
result: #1:0@Product[?]
sql String: INSERT INTO Product SET start = ?, stop = ?, parameters = [2022-03-18T21:55:23, 2022-03-20T00:23:21]
result: #2:0@Product[?]
sql String: SELECT start, stop FROM Product WHERE start <= ? AND stop >= ? ORDER BY start DESC, stop DESC LIMIT 1, parameters = [2022-03-19T00:26:24.404379, 2022-03-19T00:28:26.525650]
start = 2022-11-27T21:55:23, stop = 2022-11-29T00:23:22
testLocalDateTimeComparisonWithoutIndex.zip
testLocalDateTimeComparisonWithIndex.zip
Originally posted by @vic0824 in #794 (comment)