Skip to content

Unexpected results when using CASE WHEN with RIGHT JOIN #7993

@suyZhong

Description

@suyZhong

Considering the test case below.

CREATE TABLE t0(c0 BOOLEAN);
CREATE TABLE t1(c1 BOOLEAN);
INSERT INTO t0 (c0) VALUES (true);
INSERT INTO t1 (c1) VALUES (false);

SELECT * FROM t1 RIGHT  JOIN t0 ON t0.c0; -- false true
SELECT * FROM t1 RIGHT  JOIN t0 ON t0.c0 WHERE (CASE t1.c1 WHEN t1.c1 THEN NULL ELSE true END ); -- null true (Unexpected)

SELECT (CASE t1.c1 WHEN t1.c1 THEN NULL ELSE true END ) FROM t1 RIGHT  JOIN t0 ON t0.c0; -- null

The second SELECT returns an unexpected result: if the result of the CASE expression is NULL, the value of the WHERE clause in the second should be NULL, and thus the second query should return empty result. Additionally, the output of the second query, containing a WHERE condition, must not include rows that differ from those returned by the first query.

I found this in version LI-T6.0.0.247 where I built from source code da67351

Besides, I have one question considering an error: Too many concurrent executions of the same request [SQLState:54001, ISC error code:335544663]. Does this mean that we couldn't send many same queries (e.g. SELECT * FROM t0) to the server at the same time?

Metadata

Metadata