[MDEV-3772] LP:702345 - Empty query with SUM and HAVING, no GROUP BY returns NULL instead of empty result. Created: 2011-01-13  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Timour Katchaounov (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug702345.xml    

 Description   

The following test case (for LP BUG#609121) extracted from subselect_mat.test:

create table t1 (c1 int);
create table t2 (c2 int);
insert into t1 values (1);
insert into t2 values (2);

SELECT SUM(c1) c1_sum FROM t1 WHERE c1 IN (SELECT c2 FROM t2) HAVING c1_sum;

incorrectly produces a NULL instead of an empty result.

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2011-01-14 ]

Re: Empty query with SUM and HAVING, no GROUP BY returns NULL instead of empty result.
The bug is present both with MATERIALIZATION and with IN-TO-EXISTS,
but not with SEMIJOIN:
set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off';
set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';
set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';

Comment by Timour Katchaounov (Inactive) [ 2011-01-17 ]

Re: Empty query with SUM and HAVING, no GROUP BY returns NULL instead of empty result.
Analysis:
Close to its end JOIN::optimize() assigns having to tmp_having, and
sets the having clause to NULL:

tmp_having= having;
if (select_options & SELECT_DESCRIBE)

{ error= 0; DBUG_RETURN(0); }

having= 0;

At the same time, this query detects an empty result set, and calls
return_zero_rows(), which must check the HAVING clause by:

if (having && having->val_int() == 0)
send_row=0;

However having has been already set to NULL, so return_zero_rows
doesn't check the having clause, hence the wrong result.

Solution:
There are two ways to solve this problem:
a) check join->tmp_having in addition to join->having, or
b) do not set having= 0 in JOIN::optimize.

Comment by Rasmus Johansson (Inactive) [ 2011-01-17 ]

Launchpad bug id: 702345

Generated at Thu Feb 08 06:51:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.