1# Initialize 2--source include/default_optimizer_switch.inc 3 4--disable_warnings 5drop table if exists t1,t2,t3; 6--enable_warnings 7 8set @save_derived_optimizer_switch=@@optimizer_switch; 9set optimizer_switch='derived_merge=off,derived_with_keys=off'; 10 11select * from (select 2 from DUAL) b; 12-- error 1054 13SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b; 14-- error 1054 15SELECT 1 as a FROM (SELECT a UNION SELECT 1) b; 16CREATE TABLE t1 (a int not null, b char (10) not null); 17insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); 18CREATE TABLE t2 (a int not null, b char (10) not null); 19insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); 20select t1.a,t3.y from t1,(select a as y from t2 where b='c') as t3 where t1.a = t3.y; 21select t1.a,t3.a from t1,(select * from t2 where b='c') as t3 where t1.a = t3.a; 22CREATE TABLE t3 (a int not null, b char (10) not null); 23insert into t3 values (3,'f'),(4,'y'),(5,'z'),(6,'c'); 24select t1.a,t4.y from t1,(select t2.a as y from t2,(select t3.b from t3 where t3.a>3) as t5 where t2.b=t5.b) as t4 where t1.a = t4.y; 25--error 1054 26SELECT a FROM (SELECT 1 FROM (SELECT 1) a HAVING a=1) b; 27--error 1052 28SELECT a,b as a FROM (SELECT '1' as a,'2' as b) b HAVING a=1; 29SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=2; 30SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=1; 31--error 1054 32SELECT 1 FROM (SELECT 1) a WHERE a=2; 33--error 1054 34SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) as a; 35select * from t1 as x1, (select * from t1) as x2; 36explain select * from t1 as x1, (select * from t1) as x2; 37drop table if exists t2,t3; 38select * from (select 1) as a; 39select a from (select 1 as a) as b; 40select 1 from (select 1) as a; 41select * from (select * from t1 union select * from t1) a; 42select * from (select * from t1 union all select * from t1) a; 43select * from (select * from t1 union all select * from t1 limit 2) a; 44explain select * from (select * from t1 union select * from t1) a; 45explain select * from (select * from t1 union all select * from t1) a; 46CREATE TABLE t2 (a int not null); 47insert into t2 values(1); 48select * from (select * from t1 where t1.a=(select a from t2 where t2.a=t1.a)) a; 49select * from (select * from t1 where t1.a=(select t2.a from t2 where t2.a=t1.a) union select t1.a, t1.b from t1) a; 50explain select * from (select t1.*, t2.a as t2a from t1,t2 where t1.a=t2.a) t1; 51drop table t1, t2; 52create table t1(a int not null, t char(8), index(a)); 53--disable_query_log 54begin; 55let $1 = 10000; 56while ($1) 57 { 58 eval insert into t1 values ($1,'$1'); 59 dec $1; 60 } 61commit; 62--enable_query_log 63SELECT * FROM (SELECT * FROM t1) as b ORDER BY a ASC LIMIT 0,20; 64explain select count(*) from t1 as tt1, (select * from t1) as tt2; 65drop table t1; 66SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b; 67select * from (select 1 as a) b left join (select 2 as a) c using(a); 68--error 1054 69SELECT * FROM (SELECT 1 UNION SELECT a) b; 70--error 1054 71SELECT 1 as a FROM (SELECT a UNION SELECT 1) b; 72--error 1054 73SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b; 74--error 1054 75select 1 from (select 2) a order by 0; 76 77# 78# Test of explain (bug #251) 79# 80 81create table t1 (id int); 82insert into t1 values (1),(2),(3); 83describe select * from (select * from t1 group by id) bar; 84drop table t1; 85 86# 87# test->used_keys test for derived tables 88# 89create table t1 (mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL); 90create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED NOT NULL); 91insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), (NULL, 'i', 9); 92insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105); 93 94SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; 95SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; 96 97explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; 98explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; 99drop table t1,t2; 100 101# 102# derived table reference 103# 104SELECT a.x FROM (SELECT 1 AS x) AS a HAVING a.x = 1; 105 106# 107# Test for select if database is not selected. 108# 109# Connect without a database as user mysqltest_1 110create user mysqltest_1; 111create table t1 select 1 as a; 112connect (con1,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK); 113connection con1; 114--source include/default_optimizer_switch.inc 115set optimizer_switch='derived_merge=off,derived_with_keys=off'; 116--error 1046 117select 2 as a from (select * from t1) b; 118use test; 119select 2 as a from (select * from t1) b; 120drop table t1; 121--error 1064 122select mail_id, if(folder.f_description!='', folder.f_description, folder.f_name) as folder_name, date, address_id, phrase, address, subject from folder, (select mail.mail_id as mail_id, date_format(mail.h_date, '%b %e, %Y %h:%i') as date, mail.folder_id, sender.address_id as address_id, sender.phrase as phrase, sender.address as address, mail.h_subject as subject from mail left join mxa as mxa_sender on mail.mail_id=mxa_sender.mail_id and mxa_sender.type='from' left join address as sender on mxa_sender.address_id=sender.address_id mxa as mxa_recipient, address as recipient, where 1 and mail.mail_id=mxa_recipient.mail_id and mxa_recipient.address_id=recipient.address_id and mxa_recipient.type='to' and match(sender.phrase, sender.address, sender.comment) against ('jeremy' in boolean mode) and match(recipient.phrase, recipient.address, recipient.comment) against ('monty' in boolean mode) order by mail.h_date desc limit 0, 25 ) as query where query.folder_id=folder.folder_id; 123 124# 125# UPDATE/DELETE/INSERT of derived tables 126# 127create table t1 (a int); 128insert into t1 values (1),(2),(3); 129-- error 1288 130update (select * from t1) as t1 set a = 5; 131-- error 1064 132delete from (select * from t1); 133-- error 1064 134insert into (select * from t1) values (5); 135drop table t1; 136 137# 138# deived tables with subquery inside all by one table 139# 140create table t1 (E1 INTEGER UNSIGNED NOT NULL, E2 INTEGER UNSIGNED NOT NULL, E3 INTEGER UNSIGNED NOT NULL, PRIMARY KEY(E1) 141); 142insert into t1 VALUES(1,1,1), (2,2,1); 143select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2; 144explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2; 145drop table t1; 146 147create table t1 (a int); 148insert into t1 values (1),(2); 149select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b; 150explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b; 151drop table t1; 152 153# 154# multi-update & multi-delete with derived tables 155# 156CREATE TABLE `t1` ( 157 `N` int(11) unsigned NOT NULL default '0', 158 `M` tinyint(1) default '0' 159) ENGINE=MyISAM DEFAULT CHARSET=latin1; 160INSERT INTO `t1` (N, M) VALUES (1, 0),(1, 0),(1, 0),(2, 0),(2, 0),(3, 0); 161UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2; 162select * from t1; 163-- error 1288 164UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2, P2.N = 2; 165-- error 1054 166UPDATE `t1` AS P1 INNER JOIN (SELECT aaaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2; 167delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; 168select * from t1; 169--replace_result P2 p2 170--error ER_NON_UPDATABLE_TABLE 171delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; 172-- error 1054 173delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; 174drop table t1; 175 176# 177# correct lex->current_select 178# 179CREATE TABLE t1 ( 180 OBJECTID int(11) NOT NULL default '0', 181 SORTORDER int(11) NOT NULL auto_increment, 182 KEY t1_SortIndex (SORTORDER), 183 KEY t1_IdIndex (OBJECTID) 184) ENGINE=MyISAM DEFAULT CHARSET=latin1; 185CREATE TABLE t2 ( 186 ID int(11) default NULL, 187 PARID int(11) default NULL, 188 UNIQUE KEY t2_ID_IDX (ID), 189 KEY t2_PARID_IDX (PARID) 190) engine=MyISAM DEFAULT CHARSET=latin1; 191INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2); 192CREATE TABLE t3 ( 193 ID int(11) default NULL, 194 DATA decimal(10,2) default NULL, 195 UNIQUE KEY t3_ID_IDX (ID) 196) engine=MyISAM DEFAULT CHARSET=latin1; 197INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75); 198select 497, TMP.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as TMP; 199drop table t1, t2, t3; 200 201# 202# explain derived 203# 204CREATE TABLE t1 (name char(1) default NULL, val int(5) default NULL); 205INSERT INTO t1 VALUES ('a',1), ('a',2), ('a',2), ('a',2), ('a',3), ('a',6), ('a',7), ('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20), ('b',2), ('b',3), ('b',4), ('b',5); 206SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name; 207explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name; 208drop table t1; 209 210# 211# "Using index" in explain 212# 213create table t2 (a int, b int, primary key (a)); 214insert into t2 values (1,7),(2,7); 215explain select a from t2 where a>1; 216explain select a from (select a from t2 where a>1) tt; 217drop table t2; 218 219# 220# select list counter 221# 222CREATE TABLE `t1` ( `itemid` int(11) NOT NULL default '0', `grpid` varchar(15) NOT NULL default '', `vendor` int(11) NOT NULL default '0', `date_` date NOT NULL default '0000-00-00', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`)); 223insert into t1 values (128, 'rozn', 2, curdate(), 10), 224 (128, 'rozn', 1, curdate(), 10); 225SELECT MIN(price) min, MAX(price) max, AVG(price) avg FROM (SELECT SUBSTRING( MAX(concat(date_,";",price)), 12) price FROM t1 WHERE itemid=128 AND grpid='rozn' GROUP BY itemid, grpid, vendor) lastprices; 226DROP TABLE t1; 227 228# 229# DISTINCT over grouped select on subquery in the FROM clause 230# 231create table t1 (a integer, b integer); 232insert into t1 values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1); 233select distinct sum(b) from t1 group by a; 234select distinct sum(b) from (select a,b from t1) y group by a; 235drop table t1; 236 237 238# 239# Test for bug #7413 "Subquery with non-scalar results participating in 240# select list of derived table crashes server" aka "VIEW with sub query can 241# cause the MySQL server to crash". If we have encountered problem during 242# filling of derived table we should report error and perform cleanup 243# properly. 244# 245CREATE TABLE t1 (a char(10), b char(10)); 246INSERT INTO t1 VALUES ('root','localhost'), ('root','%'); 247--error 1242 248SELECT * FROM (SELECT (SELECT a.a FROM t1 AS a WHERE a.a = b.a) FROM t1 AS b) AS c; 249DROP TABLE t1; 250# 251# test of union subquery in the FROM clause with complex distinct/all (BUG#6565) 252# 253create table t1(a int); 254create table t2(a int); 255create table t3(a int); 256insert into t1 values(1),(1); 257insert into t2 values(2),(2); 258insert into t3 values(3),(3); 259select * from t1 union distinct select * from t2 union all select * from t3; 260select * from (select * from t1 union distinct select * from t2 union all select * from t3) X; 261drop table t1, t2, t3; 262 263# 264# Bug #11864 non unique names are allowed in subquery 265# 266create table t1 (a int); 267create table t2 (a int); 268--error 1060 269select * from (select * from t1,t2) foo; 270drop table t1,t2; 271 272# 273# Bug#10586 - query works with 4.1.8, but not with 4.1.11 274# 275create table t1 (ID int unsigned not null auto_increment, 276 DATA varchar(5) not null, primary key (ID)); 277create table t2 (ID int unsigned not null auto_increment, 278 DATA varchar(5) not null, FID int unsigned not null, 279 primary key (ID)); 280select A.* from (t1 inner join (select * from t2) as A on t1.ID = A.FID); 281select t2.* from ((select * from t1) as A inner join t2 on A.ID = t2.FID); 282select t2.* from (select * from t1) as A inner join t2 on A.ID = t2.FID; 283drop table t1, t2; 284 285connection con1; 286disconnect con1; 287--source include/wait_until_disconnected.inc 288connection default; 289drop user mysqltest_1; 290 291--echo # End of 4.1 tests 292 293# 294# Bug #41156: List of derived tables acts like a chain of 295# mutually-nested subqueries 296# 297 298SELECT 0 FROM 299(SELECT 0) t01, (SELECT 0) t02, (SELECT 0) t03, (SELECT 0) t04, (SELECT 0) t05, 300(SELECT 0) t06, (SELECT 0) t07, (SELECT 0) t08, (SELECT 0) t09, (SELECT 0) t10, 301(SELECT 0) t11, (SELECT 0) t12, (SELECT 0) t13, (SELECT 0) t14, (SELECT 0) t15, 302(SELECT 0) t16, (SELECT 0) t17, (SELECT 0) t18, (SELECT 0) t19, (SELECT 0) t20, 303(SELECT 0) t21, (SELECT 0) t22, (SELECT 0) t23, (SELECT 0) t24, (SELECT 0) t25, 304(SELECT 0) t26, (SELECT 0) t27, (SELECT 0) t28, (SELECT 0) t29, (SELECT 0) t30, 305(SELECT 0) t31, (SELECT 0) t32, (SELECT 0) t33, (SELECT 0) t34, (SELECT 0) t35, 306(SELECT 0) t36, (SELECT 0) t37, (SELECT 0) t38, (SELECT 0) t39, (SELECT 0) t40, 307(SELECT 0) t41, (SELECT 0) t42, (SELECT 0) t43, (SELECT 0) t44, (SELECT 0) t45, 308(SELECT 0) t46, (SELECT 0) t47, (SELECT 0) t48, (SELECT 0) t49, (SELECT 0) t50, 309(SELECT 0) t51, (SELECT 0) t52, (SELECT 0) t53, (SELECT 0) t54, (SELECT 0) t55, 310(SELECT 0) t56, (SELECT 0) t57, (SELECT 0) t58, (SELECT 0) t59, (SELECT 0) t60, 311(SELECT 0) t61; # 61 == MAX_TABLES 312 313--echo # 314--echo # A nested materialized derived table is used before being populated. 315--echo # (addon for bug#19077) 316--echo # 317 318CREATE TABLE t1 (i INT, j BIGINT); 319INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2); 320SELECT * FROM (SELECT MIN(i) FROM t1 321WHERE j = SUBSTRING('12', (SELECT * FROM (SELECT MIN(j) FROM t1) t2))) t3; 322DROP TABLE t1; 323 324--echo # End of 5.0 tests 325 326--echo # 327--echo # MDEV-5005: Subquery in Procedure somehow affecting temporary table 328--echo # 329 330create temporary table if not exists t1 (id int not null); 331 332select A.* from ( select tt.* from t1 tt ) A; 333 334prepare stmt from "select A.* from ( select tt.* from t1 tt ) A "; 335execute stmt; 336deallocate prepare stmt; 337 338drop temporary table t1; 339 340--delimiter | 341CREATE PROCEDURE p () 342BEGIN 343 select A.* from ( select tt.* from t1 tt ) A ; 344END | 345--delimiter ; 346 347create temporary table if not exists t1 (id int not null); 348 349CALL p(); 350CALL p(); 351 352drop procedure p; 353 354drop temporary table t1; 355 356 357--echo # 358--echo # MDEV-5143: update of a joined table with a nested subquery with 359--echo # a syntax error crashes mysqld with signal 11 360--echo # 361 362create table t1 (id int(11) not null auto_increment, val varchar(100) null,primary key (id)); 363create table t2 (id int(11) not null auto_increment, val varchar(100) null,primary key (id)); 364 365insert into t1 (val) values('a'); 366insert into t2 (val) values('1'); 367 368--error ER_BAD_FIELD_ERROR 369update 370 ( 371 select 372 val 373 from 374 ( 375 select 376 v.val 377 from 378 t2 wrong_table_alias 379 ) t4 380 ) t3 381 inner join t1 on 382 t1.id=t3.val 383set 384 t1.val=t3.val 385; 386 387drop table t1, t2; 388 389--echo # 390--echo # MDEV-5353: server crash on subselect if WHERE applied to some 391--echo # result field 392--echo # 393 394SELECT * FROM 395( SELECT 100 a, subsel.b FROM ( SELECT 200 b ) subsel ) tmp 396WHERE tmp.b; 397SELECT * FROM 398( SELECT 100 a, subsel.b FROM ( SELECT 200 b ) subsel ) tmp 399WHERE tmp.a; 400 401--echo # 402--echo # MDEV-5356: Server crashes in Item_equal::contains on 2nd 403--echo # execution of a PS 404--echo # 405CREATE TABLE t1 (a INT, b INT); 406INSERT INTO t1 VALUES (1,2),(3,4); 407 408CREATE TABLE t2 (c INT); 409INSERT INTO t2 VALUES (5),(6); 410 411CREATE TABLE t3 (d INT); 412INSERT INTO t3 VALUES (7),(8); 413 414CREATE PROCEDURE pr() 415 UPDATE t3, 416 (SELECT c FROM 417 (SELECT 1 FROM t1 WHERE a=72 AND NOT b) sq, 418 t2 419 ) sq2 420 SET d=sq2.c; 421 422CALL pr(); 423CALL pr(); 424CALL pr(); 425 426drop procedure pr; 427drop table t1,t2,t3; 428 429--echo # End of 5.3 tests 430 431--echo # 432--echo # Bug#58730 Assertion failed: table->key_read == 0 in close_thread_table, 433--echo # temptable views 434--echo # 435 436CREATE TABLE t1 (a INT); 437CREATE TABLE t2 (b INT, KEY (b)); 438INSERT INTO t1 VALUES (1),(1); 439INSERT INTO t2 VALUES (1),(1); 440 441CREATE algorithm=temptable VIEW v1 AS 442 SELECT 1 FROM t1 LEFT JOIN t1 t3 ON 1 > (SELECT 1 FROM t1); 443CREATE algorithm=temptable VIEW v2 AS SELECT 1 FROM t2; 444 445# This caused the assert to be triggered. 446EXPLAIN SELECT 1 FROM t1 JOIN v1 ON 1 > (SELECT 1 FROM v2); 447--error ER_SUBQUERY_NO_1_ROW 448SELECT 1 FROM t1 JOIN v1 ON 1 > (SELECT 1 FROM v2); 449 450DROP TABLE t1, t2; 451DROP VIEW v1, v2; 452 453# 454# MDEV-614 lp:1050806 - different result for a query using subquery between 5.5.25 and 5.5.27 455# MySQL Bug#66845 Wrong result (extra row) on a FROM subquery with a variable and ORDER BY 456# 457create table t1 (n bigint(20) unsigned, d1 datetime, d2 datetime, key (d1)); 458insert t1 values (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00'); 459insert t1 values (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00'); 460insert t1 values (2088,'2012-03-01 00:00:00','2013-01-01 00:00:00'); 461select * from ( 462 select n, d1, d2, @result := 0 as result 463 from t1 464 where d1 < '2012-12-12 12:12:12' and n in (2085, 2084) order by d2 asc 465) as calculated_result; 466drop table t1; 467 468# 469# MDEV-5012 Server crashes in Item_ref::real_item on EXPLAIN with select subqueries or views, constant table, derived_merge+derived_with_keys 470# 471set @save_derived_optimizer_switch_bug=@@optimizer_switch; 472SET optimizer_switch = 'derived_merge=on,derived_with_keys=on,in_to_exists=on'; 473CREATE TABLE t1 (a INT) ENGINE=MyISAM; 474INSERT INTO t1 VALUES (8); 475CREATE TABLE t2 (b INT) ENGINE=MyISAM; 476INSERT INTO t2 VALUES (1),(7); 477EXPLAIN SELECT * FROM (SELECT * FROM t1) AS table1, 478 (SELECT DISTINCT * FROM t2) AS table2 WHERE b = a AND a <> ANY (SELECT 9); 479DROP TABLE t1, t2; 480set optimizer_switch=@save_derived_optimizer_switch_bug; 481 482--echo # 483--echo # MDEV-6163: Error while executing an update query that has the 484--echo # same table in a sub-query 485--echo # 486 487set @save_derived_optimizer_switch_bug=@@optimizer_switch; 488SET optimizer_switch = 'derived_merge=on'; 489create table t1 (balance float, accountId varchar(64), primary key (accountId)); 490 491insert into t1 (accountId,balance) values 492('dealer-1',199354.0),('dealer-2',0),('dealer-3',0),('dealer-5',0),('FINANCE',-200000),('OPERATOR',0); 493 494update t1 set balance=(select sum(balance) from (SELECT balance FROM t1 where accountId like 'dealer%') AS copied) where accountId = 'OPERATOR'; 495set optimizer_switch=@save_derived_optimizer_switch_bug; 496drop table t1; 497 498--echo # 499--echo # MDEV-6219:Server crashes in Bitmap<64u>::merge 500--echo # (this=0x180, map2=...) on 2nd execution of PS with INSERT .. SELECT, 501--echo # derived_merge 502--echo # 503 504CREATE TABLE t1 (a VARCHAR(8)) ENGINE=MyISAM; 505INSERT INTO t1 VALUES ('foo'),('bar'); 506 507create procedure p1() 508 INSERT INTO t1 SELECT * FROM ( 509 SELECT * FROM t1 510 ) AS sq 511 WHERE sq.a IN ( SELECT 'baz' FROM DUAL ); 512 513call p1(); 514call p1(); 515drop procedure p1; 516 517PREPARE stmt FROM " 518 INSERT INTO t1 SELECT * FROM ( 519 SELECT * FROM t1 520 ) AS sq 521 WHERE sq.a IN ( SELECT 'baz' FROM DUAL ) 522"; 523 524EXECUTE stmt; 525EXECUTE stmt; 526 527deallocate prepare stmt; 528 529drop table t1; 530 531--echo # 532--echo # MDEV-6892: WHERE does not apply 533--echo # 534create table t1 (id int); 535create table t2 (id int); 536insert into t1 values(1),(2),(3); 537insert into t2 values(4),(5),(6); 538#explain extended 539select x.id, message from (select id from t1) x left join 540(select id, 1 as message from t2) y on x.id=y.id 541where coalesce(message,0) <> 0; 542explain extended 543select x.id, message from (select id from t1) x left join 544(select id, 1 as message from t2) y on x.id=y.id 545where message <> 0; 546drop table t1,t2; 547 548--echo # 549--echo # MDEV-7827: Assertion `!table || (!table->read_set || 550--echo # bitmap_is_set(table->read_set, field_index))' failed 551--echo # in Field_long::val_str on EXPLAIN EXTENDED 552--echo # 553 554CREATE TABLE t1 (f1 INT, f2 INT, KEY(f2)) ENGINE=MyISAM; 555INSERT INTO t1 VALUES (6,9); 556 557CREATE TABLE t2 (f3 INT) ENGINE=MyISAM; 558INSERT INTO t2 VALUES (2),(0); 559 560EXPLAIN EXTENDED 561SELECT f1 FROM ( SELECT * FROM t1 ) AS sq 562WHERE f1 IN ( 563 SELECT f3 FROM t2 WHERE f2 IN ( 564 SELECT f3 FROM t2 HAVING f3 >= 8 565 ) 566); 567 568DROP TABLE t2,t1; 569 570--echo # 571--echo # MDEV-9462: Out of memory using explain on 2 empty tables 572--echo # 573 574CREATE TABLE `t1` ( 575 `REC_GROUP` char(2) DEFAULT NULL, 576 `CLIENT_INFO` text CHARACTER SET utf8, 577 `NAME` text, 578 `PHONE_NUMBER` text, 579 `ATTENTION_NAME` text, 580 `PAYMENT_TERM` text CHARACTER SET utf8, 581 `CREDIT_LIMIT` decimal(12,2) DEFAULT NULL, 582 `LAST_PAY_DATE` text CHARACTER SET utf8, 583 `TOTAL` double DEFAULT NULL, 584 `TOTAL_MCL` double DEFAULT NULL, 585 `TOTAL_MFS` double DEFAULT NULL, 586 `TOTAL_MIS` double DEFAULT NULL, 587 `BEFORE_DUE_7_MCL` double DEFAULT NULL, 588 `BEFORE_DUE_7_MFS` double DEFAULT NULL, 589 `BEFORE_DUE_7_MIS` double DEFAULT NULL, 590 `PER1_MCL` double DEFAULT NULL, 591 `PER1_MFS` double DEFAULT NULL, 592 `PER1_MIS` double DEFAULT NULL, 593 `PER2_MCL` double DEFAULT NULL, 594 `PER2_MFS` double DEFAULT NULL, 595 `PER2_MIS` double DEFAULT NULL, 596 `PER3_MCL` double DEFAULT NULL, 597 `PER3_MFS` double DEFAULT NULL, 598 `PER3_MIS` double DEFAULT NULL, 599 `PER4_MCL` double DEFAULT NULL, 600 `PER4_MFS` double DEFAULT NULL, 601 `PER4_MIS` double DEFAULT NULL, 602 `PER5_MCL` double DEFAULT NULL, 603 `PER5_MFS` double DEFAULT NULL, 604 `PER5_MIS` double DEFAULT NULL, 605 `PER6_MCL` double DEFAULT NULL, 606 `PER6_MFS` double DEFAULT NULL, 607 `PER6_MIS` double DEFAULT NULL, 608 `PER7_MCL` double DEFAULT NULL, 609 `PER7_MFS` double DEFAULT NULL, 610 `PER7_MIS` double DEFAULT NULL, 611 `BEFORE_DUE_7` double DEFAULT NULL, 612 `PER1` double DEFAULT NULL, 613 `PER2` double DEFAULT NULL, 614 `PER3` double DEFAULT NULL, 615 `PER4` double DEFAULT NULL, 616 `PER5` double DEFAULT NULL, 617 `PER6` double DEFAULT NULL, 618 `PER7` double DEFAULT NULL, 619 `REF` varchar(30) DEFAULT NULL, 620 `TYPE` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL 621); 622 623 624CREATE TABLE `t2` ( 625 `RECEIVABLE_GROUP` char(2) DEFAULT NULL, 626 `CLIENT_NUMBER` varchar(35) DEFAULT NULL, 627 `CLIENT_NAME` varchar(73) DEFAULT NULL, 628 `PHONE_NUMBER` char(12) DEFAULT NULL, 629 `ATTENTION_NAME` char(26) DEFAULT NULL, 630 `PAYMENT_TERM` varchar(26) CHARACTER SET utf8 DEFAULT NULL, 631 `CREDIT_LIMIT` decimal(12,2) DEFAULT NULL, 632 `LAST_PAY_DATE` varchar(42) CHARACTER SET utf8 DEFAULT NULL, 633 `TOTAL` decimal(12,2) DEFAULT NULL, 634 `BEFORE_DUE_7` decimal(12,2) DEFAULT NULL, 635 `PER1` decimal(12,2) DEFAULT NULL, 636 `PER2` decimal(12,2) DEFAULT NULL, 637 `PER3` decimal(12,2) DEFAULT NULL, 638 `PER4` decimal(12,2) DEFAULT NULL, 639 `PER5` decimal(12,2) DEFAULT NULL, 640 `PER6` decimal(12,2) DEFAULT NULL, 641 `PER7` decimal(12,2) DEFAULT NULL, 642 `DIVISION` varchar(3) CHARACTER SET utf8 NOT NULL, 643 `CLIENT_INFO` varchar(294) CHARACTER SET utf8 DEFAULT NULL, 644 `EXCHANGE_RATE` double NOT NULL, 645 `REF` varchar(30) DEFAULT NULL 646); 647 648explain 649SELECT A.RECEIVABLE_GROUP,A.CLIENT_INFO,A.CLIENT_NAME,A.PHONE_NUMBER,A.ATTENTION_NAME,A.PAYMENT_TERM,A.CREDIT_LIMIT,A.LAST_PAY_DATE,A.TOTAL, 650COALESCE(B.TOTAL_MCL,0) AS TOTAL_MCL, 651COALESCE(C.TOTAL_MFS,0) AS TOTAL_MFS, 652COALESCE(D.TOTAL_MIS,0) AS TOTAL_MIS, 653COALESCE(F.BEFORE_DUE_7_MCL,0) AS BEFORE_DUE_7_MCL, 654COALESCE(G.BEFORE_DUE_7_MFS,0) AS BEFORE_DUE_7_MFS, 655COALESCE(H.BEFORE_DUE_7_MIS,0) AS BEFORE_DUE_7_MIS, 656COALESCE(I.PER1_MCL,0) AS PER1_MCL, 657COALESCE(J.PER1_MFS,0) AS PER1_MFS, 658COALESCE(K.PER1_MIS,0) AS PER1_MIS, 659COALESCE(L.PER2_MCL,0) AS PER2_MCL, 660COALESCE(M.PER2_MFS,0) AS PER2_MFS, 661COALESCE(N.PER2_MIS,0) AS PER2_MIS, 662COALESCE(O.PER3_MCL,0) AS PER3_MCL, 663COALESCE(P.PER3_MFS,0) AS PER3_MFS, 664COALESCE(R.PER3_MIS,0) AS PER3_MIS, 665COALESCE(S.PER4_MCL,0) AS PER4_MCL, 666COALESCE(T.PER4_MFS,0) AS PER4_MFS, 667COALESCE(U.PER4_MIS,0) AS PER4_MIS, 668COALESCE(V.PER5_MCL,0) AS PER5_MCL, 669COALESCE(X.PER5_MFS,0) AS PER5_MFS, 670COALESCE(Z.PER5_MIS,0) AS PER5_MIS, 671COALESCE(Q.PER6_MCL,0) AS PER6_MCL, 672COALESCE(Y.PER6_MFS,0) AS PER6_MFS, 673COALESCE(W.PER6_MIS,0) AS PER6_MIS, 674COALESCE(A1.PER7_MCL,0) AS PER7_MCL, 675COALESCE(B1.PER7_MFS,0) AS PER7_MFS, 676COALESCE(C1.PER7_MIS,0) AS PER7_MIS, 677A.BEFORE_DUE_7,A.PER1,A.PER2,A.PER3,A.PER4,A.PER5,A.PER6,A.PER7, 678CONCAT(A.DIVISION,'-',A.CLIENT_NUMBER) AS REF,"2" AS TYPE FROM 679(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER, 680GROUP_CONCAT(DISTINCT CLIENT_INFO SEPARATOR '<br>') AS CLIENT_INFO, 681GROUP_CONCAT(DISTINCT CLIENT_NAME SEPARATOR '<br>') AS CLIENT_NAME, 682GROUP_CONCAT( DISTINCT `PHONE_NUMBER` SEPARATOR '<br>' ) AS PHONE_NUMBER , 683GROUP_CONCAT( DISTINCT `ATTENTION_NAME` SEPARATOR '<br>' ) AS ATTENTION_NAME, 684GROUP_CONCAT( DISTINCT `PAYMENT_TERM` SEPARATOR '<br>' ) AS PAYMENT_TERM, 685CREDIT_LIMIT , 686GROUP_CONCAT( `LAST_PAY_DATE` SEPARATOR '<br>' ) AS LAST_PAY_DATE, 687SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL, 688SUM( `BEFORE_DUE_7`*EXCHANGE_RATE ) AS BEFORE_DUE_7, 689SUM( `PER1`*EXCHANGE_RATE ) AS PER1, 690SUM( `PER2`*EXCHANGE_RATE ) AS PER2, 691SUM( `PER3`*EXCHANGE_RATE ) AS PER3, 692SUM( `PER4`*EXCHANGE_RATE ) AS PER4, 693SUM( `PER5`*EXCHANGE_RATE ) AS PER5, 694SUM( `PER6`*EXCHANGE_RATE ) AS PER6, 695SUM( `PER7`*EXCHANGE_RATE ) AS PER7 696FROM `t2` 697WHERE REF IS NULL GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS A 698LEFT JOIN 699(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MCL 700FROM `t2` 701WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS B ON A.CLIENT_NUMBER=B.CLIENT_NUMBER AND 702A.DIVISION=B.DIVISION AND A.RECEIVABLE_GROUP=B.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=B.CREDIT_LIMIT 703LEFT JOIN 704(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MFS 705FROM `t2` 706WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS C ON A.CLIENT_NUMBER=C.CLIENT_NUMBER 707AND 708A.DIVISION=C.DIVISION AND A.RECEIVABLE_GROUP=C.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=C.CREDIT_LIMIT 709LEFT JOIN 710(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MIS 711FROM `t2` 712WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS D ON A.CLIENT_NUMBER=D.CLIENT_NUMBER AND 713A.DIVISION=D.DIVISION AND A.RECEIVABLE_GROUP=D.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=D.CREDIT_LIMIT 714LEFT JOIN 715(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MCL 716FROM `t2` 717WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS F ON A.CLIENT_NUMBER=F.CLIENT_NUMBER AND 718A.DIVISION=F.DIVISION AND A.RECEIVABLE_GROUP=F.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=F.CREDIT_LIMIT 719LEFT JOIN 720(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MFS 721FROM `t2` 722WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS G ON A.CLIENT_NUMBER=G.CLIENT_NUMBER AND 723A.DIVISION=G.DIVISION AND A.RECEIVABLE_GROUP=G.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=G.CREDIT_LIMIT 724LEFT JOIN 725(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MIS 726FROM `t2` 727WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS H ON A.CLIENT_NUMBER=H.CLIENT_NUMBER AND 728A.DIVISION=H.DIVISION AND A.RECEIVABLE_GROUP=H.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=H.CREDIT_LIMIT 729LEFT JOIN 730(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MCL 731FROM `t2` 732WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS I ON A.CLIENT_NUMBER=I.CLIENT_NUMBER AND 733A.DIVISION=I.DIVISION AND A.RECEIVABLE_GROUP=I.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=I.CREDIT_LIMIT 734LEFT JOIN 735(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MFS 736FROM `t2` 737WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS J ON A.CLIENT_NUMBER=J.CLIENT_NUMBER AND 738A.DIVISION=J.DIVISION AND A.RECEIVABLE_GROUP=J.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=J.CREDIT_LIMIT 739LEFT JOIN 740(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MIS 741FROM `t2` 742WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS K ON A.CLIENT_NUMBER=K.CLIENT_NUMBER AND 743A.DIVISION=K.DIVISION AND A.RECEIVABLE_GROUP=K.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=K.CREDIT_LIMIT 744LEFT JOIN 745(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MCL 746FROM `t2` 747WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS L ON A.CLIENT_NUMBER=L.CLIENT_NUMBER AND 748A.DIVISION=L.DIVISION AND A.RECEIVABLE_GROUP=L.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=L.CREDIT_LIMIT 749LEFT JOIN 750(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MFS 751FROM `t2` 752WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS M ON A.CLIENT_NUMBER=M.CLIENT_NUMBER AND 753A.DIVISION=M.DIVISION AND A.RECEIVABLE_GROUP=M.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=M.CREDIT_LIMIT 754LEFT JOIN 755(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MIS 756FROM `t2` 757WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS N ON A.CLIENT_NUMBER=N.CLIENT_NUMBER AND 758A.DIVISION=N.DIVISION AND A.RECEIVABLE_GROUP=N.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=N.CREDIT_LIMIT 759LEFT JOIN 760(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MCL 761FROM `t2` 762WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS O ON A.CLIENT_NUMBER=O.CLIENT_NUMBER AND 763A.DIVISION=O.DIVISION AND A.RECEIVABLE_GROUP=O.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=O.CREDIT_LIMIT 764LEFT JOIN 765(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MFS 766FROM `t2` 767WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS P ON A.CLIENT_NUMBER=P.CLIENT_NUMBER AND 768A.DIVISION=P.DIVISION AND A.RECEIVABLE_GROUP=P.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=P.CREDIT_LIMIT 769LEFT JOIN 770(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MIS 771FROM `t2` 772WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS R ON A.CLIENT_NUMBER=R.CLIENT_NUMBER AND 773A.DIVISION=R.DIVISION AND A.RECEIVABLE_GROUP=R.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=R.CREDIT_LIMIT 774LEFT JOIN 775(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MCL 776FROM `t2` 777WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS S ON A.CLIENT_NUMBER=S.CLIENT_NUMBER AND 778A.DIVISION=S.DIVISION AND A.RECEIVABLE_GROUP=S.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=S.CREDIT_LIMIT 779LEFT JOIN 780(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MFS 781FROM `t2` 782WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS T ON A.CLIENT_NUMBER=T.CLIENT_NUMBER AND 783A.DIVISION=T.DIVISION AND A.RECEIVABLE_GROUP=T.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=T.CREDIT_LIMIT 784LEFT JOIN 785(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MIS 786FROM `t2` 787WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS U ON A.CLIENT_NUMBER=U.CLIENT_NUMBER AND 788A.DIVISION=U.DIVISION AND A.RECEIVABLE_GROUP=U.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=U.CREDIT_LIMIT 789LEFT JOIN 790(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MCL 791FROM `t2` 792WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS V ON A.CLIENT_NUMBER=V.CLIENT_NUMBER AND 793A.DIVISION=V.DIVISION AND A.RECEIVABLE_GROUP=V.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=V.CREDIT_LIMIT 794LEFT JOIN 795(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MFS 796FROM `t2` 797WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS X ON A.CLIENT_NUMBER=X.CLIENT_NUMBER AND 798A.DIVISION=X.DIVISION AND A.RECEIVABLE_GROUP=X.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=X.CREDIT_LIMIT 799LEFT JOIN 800(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MIS 801FROM `t2` 802WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Z ON A.CLIENT_NUMBER=Z.CLIENT_NUMBER AND 803A.DIVISION=Z.DIVISION AND A.RECEIVABLE_GROUP=Z.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Z.CREDIT_LIMIT 804LEFT JOIN 805(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MCL 806FROM `t2` 807WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Q ON A.CLIENT_NUMBER=Q.CLIENT_NUMBER AND 808A.DIVISION=Q.DIVISION AND A.RECEIVABLE_GROUP=Q.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Q.CREDIT_LIMIT 809LEFT JOIN 810(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MFS 811FROM `t2` 812WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Y ON A.CLIENT_NUMBER=Y.CLIENT_NUMBER AND 813A.DIVISION=Y.DIVISION AND A.RECEIVABLE_GROUP=Y.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Y.CREDIT_LIMIT 814LEFT JOIN 815(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MIS 816FROM `t2` 817WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS W ON A.CLIENT_NUMBER=W.CLIENT_NUMBER AND 818A.DIVISION=W.DIVISION AND A.RECEIVABLE_GROUP=W.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=W.CREDIT_LIMIT 819LEFT JOIN 820(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MCL 821FROM `t2` 822WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS A1 ON A.CLIENT_NUMBER=A1.CLIENT_NUMBER AND 823A.DIVISION=A1.DIVISION AND A.RECEIVABLE_GROUP=A1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=A1.CREDIT_LIMIT 824LEFT JOIN 825(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MFS 826FROM `t2` 827WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS B1 ON A.CLIENT_NUMBER=B1.CLIENT_NUMBER AND 828A.DIVISION=B1.DIVISION AND A.RECEIVABLE_GROUP=B1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=B1.CREDIT_LIMIT 829LEFT JOIN 830(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MIS 831FROM `t2` 832WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS C1 ON A.CLIENT_NUMBER=C1.CLIENT_NUMBER AND 833A.DIVISION=C1.DIVISION AND A.RECEIVABLE_GROUP=C1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=C1.CREDIT_LIMIT 834ORDER BY TOTAL DESC; 835 836DROP TABLES t1,t2; 837 838set optimizer_switch=@save_derived_optimizer_switch; 839 840--echo # 841--echo # MDEV-10663: Use of Inline table columns in HAVING clause 842--echo # throws 1463 Error 843--echo # 844 845set @save_sql_mode = @@sql_mode; 846set sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 847 848CREATE TABLE `example1463` ( 849 `Customer` varchar(255) NOT NULL, 850 `DeliveryStatus` varchar(255) NOT NULL, 851 `OrderSize` int(11) NOT NULL 852); 853INSERT INTO example1463 VALUES ('Charlie', 'Success', 100); 854INSERT INTO example1463 VALUES ('David', 'Success', 110); 855INSERT INTO example1463 VALUES ('Charlie', 'Failed', 200); 856INSERT INTO example1463 VALUES ('David', 'Success', 100); 857INSERT INTO example1463 VALUES ('David', 'Unknown', 100); 858INSERT INTO example1463 VALUES ('Edward', 'Success', 150); 859INSERT INTO example1463 VALUES ('Edward', 'Pending', 150); 860 861SELECT Customer, Success, SUM(OrderSize) 862 FROM (SELECT Customer, 863 CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success, 864 OrderSize 865 FROM example1463) as subQ 866 GROUP BY Success, Customer 867 WITH ROLLUP; 868SELECT Customer, Success, SUM(OrderSize) 869 FROM (SELECT Customer, 870 CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success, 871 OrderSize 872 FROM example1463) as subQ 873 GROUP BY Success, Customer; 874SELECT Customer, Success, SUM(OrderSize) 875 FROM (SELECT Customer, 876 CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success, 877 OrderSize 878 FROM example1463) as subQ 879 GROUP BY Success, Customer 880 HAVING Success IS NOT NULL; 881 882DROP TABLE example1463; 883set sql_mode= @save_sql_mode; 884 885--echo # 886--echo # MDEV-9028: SELECT DISTINCT constant column of derived table 887--echo # used as the second operand of LEFT JOIN 888--echo # 889 890create table t1 (id int, data varchar(255)); 891insert into t1 values (1,'yes'),(2,'yes'); 892 893select distinct t1.id, tt.id, tt.data 894 from t1 895 left join 896 (select t1.id, 'yes' as data from t1) as tt 897 on t1.id = tt.id; 898 899select distinct t1.id, tt.id, tt.data 900 from t1 901 left join 902 (select t1.id, 'yes' as data from t1 where id > 1) as tt 903 on t1.id = tt.id; 904 905drop table t1; 906 907--echo # 908--echo # MDEV-14241: Server crash in key_copy / get_matching_chain_by_join_key 909--echo # or valgrind warnings 910--echo # 911 912CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM; 913CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; 914INSERT INTO t1 VALUES ('foo'),('bar'); 915 916CREATE TABLE t2 (b integer auto_increment primary key) ENGINE=MyISAM; 917INSERT INTO t2 VALUES (NULL),(NULL); 918 919CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; 920CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; 921INSERT INTO t3 VALUES ('abc',NULL),('def',4); 922 923set @save_join_cache_level= @@join_cache_level; 924SET join_cache_level= 8; 925explain 926SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; 927SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; 928 929DROP VIEW v1, v3; 930DROP TABLE t1, t2, t3; 931 932--echo # 933--echo # MDEV-14786: Server crashes in Item_cond::transform on 2nd 934--echo # execution of SP querying from a view 935--echo # 936create table t1 (i int, row_start timestamp(6) not null default now(), 937 row_end timestamp(6) not null default '2030-01-01 0:0:0'); 938create view v1 as select i from t1 where i < 5 and (row_end = 939TIMESTAMP'2030-01-01 0:0:0' or row_end is null); 940create procedure pr(x int) select i from v1; 941call pr(1); 942call pr(2); 943drop procedure pr; 944drop view v1; 945drop table t1; 946set @@join_cache_level= @save_join_cache_level; 947 948--echo # 949--echo # MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views 950--echo # 951 952CREATE TABLE t1 (c1 text, c2 int); 953INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3); 954CREATE TABLE t2 (c1 text, c2 int); 955INSERT INTO t2 VALUES ('b',2), ('c',3); 956CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; 957 958explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; 959SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; 960set @save_join_cache_level= @@join_cache_level; 961set @@join_cache_level=4; 962explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; 963SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; 964drop table t1,t2; 965drop view v1; 966set @@join_cache_level= @save_join_cache_level; 967--echo # end of 5.5 968 969--echo # 970--echo # Start of 10.1 tests 971--echo # 972 973--echo # 974--echo # MDEV-8747 Wrong result for SELECT..WHERE derived_table_column='a' AND derived_table_column<>_latin1'A' COLLATE latin1_bin 975--echo # 976CREATE TABLE t1 (a VARCHAR(10)); 977INSERT INTO t1 VALUES ('a'),('A'); 978SELECT * FROM t1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin; 979SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='a' AND a <> _latin1'A' COLLATE latin1_bin; 980DROP TABLE t1; 981 982CREATE TABLE t1 (a ENUM('5','6')); 983INSERT INTO t1 VALUES ('5'),('6'); 984SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5'; 985SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1; 986SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1; 987DROP TABLE t1; 988 989--echo # 990--echo # MDEV-8749 Wrong result for SELECT..WHERE derived_table_enum_column='number' AND derived_table_enum_column OP number2 991--echo # 992CREATE TABLE t1 (a ENUM('5','6')); 993INSERT INTO t1 VALUES ('5'),('6'); 994SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5'; 995SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a=1; 996SELECT * FROM (SELECT * FROM t1) AS table1 WHERE a='5' AND a=1; 997DROP TABLE t1; 998 999--echo # 1000--echo # End of 10.1 tests 1001--echo # 1002 1003--echo # 1004--echo # MDEV-10554: Assertion `!derived->first_select()-> 1005--echo # exclude_from_table_unique_test || derived->outer_select()-> 1006--echo # exclude_from_table_unique_test' 1007--echo # failed in TABLE_LIST::set_check_merged() 1008--echo # 1009 1010CREATE TABLE t1 (f INT); 1011CREATE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT * FROM ( SELECT * FROM t1 ) AS sq; 1012 1013PREPARE stmt FROM 'SELECT * FROM v1'; 1014EXECUTE stmt; 1015EXECUTE stmt; 1016 1017drop view v1; 1018drop table t1; 1019 1020--echo # 1021--echo # MDEV-11363: Assertion `!derived->first_sel ect()->first_inner_unit() || 1022--echo # derived->first_select()->first_inner_unit()->first_select()-> 1023--echo # exclude_from_table_unique_test' failed in 1024--echo # TABLE_LIST::set_check_materialized() 1025--echo # 1026 1027CREATE TABLE t1 (f1 INT); 1028CREATE TABLE t2 (f2 INT); 1029CREATE TABLE t3 (f3 INT); 1030CREATE VIEW v1 AS ( SELECT f1 AS f FROM t1 ) UNION ( SELECT f2 AS f FROM t2 ); 1031CREATE VIEW v2 AS SELECT f3 AS f FROM t3; 1032CREATE VIEW v3 AS SELECT f FROM ( SELECT f3 AS f FROM v1, t3 ) AS sq; 1033CREATE VIEW v4 AS SELECT COUNT(*) as f FROM v3; 1034REPLACE INTO v2 ( SELECT * FROM v4 ) UNION ( SELECT f FROM v2 ); 1035 1036drop view v1,v2,v3,v4; 1037drop table t1,t2,t3; 1038 1039--echo # 1040--echo # MDEV-20325: Assertion `outer_context || !*from_field || *from_field == not_found_field' failed in Item_field::fix_outer_field | `!derived->is_excluded()' failed in TABLE_LIST::set_check_materialized | SIGEGV in st_select_lex::mark_as_dependent (optimized builds) 1041--echo # 1042CREATE TABLE t1 (a INT); 1043 1044--echo # Check that re-execution of a stored routine containing 1045--echo # a query with subquery in the FROM clause doesn't result in 1046--echo # assert failure in case the 'derived_merge' optimizer option 1047--echo # has been turned on/off 1048CREATE PROCEDURE sp() SELECT * FROM (SELECT a FROM t1) tb; 1049CALL sp(); 1050SET optimizer_switch='derived_merge=off'; 1051--echo # Without the patch the following statement would result in assert 1052--echo # failure 1053CALL sp(); 1054 1055--echo # Check the same test case for Prepared Statement 1056SET optimizer_switch='derived_merge=on'; 1057PREPARE stmt FROM "SELECT * FROM (SELECT a FROM t1) tb"; 1058EXECUTE stmt; 1059SET optimizer_switch='derived_merge=off'; 1060--echo # Without the patch the following statement would result in assert 1061--echo # failure 1062EXECUTE stmt; 1063DEALLOCATE PREPARE stmt; 1064 1065--echo # Here check the reverse test case - first turn off the 'derived_merge' 1066--echo # optimizer option, run the stored routine containing a query with 1067--echo # subquery in the FROM clause, then turn on the 'derived_merge' 1068--echo # optimizer option and re-execute the same stored routine to check that 1069--echo # the routine is finished successfully. 1070CREATE PROCEDURE sp1() SELECT * FROM (SELECT a FROM t1) tb; 1071SET optimizer_switch='derived_merge=off'; 1072CALL sp1(); 1073SET optimizer_switch='derived_merge=on'; 1074CALL sp1(); 1075 1076--echo # Check the same test case for Prepared Statement 1077SET optimizer_switch='derived_merge=off'; 1078PREPARE stmt FROM "SELECT * FROM (SELECT a FROM t1) tb"; 1079EXECUTE stmt; 1080SET optimizer_switch='derived_merge=on'; 1081--echo # Without the patch the following statement would result in assert 1082--echo # failure 1083EXECUTE stmt; 1084DEALLOCATE PREPARE stmt; 1085 1086DROP PROCEDURE sp; 1087DROP PROCEDURE sp1; 1088DROP TABLE t1; 1089 1090--echo # 1091--echo # End of 10.2 tests 1092--echo # 1093 1094--echo # 1095--echo # MDEV-9959: A serious MariaDB server performance bug 1096--echo # 1097 1098create table t1(a int); 1099insert into t1 values (1),(2),(3),(4),(5),(6); 1100create table t2(a int, b int,c int); 1101insert into t2(a,b,c) values (1,1,2),(2,2,3),(3,1,4),(4,2,2),(5,1,1),(6,2,5); 1102create table t3(a int, b int); 1103insert into t3(a,b) values (1,1),(2,2),(2,1),(1,2),(5,1),(9,2); 1104 1105--echo table "<derived2>" should have type=ref and rows=1 1106--echo one select in derived table 1107 1108--echo with distinct 1109analyze select * from t1 , ((select distinct t2.a from t2 order by c))q where t1.a=q.a; 1110analyze select * from t1 , ((select distinct t2.a, t2.b from t2 order by c))q where t1.a=q.a; 1111 1112--echo # multiple selects in derived table 1113--echo # NO UNION ALL 1114analyze select * from t1 , ( (select t2.a from t2 order by c) union (select t2.a from t2 order by c))q where t1.a=q.a; 1115select * from t1 , ( (select t2.a from t2 order by c) union (select t2.a from t2 order by c))q where t1.a=q.a; 1116 1117--echo # UNION ALL and EXCEPT 1118analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; 1119 1120select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; 1121 1122drop table t1,t2,t3; 1123