Bug #100339 LEFT JOIN doesn't work Join and Right join does on INformation_Scheam
Submitted: 27 Jul 2020 17:44 Modified: 27 Jul 2020 18:20
Reporter: Pedro da Costa Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.21 OS:Windows (Windows 10 professionqal x64)
Assigned to: CPU Architecture:x86 (ryzen 7 3800)
Tags: Join Workbench informatio_schema

[27 Jul 2020 17:44] Pedro da Costa 
Description:
joining information_schema.KEY_COLUMN_USAGE and TABLE_CONSTRAINTS  doesn't always give an result.
Depending on the prder of the join a left join or a right join only result in NULL

How to repeat:
-- MySQL dump 10.13  Distrib 8.0.21, for Win64 (x86_64)
--
-- Host: localhost    Database: testdb
-- ------------------------------------------------------
-- Server version	8.0.21

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `association`
--
CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

DROP TABLE IF EXISTS `association`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `association` (
  `code_asso` int unsigned NOT NULL AUTO_INCREMENT,
  `id_product` int unsigned DEFAULT NULL,
  `id_category` smallint unsigned DEFAULT NULL,
  PRIMARY KEY (`code_asso`),
  KEY `id_product` (`id_product`),
  KEY `id_category` (`id_category`),
  CONSTRAINT `association_ibfk_1` FOREIGN KEY (`id_product`) REFERENCES `products` (`id_product`),
  CONSTRAINT `association_ibfk_2` FOREIGN KEY (`id_category`) REFERENCES `category` (`id_category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `bar`
--

DROP TABLE IF EXISTS `bar`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `bar` (
  `id` int unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `category`
--

DROP TABLE IF EXISTS `category`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `category` (
  `id_category` smallint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `data`
--

DROP TABLE IF EXISTS `data`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `data` (
  `callDate` text,
  `callerName` text,
  `callLength` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `foo`
--

DROP TABLE IF EXISTS `foo`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `foo` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `fk_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `foo_ibfk_1` (`fk_id`),
  CONSTRAINT `foo_ibfk_1` FOREIGN KEY (`fk_id`) REFERENCES `bar` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `new_table`
--

DROP TABLE IF EXISTS `new_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `new_table` (
  `idnew_table` int NOT NULL,
  `new_tablecol` varchar(45) DEFAULT NULL,
  `new_tablecol1` varchar(45) DEFAULT NULL,
  `new_tablecol2` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idnew_table`),
  UNIQUE KEY `new_tablecol_UNIQUE` (`new_tablecol`),
  UNIQUE KEY `new_tablecol1_UNIQUE` (`new_tablecol1`),
  UNIQUE KEY `new_tablecol2_UNIQUE` (`new_tablecol2`),
  CONSTRAINT `ewst` FOREIGN KEY (`new_tablecol`) REFERENCES `new_table2` (`new_tablecol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `new_table2`
--

DROP TABLE IF EXISTS `new_table2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `new_table2` (
  `idnew_table` int NOT NULL,
  `new_tablecol` varchar(45) NOT NULL,
  `new_tablecol1` varchar(45) DEFAULT NULL,
  `new_tablecol2` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`new_tablecol`),
  KEY `e2` (`new_tablecol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `new_table3`
--

DROP TABLE IF EXISTS `new_table3`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `new_table3` (
  `idnew_table` int NOT NULL,
  `new_tablecol` varchar(45) DEFAULT NULL,
  `new_tablecol1` varchar(45) DEFAULT NULL,
  `new_tablecol2` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `products`
--

DROP TABLE IF EXISTS `products`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `products` (
  `id_product` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `nutriscore` varchar(1) DEFAULT NULL,
  `url` varchar(250) DEFAULT NULL,
  `stores` longtext,
  PRIMARY KEY (`id_product`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `scaling`
--

DROP TABLE IF EXISTS `scaling`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `scaling` (
  `idScaling` int NOT NULL,
  PRIMARY KEY (`idScaling`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `values`
--

DROP TABLE IF EXISTS `values`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `values` (
  `value1` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-07-27 19:38:49

THIS gives a result(if you have a foreign key

SELECT kcu.*,tc.* FROM information_schema.KEY_COLUMN_USAGE kcu
LEFT JOIN  information_schema.TABLE_CONSTRAINTS tc ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND tc.TABLE_SCHEMA = 'testdb';

SELECT kcu.*,tc.* FROM information_schema.TABLE_CONSTRAINTS tc 
RIGHT JOIN information_schema.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND tc.TABLE_SCHEMA = 'testdb'

And This resultset has only NULL

SELECT kcu.*,tc.* FROM information_schema.KEY_COLUMN_USAGE kcu
RIGHT JOIN  information_schema.TABLE_CONSTRAINTS tc ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND tc.TABLE_SCHEMA = 'testdb';

SELECT kcu.*,tc.* FROM information_schema.TABLE_CONSTRAINTS tc 
LEFT JOIN information_schema.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND tc.TABLE_SCHEMA = 'testdb'

Suggested fix:
in that database is joing possible, when a right join and join give an result, also a left join should give a result and not all NULL
[27 Jul 2020 18:20] MySQL Verification Team 
Thank you for the bug report.
[17 Aug 2020 10:51] Rauli Ikonen 
Since status is verified I guess this has been reproduced already but just in case, here's a very simple repro for the issue:

create table nopk (id integer);
create table pk (id integer primary key);

select * from information_schema.table_constraints where constraint_schema = 'defaultdb';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | defaultdb         | PRIMARY         | defaultdb    | pk         | PRIMARY KEY     | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+

For the following queries the only difference is that the latter query does LEFT JOIN and the first one JOIN. LEFT JOIN causes the join not to return the matching results at all. This is categorized as non-critical but seems pretty critical to me as it breaks a lot of queries that need to deal with database metadata.

SELECT
    tab.table_schema AS database_name,
    tab.table_name AS table_name,
    tab.table_rows AS table_rows,
    tco.constraint_type AS constraint_type
FROM information_schema.tables tab
JOIN information_schema.table_constraints tco
          ON (tab.table_schema = tco.table_schema AND tab.table_name = tco.table_name)
WHERE
    tab.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
    AND tab.table_type = 'BASE TABLE';
+---------------+------------+------------+-----------------+
| database_name | table_name | table_rows | constraint_type |
+---------------+------------+------------+-----------------+
| defaultdb     | pk         |          0 | PRIMARY KEY     |
+---------------+------------+------------+-----------------+

SELECT
    tab.table_schema AS database_name,
    tab.table_name AS table_name,
    tab.table_rows AS table_rows,
    tco.constraint_type AS constraint_type
FROM information_schema.tables tab
LEFT JOIN information_schema.table_constraints tco
          ON (tab.table_schema = tco.table_schema AND tab.table_name = tco.table_name)
WHERE
    tab.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
    AND tab.table_type = 'BASE TABLE';
+---------------+------------+------------+-----------------+
| database_name | table_name | table_rows | constraint_type |
+---------------+------------+------------+-----------------+
| defaultdb     | nopk       |          0 | NULL            |
| defaultdb     | pk         |          0 | NULL            |
+---------------+------------+------------+-----------------+