Re: Valgrind failure in having.test in Item_in_subselect::init_left_expr_cache()
The branch where this happens is lp:~maria-captains/maria/5.3-merge-from-5.2 .

Re: Valgrind failure in having.test in Item_in_subselect::init_left_expr_cache()
A testcase:

--disable_warnings
DROP TABLE if exists t1, t2, t3;
--enable_warnings

CREATE TABLE t1 (f1 INT, f2 VARCHAR(1));
INSERT INTO t1 VALUES (16,'f');
INSERT INTO t1 VALUES (16,'f');
CREATE TABLE t2 (f1 INT, f2 VARCHAR(1));
INSERT INTO t2 VALUES (13,'f');
INSERT INTO t2 VALUES (20,'f');
CREATE TABLE t3 (f1 INT, f2 VARCHAR(1));
INSERT INTO t3 VALUES (7,'f');

SELECT t1.f2 FROM t1
STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2
HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
ORDER BY f2;

Re: Valgrind failure in having.test in Item_in_subselect::init_left_expr_cache()
The problem is with this piece of code:

bool Item_in_subselect::init_left_expr_cache()
{
...
/*
If we use end_[send | write]_group to handle complete rows of the outer
query, make the cache of the left IN operand use Item_field::result_field
instead of Item_field::field. We need this because normally
Cached_item_field uses Item::field to fetch field data, while
copy_ref_key() that copies the left IN operand into a lookup key uses
Item::result_field. In the case end_[send | write]_group result_field is
one row behind field.
*/
end_select= outer_join->join_tab[outer_join->tables-1].next_select;
if (end_select == end_send_group || end_select == end_write_group)
use_result_field= TRUE;

It tries to access

outer_join->join_tab[outer_join->tables-1].next_select

which is not yet set by the time we invoke the subquery. It is not yet set, because we're using the following query plan:

MariaDB [test]> explain SELECT t1.f2 FROM t1
-> STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2
-> HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
-> ORDER BY f2;
-----------------------------------------------------------------------------------------+

-----------------------------------------------------------------------------------------+

-----------------------------------------------------------------------------------------+
4 rows in set (0.03 sec)

we first do filesort() over table t1, and then join it with t2. The subquery is evaluated during filesort(), and we don't yet have set at that point.

Re: Valgrind failure in having.test in Item_in_subselect::init_left_expr_cache()
I think that the above-quoted part of code uses an approach that's in general, incorrect.

As far as I understand it, the code tries to solve the following issue:

if a subquery in HAVING/select_list/

has references to a table column TC, it t needs to look not at TC but at its image in the grouping table (aka "result field").

This wasn't coded correctly.

First,

• The above "if (end_select == ..." ignores the location of the subquery predicate. What if the subquery predicate is in the WHERE, and so is evaluated before the grouping? In that case we won't need to access the "result field", while with this code we'll still do.

Second, I don't see a reason why would Item_in_subselect::init_left_expr_cache() and co. bother at all with the question of whether they should use the "result field"? That question is abstracted away by wrapping needed items inside Item_ref(...) which does val_int() -> val_result() conversions when necessary.
I suspect that if we just removed real_item() calls from new_Cached_item(), then it would use Item_ref objects that would automatically do the right thing and we won't have any need for the above quoted "if (end_select ==..." statement.

Anybody who has an idea about this code please give some feedback.

Re: Valgrind failure and crash in having.test in Item_in_subselect::init_left_expr_cache()
This is not just a valgrind warning, but can be a full-blown crash. The unsimplified test case is pasted below and will be shortly simplified further.

--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
DROP TABLE /*! IF EXISTS */ BB;
DROP TABLE /*! IF EXISTS */ B;
--enable_warnings

CREATE TABLE `CC` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,8,NULL,'v','v');
INSERT INTO `CC` VALUES (11,9,'2006-06-14','r','r');
INSERT INTO `CC` VALUES (12,9,'2002-09-12','a','a');
INSERT INTO `CC` VALUES (13,186,'2005-02-15','m','m');
INSERT INTO `CC` VALUES (14,NULL,NULL,'y','y');
INSERT INTO `CC` VALUES (15,2,'2008-11-04','j','j');
INSERT INTO `CC` VALUES (16,3,'2004-09-04','d','d');
INSERT INTO `CC` VALUES (17,0,'2006-06-05','z','z');
INSERT INTO `CC` VALUES (18,133,'1900-01-01','e','e');
INSERT INTO `CC` VALUES (19,1,'1900-01-01','h','h');
INSERT INTO `CC` VALUES (20,8,'1900-01-01','b','b');
INSERT INTO `CC` VALUES (21,5,'2005-01-13','s','s');
INSERT INTO `CC` VALUES (22,5,'2006-05-21','e','e');
INSERT INTO `CC` VALUES (23,8,'2003-09-08','j','j');
INSERT INTO `CC` VALUES (24,6,'2006-12-23','e','e');
INSERT INTO `CC` VALUES (25,51,'2006-10-15','f','f');
INSERT INTO `CC` VALUES (26,4,'2005-04-06','v','v');
INSERT INTO `CC` VALUES (27,7,'2008-04-07','x','x');
INSERT INTO `CC` VALUES (28,6,'2006-10-10','m','m');
INSERT INTO `CC` VALUES (29,4,'1900-01-01','c','c');
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,2,NULL,'w','w');
INSERT INTO `C` VALUES (2,9,'2001-09-19','m','m');
INSERT INTO `C` VALUES (3,3,'2004-09-12','m','m');
INSERT INTO `C` VALUES (4,9,NULL,'k','k');
INSERT INTO `C` VALUES (5,NULL,'2002-07-19','r','r');
INSERT INTO `C` VALUES (6,9,'2002-12-16','t','t');
INSERT INTO `C` VALUES (7,3,'2006-02-08','j','j');
INSERT INTO `C` VALUES (8,8,'2006-08-28','u','u');
INSERT INTO `C` VALUES (9,8,'2001-04-14','h','h');
INSERT INTO `C` VALUES (10,53,'2000-01-05','o','o');
INSERT INTO `C` VALUES (11,0,'2003-12-06',NULL,NULL);
INSERT INTO `C` VALUES (12,5,'1900-01-01','k','k');
INSERT INTO `C` VALUES (13,166,'2002-11-27','e','e');
INSERT INTO `C` VALUES (14,3,NULL,'n','n');
INSERT INTO `C` VALUES (15,0,'2003-05-27','t','t');
INSERT INTO `C` VALUES (16,1,'2005-05-03','c','c');
INSERT INTO `C` VALUES (17,9,'2001-04-18','m','m');
INSERT INTO `C` VALUES (18,5,'2005-12-27','y','y');
INSERT INTO `C` VALUES (19,6,'2004-08-20','f','f');
INSERT INTO `C` VALUES (20,2,'1900-01-01','d','d');
CREATE TABLE `BB` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,'2002-02-21',NULL,NULL);
CREATE TABLE `B` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,7,'1900-01-01','f','f');

SELECT table1 . `col_date_key` AS field1
FROM ( BB AS table1 LEFT JOIN ( ( (
SELECT SUBQUERY1_t1 . *
FROM ( C AS SUBQUERY1_t1 INNER JOIN CC AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUERY1_t1 . `pk` ) )
WHERE SUBQUERY1_t2 . `col_varchar_key` <= (
SELECT COUNT( CHILD_SUBQUERY1_t1 . `col_varchar_nokey` ) AS CHILD_SUBQUERY1_field1
FROM ( CC AS CHILD_SUBQUERY1_t1 STRAIGHT_JOIN C AS CHILD_SUBQUERY1_t2 ON (CHILD_SUBQUERY1_t2 . `col_int_key` = CHILD_SUBQUERY1_t1 . `col_int_key` ) )
WHERE CHILD_SUBQUERY1_t2 . `col_int_key` < CHILD_SUBQUERY1_t2 . `col_int_key` ) ) AS table2 INNER JOIN (
SELECT SUBQUERY2_t1 . *
FROM CC AS SUBQUERY2_t1 ) AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_varchar_key` = table2 . `col_varchar_nokey` ) )
WHERE ( ( 5, 3 ) NOT IN (
SELECT DISTINCT SUBQUERY3_t1 . `col_int_key` AS SUBQUERY3_field1 , SUM( SUBQUERY3_t2 . `pk` ) AS SUBQUERY3_field2
FROM ( B AS SUBQUERY3_t1 LEFT OUTER JOIN BB AS SUBQUERY3_t2 ON (SUBQUERY3_t2 . `col_varchar_key` = SUBQUERY3_t1 . `col_varchar_key` AND SUBQUERY3_t2 . `pk` NOT IN (
SELECT DISTINCT CHILD_SUBQUERY2_t1 . `pk` AS CHILD_SUBQUERY2_field1
FROM ( CC AS CHILD_SUBQUERY2_t1 LEFT OUTER JOIN C AS CHILD_SUBQUERY2_t2 ON (CHILD_SUBQUERY2_t2 . `col_int_key` = CHILD_SUBQUERY2_t1 . `pk` ) )
WHERE CHILD_SUBQUERY2_t1 . `col_varchar_nokey` = 'v'
GROUP BY child_subquery2_field1 ) ) ) ) ) AND ( table3 . `col_varchar_key` <> 'k' OR table1 . `col_varchar_key` <= table3 . `col_varchar_key` )
GROUP BY field1
;

DROP TABLE CC;
DROP TABLE C;
DROP TABLE BB;
DROP TABLE B;

Re: Valgrind failure and crash in having.test in Item_in_subselect::init_left_expr_cache()
Automatically simplified test case. Further manual simplification, e.g. replacing the two-field IN with a single-field IN results in bug #600958

--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ BB;
DROP TABLE /*! IF EXISTS */ B;
--enable_warnings

CREATE TABLE `CC` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,8,NULL,'v');
INSERT INTO `CC` VALUES (11,9,'2006-06-14','r');
INSERT INTO `CC` VALUES (12,9,'2002-09-12','a');
INSERT INTO `CC` VALUES (13,186,'2005-02-15','m');
INSERT INTO `CC` VALUES (14,NULL,NULL,'y');
INSERT INTO `CC` VALUES (15,2,'2008-11-04','j');
INSERT INTO `CC` VALUES (16,3,'2004-09-04','d');
INSERT INTO `CC` VALUES (17,0,'2006-06-05','z');
INSERT INTO `CC` VALUES (18,133,'1900-01-01','e');
INSERT INTO `CC` VALUES (19,1,'1900-01-01','h');
INSERT INTO `CC` VALUES (20,8,'1900-01-01','b');
INSERT INTO `CC` VALUES (21,5,'2005-01-13','s');
INSERT INTO `CC` VALUES (22,5,'2006-05-21','e');
INSERT INTO `CC` VALUES (23,8,'2003-09-08','j');
INSERT INTO `CC` VALUES (24,6,'2006-12-23','e');
INSERT INTO `CC` VALUES (25,51,'2006-10-15','f');
INSERT INTO `CC` VALUES (26,4,'2005-04-06','v');
INSERT INTO `CC` VALUES (27,7,'2008-04-07','x');
INSERT INTO `CC` VALUES (28,6,'2006-10-10','m');
INSERT INTO `CC` VALUES (29,4,'1900-01-01','c');
CREATE TABLE `BB` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,'2002-02-21',NULL);
CREATE TABLE `B` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,7,'1900-01-01','f');

SELECT `col_date_key`
FROM BB
WHERE ( 5 , 3 ) IN (
SELECT SUBQUERY3_t1 .`col_int_key` , SUM( SUBQUERY3_t2 .`pk` )
FROM B SUBQUERY3_t1 LEFT JOIN BB SUBQUERY3_t2 ON SUBQUERY3_t2 .`col_varchar_key` = SUBQUERY3_t1 .`col_varchar_key` AND SUBQUERY3_t2 .`pk` IN (
SELECT `pk` CHILD_SUBQUERY2_field1
FROM CC
GROUP BY child_subquery2_field1 ) ) ;

Re: Valgrind failure and crash in having.test in Item_in_subselect::init_left_expr_cache()
The valgrind warning part has been fixed as part of 5.2->5.3 merge.

The RQG testcase probably wasn't (didn't check), as it is a manifestation of "outer join + semi-join" problem.

Re: Valgrind failure and crash in having.test in Item_in_subselect::init_left_expr_cache()
The bug is not reproducible with any combination of subquery-related optimizer switches.
Tried with the latest 5.3 debug build as of 14-06-2011, tip:
------------------------------------------------------------
revno: 3041
committer: Sergey Petrunya <psergey@askmonty.org>
branch nick: 5.3-push3
timestamp: Mon 2011-06-13 12:41:19 +0400
message:
Remove redundant code that is a result of a wrong merge.
(Changeset sp1r-igor@olga.mysql.com-20070526173301-38848 moved this loop from one place
to another, then the merge of sp1r-gshchepa/uchum@gleb.loc-20070527192244-26330 have
kept both copies).
------------------------------------------------------------

Launchpad bug id: 598972