Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.14, 10.2.7
-
10.2.10
Description
Basically, MariaDB is affected by the upstream https://bugs.mysql.com/bug.php?id=86930, but our case is even worse, as a workaround with subquery does not work:
openxs@ao756:~/dbs/maria10.2$ bin/mysql --host=127.0.0.1 --port=3308 -uroot testReading 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 MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 8
|
Server version: 10.2.7-MariaDB Source distribution
|
|
|
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [test]> create table tt1(c1 int);
|
Query OK, 0 rows affected (0.31 sec)
|
|
|
MariaDB [test]> create table tt2(c2 int);
|
Query OK, 0 rows affected (0.22 sec)
|
|
|
MariaDB [test]> 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.04 sec)
|
|
|
MariaDB [test]> 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)
|
|
|
MariaDB [test]> 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.01 sec)
|
|
|
MariaDB [test]> 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(*) |
|
+----------+
|
| 0 |
|
+----------+
|
1 row in set (0.02 sec)
|
|
|
MariaDB [test]> 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)
|
Attachments
Issue Links
Activity
| Field | Original Value | New Value |
|---|---|---|
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Fix Version/s | 10.1 [ 16100 ] |
| Fix Version/s | 10.2 [ 14601 ] |
| Comment | [ A comment with security level 'Developers' was removed. ] |
| Remote Link | This issue links to "Bug #86930 Wrong results for queries with row constructors and information_schema (Web Link)" [ 28049 ] |
| Assignee | Alexander Barkov [ bar ] |
| Sprint | 10.2.10 [ 183 ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| issue.field.resolutiondate | 2017-10-05 09:42:52.0 | 2017-10-05 09:42:52.237 |
| Fix Version/s | 10.1.29 [ 22636 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 81552 ] | MariaDB v4 [ 152434 ] |
| Zendesk Related Tickets | 123469 |
If I remove the table_schema='test' part from the condition:
It still returns empty set.
If I rewrite the query as follows:
it correctly returns this result:
+------------+-------------+----+| table_name | column_name | IN |+------------+-------------+----+| tt1 | c1 | 1 || tt2 | c2 | 1 |+------------+-------------+----+If I copy the data into a "normal" table, it works as expected:
+----------+| count(*) |+----------+| 2 |+----------+and this query returns two rows: