| Bug #101390 | Left Join on information_schema.columns doest work as expected | ||
|---|---|---|---|
| Submitted: | 30 Oct 2020 2:52 | Modified: | 11 Jan 2021 18:04 |
| Reporter: | Jorge Jaen | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.21 MySQL Community Server | OS: | Windows |
| Assigned to: | CPU Architecture: | Other (64 bit) | |
| Tags: | #Query #information_schema.columns | ||
[30 Oct 2020 2:52]
Jorge Jaen
[30 Oct 2020 12:54]
MySQL Verification Team
Hi Mr. Jaen, Thank you for your bug report. However, we are unable to repeat your problem. We tried to repeat it on our tables and things worked properly. Hence, we need a definition of your tables which produce wrong result on 8.0.22. Best test case would be DDL for the creation of two constrained tables. Next, both queries that you have provided are actually identical. Next, you have not shown the difference in the results from 8.0.18 and 8.0.22. We are always only testing our latest release. Next, table key_column_usage is used only for foreign keys and nothing else. Waiting on your feedback.
[30 Oct 2020 22:28]
Jorge Jaen
Thank you for your response, I have made a litle change to the query in order to display only 3 tables and post here the results
Its important to let you know that this issue is on versión 8.0.21, I dont kow how it Works on versión 8.0.22
mysql> select
-> A.Table_Name,
-> A.Column_Name,
-> B.Constraint_name
-> from information_schema.columns as A
-> join information_schema.KEY_COLUMN_USAGE as B on
-> A.Table_schema = B.Table_Schema and
-> A.Table_name = B.Table_Name and
-> A.Column_name = B.Column_Name
-> Where
-> A.Table_Name in ("bd","Conexiones","FK_Display_Default")
-> Order by
-> A.Table_Name,
-> A.Column_Name;
+--------------------+----------------+---------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME |
+--------------------+----------------+---------------------------+
| bd | BaseDatos | UQ_BaseDatos |
| bd | idBD | PRIMARY |
| bd | idBDAlias | FK_BD_BDAlias |
| conexiones | idBD | UQ_Conexion |
| conexiones | idBD | FK_Conexiones_BD |
| conexiones | idBDAlias | FK_Conexiones_DBAlias |
| conexiones | idConexion | PRIMARY |
| conexiones | idLoc | UQ_Conexion |
| conexiones | idLoc | FK_Conexiones_Localidades |
| conexiones | idServidor | UQ_Conexion |
| conexiones | idServidor | FK_Conexiones_Servidores |
| conexiones | idSistema | FK_Conexiones_Sis |
| fk_display_default | ColumnaMaestra | PRIMARY |
| fk_display_default | idBD | PRIMARY |
| fk_display_default | idBD | FK_DisplayDefault_BD |
| fk_display_default | idDBAlias | FK_DisplayDefault_BDAlias |
| fk_display_default | idSistema | FK_DisplayDefault_Sis |
| fk_display_default | TablaMaestra | PRIMARY |
+--------------------+----------------+---------------------------+
18 rows in set (0.01 sec)
The Second Query is almost identical, the difference is Left Join vs Join
mysql> select
-> A.Table_Name,
-> A.Column_Name,
-> B.Constraint_name
-> from information_schema.columns as A
-> Left join information_schema.KEY_COLUMN_USAGE as B on
-> A.Table_schema = B.Table_Schema and
-> A.Table_name = B.Table_Name and
-> A.Column_name = B.Column_Name
-> Where
-> A.Table_Name in ("bd","Conexiones","FK_Display_Default")
-> Order by
-> A.Table_Name,
-> A.Column_Name;
+--------------------+------------------+-----------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME |
+--------------------+------------------+-----------------+
| bd | BaseDatos | UQ_BaseDatos |
| bd | idBD | PRIMARY |
| bd | idBDAlias | FK_BD_BDAlias |
| bd | SMBD | NULL |
| conexiones | Descripcion | NULL |
| conexiones | idBD | PRIMARY |
| conexiones | idBDAlias | FK_BD_BDAlias |
| conexiones | idConexion | NULL |
| conexiones | idLoc | NULL |
| conexiones | idServidor | NULL |
| conexiones | idSistema | NULL |
| conexiones | Puerto | NULL |
| fk_display_default | ColumnaMaestra | NULL |
| fk_display_default | ExpresionDefault | NULL |
| fk_display_default | idBD | PRIMARY |
| fk_display_default | idDBAlias | NULL |
| fk_display_default | idSistema | NULL |
| fk_display_default | LabelCampo | NULL |
| fk_display_default | Propiedades | NULL |
| fk_display_default | TablaMaestra | NULL |
+--------------------+------------------+-----------------+
20 rows in set (0.01 sec)
[31 Oct 2020 18:02]
Jorge Jaen
I just upgraded to 8.0.22 but i have same results (equals to 8.0.21)
[31 Oct 2020 18:33]
MySQL Verification Team
Please provide the complete test case (a SQL script file with create tables, insert data, queries, real result and the expected one). Thanks in advance.
[31 Oct 2020 21:59]
Jorge Jaen
It seems to me that it is not necessary to provide aditional information, it is the metadata of any table that has foreign keys, if you tell me that these queries are fine in your environment then it must be something that has to do with my installation.
I will only comment I finally install version 8.0.18 and it works as expected, here are the correct results
Thank you.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 148
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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> select
-> A.Table_Name,
-> A.Column_Name,
-> B.Constraint_name
-> from information_schema.columns as A
-> join information_schema.KEY_COLUMN_USAGE as B on
-> A.Table_schema = B.Table_Schema and
-> A.Table_name = B.Table_Name and
-> A.Column_name = B.Column_Name
-> Where
-> A.Table_Name in ("bd","Conexiones","FK_Display_Default")
-> Order by
-> A.Table_Name,
-> A.Column_Name;
+--------------------+----------------+---------------------------+
| TABLE_NAME | COLUMN_NAME | Constraint_name |
+--------------------+----------------+---------------------------+
| bd | BaseDatos | UQ_BaseDatos |
| bd | idBD | PRIMARY |
| bd | idBDAlias | FK_BD_BDAlias |
| conexiones | idBD | UQ_Conexion |
| conexiones | idBD | FK_Conexiones_BD |
| conexiones | idBDAlias | FK_Conexiones_DBAlias |
| conexiones | idConexion | PRIMARY |
| conexiones | idLoc | UQ_Conexion |
| conexiones | idLoc | FK_Conexiones_Localidades |
| conexiones | idServidor | UQ_Conexion |
| conexiones | idServidor | FK_Conexiones_Servidores |
| conexiones | idSistema | FK_Conexiones_Sis |
| fk_display_default | ColumnaMaestra | PRIMARY |
| fk_display_default | idBD | PRIMARY |
| fk_display_default | idBD | FK_DisplayDefault_BD |
| fk_display_default | idDBAlias | FK_DisplayDefault_BDAlias |
| fk_display_default | idSistema | FK_DisplayDefault_Sis |
| fk_display_default | TablaMaestra | PRIMARY |
+--------------------+----------------+---------------------------+
18 rows in set (0.02 sec)
mysql>
mysql>
mysql> select
-> A.Table_Name,
-> A.Column_Name,
-> B.Constraint_name
-> from information_schema.columns as A
-> left join information_schema.KEY_COLUMN_USAGE as B on
-> A.Table_schema = B.Table_Schema and
-> A.Table_name = B.Table_Name and
-> A.Column_name = B.Column_Name
-> Where
-> A.Table_Name in ("bd","Conexiones","FK_Display_Default")
-> Order by
-> A.Table_Name,
-> A.Column_Name;
+--------------------+------------------+---------------------------+
| TABLE_NAME | COLUMN_NAME | Constraint_name |
+--------------------+------------------+---------------------------+
| bd | BaseDatos | UQ_BaseDatos |
| bd | idBD | PRIMARY |
| bd | idBDAlias | FK_BD_BDAlias |
| bd | SMBD | NULL |
| conexiones | Descripcion | NULL |
| conexiones | idBD | UQ_Conexion |
| conexiones | idBD | FK_Conexiones_BD |
| conexiones | idBDAlias | FK_Conexiones_DBAlias |
| conexiones | idConexion | PRIMARY |
| conexiones | idLoc | UQ_Conexion |
| conexiones | idLoc | FK_Conexiones_Localidades |
| conexiones | idServidor | UQ_Conexion |
| conexiones | idServidor | FK_Conexiones_Servidores |
| conexiones | idSistema | FK_Conexiones_Sis |
| conexiones | Puerto | NULL |
| fk_display_default | ColumnaMaestra | PRIMARY |
| fk_display_default | ExpresionDefault | NULL |
| fk_display_default | idBD | PRIMARY |
| fk_display_default | idBD | FK_DisplayDefault_BD |
| fk_display_default | idDBAlias | FK_DisplayDefault_BDAlias |
| fk_display_default | idSistema | FK_DisplayDefault_Sis |
| fk_display_default | LabelCampo | NULL |
| fk_display_default | Propiedades | NULL |
| fk_display_default | TablaMaestra | PRIMARY |
+--------------------+------------------+---------------------------+
24 rows in set (0.01 sec)
mysql>
[2 Nov 2020 12:44]
MySQL Verification Team
Hi Mr. Jaen, The output from the INNER and OUTER JOINs are very consistent. The OUTER JOIN has all the rows as the INNER one, plus the ones that do not have a match. Not a bug.
[2 Jan 2021 7:23]
Przemysław Loesch
Still LEFT JOIN on 'information_schema' doesn't work as expected. This is minimal code to recreate the bug: CREATE TABLE `table1` ( `id` int NOT NULL AUTO_INCREMENT, `some_data` int, `some_other_data` int, PRIMARY KEY (`id`) ); CREATE TABLE `table2` ( `id` int NOT NULL AUTO_INCREMENT, `ref` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `r_idx` (`ref`), CONSTRAINT `ref` FOREIGN KEY (`ref`) REFERENCES `table1` (`id`) ); Now try: SELECT c.table_name, c.column_name, c.data_type, u.table_name ref_table_name, u.column_name ref_column_name FROM information_schema.columns c INNER JOIN information_schema.key_column_usage u ON (c.table_name = u.referenced_table_name and c.column_name = u.referenced_column_name) Inner join works as expected - it returns one row with all selected fields values: TABLE_NAME COLUMN_NAME DATA_TYPE ref_table_name ref_column_name table1 id int table2 ref When you try to LEFT JOIN: SELECT c.table_name, c.column_name, c.data_type, u.table_name ref_table_name, u.column_name ref_column_name FROM information_schema.columns c LEFT JOIN information_schema.key_column_usage u ON (c.table_name = u.referenced_table_name and c.column_name = u.referenced_column_name) You receive all columns of the defined tables but LEFT JOIN returns empty fields: TABLE_NAME COLUMN_NAME DATA_TYPE ref_table_name ref_column_name table1 id int table1 some_data int table1 some_other_data int table2 id int table2 ref int You may add WHERE c.table_schema = 'your_schema_name' at the end of the query to filter results. The second query - LEFT JOIN ON information_schema is in my opinion very important task. Personally I use it for form generation. Now it becomes impossible. Last MySQL version which works for me is 8.0.18.
[11 Jan 2021 13:46]
MySQL Verification Team
Hi Mr. Jaen, That output does not tell us anything. It is possible that left side is empty, in which case the output is correct.
[11 Jan 2021 18:04]
Jorge Jaen
1) The output is incorrect, is NOT possible that left side is empty. (unless some one can explain us why in the simple join shows data and in left join shows null) 2) It was Mr.Przemysław Loesch who made the last comment, I finnaly gave up with this bug on 8.0.22. (Im using MYSQ 8.0.18 in this versión those outputs Works as espected) Best Regards Jorge Jaen
[12 Jan 2021 12:48]
MySQL Verification Team
Thank you for informing us that this bug is fixed.
[22 Feb 2021 14:53]
HGM HGM
Schema for simulation.
Attachment: create-schema.sql (application/octet-stream, text), 10.40 KiB.
[22 Feb 2021 14:53]
HGM HGM
Sample select.
Attachment: select-sample.sql (application/octet-stream, text), 748 bytes.
[22 Feb 2021 14:56]
HGM HGM
I'm having this problem with the current release (8.0.23). When I try to use the select to retrieve FKs, the referenced fields is null. I noted also that if a change the first left join to inner join, I can get the data (at least the first referenced table/column). I also noted that this works fine with version 5.x and until version 8.0.18. I attached the script to create the database and the sql with problem.
[23 Feb 2021 13:37]
MySQL Verification Team
Hi, Last user report is different then the original. Anyway, both reports show that this bug is a duplicate of : https://bugs.mysql.com/bug.php?id=102298 You can follow the progress on this bug. We do not know of the plans of when will it be scheduled for fixing.
[1 Nov 2022 11:36]
Patrik Potocki
It's not possible https://bugs.mysql.com/bug.php?id=102298 just getting "You do not have access to bug #102298." Can that ticket be opened up so it's possible to view it?
[1 Nov 2022 13:34]
MySQL Verification Team
Hi Mr. Potocki, No, it is not possible to open the original bug for inspection. The reporter of the original bug has provided lot's of their own private data in order to verify that bug, so we must respect that. However, we have found out that this bug is fixed in 8.0.31, so you can upgrade and get rid of the problem.
