Equivalent queries with Impossible where return different results

[Philip Stoev]

The following two equivalent queries return different results in maria 5.2 and 5.3 (and identical results in mysql 5.5.5) :

SELECT SUM( DISTINCT table1 .`pk` ) FROM B table1 STRAIGHT_JOIN ( BB table2 JOIN CC ON table2 .`col_varchar_key` ) ON table2 .`pk` ;

SELECT * FROM ( SELECT SUM( DISTINCT table1 .`pk` ) FROM B table1 STRAIGHT_JOIN ( BB table2 JOIN CC ON table2 .`col_varchar_key` ) ON table2 .`pk` );

MariaDB returns 0 on the second query and NULL on the first, whereas MySQL returns NULL on both. In MariaDB, both EXPLAIN plans agree that "Impossible WHERE noticed after reading const tables"

[Philip Stoev (pstoev-askmonty)]

Test case:

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,8,'v');
INSERT INTO `CC` VALUES (11,9,'r');
INSERT INTO `CC` VALUES (12,9,'a');
INSERT INTO `CC` VALUES (13,186,'m');
INSERT INTO `CC` VALUES (14,NULL,'y');
INSERT INTO `CC` VALUES (15,2,'j');
INSERT INTO `CC` VALUES (16,3,'d');
INSERT INTO `CC` VALUES (17,0,'z');
INSERT INTO `CC` VALUES (18,133,'e');
INSERT INTO `CC` VALUES (19,1,'h');
INSERT INTO `CC` VALUES (20,8,'b');
INSERT INTO `CC` VALUES (21,5,'s');
INSERT INTO `CC` VALUES (22,5,'e');
INSERT INTO `CC` VALUES (23,8,'j');
INSERT INTO `CC` VALUES (24,6,'e');
INSERT INTO `CC` VALUES (25,51,'f');
INSERT INTO `CC` VALUES (26,4,'v');
INSERT INTO `CC` VALUES (27,7,'x');
INSERT INTO `CC` VALUES (28,6,'m');
INSERT INTO `CC` VALUES (29,4,'c');
CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,NULL);
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,7,'f');

SELECT SUM( DISTINCT table1 .`pk` ) FROM B table1 STRAIGHT_JOIN ( BB table2 JOIN CC ON table2 .`col_varchar_key` ) ON table2 .`pk` ;
SELECT * FROM ( SELECT SUM( DISTINCT table1 .`pk` ) FROM B table1 STRAIGHT_JOIN ( BB table2 JOIN CC ON table2 .`col_varchar_key` ) ON table2 .`pk` ) AS t1;

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

It differ also in 5.1

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

NULL is correct result, because set is empty

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

Problem is that Item_sum_sum_distiinct has maybe_null set to FALSE when it can be NULL.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

In MySQL 5.5.5 it is fixed in Aggregator_distinct::setup (which is absend in 5.1)

[Igor Babaev (igorb-seattle)]

The following simple test case displays the same behavior as the test case in the submitted report.

MariaDB [test]> create table t1 (a int not null);
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> create table t2 (a int not null primary key);
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into t2 values (10);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> explain select sum(distinct t1.a) from t1,t2 where t1.a=t2.a;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> explain select * from (select sum(distinct t1.a) from t1,t2 where t1.a=t2.a) as t;
+----+-------------+------------+--------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+------------+--------+---------------+------+---------+------+------+-----------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [test]> select sum(distinct t1.a) from t1,t2 where t1.a=t2.a;
+--------------------+
| sum(distinct t1.a) |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.00 sec)

MariaDB [test]> select * from (select sum(distinct t1.a) from t1,t2 where t1.a=t2.a) as t;
+--------------------+
| sum(distinct t1.a) |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)

Both queries must return
+--------------------+
| sum(distinct t1.a) |
+--------------------+
| NULL |
+--------------------+

I've checked that absolutely the same problem we have with mysql-5.1.49 and with the current mysql-5.1 development tree.

[Stewart Smith (stewart)]

We have some slightly different output in drizzle:

main.bug_lp611379 [ fail ]
drizzletest: At line 9: query 'explain select * from (select sum(distinct t1.a) from t1,t2 where t1.a=t2.a)
as t' failed: 1048: Column 'sum(distinct t1.a)' cannot be null

but the fix gets us the correct query results, although with slightly different execution plans.

[Igor Babaev (igorb-seattle)]

The problem can be demonstrated even without any join:

MariaDB [test]> create table t3 (a int not null);
Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> insert into t3 values (3), (1), (2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [test]> select sum(distinct a) from t3 where a < 0;
+-----------------+
| sum(distinct a) |
+-----------------+
| NULL |
+-----------------+
1 row in set (0.00 sec)

MariaDB [test]> select * from (select sum(distinct a) from t3 where a < 0) as t;
+-----------------+
| sum(distinct a) |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.00 sec)