⚝
One Hat Cyber Team
⚝
Your IP:
216.73.216.124
Server IP:
50.28.103.30
Server:
Linux host.jcukjv-lwsites.com 4.18.0-553.22.1.el8_10.x86_64 #1 SMP Tue Sep 24 05:16:59 EDT 2024 x86_64
Server Software:
nginx/1.28.0
PHP Version:
8.3.12
Buat File
|
Buat Folder
Eksekusi
Dir :
~
/
www
/
server
/
mysql
/
mysql-test
/
r
/
View File Name :
subquery_bugs.result
# # Bug#27182010 SUBQUERY INCORRECTLY SHOWS DUPLICATE VALUES ON SUBQUERIES # CREATE TABLE p (Id INT,PRIMARY KEY (Id)); INSERT INTO p VALUES (1); # Test UNIQUE KEY with NULL values CREATE TABLE s (Id INT, u INT, UNIQUE KEY o(Id, u) ); INSERT INTO s VALUES (1, NULL),(1, NULL); EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN ( SELECT s.Id FROM s WHERE Id=1 AND u IS NULL)ORDER BY Id DESC; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE p NULL const PRIMARY PRIMARY 4 const 1 100.00 Using index 1 SIMPLE s NULL ref o o 10 const,const 2 100.00 Using where; Using index; FirstMatch(p) Warnings: Note 1003 /* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s`) where ((`test`.`s`.`Id` = 1) and isnull(`test`.`s`.`u`)) order by '1' desc EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN ( SELECT s.Id FROM s WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE p NULL const PRIMARY PRIMARY 4 const 1 100.00 Using index 1 SIMPLE s NULL range o o 10 NULL 1 100.00 Using where; Using index; FirstMatch(p) Warnings: Note 1003 /* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s`) where ((`test`.`s`.`Id` = 1) and (`test`.`s`.`u` is not null)) order by '1' desc SELECT p.Id FROM (p) WHERE p.Id IN ( SELECT s.Id FROM s WHERE Id=1 AND u IS NULL)ORDER BY Id DESC; Id 1 SELECT p.Id FROM (p) WHERE p.Id IN ( SELECT s.Id FROM s WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC; Id # UNIQUE KEY without NULL values CREATE TABLE s1 (Id INT, u INT, UNIQUE KEY o(Id, u) ); INSERT INTO s1 VALUES (1, 2),(1, 3); EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN ( SELECT s1.Id FROM s1 WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE p NULL const PRIMARY PRIMARY 4 const 1 100.00 Using index 1 SIMPLE s1 NULL ref o o 5 const 2 50.00 Using where; Using index; FirstMatch(p) Warnings: Note 1003 /* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s1`) where ((`test`.`s1`.`Id` = 1) and (`test`.`s1`.`u` is not null)) order by '1' desc EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN ( SELECT s1.Id FROM s1 WHERE Id=1 AND u != 1) ORDER BY Id DESC; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE p NULL const PRIMARY PRIMARY 4 const 1 100.00 Using index 1 SIMPLE s1 NULL ref o o 5 const 3 50.00 Using where; Using index; FirstMatch(p) Warnings: Note 1003 /* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s1`) where ((`test`.`s1`.`Id` = 1) and (`test`.`s1`.`u` <> 1)) order by '1' desc SELECT p.Id FROM (p) WHERE p.Id IN ( SELECT s1.Id FROM s1 WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC; Id 1 SELECT p.Id FROM (p) WHERE p.Id IN ( SELECT s1.Id FROM s1 WHERE Id=1 AND u != 1) ORDER BY Id DESC; Id 1 # NON UNIQUE KEY Scenario CREATE TABLE s2 (Id INT, u INT, KEY o(Id, u) ); INSERT INTO s2 VALUES (1, NULL),(1, NULL); #UNIQUE KEY with NON NULL FIELDS CREATE TABLE s3 (Id INT NOT NULL, u INT NOT NULL, UNIQUE KEY o(Id, u)); INSERT INTO s3 VALUES (1, 2),(1, 3); EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN ( SELECT s.Id FROM s2 s WHERE Id=1 AND u IS NULL) ORDER BY Id DESC; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE p NULL const PRIMARY PRIMARY 4 const 1 100.00 Using index 1 SIMPLE s NULL ref o o 10 const,const 2 100.00 Using where; Using index; FirstMatch(p) Warnings: Note 1003 /* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s2` `s`) where ((`test`.`s`.`Id` = 1) and isnull(`test`.`s`.`u`)) order by '1' desc EXPLAIN SELECT p.Id FROM (p) WHERE p.Id IN ( SELECT s.Id FROM s3 s WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE p NULL const PRIMARY PRIMARY 4 const 1 100.00 Using index 1 SIMPLE s NULL ref o o 4 const 2 50.00 Using where; Using index; FirstMatch(p) Warnings: Note 1003 /* select#1 */ select '1' AS `Id` from `test`.`p` semi join (`test`.`s3` `s`) where ((`test`.`s`.`Id` = 1) and (`test`.`s`.`u` is not null)) order by '1' desc SELECT p.Id FROM (p) WHERE p.Id IN ( SELECT s.Id FROM s2 s WHERE Id=1 AND u IS NULL) ORDER BY Id DESC; Id 1 SELECT p.Id FROM (p) WHERE p.Id IN ( SELECT s.Id FROM s3 s WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC; Id 1 DROP TABLE p, s, s1, s2, s3;