Inconsistent query plan for a complex join query

[sayJason]

Describe the problem

I obtain different query plans for the same query and obtain two query plans, which cause different query results.

To Reproduce

CREATE TABLE t0 ( c1 VARCHAR NULL, INDEX i2 (c1 ASC));
INSERT INTO t0 VALUES ('z'), (null), ('D'), ('gfovpxhvnP');
(SELECT c1 AS ca1, c1 AS ca2, c1 AS ca3 FROM t0) INTERSECT ALL (SELECT DISTINCT ca8 AS ca4, ca9 AS ca5, ca12 AS ca6 FROM (SELECT c1 AS ca7, c1 AS ca8, c1 AS ca9 FROM t0) AS ta1 LEFT OUTER JOIN (SELECT ca14 AS ca10, ca15 AS ca11, ca15 AS ca12 FROM (SELECT c1 AS ca13, c1 AS ca14, c1 AS ca15 FROM t0) AS ta2) AS ta3 ON TRUE);

For the first query plan, four rows are returned.

intersect-all
 ├── scan t0
 └── distinct-on
      └── left-join (cross)
           ├── scan t0 [as=ta1]
           ├── scan t0 [as=ta3]
           └── filters (true)

For the second query plan, only one row is returned.

intersect-all
 ├── scan t0@i2
 └── sort
      └── distinct-on
           └── left-join (cross)
                ├── scan t0 [as=ta1]
                ├── scan t0 [as=ta3]
                └── filters (true)

Expected behavior I think these two query plans should return the same query result.

Environment: I download the official binary with version of CCL v24.2.3 @ 2024/09/23 22:30:53 (go1.22.5 X:nocoverageredesign)

Jira issue: CRDB-42975

[blathers-crl[bot]]

Hi @sayJason, please add branch-* labels to identify which branch(es) this C-bug affects.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-queries (found keywords: plan)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[sayJason]

I attach the original bug report, which may help understand this bug. crdb-132323-original.log

[DrewKimball]

Hi @sayJason, thanks for the report. Can you explain how you produced the second query plan?

[sayJason]

Hi @DrewKimball, I have devised a novel test method which is about to be published. The basic idea is that enforce different execution plans for the same query. Instead of adding query hints that are not commonly available, we combine different SQL statements to construct different metadata, e.g., different data statistics or different index usage, for the same query. If the same query returns different query results, our tool will output a bug report.