You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "2010YOUY01 (via GitHub)" <gi...@apache.org> on 2024/07/29 11:10:12 UTC
[I] Incorrect result returned by a NLJ query with filter (SQLancer-NoREC) [datafusion]
2010YOUY01 opened a new issue, #11704:
URL: https://github.com/apache/datafusion/issues/11704
### Describe the bug
The following query should return an empty set (verified in postgres and DuckDB), but in DataFusion it returns 1 row as output.
Reproducer in datafusion-cli
```
/*DML*/CREATE TABLE t1(v0 BIGINT, v1 STRING);
/*DML*/CREATE TABLE t2(v0 BOOLEAN, v1 BIGINT);
/*DML*/CREATE TABLE t3(v0 STRING, v1 BOOLEAN);
/*DML*/INSERT INTO t1(v1) VALUES ('1381560824');
/*DML*/INSERT INTO t2(v1) VALUES (1381560824);
/*DML*/INSERT INTO t2(v0) VALUES (true);
/*DML*/INSERT INTO t3(v1) VALUES (false);
```
```
> SELECT * FROM t1 CROSS JOIN t2 LEFT JOIN t3 ON ((t1.v0) == (t2.v1)) WHERE ((t3.v1) < (t2.v0));
+----+------------+------+----+----+-------+
| v0 | v1 | v0 | v1 | v0 | v1 |
+----+------------+------+----+----+-------+
| | 1381560824 | true | | | false |
+----+------------+------+----+----+-------+
```
More details: If this query is run without filter, the result will be
```
> SELECT * FROM t1 CROSS JOIN t2 LEFT JOIN t3 ON ((t1.v0) == (t2.v1));
+----+------------+------+------------+----+----+
| v0 | v1 | v0 | v1 | v0 | v1 |
+----+------------+------+------------+----+----+
| | 1381560824 | | 1381560824 | | |
| | 1381560824 | true | | | |
+----+------------+------+------------+----+----+
```
the removed filter is `((t3.v1) < (t2.v0))`, notice `t3.v1` is all `NULL` here, so I guess somewhere during execution, `NULL` < `NULL` is incorrectly evaluated to `true`
### To Reproduce
_No response_
### Expected behavior
_No response_
### Additional context
Found by SQLancer(https://github.com/apache/datafusion/issues/11030)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Incorrect result returned by a NLJ query with filter (SQLancer-NoREC) [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #11704: Incorrect result returned by a NLJ query with filter (SQLancer-NoREC)
URL: https://github.com/apache/datafusion/issues/11704
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Incorrect result returned by a NLJ query with filter (SQLancer-NoREC) [datafusion]
Posted by "jonahgao (via GitHub)" <gi...@apache.org>.
jonahgao commented on issue #11704:
URL: https://github.com/apache/datafusion/issues/11704#issuecomment-2256083730
👍
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org