Bug #83006 Incorrect processing of WHERE condition with ELT

• Submitted: 15 Sep 2016 7:51
• Modified: 15 Sep 2016 8:36
• Reporter: Grigory Rubtsov
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.6.24
• OS: Any
• CPU Architecture: Any

[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] MySQL Verification Team

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