[#MDEV-6294] [5.1-5.3] Wrong result (extra rows) with subquery in HAVING clause

It's an upstream bug, it was introduced by a 5.1 merge, particularly by the following revision:

revno: 4005

revision-id: sergey.glukhov@oracle.com-20130507091058-x4li5jat6pvhop96

parent: annamalai.gurusami@oracle.com-20130506105856-d5il8vpqcovru47g

committer: Sergey Glukhov <sergey.glukhov@oracle.com>

branch nick: mysql-5.1

timestamp: Tue 2013-05-07 13:10:58 +0400

message:

  Bug#16095534 CRASH: PREPARED STATEMENT CRASHES IN ITEM_BOOL_FUNC2::FIX_LENGTH_AND_DEC

  The problem happened due to broken left expression in Item_in_optimizer object.

  In case of the bug left expression is runtime created Item_outer_ref item which

  is deleted at the end of the statement and one of Item_in_optimizer arguments

  becomes bad when re-executed. The fix is to use real_item() instead of original

  left expression. Note: It feels a bit weird that after preparing, the field is

  directly part of the generated Item_func_eq, whereas in execution it is replaced

  with an Item_outer_ref wrapper object.

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (8),(3);

SELECT a FROM t1 GROUP BY a HAVING a IN ( SELECT 3 );

Reproducible on MySQL 5.1.73, 5.5.38.
Not reproducible on MySQL 5.6.17.
Reproducible on current MariaDB 5.1 (revno 3221), 5.2 (revno 3233), 5.3 (revno 3788).
Not reproducible on current MariaDB 5.5 (revno 4192) and 10.0 (revno 4212).

[MDEV-6294] [5.1-5.3] Wrong result (extra rows) with subquery in HAVING clause Created: 2014-06-02 Updated: 2018-07-10 Resolved: 2018-07-10
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	N/A
Fix Version/s:	5.1.73, 5.2.15, 5.3.13, N/A

[MDEV-6294] [5.1-5.3] Wrong result (extra rows) with subquery in HAVING clause Created: 2014-06-02 Updated: 2018-07-10 Resolved: 2018-07-10