Inconsistent query results #9863
Description
What happens?
I have a table of values and a simple SELECT statement, which does not appear to have any dynamic aspects to it. However, running the SELECT repeatedly yields multiple results. The query in question is:
SELECT CAST('2020-02-20' AS date) - CAST(min("ta_1"."lo_commitdate") AS date) AS "ca_1"
FROM "tpch5k_falcon_default_schema_lineorder" AS "ta_1"
HAVING CAST('2020-02-20' AS date) - CAST(min("ta_1"."lo_commitdate") AS date) > 4
ORDER BY "ca_1" ASC;When I run this repeatedly via the following:
CREATE TABLE tpch5k_falcon_default_schema_lineorder AS SELECT * FROM 'tpch5k_falcon_default_schema_lineorder.csv';
SELECT CAST('2020-02-20' AS date) - CAST(min("ta_1"."lo_commitdate") AS date) AS "ca_1"
FROM "tpch5k_falcon_default_schema_lineorder" AS "ta_1"
HAVING CAST('2020-02-20' AS date) - CAST(min("ta_1"."lo_commitdate") AS date) > 4
ORDER BY "ca_1" ASC;
SELECT CAST('2020-02-20' AS date) - CAST(min("ta_1"."lo_commitdate") AS date) AS "ca_1"
FROM "tpch5k_falcon_default_schema_lineorder" AS "ta_1"
HAVING CAST('2020-02-20' AS date) - CAST(min("ta_1"."lo_commitdate") AS date) > 4
ORDER BY "ca_1" ASC;
SELECT CAST('2020-02-20' AS date) - CAST(min("ta_1"."lo_commitdate") AS date) AS "ca_1"
FROM "tpch5k_falcon_default_schema_lineorder" AS "ta_1"
HAVING CAST('2020-02-20' AS date) - CAST(min("ta_1"."lo_commitdate") AS date) > 4
ORDER BY "ca_1" ASC;
SELECT CAST('2020-02-20' AS date) - CAST(min("ta_1"."lo_commitdate") AS date) AS "ca_1"
FROM "tpch5k_falcon_default_schema_lineorder" AS "ta_1"
HAVING CAST('2020-02-20' AS date) - CAST(min("ta_1"."lo_commitdate") AS date) > 4
ORDER BY "ca_1" ASC;
SELECT CAST('2020-02-20' AS date) - CAST(min("ta_1"."lo_commitdate") AS date) AS "ca_1"
FROM "tpch5k_falcon_default_schema_lineorder" AS "ta_1"
HAVING CAST('2020-02-20' AS date) - CAST(min("ta_1"."lo_commitdate") AS date) > 4
ORDER BY "ca_1" ASC;I get the following results:
┌───────┐
│ ca_1 │
│ int64 │
├───────┤
│ 10237 │
└───────┘
┌────────┐
│ ca_1 │
│ int64 │
├────────┤
│ 0 rows │
└────────┘
┌────────┐
│ ca_1 │
│ int64 │
├────────┤
│ 0 rows │
└────────┘
┌────────┐
│ ca_1 │
│ int64 │
├────────┤
│ 0 rows │
└────────┘
┌────────┐
│ ca_1 │
│ int64 │
├────────┤
│ 0 rows │
└────────┘
As you can see, sometimes the result is 10237 and sometimes the result is zero rows returned.
To Reproduce
I am attaching the SQL (test.sql) and the input data (tpch5k_falcon_default_schema_lineorder.csv) as a ZIP file:
Unzip the file and run the supplied SQL through DuckDB:
unzip Archive.zip
cat test.sql | duckdb
You should see that sometimes the query returns 1 row and sometimes it returns no rows.
OS:
macOS aarch64
DuckDB Version:
0.9.2
DuckDB Client:
CLI, JDBC
Full Name:
Ryan Kennedy
Affiliation:
Mode Analytics/ThoughtSpot
Have you tried this on the latest main branch?
I have tested with a release build (and could not test with a main build)
Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
- Yes, I have