Unexpected result on two JOIN

[DerZc]

Bug Report

1. Minimal reproduce step (Required)

Consider the following program:

USE test;
DROP DATABASE IF EXISTS database10;
CREATE DATABASE database10;
USE database10;
CREATE TABLE t0(c0 INTEGER);
INSERT INTO t0 VALUES (1);
CREATE VIEW v0(c0) AS SELECT t0.c0 FROM t0;

SELECT v0.c0 AS c0 FROM  v0 NATURAL RIGHT JOIN t0;  -- 1

SELECT v0.c0 AS c0, t0.c0 AS c1 FROM  t0 LEFT  OUTER JOIN v0 ON ((NULL) IS NOT NULL) WHERE ((t0.c0)<=(1));  -- NULL | 1

SELECT v0.c0 AS c0, t0.c0 AS c1 FROM  t0 LEFT  OUTER JOIN v0 ON ((NULL) IS NOT NULL) WHERE ((t0.c0)<=(SELECT v0.c0 AS c0 FROM  v0 NATURAL RIGHT JOIN t0));  -- empty

2. What did you expect to see? (Required)

The second SELECT is equivalent to the third SELECT query, but the third SELECT query should have non-empty result. I run this query with MySQL, it generates non-empty results. https://www.db-fiddle.com/f/ghC6nTdVaf5tBnCa9vFYC4/0
If I remove one of the JOIN in the third SELECT query, it will have correct results.

3. What did you see instead (Required)

The third SELECT query generate empty results.

4. What is your TiDB version? (Required)

| Release Version: v7.1.0-alpha-162-gc233969b2
Edition: Community
Git Commit Hash: c233969b2c385b4292a7caaf16517dd8b152d7f1
Git Branch: master
UTC Build Time: 2023-04-11 03:01:14
GoVersion: go1.20.3
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore |

[hawkingrei]

@DerZc Do you use sqlancer? I think you are using your tools.

[DerZc]

@hawkingrei I am implementing a new oracle based on sqlancer

[zanmato1984]

Changing

SELECT v0.c0 AS c0, t0.c0 AS c1 FROM  t0 LEFT  OUTER JOIN v0 ON ((NULL) IS NOT NULL) WHERE ((t0.c0)<=(SELECT v0.c0 AS c0 FROM  v0 NATURAL RIGHT JOIN t0));

to

SELECT v0.c0 AS c0, t0.c0 AS c1 FROM  t0 LEFT  OUTER JOIN v0 ON ((NULL) IS NOT NULL) WHERE ((t0.c0)<=(SELECT vv.c0 AS c0 FROM  v0 vv NATURAL RIGHT JOIN t0 tt));

will get the correct result.

Similar to #42912, issue in de-correlation.

[DerZc]

Hi @zanmato1984 this bug has been fixed now and I can confirm it is a duplicate bug. So I close it. Thank you very much!

[hawkingrei]

@hawkingrei I am implementing a new oracle based on sqlancer

It is an amazing news.