MySQL Bugs: #21853: Query with "ALL" keyworkd doesnt return what's expected

Bug #21853	Query with "ALL" keyworkd doesnt return what's expected
Submitted:	27 Aug 2006 20:36	Modified:	5 Oct 2006 16:57
Reporter:	Lucas Roman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0	OS:	Linux (Linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	ALL, subquery

Description:
When i use an ">= ALL" keyword followed by a subquery, it doesn't return what's expected when i try to filter the inner query based on the outer query. I just can't figure why it's returning those values but i know it's wrong, and the cause is probably the WHERE condition inside the subquery

The query is:

SELECT o.field1, o.field2
FROM outertable o
GROUP BY o.field1, o.field2
HAVING COUNT(*) >= ALL (
	SELECT fieldB FROM innerTable i WHERE i.fieldA = o.field1
);

This is a simplified version of a longer query i was using.

It should make groups for table "outertable " having the same values in field 1 and 2. Then, when the HAVING condition is met, it should return only the groups for which the count of records that were used to make the group is greater than all the values of the subquery. 

Unfortunately, that is not happening
See the "how to repeat" section to see what happens

How to repeat:
I have the following 2 tables:
outertable  CREATE TABLE `outertable` (                  
              `field1` int(11) NOT NULL,                 
              `field2` int(11) NOT NULL,                 
              `field3` int(11) NOT NULL,                 
              PRIMARY KEY  (`field1`,`field2`,`field3`)  
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1       
innertable  CREATE TABLE `innertable` (             
              `fieldA` int(11) NOT NULL,            
              `fieldB` int(11) NOT NULL,            
              PRIMARY KEY  (`fieldA`,`fieldB`)      
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1  

2 simple tables, with all int fields. The test data in them is:

/*INNER TABLE */
insert into `innertable` values (1,1);
insert into `innertable` values (1,2);
insert into `innertable` values (1,3);
/*OUTER TABLE */
insert into `outertable` values (1,1,1);
insert into `outertable` values (1,1,2);
insert into `outertable` values (1,2,1);
insert into `outertable` values (1,2,2);
insert into `outertable` values (1,2,3);
insert into `outertable` values (1,3,1);

If you just run the query without the having condition to check the value for COUNT(*) for each group, that is
SELECT o.field1, o.field2, COUNT(*)
FROM outertable o
GROUP BY o.field1, o.field2

It returns: 
1 1 2
1 2 3
1 3 1

The inner query -- SELECT fieldB FROM innerTable i WHERE i.fieldA = o.field1 --, will always return all the values of fieldB from "innerTable". That's because i.fieldA will always be equalled to 1, since o.field1 is 1 for every group.

The COUNT(*) of each group should then be greater or equal than 3, which is the maximum value for fieldB in the innerTable. So, the original query should return  field1 and field2 of the second group, the only one to pass the HAVING condition

However, the result of: 
SELECT o.field1, o.field2
FROM outertable o
GROUP BY o.field1, o.field2
HAVING COUNT(*) >= ALL (
	SELECT fieldB FROM innerTable i WHERE i.fieldA = o.field1
);

is: field1 = 1, field2 = 3

I have no clue why it returns that
But since this example is very simple, a simpler version of what i was working with, i constructed the test case so that you could take the WHERE clause on the subquery. That WHERE subclause shouldnt influence the results because all "fieldA" values for table "innertable" had the value "1", and all the "field1" values for table "outertable" had the value "1" also. However, if i take the condition and run the query 

SELECT o.field1, o.field2
FROM outertable o
GROUP BY o.field1, o.field2
HAVING COUNT(*) >= ALL (
	SELECT fieldB FROM innerTable i 
);

The result is now field1 = 1 and field2 = 2, which is correct
However, i could only do this because al "fieldA" in innertable had the same values, in the real case i cant do that

Suggested fix:
I'm working around this problem changing the query to be:
SELECT o.field1, o.field2
FROM outertable o
GROUP BY o.field1, o.field2
HAVING COUNT(*) >=  (
	SELECT fieldB FROM innerTable i WHERE i.fieldA = o.field1
	ORDER BY 1 DESC LIMIT 1
);

So, instead of making it to be greater to ALL the values of the inner query, i sort the inner query descendent and limit to 1 result, so i can get the highest value

Thank you for the report.

Verified as described on Linux using last BK sources build with help of BUILD/compile-pentium script.

Debug build crashes (item_subselect.cc:708: virtual String* Item_in_subselect::val_str(String*): Assertion `0' failed.):

mysql> SELECT o.field1, o.field2
    -> FROM outertable o
    -> GROUP BY o.field1, o.field2
    -> HAVING COUNT(*) >= ALL (
    -> SELECT fieldB FROM innertable i WHERE i.fieldA = o.field1
    -> );
ERROR 2013 (HY000): Lost connection to MySQL server during query

Error log content:

mysqld: item_subselect.cc:708: virtual String* Item_in_subselect::val_str(String*): Assertion `0' failed.
mysqld got signal 6;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16384
read_buffer_size=258048
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 31612 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x9d21b00
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb1078b48, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x82105fa
0x2ce420
0xb1078f88
0x30e269
0x81d9680
0x81797ca
0x8286292
0x828686c
0x827b942
0x827bcd9
0x827c02f
0x828f8a2
0x828fc1a
0x828ff28
0x822d573
0x823610d
0x8236c38
0x82381f3
0x82385fc
0x45fbd4
0x3b74fe
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x9d38428 = SELECT o.field1, o.field2
FROM outertable o
GROUP BY o.field1, o.field2
HAVING COUNT(*) >= ALL (
SELECT fieldB FROM innertable i WHERE i.fieldA = o.field1
)
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12467

ChangeSet@1.2572, 2006-09-25 05:24:07-07:00, igor@rurik.mysql.com +3 -0
  Fixed bug #21853: assert failure for a grouping query with
  an ALL/ANY quantified subquery in HAVING.
  The Item::split_sum_func2 method should not create Item_ref
  for objects of any class derived from Item_subselect.

Pushed in 4.1.22/5.0.26/5.1.12

Noted in 4.1.22, 5.0.26, 5.1.12 changelogs.

A query that used GROUP BY and an ALL or ANY quantified subquery in a
HAVING clause could trigger an assertion failure.