Unexpected results on specific values

[DerZc]

What happens?

Consider the following program:

CREATE TABLE t0(c0 SMALLINT);

INSERT INTO t0(c0) VALUES ((0.8486559842619935));
INSERT INTO t0(c0) VALUES ((false));
INSERT INTO t0(c0) VALUES ((-1));
INSERT INTO t0(c0) VALUES (('3'));
INSERT INTO t0(c0) VALUES (('8'));
INSERT INTO t0(c0) VALUES (('	4'));

CREATE TABLE temp_table2 (c0 SMALLINT);
INSERT INTO temp_table2 SELECT (t0.c0) AS c0 FROM t0 WHERE (((20))!~((t0.c0 IN (SELECT (t0.c0) AS c0 FROM t0 ))));
SELECT (t0.c0) AS c0, (temp_table2.c0) AS c1 FROM t0, temp_table2 WHERE ((((+ temp_table2.c0)) ::VARCHAR) BETWEEN t0.c0 AND t0.c0);
DROP TABLE temp_table2;

WITH temp_table2 AS(SELECT (t0.c0) AS c0 FROM t0 WHERE (((20))!~((t0.c0 IN (SELECT (t0.c0) AS c0 FROM t0 ))))) SELECT (t0.c0) AS c0, (temp_table2.c0) AS c1 FROM t0, temp_table2 WHERE ((((+ temp_table2.c0)) ::VARCHAR) BETWEEN t0.c0 AND t0.c0);

These two query should be equivalent as the first one create table and insert values with a subquery, the second create a temporary from a subquery. But they have different results:

┌───────┬───────┐
│  c0   │  c1   │
│ int16 │ int16 │
├───────┼───────┤
│     8 │     8 │
│     4 │     4 │
│     3 │     3 │
│     1 │     1 │
│     0 │     0 │
│    -1 │    -1 │
└───────┴───────┘
┌───────┬───────┐
│  c0   │  c1   │
│ int16 │ int16 │
├───────────────┤
│    0 rows     │
└───────────────┘

If I remove any value of t0, this bug will can not reproduce.

To Reproduce

I build DuckDB from the last commit version 5f6c51b

OS:

ubuntu 22.04

DuckDB Version:

5f6c51b

DuckDB Client:

CLI

Full Name:

Chi Zhang

Affiliation:

Nanjing University, National University of Singapore

Have you tried this on the latest master branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[hawkfish]

@DerZc I believe this was an issue with the pipeline scheduling for IEJoin that was resolved in 0.8.1. It no longer reproduces for me. Can you check?

[DerZc]

Hi @hawkfish I can confirm this can not reproduce with the latest commit version 5c94293. Thanks for your confirmation! I close this issue.