Description:
In this type of query:
select * from t1 where t1.col <=> (select t2.col from t2 ...)
If the subquery returns no rows, then the condition "where t1.col <=> subquery" should not be satisfied either. However, in reality, if t1.col is null, it will result in a successful match.
How to repeat:
CREATE TABLE noshard_1 (
col_mediumtext TEXT,
col_datetime DATETIME
);
CREATE TABLE mysql_partition_listcolumns_3 (
col_mediumtext TEXT,
col_datetime DATETIME,
col_bool VARCHAR(10)
);
insert into noshard_1 values ("s", "2003-05-23 03:32:24");
insert into noshard_1 values (NULL, "1984-04-18 04:16:25");
insert into mysql_partition_listcolumns_3 values (NULL, "2012-08-21 00:00:00", 1);
insert into mysql_partition_listcolumns_3 values ("-6.759033203125e+80", "2007-12-13 00:00:00", 127);
insert into mysql_partition_listcolumns_3 values (NULL, "2002-04-09 14:45:46", NULL);
insert into mysql_partition_listcolumns_3 values ("2032-12-28 15:58:23.009256", "2010-02-05 00:00:00", 0);
insert into mysql_partition_listcolumns_3 values ("pfsdmycolxxkscbwgsmnaljaqxuxeyuukvxfgncmtfcalnprybwvdutkwuxksuwhstwyogaivwtbblhevsjfpliycvupyfssjmfoltcjmfkbgizdtunsooiwadqkeeiyjnaortstict", "2034-07-20 08:26:51", 1);
insert into mysql_partition_listcolumns_3 values ("ycolxxkscbwgsmnaljaqxuxeyuukvxfgncmtfcalnprybwvdutkwuxksuwhstwyogaivwtbblhevsjfpliycvupyfssjmfoltcjmfkbgizdtunsooiwadqkeeiyjnaortstictdnolovdptnyfgfomjisvuppghtlqdahvicvkglpfqytdoxjmeeygkpkjiookednbqrvmgxgtkjhxfcurjycofrdrycntpgoxwdesgrgzaxkdflzquffkqvrppmqvzmaihsfixcjbukmtyxpradwadeevixuwojzfgconoegwiacwnsjifirvdcgtcbmzawtsizmuergadqagwhuaserhfhdffsuqoqhmxteuplpgxtwjh", "1974-10-15 23:00:32", 127);
insert into mysql_partition_listcolumns_3 values (NULL, "2024-04-03 14:47:58", 8);
SELECT col_mediumtext,col_datetime FROM noshard_1 WHERE `col_mediumtext` <=> (SELECT `col_mediumtext` FROM mysql_partition_listcolumns_3 WHERE col_bool LIKE '%d%' ORDER BY `col_mediumtext`,`col_datetime` limit 1);
# result
+----------------+---------------------+
| col_mediumtext | col_datetime |
+----------------+---------------------+
| NULL | 1984-04-18 04:16:25 |
+----------------+---------------------+
# Execute the subquery separately.
SELECT `col_mediumtext` FROM mysql_partition_listcolumns_3 WHERE col_bool LIKE '%d%' ORDER BY `col_mediumtext`,`col_datetime` limit 1
# result
Empty set
Description: In this type of query: select * from t1 where t1.col <=> (select t2.col from t2 ...) If the subquery returns no rows, then the condition "where t1.col <=> subquery" should not be satisfied either. However, in reality, if t1.col is null, it will result in a successful match. How to repeat: CREATE TABLE noshard_1 ( col_mediumtext TEXT, col_datetime DATETIME ); CREATE TABLE mysql_partition_listcolumns_3 ( col_mediumtext TEXT, col_datetime DATETIME, col_bool VARCHAR(10) ); insert into noshard_1 values ("s", "2003-05-23 03:32:24"); insert into noshard_1 values (NULL, "1984-04-18 04:16:25"); insert into mysql_partition_listcolumns_3 values (NULL, "2012-08-21 00:00:00", 1); insert into mysql_partition_listcolumns_3 values ("-6.759033203125e+80", "2007-12-13 00:00:00", 127); insert into mysql_partition_listcolumns_3 values (NULL, "2002-04-09 14:45:46", NULL); insert into mysql_partition_listcolumns_3 values ("2032-12-28 15:58:23.009256", "2010-02-05 00:00:00", 0); insert into mysql_partition_listcolumns_3 values ("pfsdmycolxxkscbwgsmnaljaqxuxeyuukvxfgncmtfcalnprybwvdutkwuxksuwhstwyogaivwtbblhevsjfpliycvupyfssjmfoltcjmfkbgizdtunsooiwadqkeeiyjnaortstict", "2034-07-20 08:26:51", 1); insert into mysql_partition_listcolumns_3 values ("ycolxxkscbwgsmnaljaqxuxeyuukvxfgncmtfcalnprybwvdutkwuxksuwhstwyogaivwtbblhevsjfpliycvupyfssjmfoltcjmfkbgizdtunsooiwadqkeeiyjnaortstictdnolovdptnyfgfomjisvuppghtlqdahvicvkglpfqytdoxjmeeygkpkjiookednbqrvmgxgtkjhxfcurjycofrdrycntpgoxwdesgrgzaxkdflzquffkqvrppmqvzmaihsfixcjbukmtyxpradwadeevixuwojzfgconoegwiacwnsjifirvdcgtcbmzawtsizmuergadqagwhuaserhfhdffsuqoqhmxteuplpgxtwjh", "1974-10-15 23:00:32", 127); insert into mysql_partition_listcolumns_3 values (NULL, "2024-04-03 14:47:58", 8); SELECT col_mediumtext,col_datetime FROM noshard_1 WHERE `col_mediumtext` <=> (SELECT `col_mediumtext` FROM mysql_partition_listcolumns_3 WHERE col_bool LIKE '%d%' ORDER BY `col_mediumtext`,`col_datetime` limit 1); # result +----------------+---------------------+ | col_mediumtext | col_datetime | +----------------+---------------------+ | NULL | 1984-04-18 04:16:25 | +----------------+---------------------+ # Execute the subquery separately. SELECT `col_mediumtext` FROM mysql_partition_listcolumns_3 WHERE col_bool LIKE '%d%' ORDER BY `col_mediumtext`,`col_datetime` limit 1 # result Empty set