Bug #74331 Tables_in_<schema> broken

• Submitted: 11 Oct 2014 11:40
• Modified: 5 Jul 2016 8:44
• Reporter: Daniël van Eeden (OCA)
• Status: Closed
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.7.5-labs-dd
• OS: Any
• CPU Architecture: Any
• Tags: data dictionary, workbench

[11 Oct 2014 11:40] Daniël van Eeden

Description:
MySQL Workbench 6.2 uses this query when creating a new table:
SHOW FULL TABLES FROM `geo` WHERE `Tables_in_geo` = 'foo' AND Table_type != 'VIEW'

This doesn't work anymore in 5.7.5-labs-dd but does work in 5.7.5-m15

How to repeat:
CREATE SCHEMA geo;
SHOW FULL TABLES FROM `geo` WHERE `Tables_in_geo` = 'foo'

[11 Oct 2014 11:53] Daniël van Eeden

5.7.5-labs-dd:
mysql> SHOW FULL TABLES FROM `geo` WHERE Tables_in_geo='geo1';
ERROR 1054 (42S22): Unknown column 'Tables_in_geo' in 'where clause'

5.7.5-m15:
mysql> SHOW FULL TABLES FROM `geo` WHERE `Tables_in_geo` = 'geo1';
Empty set (0.00 sec)

[11 Oct 2014 15:39] Daniël van Eeden

test case

Attachment: bug74331.test (application/octet-stream, text), 115 bytes.

[11 Oct 2014 15:39] Daniël van Eeden

test case (result file)

Attachment: bug74331.result (application/octet-stream, text), 145 bytes.

[13 Oct 2014 5:02] MySQL Verification Team

Hello Daniël,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh

[13 Oct 2014 5:04] MySQL Verification Team

///// 5.7.5-labs-dd-log

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.5                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.7.5-labs-dd-log            |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-el6                    |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql> CREATE SCHEMA bug74331;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW FULL TABLES FROM `bug74331` WHERE `Tables_in_bug74331` = 'foo';
ERROR 1054 (42S22): Unknown column 'Tables_in_bug74331' in 'where clause'
mysql> DROP SCHEMA bug74331;
Query OK, 0 rows affected (0.00 sec)

/ 5.7.6

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.7.6                                                   |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.7.6-m16-enterprise-commercial-advanced-log            |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> CREATE SCHEMA geo;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW FULL TABLES FROM `geo` WHERE `Tables_in_geo` = 'foo';
Empty set (0.00 sec)

[5 Jul 2016 8:44] Erlend Dahl

Fixed in the upcoming 8.0.0 release.