Wrong empty result for MIN/MAX without GROUP BY filtered by an empty subquery

Bug #702301 reported by Timour Katchaounov
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
High
Igor Babaev

Bug Description

The following test case subselect_mat.test:
"BUG#37896 Assertion on entry of Item_in_subselect::exec on subquery with AND NOT"
produces an incorrect empty result for MIN/MAX, while it should produce a NULL
according to the SQL standard.

Changed in maria:
milestone: none → 5.3
importance: Undecided → High
assignee: nobody → Timour Katchaounov (timour)
status: New → Confirmed
Revision history for this message
Timour Katchaounov (timour) wrote :

This bug is also present in the latest MySQL server as:
http://bugs.mysql.com/bug.php?id=40037

Revision history for this message
Timour Katchaounov (timour) wrote :

Test case extracted from subselect_mat.test:

create table t1 (a1 int key);
create table t2 (b1 int);
insert into t1 values (5);
-- echo Only the last query returns correct result. Filed as BUG#40037.
# Query with group by, executed via materialization
set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
# Query with group by, executed via IN=>EXISTS
set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';
explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
select min(a1) from t1 where 7 in (select b1 from t2 group by b1);

# Executed with materialization
set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
explain select min(a1) from t1 where 7 in (select b1 from t2);
select min(a1) from t1 where 7 in (select b1 from t2);
# Executed via IN=>EXISTS
set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';
explain select min(a1) from t1 where 7 in (select b1 from t2);
select min(a1) from t1 where 7 in (select b1 from t2);
# Executed with semi-join. Notice, this time we get a different result (NULL).
# This is the only correct result of all five queries. This difference is
# filed as BUG#40037.
set @@optimizer_switch='materialization=off,in_to_exists=off,semijoin=on';
explain select min(a1) from t1 where 7 in (select b1 from t2);
select min(a1) from t1 where 7 in (select b1 from t2);
drop table t1,t2;

Changed in maria:
importance: High → Medium
Revision history for this message
Igor Babaev (igorb-seattle) wrote :

The following simple test case constructed by Timour demonstrates this problem:

CREATE TABLE t1 (a int, b int, KEY (b));
INSERT INTO t1 VALUES (3,1), (4,2);
CREATE TABLE t2 (a int);
INSERT INTO t2 VALUES (7), (8);

MariaDB [test]> SELECT MAX(t1.b) AS max_res FROM t1 WHERE (9) IN (SELECT a FROM t2)
Empty set (0.00 sec)

Changed in maria:
importance: Medium → High
status: Confirmed → In Progress
assignee: Timour Katchaounov (timour) → Igor Babaev (igorb-seattle)
Changed in maria:
status: In Progress → Fix Committed
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.