Bug #83006 Incorrect processing of WHERE condition with ELT
Submitted: 15 Sep 2016 7:51 Modified: 15 Sep 2016 8:36
Reporter: Grigory Rubtsov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.24 OS:Any
Assigned to:

[15 Sep 2016 7:51] Grigory Rubtsov
Description:
Some queries with JOIN and ELT string function in WHERE part return incorrect result.

How to repeat:
CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
SELECT t1.id,t2.id FROM t1 LEFT JOIN t2 USING(id) WHERE  ELT(1, t1.id, t2.id) = 1\G
        Empty set (0.00 sec)
        This result is incorrect. If one removes reference to t2.id in ELT the query shows correct result:

SELECT t1.id,t2.id FROM t1 LEFT JOIN t2 USING(id) WHERE  ELT(1, t1.id, NULL) = 1\G
*************************** 1. row ***************************
id: 1
id: NULL
1 row in set (0.00 sec)
[15 Sep 2016 8:36] Umesh Shastry
Hello Grigory,

Thank you for the report and test case.
Observed this with 5.5.52, 5.6.33 and 5.7.15 builds.

Thanks,
Umesh