Description:
adding DEFAULT FUNCTION in the where clause, the query returns a wrong result caused by the cached value.
mysql> SELECT t0.c1, t1.c4, t1.c0,t1.c2, t0.c2 FROM t1 LEFT JOIN t0 ON (t1.c0 < t1.c2 ) WHERE (((((t0.c1))OR(t0.c1))) IS NULL);
+------+----+--------------------+------------+------+
| c1 | c4 | c0 | c2 | c2 |
+------+----+--------------------+------------+------+
| NULL | 0 | A(a? | NULL | NULL |
| NULL | 1 | GrDe | 0000000000 | NULL |
| NULL | 1 | n뗪줙EX | 0000000000 | NULL |
| NULL | 1 | 0.4713822719417786 | 0000000000 | NULL |
| NULL | 1 | 1100904778 | 0000000000 | NULL |
+------+----+--------------------+------------+------+
5 rows in set, 4 warnings (0.00 sec)
adding DEFAULT:
mysql> SELECT t0.c1, t1.c4, t1.c0,t1.c2, t0.c2 FROM t1 LEFT JOIN t0 ON (t1.c0 < t1.c2 ) WHERE ((((DEFAULT(t0.c1))OR(t0.c1))) IS NULL);
Empty set, 4 warnings (0.00 sec)
How to repeat:
DROP TABLE IF EXISTS t0,t1;
CREATE TABLE t0 (
c1 tinyint(1) NOT NULL DEFAULT '1',
c2 tinyint(1) NOT NULL,
PRIMARY KEY (c2,c1)
) ENGINE=InnoDB;
INSERT INTO t0 VALUES (0,0),(1,0);
CREATE TABLE t1 (
c0 varchar(277) NOT NULL,
c2 decimal(10,0) unsigned zerofill DEFAULT NULL,
c3 varchar(230) NOT NULL,
c4 tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (c4,c3,c0)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES
('-593434473',0000000000,'-lEuP\r[(',0),('A(a?',NULL,'b\뀝s>o3{k',0),('GrDe',0000000000,'',1),('n뗪줙EX',0000000000,'',1),('0.4713822719417786',0000000000,' )vv^JV',1),('-1202850844',0000000000,'(XKTi2ﭛ|',1),('',1514182157,'1 j(5加X',1),('u',1901639783,'1 j(5加X',1),('1100904778',0000000000,'7OC땼',1);
SELECT t0.c1, t1.c4, t1.c0,t1.c2, t0.c2 FROM t1 LEFT JOIN t0 ON (t1.c0 < t1.c2 ) WHERE ((((DEFAULT(t0.c1))OR(t0.c1))) IS NULL);
Suggested fix:
after analyzing, maybe it is caused by the wrong cached value.
we can see the analyze infor for this query:
mysql> explain analyze SELECT t0.c1, t1.c4, t1.c0,t1.c2, t0.c2 FROM t1 LEFT JOIN t0 ON (t1.c0 < t1.c2 ) WHERE ((((DEFAULT(t0.c1))OR(t0.c1))) IS NULL);
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: ((<cache>((0 <> default(t0.c1))) or (0 <> t0.c1)) is null) (cost=5.2 rows=18) (actual time=0.0569..0.0569 rows=0 loops=1)
-> Nested loop left join (cost=5.2 rows=18) (actual time=0.0215..0.0529 rows=13 loops=1)
-> Table scan on t1 (cost=1.15 rows=9) (actual time=0.0127..0.0174 rows=9 loops=1)
-> Filter: (cast(t1.c0 as double) < cast(t1.c2 as double)) (cost=0.272 rows=2) (actual time=0.00296..0.0035 rows=0.889 loops=9)
-> Covering index scan on t0 using PRIMARY (cost=0.272 rows=2) (actual time=0.00112..0.00175 rows=2 loops=9)
and the first row of (t0.c1) is NULL.
so this query just return an empty and wrong result.
Description: adding DEFAULT FUNCTION in the where clause, the query returns a wrong result caused by the cached value. mysql> SELECT t0.c1, t1.c4, t1.c0,t1.c2, t0.c2 FROM t1 LEFT JOIN t0 ON (t1.c0 < t1.c2 ) WHERE (((((t0.c1))OR(t0.c1))) IS NULL); +------+----+--------------------+------------+------+ | c1 | c4 | c0 | c2 | c2 | +------+----+--------------------+------------+------+ | NULL | 0 | A(a? | NULL | NULL | | NULL | 1 | GrDe | 0000000000 | NULL | | NULL | 1 | n뗪줙EX | 0000000000 | NULL | | NULL | 1 | 0.4713822719417786 | 0000000000 | NULL | | NULL | 1 | 1100904778 | 0000000000 | NULL | +------+----+--------------------+------------+------+ 5 rows in set, 4 warnings (0.00 sec) adding DEFAULT: mysql> SELECT t0.c1, t1.c4, t1.c0,t1.c2, t0.c2 FROM t1 LEFT JOIN t0 ON (t1.c0 < t1.c2 ) WHERE ((((DEFAULT(t0.c1))OR(t0.c1))) IS NULL); Empty set, 4 warnings (0.00 sec) How to repeat: DROP TABLE IF EXISTS t0,t1; CREATE TABLE t0 ( c1 tinyint(1) NOT NULL DEFAULT '1', c2 tinyint(1) NOT NULL, PRIMARY KEY (c2,c1) ) ENGINE=InnoDB; INSERT INTO t0 VALUES (0,0),(1,0); CREATE TABLE t1 ( c0 varchar(277) NOT NULL, c2 decimal(10,0) unsigned zerofill DEFAULT NULL, c3 varchar(230) NOT NULL, c4 tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (c4,c3,c0) ) ENGINE=InnoDB; INSERT INTO t1 VALUES ('-593434473',0000000000,'-lEuP\r[(',0),('A(a?',NULL,'b\뀝s>o3{k',0),('GrDe',0000000000,'',1),('n뗪줙EX',0000000000,'',1),('0.4713822719417786',0000000000,' )vv^JV',1),('-1202850844',0000000000,'(XKTi2ﭛ|',1),('',1514182157,'1 j(5加X',1),('u',1901639783,'1 j(5加X',1),('1100904778',0000000000,'7OC땼',1); SELECT t0.c1, t1.c4, t1.c0,t1.c2, t0.c2 FROM t1 LEFT JOIN t0 ON (t1.c0 < t1.c2 ) WHERE ((((DEFAULT(t0.c1))OR(t0.c1))) IS NULL); Suggested fix: after analyzing, maybe it is caused by the wrong cached value. we can see the analyze infor for this query: mysql> explain analyze SELECT t0.c1, t1.c4, t1.c0,t1.c2, t0.c2 FROM t1 LEFT JOIN t0 ON (t1.c0 < t1.c2 ) WHERE ((((DEFAULT(t0.c1))OR(t0.c1))) IS NULL); +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: ((<cache>((0 <> default(t0.c1))) or (0 <> t0.c1)) is null) (cost=5.2 rows=18) (actual time=0.0569..0.0569 rows=0 loops=1) -> Nested loop left join (cost=5.2 rows=18) (actual time=0.0215..0.0529 rows=13 loops=1) -> Table scan on t1 (cost=1.15 rows=9) (actual time=0.0127..0.0174 rows=9 loops=1) -> Filter: (cast(t1.c0 as double) < cast(t1.c2 as double)) (cost=0.272 rows=2) (actual time=0.00296..0.0035 rows=0.889 loops=9) -> Covering index scan on t0 using PRIMARY (cost=0.272 rows=2) (actual time=0.00112..0.00175 rows=2 loops=9) and the first row of (t0.c1) is NULL. so this query just return an empty and wrong result.