Description:
Results of two logic-equal sql queries on Mysql are different, which means a logic bug.
select stu_info.gender,stu_score.id from stu_info,stu_score where stu_score.id = stu_info.id and stu_info.gender = "f" and stu_info.id < 10;
Empty set (0.00 sec)
select stu_score.id ,(select stu_info.gender from stu_info where stu_score.id = stu_info.id and stu_info.gender = "f" and stu_info.id < 10) as sub from stu_score ;
+------+------+
| id | sub |
+------+------+
| 1 | NULL |
| 3 | NULL |
| 2 | NULL |
| 4 | NULL |
| 8 | NULL |
| 10 | NULL |
| 5 | NULL |
| 7 | NULL |
| 6 | NULL |
| 9 | NULL |
+------+------+
10 rows in set (0.00 sec)
These two queries are logic equal, but the results are different.
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,'河南','sfddf123dd@163.com','13323564321'),
('王兵','男','数学系',25,'江苏','lss1993@163.com','17823774329'),
('刘伟','男','计算机系',21,'江苏','qawsed112@126.com','13834892240'),
('张峰','男','管理系',22,'上海','102945328@qq.com','13923654481'),
('董敏','女','生物系',22,'浙江','82378339@qq.com','13428439022'),
('徐晓红','女','计算机系',24,'浙江','xixiaohong@gmail.com','13720097528'),
('赵伊美','女','数学系',21,'江苏','zhaomeimei@163.com','13417723980'),
('王建国','男','管理系',24,'浙江','9213228402@qq.com','13768329901'),
('刘清','女','统计系',23,'安徽','lq1128@gmail.com','17823651180'),
('赵家和','男','计算机系',28,'山东','dcrzdbjh@163.com','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);
Description: Results of two logic-equal sql queries on Mysql are different, which means a logic bug. select stu_info.gender,stu_score.id from stu_info,stu_score where stu_score.id = stu_info.id and stu_info.gender = "f" and stu_info.id < 10; Empty set (0.00 sec) select stu_score.id ,(select stu_info.gender from stu_info where stu_score.id = stu_info.id and stu_info.gender = "f" and stu_info.id < 10) as sub from stu_score ; +------+------+ | id | sub | +------+------+ | 1 | NULL | | 3 | NULL | | 2 | NULL | | 4 | NULL | | 8 | NULL | | 10 | NULL | | 5 | NULL | | 7 | NULL | | 6 | NULL | | 9 | NULL | +------+------+ 10 rows in set (0.00 sec) These two queries are logic equal, but the results are different. 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,'河南','sfddf123dd@163.com','13323564321'), ('王兵','男','数学系',25,'江苏','lss1993@163.com','17823774329'), ('刘伟','男','计算机系',21,'江苏','qawsed112@126.com','13834892240'), ('张峰','男','管理系',22,'上海','102945328@qq.com','13923654481'), ('董敏','女','生物系',22,'浙江','82378339@qq.com','13428439022'), ('徐晓红','女','计算机系',24,'浙江','xixiaohong@gmail.com','13720097528'), ('赵伊美','女','数学系',21,'江苏','zhaomeimei@163.com','13417723980'), ('王建国','男','管理系',24,'浙江','9213228402@qq.com','13768329901'), ('刘清','女','统计系',23,'安徽','lq1128@gmail.com','17823651180'), ('赵家和','男','计算机系',28,'山东','dcrzdbjh@163.com','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);