1057 lines
46 KiB
Plaintext
1057 lines
46 KiB
Plaintext
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 (Block Nested Loop)
|
|
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 DELETE outr1 NULL range col_int_key col_int_key 5 NULL 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; FirstMatch(outr1)
|
|
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 PRIMARY dd NULL ref col_blob_key col_blob_key 258 test.d.col_blob_key 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`.`outr2`.`col_date` = `test`.`outr1`.`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 (`test`.`outr2`.`col_blob_key` = `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 1265 Data truncated for column 'col_datetime_key' at row 1
|
|
Warning 1265 Data truncated for column 'col_datetime_key' at row 2
|
|
Warning 1265 Data truncated for column 'col_datetime_key' at row 7
|
|
Warning 1265 Data truncated for column 'col_datetime_key' at row 8
|
|
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);
|
|
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 (Block Nested Loop)
|
|
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;
|