Re: Crash in Item_field::used_tables() with view + subquery + prepared statements
SET SESSION optimizer_switch = 'partial_match_rowid_merge=off,partial_match_table_scan=off,semijoin=off,subquery_cache=off';

CREATE TABLE `B` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_date_nokey` date DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_time_nokey` time DEFAULT NULL,
`col_datetime_key` datetime DEFAULT NULL,
`col_datetime_nokey` datetime 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_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

INSERT INTO `B` VALUES (1,1,7,'1900-01-01','1900-01-01','01:13:38','01:13:38','2005-02-05 00:00:00','2005-02-05 00:00:00','f','f');

CREATE VIEW `view_B` AS select * FROM B;

CREATE TABLE `C` (
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `C` VALUES (2,'w','w');
INSERT INTO `C` VALUES (9,'m','m');
INSERT INTO `C` VALUES (3,'m','m');
INSERT INTO `C` VALUES (9,'k','k');
INSERT INTO `C` VALUES (NULL,'r','r');
INSERT INTO `C` VALUES (9,'t','t');
INSERT INTO `C` VALUES (3,'j','j');
INSERT INTO `C` VALUES (8,'u','u');
INSERT INTO `C` VALUES (8,'h','h');
INSERT INTO `C` VALUES (53,'o','o');
INSERT INTO `C` VALUES (0,NULL,NULL);
INSERT INTO `C` VALUES (5,'k','k');
INSERT INTO `C` VALUES (166,'e','e');
INSERT INTO `C` VALUES (3,'n','n');
INSERT INTO `C` VALUES (0,'t','t');
INSERT INTO `C` VALUES (1,'c','c');
INSERT INTO `C` VALUES (9,'m','m');
INSERT INTO `C` VALUES (5,'y','y');
INSERT INTO `C` VALUES (6,'f','f');
INSERT INTO `C` VALUES (2,'d','d');

PREPARE st1 FROM "SELECT table1 .`col_int_key`
FROM C table1 JOIN view_B table2 ON table1 .`col_varchar_key`
WHERE table2 .`col_varchar_nokey` IN (
SELECT `col_varchar_key`
FROM C ) ";

EXECUTE st1;
EXECUTE st1;

Re: Crash in Item_field::used_tables() with view + subquery + prepared statements
Repeatable with maria-5.3 . Not repeatable with maria-5.3-mwl89

simplified test case:

SET SESSION optimizer_switch = 'partial_match_rowid_merge=off,partial_match_table_scan=off,semijoin=off,subquery_cache=off';

CREATE TABLE t1 ( f1 int(11), f2 varchar(1));

CREATE TABLE t2 ( f3 varchar(1));

CREATE VIEW v2 AS SELECT * FROM t2;

PREPARE st1 FROM "SELECT *
FROM t1 JOIN v2 ON t1.f2
WHERE v2.f3 IN (
SELECT f2
FROM t1 ) ";

EXECUTE st1;
EXECUTE st1;

Re: Crash in Item_field::used_tables() with view + subquery + prepared statements
If one adds rows into the tables:

insert into t1 values (2,'x'), (5,'y');
insert into t2 values ('x'), ('z');

one still have a crash on the second execution of the prepared statement:

MariaDB [test]> PREPARE st1 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MariaDB [test]> EXECUTE st1;
--------------

--------------

--------------
2 rows in set (0.01 sec)

MariaDB [test]> EXECUTE st1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Re: Crash in Item_field::used_tables() with view + subquery + prepared statements
Not repeatable in 5.3-mwl89.

The most likely reason there is no crash in mwl89 is that in mwl89
all materialization-related transformations/initializations are
repeated for each re-execution (and are not stored in PS memory).

(see LP BUG#611396 - both bugs exposes a general problem with
PS+views+materialization)

Re: Crash in Item_field::used_tables() with view + subquery + prepared statements
Test case for 5.3-mwl#89 for both non-semijoin
subquery strategies:
---------------------------------------------------------------

CREATE TABLE t1 ( f1 int(11), f2 varchar(1));
CREATE TABLE t2 ( f3 varchar(1));
insert into t1 values (2,'x'), (5,'y');
insert into t2 values ('x'), ('z');
CREATE VIEW v2 AS SELECT * FROM t2;

set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
PREPARE st1 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )";
EXECUTE st1;
EXECUTE st1;

set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';
PREPARE st2 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )";
EXECUTE st2;
EXECUTE st2;

set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off';
PREPARE st3 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )";
EXECUTE st3;
EXECUTE st3;

Re: Crash in Item_field::used_tables() with view + subquery + prepared statements
Test case pushed to 5.3.

Re: Crash in Item_field::used_tables() with view + subquery + prepared statements
The bug itself has been fixed by MWL#89.

Launchpad bug id: 612543