[MDEV-2956] LP:791761 - HAVING not observed with subquery in maria-5.3 Created: 2011-06-02  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: Minor
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug791761.xml    

 Description   

Not repeatable on maria-5.2, mysql-5.5. HAVING is not observed in the query below, so the result set contains 1 row that does not match the having.

Explain:

1 PRIMARY t1 system NULL NULL NULL NULL 1  
1 PRIMARY t2 system NULL NULL NULL NULL 1  
2 SUBQUERY t3 system NULL NULL NULL NULL 1  

test case:

CREATE TABLE t1 ( f2 int, f3 int , KEY (f3) ) ;
INSERT IGNORE INTO t1 VALUES (3,1);

CREATE TABLE t2 ( f1 int NOT NULL ) ;
INSERT IGNORE INTO t2 VALUES (29);

CREATE TABLE t3 ( f3 int) ;
INSERT INTO t3 VALUES (NULL);

SELECT MAX( t1.f3 ) AS field1
FROM t1 JOIN t2 ON t2.f1 != 0
WHERE ( SELECT f3 FROM t3 )
HAVING field1 <> 6 ;

should return an empty result, but instead returns NULL

bzr version-info:

revision-id: <email address hidden>
date: 2011-05-31 12:16:02 +0200
build-date: 2011-06-02 12:42:10 +0300
revno: 3016
branch-nick: maria-5.3

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

Re: HAVING not observed with subquery in maria-5.3
A bit more obvious example. The subquery in the WHERE clause produces a single NULL,
and is essential for the wrong result. Therefore this bug looks like an edge case.

SELECT MAX( t1.f3 ) AS field1
FROM t1 JOIN t2 ON t2.f1 != 0
WHERE ( SELECT f3 FROM t3 )
HAVING field1 is not null;
--------

field1

--------

NULL

--------

Comment by Philip Stoev (Inactive) [ 2011-06-29 ]

Re: HAVING not observed with subquery in maria-5.3
Here is an example where the subquery does not return a single null, but instead returns 1 or more rows. So , it is not such an edge case:

total optimizer switch in effect:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on

minimal optimizer switch: semijoin=off

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1
1 PRIMARY alias2 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where

test case:

SET SESSION optimizer_switch='semijoin=off';

CREATE TABLE t1 ( f1 int );
INSERT INTO t1 VALUES (0),(0);

CREATE TABLE t2 ( f1 int , KEY (f1)) ;
INSERT INTO t2 VALUES (0);

SELECT MAX( alias1.f1 ) AS field1
FROM t2 AS alias1 , t2 AS alias2
WHERE ( 3 ) IN ( SELECT f1 FROM t1 )
HAVING field1 IS NOT NULL;

Comment by Timour Katchaounov (Inactive) [ 2011-07-07 ]

Re: HAVING not observed with subquery in maria-5.3
Don't forget to check the duplicate
https://bugs.launchpad.net/maria/+bug/806955

Comment by Oleksandr Byelkin [ 2012-02-25 ]

Re: HAVING not observed with subquery in maria-5.3
In 5.2 this bug fixed as bug#938518

Comment by Rasmus Johansson (Inactive) [ 2012-02-25 ]

Launchpad bug id: 791761

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