Equivalent queries with Impossible where return different results

Bug #611379 reported by Philip Stoev on 2010-07-29
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Drizzle
Medium
Stewart Smith
MariaDB
Fix Released
High
Oleksandr "Sanja" Byelkin

Bug Description

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"

Related branches

Philip Stoev (pstoev-askmonty) wrote :

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;

Changed in maria:
importance: Undecided → High
milestone: none → 5.2
milestone: 5.2 → none
milestone: none → 5.2
tags: added: regression
Michael Widenius (monty) on 2010-08-07
Changed in maria:
assignee: nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin)
Changed in maria:
status: New → In Progress

It differ also in 5.1

NULL is correct result, because set is empty

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

Changed in maria:
status: In Progress → Fix Committed
Changed in maria:
milestone: 5.2 → 5.1

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

Igor Babaev (igorb-seattle) wrote :

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) on 2010-08-12
Changed in drizzle:
status: New → Confirmed
importance: Undecided → Medium
assignee: nobody → Stewart Smith (stewart-flamingspork)
Stewart Smith (stewart) wrote :

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.

Changed in drizzle:
milestone: none → 2010-08-16
Stewart Smith (stewart) on 2010-08-12
Changed in drizzle:
status: Confirmed → Fix Committed
Igor Babaev (igorb-seattle) wrote :

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)

Stewart Smith (stewart) on 2010-08-17
Changed in drizzle:
status: Fix Committed → Fix Released
Michael Widenius (monty) on 2010-09-09
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers