sql: inconsistent estimated rows with JOINs

[malwaregarry]

Describe the problem

RIGHT / LEFT OUTER JOIN should give us a larger table compared to INNER JOIN given the same ON condition.

However, the explain queries below for RIGHT OUTER JOIN returns an estimate lower than INNER JOIN

To Reproduce

CREATE TABLE t0 (c0 FLOAT);
CREATE TABLE t2 (c0 INT);
INSERT INTO t0 (c0) VALUES(0.1), (0.2);
INSERT INTO t2 (c0) VALUES(1), (2), (3), (4);
ANALYZE t0;
ANALYZE t2;
EXPLAIN SELECT DISTINCT t2.c0 FROM t2 RIGHT OUTER JOIN t0 ON (0.1 < SOME (t0.c0, 0.4)); --2
EXPLAIN SELECT DISTINCT t2.c0 FROM t2 INNER JOIN t0 ON (0.1 < SOME (t0.c0, 0.4)); --3

CREATE TABLE t0 (c0 INT);
CREATE TABLE t1 (c2 INT);
INSERT INTO t0 (c0) VALUES(1), (2);
INSERT INTO t1 (c2) VALUES(1), (null), (null);
ANALYZE t0;
ANALYZE t1;
EXPLAIN SELECT DISTINCT t0.c0 FROM t0 RIGHT OUTER JOIN t1 ON ((t1.c2) ::BOOL); --1
EXPLAIN SELECT DISTINCT t0.c0 FROM t0 INNER JOIN t1 ON ((t1.c2) ::BOOL); --2

What did you do? Describe in your own words.

1. Set up CockroachDB cluster
2. Send SQL
3. Compare estimated rows in explain query

Expected behavior The second query should return an estimate lower than the first query

Additional data / screenshots

Environment:

• CockroachDB version: 24.2.3
• Server OS: Linux Arch
• Client app cockroach sql

Additional context Test case generated with SQLancer

Jira issue: CRDB-42639

[blathers-crl[bot]]

Hi @malwaregarry, 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 was unable to automatically find someone to ping.

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.}

[DrewKimball]

Hi @malwaregarry, similar to #131875, I'll close this one out since it isn't considered a bug. However, it does suggest another opportunity for a normalization rule, tracked here: #132328.