ChaseHuangxu opened a new issue, #19613: URL: https://github.com/apache/doris/issues/19613
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version Apache Doris 1.2.2 RELEASE ### What's Wrong? I'm trying to add Apache Doris support to SQLancer, this may be a logic bug found by @[SQLancer](https://github.com/sqlancer/sqlancer). Two opposing SQLs achieve the same result. ``` SELECT * FROM t15 RIGHT OUTER JOIN t0 ON (('') like ('15DScmSM')) WHERE ('abc' LIKE 'abc'); SELECT * FROM t15 RIGHT OUTER JOIN t0 ON (('') like ('15DScmSM')) WHERE ('abc' NOT LIKE 'abc'); ``` <img width="753" alt="image" src="https://github.com/apache/doris/assets/91189188/2ad8efe3-5f5d-4616-8450-b8e4ae9ba7c6";> ### What You Expected? The SQL `SELECT * FROM t15 RIGHT OUTER JOIN t0 ON (('') like ('15DScmSM')) WHERE ('abc' NOT LIKE 'abc');` should obtain empty set as result. ### How to Reproduce? You can reproduce this error from the following steps. 1. data prepare ``` DROP DATABASE IF EXISTS doris34; CREATE DATABASE doris34; USE doris34; SET enable_nereids_planner=true; CREATE TABLE t0(c0 DECIMALV3(8,3)) DISTRIBUTED BY HASH (c0) BUCKETS 1 PROPERTIES ("replication_num" = "1"); CREATE TABLE t1(c0 SMALLINT NOT NULL DEFAULT -1833256236) DISTRIBUTED BY HASH (c0) BUCKETS 24 PROPERTIES ("replication_num" = "1"); CREATE TABLE t15(c0 CHAR(249)) AGGREGATE KEY(c0) DISTRIBUTED BY RANDOM BUCKETS 30 PROPERTIES ("replication_num" = "1"); INSERT INTO t15 (c0) VALUES ('dr'), ('x7Tq'), (''); INSERT INTO t0 (c0) VALUES (0.47683432698249817), (0.8864791393280029); INSERT INTO t0 (c0) VALUES (0.11287713050842285); INSERT INTO t15 (c0) VALUES (''); INSERT INTO t15 (c0) VALUES (''); INSERT INTO t15 (c0) VALUES ('hb'); ``` 2. check data of table t15 and t0 <img width="206" alt="image" src="https://github.com/apache/doris/assets/91189188/e525add4-903f-4696-b8dd-85379485bb95";> 3. execute `SELECT * FROM t15 RIGHT OUTER JOIN t0 ON (('') like ('15DScmSM')) WHERE ('abc' LIKE 'abc');`, this sql should obtain 3 reocrds, and the real result is correct. <img width="712" alt="image" src="https://github.com/apache/doris/assets/91189188/9455500d-80ea-4f06-8fa1-8962820408f5";> 4. Change where condition to NOT and execute, `SELECT * FROM t15 RIGHT OUTER JOIN t0 ON (('') like ('15DScmSM')) WHERE ('abc' NOT LIKE 'abc');`, this sql should return empty set because where condition is always false, but obtain 3 reocrds in actually. <img width="744" alt="image" src="https://github.com/apache/doris/assets/91189188/4dee5f14-a857-4e07-b531-4ff9d13a2b1f";> 5. Change join type t0 left outer join or natural join and execute, they get the same and correct results (empty set). ``` SELECT * FROM t15 JOIN t0 ON (('') like ('15DScmSM')) WHERE ('abc' NOT LIKE 'abc'); SELECT * FROM t15 LEFT OUTER JOIN t0 ON (('') like ('15DScmSM')) WHERE ('abc' NOT LIKE 'abc'); ``` <img width="741" alt="image" src="https://github.com/apache/doris/assets/91189188/01a793c5-d751-4f1c-98e0-17722995a3f0";> ### Anything Else? _No response_ ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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: commits-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
