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