Unexpected result when comparing `IN` expression and integer

[suyZhong]

Please make sure that the version you're using is still supported (you can find the list here).

You have to provide the following information whenever possible.

Company or project name

National University of Singapore

Describe what's wrong

Consider the following test cases. The third query should return one row in the table after join, since the second query shows that the WHERE condition is evaluated to 1, and thus the row should not be filtered.

DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t1;

CREATE TABLE t0(c0 INTEGER, PRIMARY KEY(c0)) ENGINE=MergeTree();
CREATE TABLE t1(c0 INTEGER, PRIMARY KEY(c0)) ENGINE=MergeTree();
INSERT INTO t0(c0) VALUES (1);
INSERT INTO t1(c0) VALUES (0);

SELECT * FROM t1, t0; -- 0 1
SELECT ((t0.c0 NOT IN (true))=(t1.c0/-1)) FROM t1, t0; -- 1
SELECT * FROM t1, t0 WHERE ((t0.c0 NOT IN (true))=(t1.c0/-1));
-- Expected: 0 1
-- Actual: Empty set

https://fiddle.clickhouse.com/05bf430f-4059-46fc-9f63-fce93f2b022d

Does it reproduce on the most recent release?

yes

Expected behavior

The third query returns the same as the first query.

Additional context

Add any other context about the problem here.

[SaltTan]

workaround set cross_to_inner_join_rewrite=0;
https://fiddle.clickhouse.com/c8a73ae1-ff38-47bb-9e85-a63890c74c32

[chloro-pn]

I guess that after applying the cross_to_inner_join_rewrite rule, the where statement is placed in the join statement, and t1.c0/-1 generates -0. Look ： #50192

[alexey-milovidov]

Tested on current master (ed6302e) with clickhouse-local. The issue is still reproducible — the expression evaluates to 1 (true) but the WHERE clause with the same expression returns no rows:

SELECT ((t0.c0 NOT IN (true))=(t1.c0/-1)) FROM t1, t0;
-- Returns: 1

SELECT * FROM t1, t0 WHERE ((t0.c0 NOT IN (true))=(t1.c0/-1));
-- Returns: empty (expected: 0  1)