Inconsistent results between 6.3 and 6.4 #2182
Description
Describe the bug
From the description of the queries below, you will see that:
Query A1 with QuestDB 6.4 returns an incorrect number of rows
Query A1 and A2 are perform very poorly (and never even finish with larger databases)
Query B1 results in a puzzling extra row
A copy of the database can be made available upon request.
SELECT * FROM bus WHERE datetime = to_timestamp('20201031_2330','yyyyMMdd_HHmm')2442 rows
SELECT DISTINCT * FROM bus WHERE datetime = to_timestamp('20201031_2330','yyyyMMdd_HHmm')2442 rows
SELECT * FROM branch WHERE datetime = to_timestamp('20201031_2330','yyyyMMdd_HHmm')4106 rows
SELECT DISTINCT * FROM bus WHERE datetime = to_timestamp('20201031_2330','yyyyMMdd_HHmm')4106 rows
Query A1
SELECT DISTINCT * FROM bus WHERE datetime = to_timestamp('20201031_2330','yyyyMMdd_HHmm') AND
(busId IN (SELECT "from" FROM branch WHERE datetime = to_timestamp('20201031_2330','yyyyMMdd_HHmm') AND logical) OR
busId IN (SELECT "to" FROM branch WHERE datetime = to_timestamp('20201031_2330','yyyyMMdd_HHmm') AND logical))Query A2
SELECT * FROM bus WHERE datetime = to_timestamp('20201031_2330','yyyyMMdd_HHmm') AND
(busId IN (SELECT "from" FROM branch WHERE datetime = to_timestamp('20201031_2330','yyyyMMdd_HHmm') AND logical) OR
busId IN (SELECT "to" FROM branch WHERE datetime = to_timestamp('20201031_2330','yyyyMMdd_HHmm') AND logical))Query B1
SELECT * FROM bus INNER JOIN ((SELECT datetime,"from" as busId FROM branch WHERE logical) UNION (SELECT datetime,"to" as busId FROM branch WHERE logical)) u ON bus.busId = u.busId AND bus.datetime = u.datetime WHERE bus.datetime = to_timestamp('20201031_2330','yyyyMMdd_HHmm')Query B2
SELECT * FROM bus INNER JOIN ((SELECT DISTINCT datetime,"from" as busId FROM branch WHERE logical) UNION (SELECT DISTINCT datetime,"to" as busId FROM branch WHERE logical)) u ON bus.busId = u.busId AND bus.datetime = u.datetime WHERE bus.datetime = to_timestamp('20201031_2330','yyyyMMdd_HHmm')Query B3
SELECT DISTINCT * FROM bus INNER JOIN ((SELECT datetime,"from" as busId FROM branch WHERE logical) UNION (SELECT datetime,"to" as busId FROM branch WHERE logical)) u ON bus.busId = u.busId AND bus.datetime = u.datetime WHERE bus.datetime = to_timestamp('20201031_2330','yyyyMMdd_HHmm')QuestDB 6.3
Query A1: 862 rows in 9.78s
Query A2: 862 rows in 10.01s
Query B1: 863 rows in 29ms
Query B2: 862 rows in 79ms
Query B3: 862 rows in 83ms
QuestDB 6.4
Query A1: 1818 rows in 14.09s
Query A2: 862 rows in 13.92s
Query B1: 863 rows in 89ms
Query B2: 862 rows in 138ms
Query B3: 862 rows in 69ms