create table t1(a int);
insert into t1 values(1),(2);
with qn(a) as (select 1 from t1 limit 2)
select * from qn where qn.a=(select * from qn qn1 limit 1) union select 2;
a
1
2
drop table t1;
#
# Bug#23637091 WL883: SIGNAL 11 IN ACTUAL_KEY_PARTS AT SQL/SQL_SELECT.CC
#
CREATE TABLE c (
col_date date DEFAULT NULL,
col_time_key time DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_time time DEFAULT NULL,
col_datetime datetime DEFAULT NULL,
col_blob_key blob,
pk int(11) NOT NULL AUTO_INCREMENT,
col_blob blob,
col_int_key int(11) DEFAULT NULL,
col_int int(11) DEFAULT NULL,
col_varchar varchar(1) DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_time_key (col_time_key),
KEY col_datetime_key (col_datetime_key),
KEY col_date_key (col_date_key),
KEY col_blob_key (col_blob_key(255)),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE cc (
col_time time DEFAULT NULL,
col_blob blob,
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
col_date date DEFAULT NULL,
col_int int(11) DEFAULT NULL,
col_blob_key blob,
col_datetime datetime DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
col_time_key time DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_varchar varchar(1) DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key),
KEY col_blob_key (col_blob_key(255)),
KEY col_time_key (col_time_key),
KEY col_datetime_key (col_datetime_key),
KEY col_date_key (col_date_key)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE bb (
col_varchar varchar(1) DEFAULT NULL,
col_blob_key blob,
pk int(11) NOT NULL AUTO_INCREMENT,
col_date date DEFAULT NULL,
col_int int(11) DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_blob blob,
col_int_key int(11) DEFAULT NULL,
col_datetime datetime DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_time_key time DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
col_time time DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_blob_key (col_blob_key(255)),
KEY col_datetime_key (col_datetime_key),
KEY col_int_key (col_int_key),
KEY col_date_key (col_date_key),
KEY col_time_key (col_time_key),
KEY col_varchar_key (col_varchar_key)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE OR REPLACE VIEW view_c AS SELECT * FROM c;
EXPLAIN WITH cte AS (
SELECT alias1 . col_time_key AS field1
FROM  cc AS alias1  LEFT OUTER JOIN view_c AS alias2
ON  alias1 . col_varchar_key =  alias2 . col_blob_key
WHERE  alias2 . col_varchar_key >= 'n'
ORDER BY field1
LIMIT 1000 OFFSET 9)
DELETE  FROM outr1.*, outr2.*
USING c AS outr1 RIGHT OUTER JOIN c AS outr2
ON ( outr1 . col_blob_key = outr2 . col_blob )
RIGHT JOIN cte AS outrcte
ON outr2 . col_blob = outrcte.field1
WHERE outr1 . col_blob_key <>
(
SELECT
DISTINCT innr1 . col_blob AS y
FROM bb AS innr1 LEFT JOIN cte AS innrcte
ON innr1.pk <> innrcte.field1
);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
3	SUBQUERY	innr1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using temporary
3	SUBQUERY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Distinct; Using join buffer (hash join)
2	DERIVED	alias1	NULL	ALL	col_varchar_key	NULL	NULL	NULL	1	100.00	Using filesort
2	DERIVED	c	NULL	ref	col_blob_key,col_varchar_key	col_blob_key	258	test.alias1.col_varchar_key	1	100.00	Using where
Warnings:
Warning	1739	Cannot use ref access on index 'col_varchar_key' due to type or collation conversion on field 'col_varchar_key'
Warning	1739	Cannot use ref access on index 'col_blob_key' due to type or collation conversion on field 'col_blob_key'
Warning	1739	Cannot use ref access on index 'col_varchar_key' due to type or collation conversion on field 'col_varchar_key'
Note	1003	with `cte` as (/* select#2 */ select `test`.`alias1`.`col_time_key` AS `field1` from `test`.`cc` `alias1` join `test`.`c` where ((`test`.`c`.`col_varchar_key` >= 'n') and (`test`.`alias1`.`col_varchar_key` = `test`.`c`.`col_blob_key`)) order by `field1` limit 9,1000) delete `test`.`c`, `test`.`c` from `cte` `outrcte` join `test`.`c` `outr2` join `test`.`c` `outr1` where ((`test`.`outr1`.`col_blob_key` <> (/* select#3 */ select distinct `test`.`innr1`.`col_blob` AS `y` from `test`.`bb` `innr1` left join `cte` `innrcte` on((cast(`test`.`innr1`.`pk` as double) <> cast(`innrcte`.`field1` as double))) where true)) and (`test`.`outr2`.`col_blob` = `outrcte`.`field1`) and multiple equal(`test`.`outr1`.`col_blob_key`, `test`.`outr2`.`col_blob`))
DROP VIEW view_c;
DROP TABLE bb,cc,c;
#
# Bug#23638702 WL883: SIGNAL 11 IN JOIN::EXTRACT_FUNC_DEPENDENT_TABLES AT SQL/SQL_OPTIMIZER.CC
#
CREATE TABLE d (
col_int int(11) DEFAULT NULL,
col_time_key time DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
col_datetime_key datetime DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_blob_key blob,
col_varchar_key varchar(1) DEFAULT NULL,
col_varchar varchar(1) DEFAULT NULL,
col_time time DEFAULT NULL,
col_blob blob,
col_date date DEFAULT NULL,
col_datetime datetime DEFAULT NULL,
col_date_key date DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_time_key (col_time_key),
KEY col_datetime_key (col_datetime_key),
KEY col_int_key (col_int_key),
KEY col_blob_key (col_blob_key(255)),
KEY col_varchar_key (col_varchar_key),
KEY col_date_key (col_date_key)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE dd (
col_datetime datetime DEFAULT NULL,
col_varchar varchar(1) DEFAULT NULL,
col_blob_key blob,
col_date date DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_blob blob,
col_varchar_key varchar(1) DEFAULT NULL,
col_int int(11) DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_time time DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
col_time_key time DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_blob_key (col_blob_key(255)),
KEY col_datetime_key (col_datetime_key),
KEY col_varchar_key (col_varchar_key),
KEY col_date_key (col_date_key),
KEY col_time_key (col_time_key),
KEY col_int_key (col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
WITH cte AS (
SELECT alias1 . col_int AS field1
FROM  dd AS alias1  LEFT  JOIN d AS alias2
ON  alias1 . col_blob =  alias2 . col_blob_key
WHERE  alias2 . col_varchar_key  IN ('m')
ORDER BY field1  LIMIT 10)
DELETE IGNORE FROM outr2.*
USING dd AS outr1 JOIN d AS outr2
ON ( outr1 . col_blob = outr2 . col_varchar )
JOIN d AS outr3
ON ( outr1 . pk = outr3 . col_int )
LEFT OUTER JOIN cte AS outrcte
ON outr2 . pk = outrcte.field1
WHERE outrcte . field1 = (
SELECT  innr1 . col_int AS y
FROM dd AS innr2 RIGHT OUTER JOIN dd AS innr1
ON ( innr2 . col_date > innr1 . col_date )
INNER JOIN cte AS innrcte
ON innr2.col_int_key <= innrcte.field1
WHERE innr1 . col_varchar_key <= 'u'
ORDER BY innr1 . col_int_key );
DROP TABLE d,dd;
#
# Bug#23638534 WL883: INNODB: ASSERTION FAILURE: DICT0DICT.CC:2667:!DICT_INDEX_IS_CLUST(INDEX)
#
CREATE TABLE d (
col_int int(11) DEFAULT NULL,
col_time_key time DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
col_datetime_key datetime DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_blob_key blob,
col_varchar_key varchar(1) DEFAULT NULL,
col_varchar varchar(1) DEFAULT NULL,
col_time time DEFAULT NULL,
col_blob blob,
col_date date DEFAULT NULL,
col_datetime datetime DEFAULT NULL,
col_date_key date DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_time_key (col_time_key),
KEY col_datetime_key (col_datetime_key),
KEY col_int_key (col_int_key),
KEY col_blob_key (col_blob_key(255)),
KEY col_varchar_key (col_varchar_key),
KEY col_date_key (col_date_key)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE OR REPLACE VIEW view_d AS SELECT * FROM d;
CREATE TABLE dd (
col_datetime datetime DEFAULT NULL,
col_varchar varchar(1) DEFAULT NULL,
col_blob_key blob,
col_date date DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_blob blob,
col_varchar_key varchar(1) DEFAULT NULL,
col_int int(11) DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_time time DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
col_time_key time DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_blob_key (col_blob_key(255)),
KEY col_datetime_key (col_datetime_key),
KEY col_varchar_key (col_varchar_key),
KEY col_date_key (col_date_key),
KEY col_time_key (col_time_key),
KEY col_int_key (col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE OR REPLACE VIEW view_dd AS SELECT * FROM dd;
EXPLAIN WITH cte AS (
SELECT
alias1 . col_date AS field1 ,
alias1 . col_blob AS field2 ,
alias1 . pk AS field3 ,
alias1 . pk AS field4
FROM  dd AS alias1  LEFT  JOIN d AS alias2
ON  alias1 . col_varchar_key =  alias2 . col_varchar_key
WHERE  alias2 . pk != 0
OR  alias2 . col_varchar_key >= 'v'
ORDER BY field4 )
DELETE /*+ NO_MERGE(outrcte) */  outr2.*
FROM d AS outr1 JOIN d AS outr2
ON ( outr1 . col_datetime_key = outr2 . col_date )
JOIN cte AS outrcte
ON outr1 . pk = outrcte.field1
WHERE outr1 . col_int_key = (
SELECT  innr1 . col_int AS y
FROM dd AS innr1 INNER JOIN cte AS innrcte
ON innr1.col_int_key = innrcte.field1
WHERE innr1 . col_blob_key = 'h'
ORDER BY innr1 . col_varchar );
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
3	SUBQUERY	innr1	NULL	ref	col_blob_key,col_int_key	col_blob_key	258	const	1	100.00	Using where
3	SUBQUERY	<derived2>	NULL	ref	<auto_key0>	<auto_key0>	4	test.innr1.col_int_key	2	100.00	Using where
2	DERIVED	alias1	NULL	index	col_varchar_key	PRIMARY	4	NULL	1	100.00	NULL
2	DERIVED	alias2	NULL	ref	PRIMARY,col_varchar_key	col_varchar_key	4	test.alias1.col_varchar_key	1	100.00	Using where; Using index
Warnings:
Note	1003	with `cte` as (/* select#2 */ select `test`.`alias1`.`col_date` AS `field1`,`test`.`alias1`.`col_blob` AS `field2`,`test`.`alias1`.`pk` AS `field3`,`test`.`alias1`.`pk` AS `field4` from `test`.`dd` `alias1` join `test`.`d` `alias2` where ((`test`.`alias2`.`col_varchar_key` = `test`.`alias1`.`col_varchar_key`) and ((`test`.`alias2`.`pk` <> 0) or (`test`.`alias1`.`col_varchar_key` >= 'v'))) order by `field4`) delete /*+ NO_MERGE(`outrcte`@`select#1`) */ `test`.`d` from `test`.`d` `outr1` join `test`.`d` `outr2` join `cte` `outrcte` where ((`test`.`outr1`.`pk` = `outrcte`.`field1`) and (`test`.`outr1`.`col_datetime_key` = `test`.`outr2`.`col_date`) and multiple equal((/* select#3 */ select `test`.`innr1`.`col_int` AS `y` from `test`.`dd` `innr1` join `cte` `innrcte` where ((`test`.`innr1`.`col_blob_key` = 'h') and (cast(`test`.`innr1`.`col_int_key` as double) = cast(`innrcte`.`field1` as double)))), `test`.`outr1`.`col_int_key`))
EXPLAIN WITH cte AS (
SELECT
alias1 . col_date AS field1 ,
alias2 . col_blob AS field2
FROM  view_d AS alias1  LEFT  JOIN view_dd AS alias2
ON  alias1 . col_blob_key =  alias2 . col_blob_key
WHERE  alias1 . col_varchar_key IS  NULL
AND  alias2 . col_int_key > 6
AND alias2 . col_int_key <= ( 7 + 5 )
OR alias1 . col_blob_key >= 'a' )
DELETE /*+ MERGE(outrcte) */
FROM outr1.*, outr2.*
USING d AS outr1 LEFT OUTER JOIN d AS outr2
ON ( outr1 . col_date = outr2 . col_date )
JOIN cte AS outrcte
ON outr2 . col_blob_key = outrcte.field1
WHERE outr1 . col_blob  IN
( SELECT DISTINCT innr1 . col_blob_key AS y
FROM dd AS innr1 RIGHT OUTER JOIN cte AS innrcte
ON innr1.pk >= innrcte.field1
WHERE outr1 . col_int_key > 6  );
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	d	NULL	ALL	col_blob_key,col_varchar_key	NULL	NULL	NULL	1	100.00	Using where
1	DELETE	outr2	NULL	ALL	col_blob_key	NULL	NULL	NULL	1	100.00	Using where
1	DELETE	outr1	NULL	range	col_int_key	col_int_key	5	NULL	1	100.00	Using where
1	PRIMARY	dd	NULL	ref	col_blob_key	col_blob_key	258	test.d.col_blob_key	1	100.00	Using where
1	PRIMARY	innr1	NULL	ref	PRIMARY,col_blob_key	col_blob_key	258	test.outr1.col_blob	1	100.00	Using where
1	PRIMARY	<derived4>	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where; FirstMatch(dd)
4	DERIVED	d	NULL	ALL	col_blob_key,col_varchar_key	NULL	NULL	NULL	1	100.00	Using where
4	DERIVED	dd	NULL	ref	col_blob_key	col_blob_key	258	test.d.col_blob_key	1	100.00	Using where
Warnings:
Note	1276	Field or reference 'test.outr1.col_int_key' of SELECT #3 was resolved in SELECT #1
Warning	1739	Cannot use ref access on index 'col_blob_key' due to type or collation conversion on field 'col_blob_key'
Warning	1739	Cannot use range access on index 'col_blob_key' due to type or collation conversion on field 'col_blob_key'
Note	1003	with `cte` as (/* select#4 */ select `test`.`d`.`col_date` AS `field1`,`test`.`dd`.`col_blob` AS `field2` from `test`.`d` left join (`test`.`dd`) on((`test`.`dd`.`col_blob_key` = `test`.`d`.`col_blob_key`)) where (((`test`.`d`.`col_varchar_key` is null) and (`test`.`dd`.`col_int_key` > 6) and (`test`.`dd`.`col_int_key` <= <cache>((7 + 5)))) or (`test`.`d`.`col_blob_key` >= 'a'))) delete /*+ MERGE(`outrcte`@`select#1`) */ `test`.`d`, `test`.`d` from `test`.`d` `outr1` join `test`.`d` `outr2` join `test`.`d` left join (`test`.`dd`) on((`test`.`dd`.`col_blob_key` = `test`.`d`.`col_blob_key`)) semi join (`cte` `innrcte` join `test`.`dd` `innr1`) where ((`test`.`outr1`.`col_date` = `test`.`outr2`.`col_date`) and (`test`.`innr1`.`col_blob_key` = `test`.`outr1`.`col_blob`) and (`test`.`outr1`.`col_int_key` > 6) and (cast(`test`.`innr1`.`pk` as double) >= cast(`innrcte`.`field1` as double)) and (cast(`test`.`outr2`.`col_blob_key` as date) = `test`.`d`.`col_date`) and (((`test`.`d`.`col_varchar_key` is null) and (`test`.`dd`.`col_int_key` > 6) and (`test`.`dd`.`col_int_key` <= <cache>((7 + 5)))) or (`test`.`d`.`col_blob_key` >= 'a')))
DROP TABLE d,dd;
DROP VIEW view_d,view_dd;
#
# Syntax which wasn't accepted
#
create table t1 (i int);
with cte as (select * from t1) (select * from cte);
i
with cte as (select * from t1) (select * from cte) ORDER BY i;
i
with cte as (select * from t1) (select * from cte) LIMIT 1;
i
with cte as (select * from t1) (select * from cte UNION select * from cte);
i
with cte as (select * from t1) (select * from cte UNION select * from cte) ORDER BY i;
i
with cte as (select * from t1) (select * from cte UNION select * from cte) LIMIT 1;
i
drop table t1;
#
# Bug#23715776 WL883: SIGNAL 11 IN HP_RB_RECORDS_IN_RANGE AT STORAGE/HEAP/HP_HASH.C
#
CREATE TABLE b (
col_time_key time,
col_int_key int,
col_varchar_key varchar (1),
col_date date,
col_blob blob,
col_datetime_key datetime,
col_date_key date,
col_time time,
col_varchar varchar (1),
col_blob_key blob,
col_datetime datetime,
pk integer auto_increment,
col_int int,
/*Indices*/
key (col_time_key ),
key (col_int_key ),
key (col_varchar_key ),
key (col_datetime_key ),
key (col_date_key ),
key (col_blob_key  (255)),
primary key (pk)) ENGINE=innodb;
CREATE VIEW view_b AS SELECT * FROM b;
INSERT /*! IGNORE */ INTO b VALUES  ('2005-05-12 20:15:58.052785', 6, 'h',
'20071120175445.006011', 'fvqjpbenl', '2008-08-28', '2003-08-01', '2009-01-10 04:04:37.053602', 's', 'vqjpbenltgiontlibvmp', '2004-02-19 08:13:58.032936',
NULL, 4) ,  ('20071118112941.009031', 9, 'e', '20080508112705.046045',
'qjpbenltg', '20000122221334.050982', '20020316014521.034828',
'20070409063441.029820', NULL, NULL, '2002-06-23 04:05:27.001486', NULL, 8) ,
('20010404150354.016080', NULL, 'm', '2004-05-14 17:28:27.039217', 'jpbenl',
'2004-09-09', '20010228000657.035507', '20030819070327.048648', 'm',
'pbenltgi', '20060303182928.062974', NULL, 6) ,  ('2008-09-24
08:53:22.016827', 5, 'h', '2003-09-01', 'benltgiontlibvm', '2008-02-14 12:17:09.031635', '20030521151251.025342', '2004-04-09 16:10:11.026258', 'f',
'enltgiontlibvmpqr', '2000-11-14 15:22:28.018134', NULL, 8) ,
('20060315071422.004876', 7, 'a', '20020528041154.021479', 'nlt',
'2001-12-10', '2000-08-14', '2009-07-08 20:24:29.025421', 'p', 'ltg',
'2002-12-18 10:27:50.063087', NULL, 7) ,  ('2002-05-27 01:01:59.035319', 7,
'o', '20020415090450.013271', 'tgiontlibv', '2007-05-10 13:54:51.019132',
'2007-02-26', '2003-11-22', 'u', 'g', '2009-06-07', NULL, 0) ,
('20061121170837.015534', 7, 'o', '2009-05-22 06:11:40.033655', 'iontlibvm',
'20010810174929.035591', '2007-06-20', '2009-05-06', 'e', 'ont',
'2008-03-03', NULL, 3) ,  ('2004-07-14 04:47:47.049162', 0, 'f', '2003-11-22 07:50:00.056590', 'ntlibvmp', '2001-02-27', '20080624151301.040796',
'20021208065236.045936', 'i', 'tli', '20030828030915.046482', NULL, 0) ,
('2003-11-27', 5, 'k', '20070502031945.009253', 'libvmp',
'20090519072058.040435', '2006-01-15', '2006-09-02', 't', 'ibvmpqr',
'2002-09-26 20:33:21.025782', NULL, NULL) ,  ('20020801190408.035296', 3,
'd', '2008-03-10', 'bvmpqrj', '20000404232830.043738',
'20080104131505.048837', '2008-03-21 14:47:29.005926', 'e', 'vmpqrjqqtervh',
'20070213100707.011219', NULL, NULL);
CREATE TABLE d (
col_blob blob,
col_date_key date,
col_varchar_key varchar (1),
col_int int,
pk integer auto_increment,
col_int_key int,
col_time time,
col_varchar varchar (1),
col_date date,
col_datetime datetime,
col_blob_key blob,
col_datetime_key datetime,
col_time_key time,
/*Indices*/
key (col_date_key ),
key (col_varchar_key ),
primary key (pk),
key (col_int_key ),
key (col_blob_key  (255)),
key (col_datetime_key ),
key (col_time_key )) ENGINE=innodb;
CREATE VIEW view_d AS SELECT * FROM d;
INSERT /*! IGNORE */ INTO d VALUES  ('vjhnsz', '2009-01-07 14:35:19.020132',
'l', 3, NULL, 3, '20000326113108.015501', 'r', '20010401050329.035127',
'20011113143139.064960', 'jhnszaxsayy', '20070118125355.047410',
'20090428164254.028210') ,  ('hnszaxs', '2008-08-27 11:54:15.047662', 'j', 4,
NULL, 0, '20010202135929.024155', 'v', '2007-08-16', '2005-04-09',
'nszaxsayy', '20080516080723.016240', '2007-01-17') ,  (NULL, '2003-09-24 00:21:21.031671', 'b', 9, NULL, 1, '20031103023944.058953', 'j',
'2003-03-13', '20060501024312.007597', 'szaxsayyl', '20081027124453.006858',
'2002-08-06') ,  ('zaxsayylmwcxvckkurpm', '20031022235229.001956', 'n', 5,
NULL, 1, '2004-07-27 08:04:56.058948', 't', '20050807070334.035272',
'2007-06-11', NULL, '20060923090920.011086', '20030612224409.056295') ,
('axsayy', '2004-01-19', 'n', 9, NULL, 9, '2006-05-14 05:40:33.042253', 'o',
'20041215133725.018148', '2007-04-24 00:00:38.063289', 'xsayylm',
'20010204112339.062692', '2002-02-02 09:29:07.046062') ,
('sayylmwcxvckkurpmkgo', '2006-06-10 22:00:28.042795', 'f', 0, NULL, NULL,
'2005-01-07 15:59:14.020326', 'l', '2001-04-04 04:45:32.007662', '2002-07-05 12:32:06.063122', 'ayylmwcxvckkurpm', '2004-12-05', '20030805231032.036854')
,  ('yylmwcxvckk', '2003-12-08', 'o', NULL, NULL, NULL, '2002-10-10', 'z',
'20060120195105.061469', '2007-12-08 15:45:05.033799', NULL,
'20030127203404.043978', '2006-06-23') ,  ('ylmwcxvckkurpmkg',
'20041116013050.054722', 'e', 9, NULL, 9, '20010118181543.064149', 'a',
'20080922201409.032468', '2001-03-22 08:12:26.049150',
'lmwcxvckkurpmkgousyu', '2009-02-13', '20000224232013.001267') ,  (NULL,
'2009-03-07 11:05:18.000692', 'f', 4, NULL, 2, '2002-09-12 12:18:58.006036',
'v', '2002-06-07 02:13:31.045026', '2008-07-09 07:24:51.028063',
'mwcxvckkurpmkgou', '20021224090830.002338', '2006-04-18 07:30:48.043718') ,
('wcxvckkur', '2004-01-17', 'y', 0, NULL, 1, '2000-05-12 01:48:35.064791',
'x', '20070410164713.053236', '2003-05-23', 'cx', '20010122125247.033418',
'2002-07-21');
CREATE TABLE cc (
col_date date,
col_int_key int,
col_date_key date,
col_blob blob,
col_varchar_key varchar (1),
col_blob_key blob,
col_datetime_key datetime,
pk integer auto_increment,
col_datetime datetime,
col_varchar varchar (1),
col_int int,
col_time time,
col_time_key time,
/*Indices*/
key (col_int_key ),
key (col_date_key ),
key (col_varchar_key ),
key (col_blob_key  (255)),
key (col_datetime_key ),
primary key (pk),
key (col_time_key )) ENGINE=innodb;
INSERT /*! IGNORE */ INTO cc VALUES  ('2005-06-10 07:33:22.052469', 8,
'20060110031422.021867', 'dwnqdsnrgazuybhjdahq', 'r', 'wnqdsnrgazuybhjda',
'20050603013634.045108', NULL, '2004-02-11', 'f', 3, '20000705085324.025237',
'2003-04-15 00:38:25.053635') ,  ('2000-10-26 05:14:38.024766', 7,
'20000612214032.039531', 'nqdsnrg', 'c', 'qdsnrgazuy', '2007-05-09', NULL,
'2000-12-15 11:35:37.016508', 'e', 8, '2009-05-06 05:01:24.056192',
'2003-01-23 23:58:06.029416') ,  ('20050504023258.045971', 7, '2008-01-15 02:21:38.039039', 'dsnrgazuybhjda', 'i', 'snrga', '2002-12-13
00:31:57.060608', NULL, '2008-07-24 11:47:14.001612', 'm', 0, '2005-03-05',
'2008-06-19 10:48:38.019334') ,  ('2000-04-15', 3, '20081004053314.044710',
'nrgazuybh', 'f', 'rgazuybhj', '20080306031449.018675', NULL, '2006-10-19',
'k', 7, '20050721191228.013016', '20020624120807.061954') ,  ('2000-12-15 17:47:06.016372', 6, '2006-05-02', 'gazuybhjdahqd', 'p',
'azuybhjdahqdxtgmcoi', '2009-12-20', NULL, '20080527214707.035996', 'b', 9,
'20081205171614.002123', '2005-02-22 01:45:02.001096') ,  ('2006-02-01 17:54:51.014899', 9, '20020314004355.038751', 'zuybhjd', 'x', 'uybhj',
'2004-08-26', NULL, '2000-01-11', 'z', 0, '2002-06-18 04:58:56.042381',
'2009-05-22 14:06:31.037897') ,  ('2008-07-13 11:52:57.026926', 7,
'20020209152856.020954', 'ybhjdahqd', 'v', 'bhjdahqdxtgmcoipfty', '2003-02-04 12:57:47.049387', NULL, '20091024085101.040905', 'a', 6,
'20020728235938.013783', '2000-03-19') ,  ('20090324051451.058328', 5,
'20010823083110.056903', 'hjdahqdxtgmcoip', 'n', 'jdahqdxtgmcoipf',
'20020724124943.026135', NULL, '2000-12-20 05:29:33.011954', 'a', 3,
'2006-09-26', '2005-08-06 23:37:38.032353') ,  ('2006-06-20 18:09:36.056461',
NULL, '20060712223533.053408', 'dahqdxtgmcoipft', 'p', 'ahqdxtgmcoipftyraxg',
'20030312223653.030796', NULL, '20060508204857.062148', 'p', NULL,
'20000425075615.007223', '2000-05-26 17:03:40.000684') ,
('20081110073618.016133', 3, '2000-07-24 16:13:45.058915', 'hqdxtgmc', 's',
NULL, '20001101104313.012643', NULL, '2005-07-09', 'o', 2,
'20040927102407.022307', '20091206042605.020200');
ANALYZE TABLE b,cc,d;
Table	Op	Msg_type	Msg_text
test.b	analyze	status	OK
test.cc	analyze	status	OK
test.d	analyze	status	OK
EXPLAIN WITH cte AS (
SELECT
alias2 . col_datetime_key AS field1 ,
alias1 . col_varchar_key AS field2 ,
alias2 . col_int_key AS field3
FROM  view_b AS alias1  LEFT  JOIN  cc AS alias2  LEFT  JOIN view_d AS alias3
ON  alias2 . col_varchar_key =  alias3 . col_blob_key
ON  alias1 . col_blob =  alias3 . col_blob_key
WHERE  alias3 . col_varchar_key  LIKE ( 'w' )
OR  alias2 . pk <> 4  )
DELETE /*+ NO_MERGE(outrcte) */  QUICK
outr1.*, outr2.* FROM d AS outr1 LEFT OUTER JOIN d AS outr2
ON ( outr1 . col_int = outr2 . col_int )
INNER JOIN cte AS outrcte
ON outr1 . pk = outrcte.field1
WHERE (4, 7 )  IN (
SELECT DISTINCT innr1 . col_int_key AS x , innr1 . col_int AS y
FROM cc AS innr1 JOIN cte AS innrcte
ON innr1.pk = innrcte.field1
WHERE innr1 . col_int >= innr1 . pk
);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	innr1	NULL	index_merge	PRIMARY,col_int_key	col_int_key,PRIMARY	9,4	NULL	1	10.00	Using intersect(col_int_key,PRIMARY); Using where
1	PRIMARY	<derived2>	NULL	ref	<auto_key0>	<auto_key0>	6	test.innr1.pk	10	100.00	Using where; FirstMatch
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	111	100.00	Using where
1	DELETE	outr1	NULL	eq_ref	PRIMARY	PRIMARY	4	outrcte.field1	1	100.00	Using where
1	DELETE	outr2	NULL	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
2	DERIVED	b	NULL	ALL	NULL	NULL	NULL	NULL	10	100.00	NULL
2	DERIVED	d	NULL	ref	col_blob_key	col_blob_key	258	test.b.col_blob	1	100.00	Using where
2	DERIVED	alias2	NULL	ALL	col_varchar_key	NULL	NULL	NULL	10	100.00	Using where
Warnings:
Warning	1739	Cannot use ref access on index 'col_varchar_key' due to type or collation conversion on field 'col_varchar_key'
Warning	1739	Cannot use ref access on index 'col_varchar_key' due to type or collation conversion on field 'col_varchar_key'
Note	1003	with `cte` as (/* select#2 */ select `test`.`alias2`.`col_datetime_key` AS `field1`,`test`.`b`.`col_varchar_key` AS `field2`,`test`.`alias2`.`col_int_key` AS `field3` from `test`.`b` left join (`test`.`cc` `alias2` join `test`.`d`) on(((`test`.`d`.`col_blob_key` = `test`.`b`.`col_blob`) and (`test`.`alias2`.`col_varchar_key` = `test`.`d`.`col_blob_key`))) where ((`test`.`d`.`col_varchar_key` like 'w') or (`test`.`alias2`.`pk` <> 4))) delete /*+ NO_MERGE(`outrcte`@`select#1`) */ quick `test`.`d`, `test`.`d` from `test`.`d` `outr1` left join `test`.`d` `outr2` on((`test`.`outr2`.`col_int` = `test`.`outr1`.`col_int`)) join `cte` `outrcte` semi join (`test`.`cc` `innr1` join `cte` `innrcte`) where ((`test`.`innr1`.`col_int` = 7) and (`test`.`innr1`.`col_int_key` = 4) and (7 >= `test`.`innr1`.`pk`) and (cast(`test`.`innr1`.`pk` as double) = cast(`innrcte`.`field1` as double)) and (cast(`test`.`outr1`.`pk` as double) = cast(`outrcte`.`field1` as double)))
DROP TABLE b,cc,d;
DROP VIEW view_d,view_b;
#
# Bug#23742722 WL883:ASSERTION `!CREATED && NEW_IDX < OLD_IDX && OLD_IDX < (INT)S->KEYS' FAILED
#
CREATE TABLE a (
col_int int,
col_date date,
col_time time,
col_time_key time,
col_date_key date,
col_blob blob,
col_varchar varchar (1),
col_datetime datetime,
col_int_key int,
pk integer auto_increment,
col_blob_key blob,
col_datetime_key datetime,
col_varchar_key varchar (1),
key (col_time_key ),
key (col_date_key ),
key (col_int_key ),
primary key (pk),
key (col_blob_key  (255)),
key (col_datetime_key ),
key (col_varchar_key )) ENGINE=innodb;
CREATE TABLE c (
col_int_key int,
col_datetime datetime,
col_time_key time,
pk integer auto_increment,
col_varchar_key varchar (1),
col_date_key date,
col_blob_key blob,
col_int int,
col_varchar varchar (1),
col_time time,
col_datetime_key datetime,
col_date date,
col_blob blob,
key (col_int_key ),
key (col_time_key ),
primary key (pk),
key (col_varchar_key ),
key (col_date_key ),
key (col_blob_key  (255)),
key (col_datetime_key )) ENGINE=innodb;
CREATE TABLE d (
col_time_key time,
col_time time,
col_blob blob,
col_datetime_key datetime,
col_varchar varchar (1),
col_int_key int,
col_int int,
col_date_key date,
col_date date,
col_blob_key blob,
col_datetime datetime,
pk integer auto_increment,
col_varchar_key varchar (1),
key (col_time_key ),
key (col_datetime_key ),
key (col_int_key ),
key (col_date_key ),
key (col_blob_key  (255)),
primary key (pk),
key (col_varchar_key )) ENGINE=innodb;
ANALYZE TABLE c,d,a;
Table	Op	Msg_type	Msg_text
test.c	analyze	status	OK
test.d	analyze	status	OK
test.a	analyze	status	OK
WITH cte AS (
SELECT alias1 . col_int_key AS field1
FROM  a AS alias1  LEFT  JOIN c AS alias2
ON  alias1 . col_blob =  alias2 . col_blob_key
WHERE  alias2 . pk > 3
AND alias2 . pk < ( 3 + 10 )
OR  alias1 . col_varchar_key >= 'z'
AND alias1 . col_varchar_key <= 'k'  )
DELETE /*+ NO_MERGE(outrcte) */ LOW_PRIORITY QUICK  outr1.*, outr2.*
FROM d AS outr1 LEFT JOIN c AS outr2
ON ( outr1 . col_int = outr2 . pk )
JOIN a AS outr3
ON ( outr1 . col_int_key = outr3 . pk )
RIGHT JOIN cte AS outrcte
ON outr1 . col_int_key = outrcte.field1
WHERE outr1 . col_blob_key <> (
SELECT  innr1 . col_blob AS y
FROM a AS innr2 INNER JOIN a AS innr1
ON ( innr2 . col_datetime >= innr1 . col_datetime )
RIGHT OUTER JOIN cte AS innrcte
ON innr2.col_int_key < innrcte.field1
WHERE innr1 . col_datetime = '2006-02-24'  );
DROP TABLE c,d,a;
#
# Bug#23738507 WL883:INNODB: ASSERTION FAILURE SHARE->IDX_TRANS_TBL.INDEX_COUNT == MYSQL_NUM_IN
#
SET @@SESSION.sql_mode='';
CREATE TABLE a (
col_int int(11) DEFAULT NULL,
col_time_key time DEFAULT NULL,
col_datetime datetime DEFAULT NULL,
col_blob_key blob,
col_varchar_key varchar(1) DEFAULT NULL,
col_date date DEFAULT NULL,
col_blob blob,
col_varchar varchar(1) DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
col_time time DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_time_key (col_time_key),
KEY col_blob_key (col_blob_key(255)),
KEY col_varchar_key (col_varchar_key),
KEY col_int_key (col_int_key),
KEY col_date_key (col_date_key),
KEY col_datetime_key (col_datetime_key)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE d (
col_date date DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_int int(11) DEFAULT NULL,
col_varchar varchar(1) DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
col_datetime datetime DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_blob_key blob,
pk int(11) NOT NULL AUTO_INCREMENT,
col_time time DEFAULT NULL,
col_time_key time DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_blob blob,
PRIMARY KEY (pk),
KEY col_date_key (col_date_key),
KEY col_varchar_key (col_varchar_key),
KEY col_datetime_key (col_datetime_key),
KEY col_blob_key (col_blob_key(255)),
KEY col_time_key (col_time_key),
KEY col_int_key (col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE bb (
col_datetime datetime DEFAULT NULL,
col_time time DEFAULT NULL,
col_int int(11) DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
col_time_key time DEFAULT NULL,
col_blob blob,
col_date date DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_blob_key blob,
col_int_key int(11) DEFAULT NULL,
col_varchar varchar(1) DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key),
KEY col_time_key (col_time_key),
KEY col_datetime_key (col_datetime_key),
KEY col_date_key (col_date_key),
KEY col_blob_key (col_blob_key(255)),
KEY col_int_key (col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO bb VALUES ('2000-03-10
07:59:07','20:09:19',7,'y',1,'00:20:02','byuvlhiviingf','2000-04-06','2002-01-
18 04:14:45','2009-02-13','yu',6,'v'),('2008-09-04
21:14:02','05:11:49',4,'b',2,'16:18:13','uvlhiviingfglqfsrku','2003-10-25','20
01-06-01 00:00:00','2003-03-15','vlhiviin',9,'x'),('2005-04-12
00:00:00','02:17:02',8,'n',3,'00:38:06','lhiv','2004-03-18','2009-09-07
01:11:02','2007-06-22','hiviingfglqfsr',8,'j'),('2008-09-03
00:00:00','23:55:54',7,'i',4,'07:58:47','ivi','2002-08-18','2002-07-04
14:47:00','2005-10-09','viingfglqfsrk',0,'v'),('2005-11-20
00:00:00','22:38:15',3,'w',5,'00:20:01','i','2003-05-13','2008-12-17
18:34:39','2009-12-27','ingfglqfsrkuzymmw',3,'t'),('2000-11-24
18:26:08','15:48:23',8,'e',6,'21:16:41','ng','2000-05-11','2005-11-16
15:25:37','2007-01-25','gfglqfsrkuz',3,'m'),('2000-05-17
00:00:00','00:20:01',2,'h',7,'00:20:09','fglqfsrkuzym','2006-02-07','2009-02-2
4 00:00:00','2007-09-17','glqfsr',1,'a'),('2002-03-14
13:32:30','17:14:34',6,'a',8,'00:20:06','lqfsrkuzymmwno','2004-02-12','2001-01
-16 21:53:44','2002-09-24','qfsrkuzymmwnorrsk',2,'a'),('2009-02-17
18:23:44','11:07:00',6,'q',9,'07:11:35','fsr','2009-01-27','2001-04-11
00:00:00','2000-07-01','srku',4,'w'),('2001-03-22
03:25:45','19:12:08',4,'n',10,'00:20:08','rkuz','2003-08-10','2000-03-27
21:15:52','2002-11-17','ku',7,'m');
Warnings:
Warning	4095	Delimiter '\n' in position 10 in datetime value '2000-03-10
07:59:07' at row 1 is deprecated. Prefer the standard ' '.
Warning	1265	Data truncated for column 'col_datetime_key' at row 1
Warning	4095	Delimiter '\n' in position 10 in datetime value '2008-09-04
21:14:02' at row 2 is deprecated. Prefer the standard ' '.
Warning	1265	Data truncated for column 'col_datetime_key' at row 2
Warning	4095	Delimiter '\n' in position 10 in datetime value '2005-04-12
00:00:00' at row 3 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2009-09-07
01:11:02' at row 3 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2008-09-03
00:00:00' at row 4 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2002-07-04
14:47:00' at row 4 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2005-11-20
00:00:00' at row 5 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2008-12-17
18:34:39' at row 5 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2000-11-24
18:26:08' at row 6 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2005-11-16
15:25:37' at row 6 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2000-05-17
00:00:00' at row 7 is deprecated. Prefer the standard ' '.
Warning	1265	Data truncated for column 'col_datetime_key' at row 7
Warning	4095	Delimiter '\n' in position 10 in datetime value '2002-03-14
13:32:30' at row 8 is deprecated. Prefer the standard ' '.
Warning	1265	Data truncated for column 'col_datetime_key' at row 8
Warning	4095	Delimiter '\n' in position 10 in datetime value '2009-02-17
18:23:44' at row 9 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2001-04-11
00:00:00' at row 9 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2001-03-22
03:25:45' at row 10 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2000-03-27
21:15:52' at row 10 is deprecated. Prefer the standard ' '.
CREATE TABLE cc (
col_varchar_key varchar(1) DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_time time DEFAULT NULL,
col_blob blob,
col_datetime datetime DEFAULT NULL,
col_time_key time DEFAULT NULL,
col_varchar varchar(1) DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
col_date date DEFAULT NULL,
col_blob_key blob,
col_datetime_key datetime DEFAULT NULL,
col_int int(11) DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key),
KEY col_date_key (col_date_key),
KEY col_int_key (col_int_key),
KEY col_time_key (col_time_key),
KEY col_blob_key (col_blob_key(255)),
KEY col_datetime_key (col_datetime_key)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO cc VALUES ('r','2009-06-19',9,'00:20:01','uzymmwnorr','2000-07-03
03:10:16','17:17:32','f',1,'2007-06-20','zym','2001-10-19
13:28:47',2),('d','2000-12-27',7,'22:21:37','ymmwn','2000-09-05
06:27:09','15:26:21','a',2,'2003-08-19','mmwnorrskzxfni','2002-06-08
06:10:17',5),('e','2009-03-11',4,'08:30:12','mwnorrskzx','2002-05-21
12:10:52','22:59:24','m',3,'2008-12-04','wnorrskzxfn','2009-11-21
10:47:03',4),('b','2003-08-25',1,'22:05:05','norrskzxfniuzodhtf','2006-03-04
02:36:50','21:40:31','k',4,'2000-03-13','orrskzx','2006-12-15
19:28:52',2),('u','2004-01-15',4,'00:20:02','rrskzxfniuzod','2004-04-23
09:09:44','00:49:02','u',5,'2009-12-13','rskzxfniuzodhtfjsf','2007-09-03
17:43:18',1),('v','2003-08-24',2,'02:46:50','skzxfni','2006-11-15
05:02:03','16:16:11','c',6,'2009-12-09','kzxfniuzodhtfj','2004-01-19
00:00:00',4),('j','2000-05-25',1,'00:20:09','zxfniuzodhtfjsfc','2004-07-18
00:00:00','06:15:00','z',7,'2001-11-03','xfniuzodhtfjs','2003-07-22
04:34:51',6),('h','2001-05-12',5,'00:20:05','fniuzodhtfjsfcyq','2008-07-11
19:49:03','21:51:53','w',8,'2008-10-18','ni','2003-02-21
00:00:00',2),('f','2005-09-24',7,'00:20:09','iuzodhtfjsfcyqrkxk','2006-09-27
00:00:00','10:33:36','f',9,'2008-02-19','uzodhtfjsfcyq','2009-10-17
08:43:06',3),('j','2000-05-07',9,'07:16:29','zodhtfjs','2009-09-07
00:00:00','00:20:05','b',10,'2005-11-10','odhtfjs','2009-04-26 00:00:00',0);
Warnings:
Warning	4095	Delimiter '\n' in position 10 in datetime value '2000-07-03
03:10:16' at row 1 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2001-10-19
13:28:47' at row 1 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2000-09-05
06:27:09' at row 2 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2002-06-08
06:10:17' at row 2 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2002-05-21
12:10:52' at row 3 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2009-11-21
10:47:03' at row 3 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2006-03-04
02:36:50' at row 4 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2006-12-15
19:28:52' at row 4 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2004-04-23
09:09:44' at row 5 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2007-09-03
17:43:18' at row 5 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2006-11-15
05:02:03' at row 6 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2004-01-19
00:00:00' at row 6 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2004-07-18
00:00:00' at row 7 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2003-07-22
04:34:51' at row 7 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2008-07-11
19:49:03' at row 8 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2003-02-21
00:00:00' at row 8 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2006-09-27
00:00:00' at row 9 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2009-10-17
08:43:06' at row 9 is deprecated. Prefer the standard ' '.
Warning	4095	Delimiter '\n' in position 10 in datetime value '2009-09-07
00:00:00' at row 10 is deprecated. Prefer the standard ' '.
CREATE TABLE dd (
col_int_key int(11) DEFAULT NULL,
col_int int(11) DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
col_date date DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_blob blob,
col_varchar varchar(1) DEFAULT NULL,
col_blob_key blob,
col_datetime datetime DEFAULT NULL,
col_time_key time DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
col_datetime_key datetime DEFAULT NULL,
col_time time DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key),
KEY col_date_key (col_date_key),
KEY col_blob_key (col_blob_key(255)),
KEY col_time_key (col_time_key),
KEY col_datetime_key (col_datetime_key)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
ANALYZE TABLE a,d,bb,cc,dd;
Table	Op	Msg_type	Msg_text
test.a	analyze	status	OK
test.d	analyze	status	OK
test.bb	analyze	status	OK
test.cc	analyze	status	OK
test.dd	analyze	status	OK
EXPLAIN WITH cte AS (
SELECT alias1 . pk AS field1 ,
alias2 . col_blob AS field2
FROM  bb AS alias1  LEFT  JOIN dd AS alias2
ON  alias1 . col_varchar_key =  alias2 . col_varchar_key
WHERE  alias2 . pk > 3
AND alias2 . pk < ( 1 + 1 )
OR alias2 . pk  IN (7, 5)
AND  alias2 . pk <> 3
AND  alias2 . pk IS NOT NULL  )
DELETE /*+ NO_MERGE(outrcte) */ LOW_PRIORITY
outr2.* FROM d AS outr1 INNER JOIN a AS outr2
ON ( outr1 . col_int = outr2 . col_int_key )
LEFT OUTER JOIN cte AS outrcte
ON outr2 . col_int_key = outrcte.field1
WHERE outr1 . col_int_key < (
SELECT DISTINCT innr1 . col_int AS y
FROM cc AS innr1 LEFT OUTER JOIN cte AS innrcte
ON innr1.col_int_key <= innrcte.field1
WHERE innr1 . col_int = 4  );
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	outr1	NULL	range	col_int_key	col_int_key	5	NULL	1	100.00	Using where
1	DELETE	outr2	NULL	ref	col_int_key	col_int_key	5	test.outr1.col_int	1	100.00	NULL
1	PRIMARY	<derived2>	NULL	ref	<auto_key0>	<auto_key0>	4	test.outr1.col_int	2	100.00	NULL
3	SUBQUERY	innr1	NULL	ALL	NULL	NULL	NULL	NULL	10	10.00	Using where; Using temporary
3	SUBQUERY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Distinct; Using join buffer (hash join)
2	DERIVED	alias2	NULL	range	PRIMARY,col_varchar_key	PRIMARY	4	NULL	2	100.00	Using where
2	DERIVED	alias1	NULL	ref	col_varchar_key	col_varchar_key	4	test.alias2.col_varchar_key	1	100.00	Using index
Warnings:
Note	1003	with `cte` as (/* select#2 */ select `test`.`alias1`.`pk` AS `field1`,`test`.`alias2`.`col_blob` AS `field2` from `test`.`bb` `alias1` join `test`.`dd` `alias2` where ((`test`.`alias1`.`col_varchar_key` = `test`.`alias2`.`col_varchar_key`) and (((`test`.`alias2`.`pk` > 3) and (`test`.`alias2`.`pk` < <cache>((1 + 1)))) or ((`test`.`alias2`.`pk` in (7,5)) and (`test`.`alias2`.`pk` <> 3) and (`test`.`alias2`.`pk` is not null))))) delete /*+ NO_MERGE(`outrcte`@`select#1`) */ `test`.`a` from `test`.`d` `outr1` join `test`.`a` `outr2` left join `cte` `outrcte` on((`outrcte`.`field1` = `test`.`outr1`.`col_int`)) where ((`test`.`outr2`.`col_int_key` = `test`.`outr1`.`col_int`) and (`test`.`outr1`.`col_int_key` < (/* select#3 */ select distinct `test`.`innr1`.`col_int` AS `y` from `test`.`cc` `innr1` left join `cte` `innrcte` on((`test`.`innr1`.`col_int_key` <= `innrcte`.`field1`)) where (`test`.`innr1`.`col_int` = 4))))
DROP TABLE a,d,bb,cc,dd;
SET @@SESSION.sql_mode=DEFAULT;
#
# Bug#24807455 WL883: ASSERTION `DB != VIEW_DB.STR && TABLE_NAME != VIEW_NAME.STR' FAILED.
#
CREATE TABLE aa (
col_time_key time DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_int int(11) DEFAULT NULL,
col_date date DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_varchar varchar(30) DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
col_blob_key blob,
col_datetime_key datetime DEFAULT NULL,
col_datetime datetime DEFAULT NULL,
col_varchar_key varchar(30) DEFAULT NULL,
col_blob blob,
col_time time DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_time_key (col_time_key),
KEY col_int_key (col_int_key),
KEY col_date_key (col_date_key),
KEY col_blob_key (col_blob_key(255)),
KEY col_datetime_key (col_datetime_key),
KEY col_varchar_key (col_varchar_key)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE b (
col_varchar_key varchar(30) DEFAULT NULL,
col_blob blob,
col_datetime datetime DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_time time DEFAULT NULL,
col_time_key time DEFAULT NULL,
col_blob_key blob,
pk int(11) NOT NULL AUTO_INCREMENT,
col_datetime_key datetime DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_varchar varchar(30) DEFAULT NULL,
col_date date DEFAULT NULL,
col_int int(11) DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key),
KEY col_date_key (col_date_key),
KEY col_time_key (col_time_key),
KEY col_blob_key (col_blob_key(255)),
KEY col_datetime_key (col_datetime_key),
KEY col_int_key (col_int_key),
KEY test_idx (col_int_key,col_int) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE bb (
col_varchar_key varchar(30) DEFAULT NULL,
col_datetime datetime DEFAULT NULL,
col_varchar varchar(30) DEFAULT NULL,
col_blob_key blob,
pk int(11) NOT NULL AUTO_INCREMENT,
col_time_key time DEFAULT NULL,
col_time time DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_blob blob,
col_date_key date DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_int int(11) DEFAULT NULL,
col_date date DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key),
KEY col_blob_key (col_blob_key(255)),
KEY col_time_key (col_time_key),
KEY col_datetime_key (col_datetime_key),
KEY col_date_key (col_date_key),
KEY col_int_key (col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE c (
col_varchar varchar(30) DEFAULT NULL,
col_time time DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
col_int int(11) DEFAULT NULL,
col_date date DEFAULT NULL,
col_blob_key blob,
col_datetime datetime DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_blob blob,
col_datetime_key datetime DEFAULT NULL,
col_varchar_key varchar(30) DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_time_key time DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_blob_key (col_blob_key(255)),
KEY col_date_key (col_date_key),
KEY col_datetime_key (col_datetime_key),
KEY col_varchar_key (col_varchar_key),
KEY col_int_key (col_int_key),
KEY col_time_key (col_time_key),
KEY test_idx (pk,col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE e (
col_time_key time DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_blob_key blob,
col_int_key int(11) DEFAULT NULL,
col_time time DEFAULT NULL,
col_blob blob,
pk int(11) NOT NULL AUTO_INCREMENT,
col_datetime datetime DEFAULT NULL,
col_varchar varchar(30) DEFAULT NULL,
col_varchar_key varchar(30) DEFAULT NULL,
col_int int(11) DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_date date DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_time_key (col_time_key),
KEY col_date_key (col_date_key),
KEY col_blob_key (col_blob_key(255)),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key),
KEY col_datetime_key (col_datetime_key),
KEY test_idx (col_int_key,col_int) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE OR REPLACE VIEW view_c AS SELECT * FROM c;
CREATE OR REPLACE VIEW view_bb AS SELECT * FROM bb;
# WHERE bogus_string IN (SELECT timestamp_column)
# makes semijoin transformation fail half-way, we want to check
# that INNRcte is properly cleaned up.
WITH cte AS (
SELECT alias1 . col_datetime_key AS field1
FROM  view_c AS alias1  RIGHT  JOIN view_bb AS alias2
ON  alias1 . col_blob_key =  alias2 . col_blob_key
WHERE
alias2 . col_varchar_key >= 'r'
AND alias2 . col_varchar_key <= 'z'
AND alias2 . col_varchar_key > 'x'
AND alias2 . col_varchar_key <= 'z'
AND  alias2 . pk > 3
AND alias2 . pk < ( 7 + 1 )
OR  alias2 . col_int_key  IN (5)
ORDER BY field1  , field1 )
DELETE     outr1.*
FROM b AS outr1 JOIN e AS outr2
ON ( outr1 . col_int_key = outr2 . col_int )
JOIN c AS outr3
ON ( outr1 . col_datetime_key = outr3 . col_date_key )
LEFT JOIN cte AS outrcte
ON outr1 . col_blob = outrcte.field1
WHERE ( _utf8 'g' )  IN (
SELECT  innrcte . field1 AS x
FROM aa AS innr1 JOIN cte AS innrcte
ON innr1.pk = innrcte.field1
WHERE outr1 . col_int_key <> 0
ORDER BY innr1 . col_blob );
ERROR 22007: Incorrect datetime value: 'g' for column 'x' at row 1
DROP VIEW view_c, view_bb;
DROP TABLE aa,b,bb,c,e;
#
# Bug#24930997 WL883: ASSERTION `!*CONVERTED' FAILED.
#
CREATE TABLE E (
col_varchar_key varchar (1),
col_datetime_key datetime,
col_varchar varchar (1),
col_int_key int,
col_time time,
col_time_key time,
col_date_key date,
col_datetime datetime,
col_int int,
col_blob blob,
col_date date,
col_blob_key blob,
pk integer auto_increment,
/*Indices*/
key (col_varchar_key ),
key (col_datetime_key ),
key (col_int_key ),
key (col_time_key ),
key (col_date_key ),
key (col_blob_key  (255)),
primary key (pk)) ENGINE=innodb;
WITH cte AS (
SELECT alias2 . col_time_key AS field1
FROM  E AS alias1  LEFT OUTER JOIN E AS alias2
ON  alias1 . col_varchar_key =  alias2 . col_blob_key
WHERE  alias2 . col_varchar_key  LIKE ( 'u' )
ORDER BY field1 DESC LIMIT 1000)
UPDATE
E AS OUTR1 JOIN E AS OUTR2
ON ( OUTR1 . col_int = OUTR2 . col_int )
LEFT OUTER JOIN cte AS OUTRcte JOIN cte AS OUTRcte1
ON OUTR1 . col_int = OUTRcte.field1
ON OUTR2 . col_varchar = OUTRcte1.field1
SET OUTR1.col_varchar_key = 0
WHERE OUTRcte . field1  IN (
SELECT  INNR1 . col_varchar AS y FROM E AS INNR1
WHERE OUTRcte1 . field1 <> 3 );
ERROR 42S22: Unknown column 'OUTR1.col_int' in 'on clause'
DROP TABLE E;
#
# Bug#30026353 NON-DETERMINISTIC CTE REFERENCED TWO TIMES CAN
# PRODUCE DIFFERENT CONTENT
#
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(1),(2),(3),(4),(5),(6);
CREATE TABLE t2 (a INT);
INSERT INTO t2
WITH cte AS (SELECT RAND() AS a FROM t1)
SELECT SUM(c.a) FROM cte c UNION ALL
SELECT SUM(c2.a) FROM cte c2;
SELECT * FROM t2 tmp1, t2 tmp2 WHERE tmp1.a<>tmp2.a;
a	a
DELETE FROM t2;
INSERT INTO t2
WITH cte AS (SELECT * FROM t1 WHERE a>3*RAND())
SELECT SUM(c.a) FROM cte c
UNION ALL SELECT SUM(c2.a) FROM cte c2;
SELECT * FROM t2 tmp1, t2 tmp2 WHERE tmp1.a<>tmp2.a;
a	a
DROP TABLE t1,t2;
#
# Bug#30796015 INCONSISTENT BEHAVIOUR FOR DELETE ON CTE
#
create table t1(a int);
insert into t1 values(2),(3);
with t1 as (select * from t1 where a=3) delete from t1;
ERROR HY000: The target table t1 of the DELETE is not updatable
with t1 as (select * from t1 where a=3) delete t1.* from t1, t1 as t2;
ERROR HY000: The target table t1 of the DELETE is not updatable
with t1 as (select * from t1 where a=3) update t1 set a=2;
ERROR HY000: The target table t1 of the UPDATE is not updatable
with t1 as (select * from t1 where a=3) update t1, t1 as t2 set t1.a=2;
ERROR HY000: The target table t1 of the UPDATE is not updatable
with t1 as (select 36 as col from t1 where a=3) select * from t1;
col
36
insert into t1 with t1 as (select 36 as col from t1) select * from t1;
select * from t1;
a
2
3
36
36
create table t1 with t1 as (select 72 as col from t1) select * from t1;
ERROR 42S01: Table 't1' already exists
create table t2 with t1 as (select 72 as col from t1) select * from t1;
select * from t1;
a
2
3
36
36
select * from t2;
col
72
72
72
72
drop table t1,t2;
# Bug#32678303: Assertion 'table_num_to_node_num[table_num] != -1 failed
CREATE TABLE t1(a INTEGER);
SELECT COUNT(sq1_t1.a) AS sq1_field1
FROM t1 AS sq1_t1 RIGHT JOIN
t1 AS sq1_t2 LEFT JOIN t1 AS sq1_t3
ON sq1_t2.a IN
(WITH cte2 AS
(SELECT sq1_t1.a AS c_sq1_field1
FROM t1 AS c_sq1_t1
)
SELECT * FROM cte2
)
ON sq1_t3.a = sq1_t2.a;
ERROR 42S22: Unknown column 'sq1_t1.a' in 'field list'
SELECT (WITH cte1 AS
(SELECT COUNT(sq1_t1.a) AS sq1_field1
FROM t1 AS sq1_t1 RIGHT JOIN
t1 AS sq1_t2 LEFT JOIN t1 AS sq1_t3
ON sq1_t2.a IN
(WITH cte2 AS
(SELECT sq1_t1.a AS c_sq1_field1
FROM t1 AS c_sq1_t1
)
SELECT * FROM cte2
)
ON sq1_t3.a = sq1_t2.a
)
SELECT * FROM cte1
) AS field3
FROM t1 AS table1;
ERROR 42S22: Unknown column 'sq1_t1.a' in 'field list'
DROP TABLE t1;
#
# Bug#32383712: CTE PRODUCES WRONG RESULT
#
CREATE TABLE t1 (f1 TEXT);
INSERT INTO t1 VALUES ('x');
WITH cte1 AS (SELECT * FROM t1 UNION SELECT * FROM t1),
cte2 AS (SELECT COUNT(*) FROM cte1) SELECT * from cte1,cte2;
f1	COUNT(*)
x	1
DROP TABLE t1;