[Bug] Wrong result when `right outer join and where false`

[ChaseHuangxu]

Search before asking

• I had searched in the issues 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.

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');

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

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.

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.

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');

Anything Else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct