Bug #101390 Left Join on information_schema.columns doest work as expected
Submitted: 30 Oct 2020 2:52 Modified: 11 Jan 18:04
Reporter: Jorge Jaen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.21 MySQL Community Server OS:Microsoft Windows
Assigned to: CPU Architecture:Other (64 bit)
Tags: #Query #information_schema.columns

[30 Oct 2020 2:52] Jorge Jaen
Description:
Left Join on information_schema.columns doest work as expected

On Mysql 8.0.18 this works fine

How to repeat:
Run Query1 and see the foreigh keys
Query1:

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 
Order by 
	A.Table_Name,
	A.Column_Name;

then change join for left join and all foreign keys are lost,
the expected result is to get the same result of query 1 plus something else 
(columns without that are not key fields or has Foreign key)

Query 2:
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 
Order by 
	A.Table_Name,
	A.Column_Name;
[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 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 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 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 12:48] MySQL Verification Team
Thank you for informing us that this bug is fixed.
[22 Feb 14:53] HGM HGM
Schema for simulation.

Attachment: create-schema.sql (application/octet-stream, text), 10.40 KiB.

[22 Feb 14:53] HGM HGM
Sample select.

Attachment: select-sample.sql (application/octet-stream, text), 748 bytes.

[22 Feb 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 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.