Description:
When using the ELT function in a WHERE clause, different behavior is observed depending on whether the function is used directly in a SELECT query or within a table created via CREATE TABLE AS SELECT.
How to repeat:
1. Create table t0 and insert a record
mysql> CREATE TABLE t0 (c0 BIT, c1 TIME);
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO t0 VALUES (1, '09:33:53');
Query OK, 1 row affected (0.01 sec)
2. Query with ELT in the SELECT statement and WHERE clause
mysql> SELECT (ELT(t0.c0, t0.c1)) FROM t0 WHERE (ELT(t0.c0, t0.c1)) != 9;
Field 1: `(ELT(t0.c0, t0.c1))`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: latin1_swedish_ci (8)
Length: 10
Max_length: 8
Decimals: 31
Flags:
+---------------------+
| (ELT(t0.c0, t0.c1)) |
+---------------------+
| 09:33:53 |
+---------------------+
1 row in set (0.00 sec)
3. Create table t1 using CREATE TABLE AS SELECT with ELT and then query t1
mysql> CREATE TABLE t1 AS (SELECT (ELT(t0.c0, t0.c1)) AS c0 FROM t0);
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT t1.c0 FROM t1 WHERE t1.c0 != 9;
Field 1: `c0`
Catalog: `def`
Database: `test`
Table: `t1`
Org_table: `t1`
Type: VAR_STRING
Collation: latin1_swedish_ci (8)
Length: 10
Max_length: 0
Decimals: 0
Flags:
0 rows in set, 1 warning (0.00 sec)
Expected Result: The SELECT results from t0 and t1 should be identical, as they are expected to have the same types and values. However, the result is the opposite: one SELECT query returns 1 row, while the other returns no values at all.
