Description:
It seems row constructors are evaluated somehow wrong for the queries to information_schema.columns table:
openxs@ao756:~/dbs/5.7$ bin/mysql --host=127.0.0.1 --port=3308 -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create table tt1(c1 int);
Query OK, 0 rows affected (0,27 sec)
mysql> create table tt2(c2 int);
Query OK, 0 rows affected (0,25 sec)
mysql> select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt1', 'c1'));
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0,00 sec)
mysql> select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt2', 'c2'));
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0,00 sec)
mysql> select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt1','c1'),('tt2', 'c2'));
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0,00 sec)
If we rewrite the query using subquery it works as expected:
mysql> select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (select 'tt1','c1' from dual union select 'tt2', 'c2' from dual);
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0,02 sec)
If we rewrite it to the equivalent one based on the manual (https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#row-constructor-range-opti...) it also works as expected:
mysql> select count(*) from information_schema.columns where table_schema='test' and (table_name='tt1' and column_name='c1') or (table_name='tt2' and column_name='c2');
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0,00 sec)
This is not the case for a normal table where IN expression with row constructors work as expected:
mysql> create table ttt(c1 varchar(10), c2 varchar(10));
Query OK, 0 rows affected (0,55 sec)
mysql> insert into ttt values ('a','b'), ('c', 'd');
Query OK, 2 rows affected (0,33 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select count(*) from ttt where (c1,c2) in (('a','b'),('c','d'));
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0,00 sec)
How to repeat:
use test;
create table tt1(c1 int);
create table tt2(c2 int);
select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt1', 'c1'));
select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt2', 'c2'));
-- compare these results with the above
select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt1','c1'),('tt2', 'c2'));
-- and the following results from equivalent queries
select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (select 'tt1','c1' from dual union select 'tt2', 'c2' from dual);
select count(*) from information_schema.columns where table_schema='test' and (table_name='tt1' and column_name='c1') or (table_name='tt2' and column_name='c2');
Suggested fix:
Process queries with row constructors properly even in the information_schema.
Description: It seems row constructors are evaluated somehow wrong for the queries to information_schema.columns table: openxs@ao756:~/dbs/5.7$ bin/mysql --host=127.0.0.1 --port=3308 -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.18 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create table tt1(c1 int); Query OK, 0 rows affected (0,27 sec) mysql> create table tt2(c2 int); Query OK, 0 rows affected (0,25 sec) mysql> select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt1', 'c1')); +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0,00 sec) mysql> select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt2', 'c2')); +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0,00 sec) mysql> select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt1','c1'),('tt2', 'c2')); +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0,00 sec) If we rewrite the query using subquery it works as expected: mysql> select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (select 'tt1','c1' from dual union select 'tt2', 'c2' from dual); +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0,02 sec) If we rewrite it to the equivalent one based on the manual (https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#row-constructor-range-opti...) it also works as expected: mysql> select count(*) from information_schema.columns where table_schema='test' and (table_name='tt1' and column_name='c1') or (table_name='tt2' and column_name='c2'); +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0,00 sec) This is not the case for a normal table where IN expression with row constructors work as expected: mysql> create table ttt(c1 varchar(10), c2 varchar(10)); Query OK, 0 rows affected (0,55 sec) mysql> insert into ttt values ('a','b'), ('c', 'd'); Query OK, 2 rows affected (0,33 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select count(*) from ttt where (c1,c2) in (('a','b'),('c','d')); +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0,00 sec) How to repeat: use test; create table tt1(c1 int); create table tt2(c2 int); select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt1', 'c1')); select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt2', 'c2')); -- compare these results with the above select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt1','c1'),('tt2', 'c2')); -- and the following results from equivalent queries select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (select 'tt1','c1' from dual union select 'tt2', 'c2' from dual); select count(*) from information_schema.columns where table_schema='test' and (table_name='tt1' and column_name='c1') or (table_name='tt2' and column_name='c2'); Suggested fix: Process queries with row constructors properly even in the information_schema.