Bug #111218 Logic bug: logic equal sqls return different results

• Submitted: 31 May 2023 11:41
• Modified: 1 Jun 2023 9:31
• Reporter: Doris Li
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.33
• OS: Ubuntu (ubuntu0.20.04.2)
• CPU Architecture: x86 (x86_64)

[31 May 2023 11:41] Doris Li

Description:
Results of two logic-equal sql queries on Mysql are different, which means a logic bug.

How to repeat:
CREATE TABLE stu_info(
	id INT AUTO_INCREMENT PRIMARY KEY, 
	iname VARCHAR(20), 
	gender CHAR(1), 
	department VARCHAR(10), 
	age TINYINT, 
	province VARCHAR(10), 
	email VARCHAR(50), 
	mobilephone CHAR(11)
);
INSERT INTO stu_info(iname,gender,department,age,province,email,mobilephone) VALUES 
	('张勇','男','数学系',23,'河南','[e-mail]','13323564321'), 
	('王兵','男','数学系',25,'江苏','[e-mail]','17823774329'), 
	('刘伟','男','计算机系',21,'江苏','[e-mail]','13834892240'), 
	('张峰','男','管理系',22,'上海','[e-mail]','13923654481'), 
	('董敏','女','生物系',22,'浙江','[e-mail]','13428439022'), 
	('徐晓红','女','计算机系',24,'浙江','[e-mail]','13720097528'), 
	('赵伊美','女','数学系',21,'江苏','[e-mail]','13417723980'), 
	('王建国','男','管理系',24,'浙江','[e-mail]','13768329901'), 
	('刘清','女','统计系',23,'安徽','[e-mail]','17823651180'), 
	('赵家和','男','计算机系',28,'山东','[e-mail]','13827811311');
CREATE TABLE stu_score( id INT , Excel TINYINT, Tableau TINYINT, MySQL TINYINT ); 
INSERT INTO stu_score VALUES 
	(1,87,72,88), 
	(3,90,66,72), 
	(2,90,70,86), 
	(4,88,82,76), 
	(8,92,67,80), 
	(10,88,82,89), 
	(5,79,66,60), 
	(7,91,78,90), 
	(6,82,79,88), 
	(9,85,70,85);

select stu_score.id,stu_info.gender from stu_score,stu_info where stu_info.id = stu_score.id and stu_score.id < 10 and stu_score.Excel < 10;
Empty set (0.00 sec)

select stu_info.gender ,(select stu_score.id from stu_score where stu_info.id = stu_score.id and stu_score.id < 10 and stu_score.Excel < 10) as sub from  stu_info ;
+--------+------+
| gender | sub  |
+--------+------+
| 男     | NULL |
| 男     | NULL |
| 男     | NULL |
| 男     | NULL |
| 女     | NULL |
| 女     | NULL |
| 女     | NULL |
| 男     | NULL |
| 女     | NULL |
| 男     | NULL |
+--------+------+
10 rows in set (0.00 sec)

[31 May 2023 13:27] MySQL Verification Team

Hi Mr. Li,

Thank you for your bug report.

However, it is not a bug.

Your first bug is a simple inner join, where no condition is satisfied. Hence you get no results.

Your second query is a query on a single table, where second field in the SELECT list is the query from the above sentence. Since, second field yields no results, you get NULL in all result rows from the second query.

In short, you can not expect that a join and a simple, single query return the same results ....

Not a bug.

[1 Jun 2023 9:31] Doris Li

There are three examples:
The first example: two logic-equal queries, which return the same result.
select stu_info.province,stu_score.MySQL from stu_info,stu_score where stu_score.id = stu_info.id and stu_score.Excel < 10 and stu_score.Tableau < 10;
Empty set (0.00 sec)
select stu_score.MySQL ,(select stu_info.province from stu_info where stu_score.id = stu_info.id) as sub from  stu_score where stu_score.Excel < 10 and stu_score.Tableau < 10;
Empty set (0.01 sec)

The second example: two logic-equal queries, which return the same result
select stu_info.department,stu_score.id from stu_info,stu_score where stu_score.id = stu_info.id and stu_info.gender = "f" and stu_score.Excel < 10;
Empty set (0.00 sec)
select stu_score.id ,(select stu_info.department from stu_info where stu_score.id = stu_info.id and stu_info.gender = "f") as sub from  stu_score where stu_score.Excel < 10;
Empty set (0.00 sec)

The third example: two logic-equal queries, which return different results.
select stu_score.id,stu_info.gender from stu_score,stu_info where stu_info.id = stu_score.id and stu_score.id < 10 and stu_score.Excel < 10;
Empty set (0.00 sec)
select stu_info.gender ,(select stu_score.id from stu_score where stu_info.id = stu_score.id and stu_score.id < 10 and stu_score.Excel < 10) as sub from  stu_info ;
+--------+------+
| gender | sub  |
+--------+------+
| 男     | NULL |
| 男     | NULL |
| 男     | NULL |
| 男     | NULL |
| 女     | NULL |
| 女     | NULL |
| 女     | NULL |
| 男     | NULL |
| 女     | NULL |
| 男     | NULL |
+--------+------+
10 rows in set (0.00 sec)

So my conclusion on theses three examples are, there is a logic bug in MySQL 8.0.33. 
Please change the status of this page.

[1 Jun 2023 13:11] MySQL Verification Team

Hi Mrs. Li,

Thank you for your comment.

MySQL is a relational database, which means that it follows SQL standards.

In SQL, there is no notion like logic-equal.

Your third case is a comparison from the outputs from two totally different types of queries, as defined by SQL standards. MySQL follows the current SQL standard, which is from 2016.

First query is an INNER JOIN, while second query is NOT a JOIN at all, but a query on a single table, where one of the fields in the SELECT list is a separate query on another table. There is nothing remotely similar in those two queries, much less equal.

Not a bug.