Bug #100617 "left join" make a bug in information_schema.TABLE_CONSTRAINTS&KEY_COLUMN_USAGE

• Submitted: 24 Aug 2020 8:45
• Modified: 25 Aug 2020 12:21
• Reporter: 庆杰 胡
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S4 (Feature request)
• Version: mysql 8.0.21
• OS: Windows (window 10 x64 professional)
• CPU Architecture: x86
• Tags: left join

[24 Aug 2020 8:45] 庆杰 胡

Description:
I'm doing metadata testing for MySQL,but I found a table connection exception:

When information_schema.TABLE_Constraints and information_schema.KEY_COLUMN_Their columns are scrambled after usage connection.

How to repeat:

***********************sql**************************
create database test;
use test;
DROP table if EXISTS class;
create table class(
	id int primary key,
	cno varchar(50),
	name varchar(50),
	createtime datetime default(NOW())
);

DROP table if EXISTS student;
create table student(
	id int primary key,
	sno varchar(50) not null unique,
	name varchar(50) not null check(LENGTH(name)<5),
	score float not null default(-1),
	classid int
);
ALTER TABLE student ADD CONSTRAINT FK_student_class FOREIGN KEY(classid) REFERENCES class(id) ON DELETE set null;

DROP table if EXISTS book;
create table book(
	id int not null,
	name varchar(50) not null
);
ALTER TABLE book ADD CONSTRAINT PK_book primary key (id,name);

DROP table if EXISTS student_book;
create table student_book(
	sid int,
	bid int
);
ALTER TABLE student_book ADD CONSTRAINT UQ_student_book UNIQUE (sid,bid);
ALTER TABLE student_book ADD CONSTRAINT CK_student_book CHECK(sid is not null and bid is not null);
***************************************************

****************here is bug*************************
SELECT
	t.TABLE_SCHEMA,
	t.TABLE_NAME,
	t.CONSTRAINT_TYPE,
	t.CONSTRAINT_NAME,
	k.COLUMN_NAME,
	k.table_schema,
	k.table_name,
	k.COLUMN_NAME,
	k.CONSTRAINT_NAME
FROM
	information_schema.TABLE_CONSTRAINTS t left join
  information_schema.KEY_COLUMN_USAGE as k ON (t.TABLE_SCHEMA = k.TABLE_SCHEMA 	AND t.TABLE_NAME = k.TABLE_NAME	AND t.CONSTRAINT_NAME = k.CONSTRAINT_NAME )
WHERE
	t.TABLE_SCHEMA = 'test' 
	 AND t.table_name IN ( 'class', 'student', 'book', 'student_book' ) 
	-- and t.table_name ='class'
	-- and t.table_name='student'
ORDER BY
	t.table_name,
	t.constraint_name;

--It shows that the primary key of the table class is id&name ,but i only define id...

***************************************************

[24 Aug 2020 12:47] MySQL Verification Team

Hi Mr. james,

Thank you for your bug report.

However, I am totally unable to repeat the problem that you are reporting.

This is what I get in my result set:

---------------------------

TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME	COLUMN_NAME	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	CONSTRAINT_NAME
test	book	PRIMARY KEY	PRIMARY	id	test	book	id	PRIMARY
test	book	PRIMARY KEY	PRIMARY	name	test	book	name	PRIMARY
test	class	PRIMARY KEY	PRIMARY	id	test	class	id	PRIMARY
test	class	PRIMARY KEY	PRIMARY	name	test	class	name	PRIMARY
test	student	FOREIGN KEY	FK_student_class	NULL	NULL	NULL	NULL	NULL
test	student	PRIMARY KEY	PRIMARY	id	test	student	id	PRIMARY
test	student	PRIMARY KEY	PRIMARY	name	test	student	name	PRIMARY
test	student	UNIQUE	sno	NULL	NULL	NULL	NULL	NULL
test	student	CHECK	student_chk_1	NULL	NULL	NULL	NULL	NULL
test	student_book	CHECK	CK_student_book	NULL	NULL	NULL	NULL	NULL
test	student_book	UNIQUE	UQ_student_book	NULL	NULL	NULL	NULL	NULL

---------------------------

Hence, nothing is scrambled. I used 8.0.21 server and 8.0.21 mysql CLI.

If you are using some GUI client, then you should report it as client bug and not server bug.

Can't repeat .........

[25 Aug 2020 0:53] 庆杰 胡

First,I get the same output as you.
But I wonder why there are these two lines of data：
***********************************************
TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME COLUMN_NAME TABLE_SCHEMA TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
test class PRIMARY KEY PRIMARY name test class name PRIMARY
test student PRIMARY KEY PRIMARY name test student name PRIMARY
******************************************************

I didn't set column("name") as the primary key for the class and student tables！

[25 Aug 2020 12:11] MySQL Verification Team

Hi Mr. james,

This is because CHECK() constraint effectively is presented in that fashion, because of the manner in which it was implemented.

[25 Aug 2020 12:21] MySQL Verification Team

Hi Mr. james,

On second thought, such a display could confuse many users.

This is still not a bug, but it makes a good feature request.

Verified as a feature request.