Potential Issue for Estimated Rows

[bajinsheng]

Describe the problem

The estimated row count of the first SELECT statement may be far from real number.

To Reproduce

CREATE TABLE t0 (c0 INT);
CREATE TABLE t1 (c0 INT);
INSERT INTO t0 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13);
INSERT INTO t1 VALUES (21),(22),(23),(24),(25);  
ANALYZE t0;
ANALYZE t1;

EXPLAIN SELECT * FROM t0 LEFT OUTER JOIN t1 ON t0.c0<1 OR t0.c0>1; -- estimated row count: 20
EXPLAIN SELECT * FROM t0 LEFT OUTER JOIN t1 ON t0.c0!=1; -- estimated row count: 60
EXPLAIN SELECT * FROM t0 INNER JOIN t1 ON t0.c0<1 OR t0.c0>1; -- estimated row count: 60

Expected behavior
The first and second SELECT statements are semantic equivalents, so I think both statements should have the same number of estimated rows? They don't in fact.

As a reference, I also rewrite LEFT JOIN to INNER JOIN and get the third SELECT statement. In my understanding, the INNER JOIN should have no greater estimated row count than LEFT JOIN.

Therefore, I suspect there is an issue for the estimated rows of the first SELECT statement.

Environment:

• CockroachDB version [7cde315]
• Server OS: [Ubuntu 20.04]
• Client app [cockroach sql]

Additional context
Although the estimated row count is an approximate number, I think this may be a potential issue which may impact the query optimization. I hope it would provide valuable information. Looking forward your reply!

Jira issue: CRDB-19831

[blathers-crl]

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.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[DrewKimball]

Thanks for opening this issue! Looks like we were doing some double-counting when estimating the selectivity of OR expressions in join ON conditions.

[bajinsheng]

Thanks for your prompt reply! I'm glad we can help CockroachDB get better!