call mtr.add_suppression("Sort aborted.*");
call mtr.add_suppression("Out of sort memory; increase server sort buffer size");
ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
CREATE TABLE t1 (
id int(6) DEFAULT '0'NOT NULL,
idservice int(5),
clee char(20) NOT NULL,
flag char(1),
KEY id (id),
PRIMARY KEY (clee)
);
INSERT INTO t1 VALUES (2,4,'6067169d','Y');
INSERT INTO t1 VALUES (2,5,'606716d1','Y');
INSERT INTO t1 VALUES (2,1,'606717c1','Y');
INSERT INTO t1 VALUES (3,1,'6067178d','Y');
INSERT INTO t1 VALUES (2,6,'60671515','Y');
INSERT INTO t1 VALUES (2,7,'60671569','Y');
INSERT INTO t1 VALUES (2,3,'dd','Y');
CREATE TABLE t2 (
id int(6) NOT NULL auto_increment,
description varchar(40) NOT NULL,
idform varchar(40),
ordre int(6) unsigned DEFAULT '0'NOT NULL,
image varchar(60),
PRIMARY KEY (id),
KEY id (id,ordre)
);
INSERT INTO t2 VALUES (1,'Emettre un appel d''offres','en_construction.html',10,'emettre.gif');
INSERT INTO t2 VALUES (2,'Emettre des soumissions','en_construction.html',20,'emettre.gif');
INSERT INTO t2 VALUES (7,'Liste des t2','t2_liste_form.phtml',51060,'link.gif');
INSERT INTO t2 VALUES (8,'Consulter les soumissions','consulter_soumissions.phtml',200,'link.gif');
INSERT INTO t2 VALUES (9,'Ajouter un type de materiel','typeMateriel_ajoute_form.phtml',51000,'link.gif');
INSERT INTO t2 VALUES (10,'Lister/modifier un type de materiel','typeMateriel_liste_form.phtml',51010,'link.gif');
INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif');
INSERT INTO t2 VALUES (4,'Modifier des clients','en_construction.html',40010,'link.gif');
INSERT INTO t2 VALUES (5,'Effacer des clients','en_construction.html',40020,'link.gif');
INSERT INTO t2 VALUES (6,'Ajouter un service','t2_ajoute_form.phtml',51050,'link.gif');
select t1.id,t1.idservice,t2.ordre,t2.description from t1, t2 where t1.id = 2and t1.idservice = t2.id order by t2.ordre;
id idservice ordre description 2110 Emettre un appel d'offres 2340000 Créer une fiche de client 2440010 Modifier des clients 2540020 Effacer des clients 2651050 Ajouter un service 2751060 Liste des t2
drop table t1,t2;
create table t1 (first char(10),last char(10));
insert into t1 values ("Michael","Widenius");
insert into t1 values ("Allan","Larsson");
insert into t1 values ("David","Axmark");
select concat(first," ",last) as name from t1 order by name;
name
Allan Larsson
David Axmark
Michael Widenius
select concat(last," ",first) as name from t1 order by name;
name
Axmark David
Larsson Allan
Widenius Michael
drop table t1;
create table t1 (i int);
insert into t1 values(1),(2),(1),(2),(1),(2),(3);
select distinct i from t1;
i 1 2 3
select distinct i from t1 order by rand(5);
i 1 3 2
select distinct i from t1 order by i desc;
i 3 2 1
select distinct i from t1 order by 1-i;
i 3 2 1
select distinct i from t1 order by mod(i,2),i;
i 2 1 3
drop table t1;
create table t1 ( pk int primary key, name varchar(255) not null, number varchar(255) not null);
insert into t1 values (1, 'Gamma', '123'), (2, 'Gamma Ext', '123a'), (3, 'Alpha', '001'), (4, 'Beta', '200c');
select distinct t1.name as 'Building Name',t1.number as 'Building Number' from t1 order by t1.name asc;
Building Name Building Number
Alpha 001
Beta 200c
Gamma 123
Gamma Ext123a
drop table t1;
create table t1 (id int not null,col1 int not null,col2 int not null,index(col1));
insert into t1 values(1,2,2),(2,2,1),(3,1,2),(4,1,1),(5,1,4),(6,2,3),(7,3,1),(8,2,4);
select * from t1 order by col1,col2;
id col1 col2 411 312 514 221 122 623 824 731
select col1 from t1 order by id;
col1 2 2 1 1 1 2 3 2
select col1 as id from t1 order by id;
id 1 1 1 2 2 2 2 3
select concat(col1) as id from t1 order by id;
id 1 1 1 2 2 2 2 3
drop table t1;
CREATE TABLE t1 (id int auto_increment primary key,aika varchar(40),aikakentta timestamp);
insert into t1 (aika) values ('Keskiviikko');
insert into t1 (aika) values ('Tiistai');
insert into t1 (aika) values ('Maanantai');
insert into t1 (aika) values ('Sunnuntai');
SELECT FIELD(SUBSTRING(t1.aika,1,2),'Ma','Ti','Ke','To','Pe','La','Su') AS test FROM t1 ORDER by test;
test 1 2 3 7
drop table t1;
CREATE TABLE t1
(
a int unsigned NOT NULL,
b int unsigned NOT NULL,
c int unsigned NOT NULL,
UNIQUE(a),
INDEX(b),
INDEX(c)
);
CREATE TABLE t2
(
c int unsigned NOT NULL,
i int unsigned NOT NULL,
INDEX(c)
);
CREATE TABLE t3
(
c int unsigned NOT NULL,
v varchar(64),
INDEX(c)
);
INSERT INTO t1 VALUES (1,1,1);
INSERT INTO t1 VALUES (2,1,2);
INSERT INTO t1 VALUES (3,2,1);
INSERT INTO t1 VALUES (4,2,2);
INSERT INTO t2 VALUES (1,50);
INSERT INTO t2 VALUES (2,25);
INSERT INTO t3 VALUES (1,'123 Park Place');
INSERT INTO t3 VALUES (2,'453 Boardwalk'); SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='outer_join_with_cache=off';
SELECT a,b,if(b = 1,i,if(b = 2,v,''))
FROM t1
LEFT JOIN t2 USING(c)
LEFT JOIN t3 ON t3.c = t1.c;
a b if(b = 1,i,if(b = 2,v,'')) 1150 2125 32123 Park Place 42453 Boardwalk
SELECT a,b,if(b = 1,i,if(b = 2,v,''))
FROM t1
LEFT JOIN t2 ON t1.c = t2.c
LEFT JOIN t3 ON t3.c = t1.c;
a b if(b = 1,i,if(b = 2,v,'')) 1150 2125 32123 Park Place 42453 Boardwalk
SELECT a,b,if(b = 1,i,if(b = 2,v,''))
FROM t1
LEFT JOIN t2 USING(c)
LEFT JOIN t3 ON t3.c = t1.c
ORDER BY a;
a b if(b = 1,i,if(b = 2,v,'')) 1150 2125 32123 Park Place 42453 Boardwalk
SELECT a,b,if(b = 1,i,if(b = 2,v,''))
FROM t1
LEFT JOIN t2 ON t1.c = t2.c
LEFT JOIN t3 ON t3.c = t1.c
ORDER BY a;
a b if(b = 1,i,if(b = 2,v,'')) 1150 2125 32123 Park Place 42453 Boardwalk SET optimizer_switch=@save_optimizer_switch;
drop table t1,t2,t3;
create table t1 (ID int not null primary key, TransactionID int not null);
insert into t1 (ID, TransactionID) values (1, 87), (2, 89), (3, 92), (4, 94), (5, 486), (6, 490), (7, 753), (9, 828), (10, 832), (11, 834), (12, 840);
create table t2 (ID int not null primary key, GroupID int not null);
insert into t2 (ID, GroupID) values (87, 87), (89, 89), (92, 92), (94, 94), (486, 486), (490, 490),(753, 753), (828, 828), (832, 832), (834, 834), (840, 840);
create table t3 (ID int not null primary key, DateOfAction date not null);
insert into t3 (ID, DateOfAction) values (87, '1999-07-19'), (89, '1999-07-19'), (92, '1999-07-19'), (94, '1999-07-19'), (486, '1999-07-18'), (490, '2000-03-27'), (753, '2000-03-28'), (828, '1999-07-27'), (832, '1999-07-27'),(834, '1999-07-27'), (840, '1999-07-27');
select t3.DateOfAction, t1.TransactionID from t1 join t2 join t3 where t2.ID = t1.TransactionID and t3.ID = t2.GroupID order by t3.DateOfAction, t1.TransactionID;
DateOfAction TransactionID 1999-07-18486 1999-07-1987 1999-07-1989 1999-07-1992 1999-07-1994 1999-07-27828 1999-07-27832 1999-07-27834 1999-07-27840 2000-03-27490 2000-03-28753
select t3.DateOfAction, t1.TransactionID from t1 join t2 join t3 where t2.ID = t1.TransactionID and t3.ID = t2.GroupID order by t1.TransactionID,t3.DateOfAction;
DateOfAction TransactionID 1999-07-1987 1999-07-1989 1999-07-1992 1999-07-1994 1999-07-18486 2000-03-27490 2000-03-28753 1999-07-27828 1999-07-27832 1999-07-27834 1999-07-27840
drop table t1,t2,t3;
CREATE TABLE t1 (
member_id int(11) NOT NULL auto_increment,
inschrijf_datum varchar(20) NOT NULL default '',
lastchange_datum varchar(20) NOT NULL default '',
nickname varchar(20) NOT NULL default '',
password varchar(8) NOT NULL default '',
voornaam varchar(30) NOT NULL default '',
tussenvoegsels varchar(10) NOT NULL default '',
achternaam varchar(50) NOT NULL default '',
straat varchar(100) NOT NULL default '',
postcode varchar(10) NOT NULL default '',
wijk varchar(40) NOT NULL default '',
plaats varchar(50) NOT NULL default '',
telefoon varchar(10) NOT NULL default '',
geboortedatum date NOT NULL default '0000-00-00',
geslacht varchar(5) NOT NULL default '',
email varchar(80) NOT NULL default '',
uin varchar(15) NOT NULL default '',
homepage varchar(100) NOT NULL default '',
internet varchar(15) NOT NULL default '',
scherk varchar(30) NOT NULL default '',
favo_boek varchar(50) NOT NULL default '',
favo_tijdschrift varchar(50) NOT NULL default '',
favo_tv varchar(50) NOT NULL default '',
favo_eten varchar(50) NOT NULL default '',
favo_muziek varchar(30) NOT NULL default '',
info text NOT NULL default '',
ipnr varchar(30) NOT NULL default '',
PRIMARY KEY (member_id)
) ENGINE=MyISAM PACK_KEYS=1;
insert into t1 (member_id) values (1),(2),(3);
select member_id, nickname, voornaam FROM t1
ORDER by lastchange_datum DESC LIMIT 2;
member_id nickname voornaam 1 2
drop table t1;
create table t1 (a int not null, b int, c varchar(10), key (a, b, c));
insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b');
insert into t1 select * from t1;
explain select * from t1 where (a = 1and b is null and c = 'b') or (a > 2) order by a desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 22 NULL 3 Using where; Using index
select * from t1 where (a = 1and b is null and c = 'b') or (a > 2) order by a desc;
a b c 1 NULL b 1 NULL b
explain select * from t1 where a >= 1and a < 3 order by a desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 4 NULL 22 Using where; Using index
select * from t1 where a >= 1and a < 3 order by a desc;
a b c 23 c 23 c 22 b 22 b 22 a 22 a 21 b 21 b 21 a 21 a 13 b 13 b 11 b 11 b 11 b 11 b 11 NULL 11 NULL 1 NULL b 1 NULL b 1 NULL NULL 1 NULL NULL
explain select * from t1 where a = 1 order by a desc, b desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 4 const 12 Using where; Using index
select * from t1 where a = 1 order by a desc, b desc;
a b c 13 b 13 b 11 b 11 b 11 b 11 b 11 NULL 11 NULL 1 NULL b 1 NULL b 1 NULL NULL 1 NULL NULL
explain select * from t1 where a = 1and b is null order by a desc, b desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 9 const,const 4 Using where; Using index; Using filesort
select * from t1 where a = 1and b is null order by a desc, b desc;
a b c 1 NULL NULL 1 NULL NULL 1 NULL b 1 NULL b
explain select * from t1 where a >= 1and a < 3and b >0 order by a desc,b desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 9 NULL 18 Using where; Using index
explain select * from t1 where a = 2and b >0 order by a desc,b desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 9 NULL 10 Using where; Using index
explain select * from t1 where a = 2and b is null order by a desc,b desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 9 const,const 1 Using where; Using index; Using filesort
explain select * from t1 where a = 2and (b is null or b > 0) order by a
desc,b desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 9 NULL 11 Using where; Using index
explain select * from t1 where a = 2and b > 0 order by a desc,b desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 9 NULL 10 Using where; Using index
explain select * from t1 where a = 2and b < 2 order by a desc,b desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 9 NULL 4 Using where; Using index
explain select * from t1 where a = 1 order by b desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 4 const 12 Using where; Using index
explain select * from t1 where a = 2and b > 0 order by a desc,b desc,b,a;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 9 NULL 10 Using where; Using index
explain select * from t1 where a = 2and b < 2 order by a desc,a,b desc,a,b;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 9 NULL 4 Using where; Using index
select * from t1 where a = 1 order by b desc;
a b c 13 b 13 b 11 b 11 b 11 b 11 b 11 NULL 11 NULL 1 NULL b 1 NULL b 1 NULL NULL 1 NULL NULL SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
alter table t1 modify b int not null, modify c varchar(10) not null;
Warnings:
Warning 1265 Data truncated for column 'b' at row 1
Warning 1265 Data truncated for column 'c' at row 1
Warning 1265 Data truncated for column 'b' at row 2
Warning 1265 Data truncated for column 'c' at row 3
Warning 1265 Data truncated for column 'b' at row 12
Warning 1265 Data truncated for column 'c' at row 12
Warning 1265 Data truncated for column 'b' at row 13
Warning 1265 Data truncated for column 'c' at row 14
explain select * from t1 order by a, b, c;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 20 NULL 22 Using index
select * from t1 order by a, b, c;
a b c 10 10 10 b 10 b 11 11 11 b 11 b 11 b 11 b 13 b 13 b 21 a 21 a 21 b 21 b 22 a 22 a 22 b 22 b 23 c 23 c
explain select * from t1 order by a desc, b desc, c desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 20 NULL 22 Using index
select * from t1 order by a desc, b desc, c desc;
a b c 23 c 23 c 22 b 22 b 22 a 22 a 21 b 21 b 21 a 21 a 13 b 13 b 11 b 11 b 11 b 11 b 11 11 10 b 10 b 10 10
explain select * from t1 where (a = 1and b = 1and c = 'b') or (a > 2) order by a desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 20 NULL 5 Using where; Using index
select * from t1 where (a = 1and b = 1and c = 'b') or (a > 2) order by a desc;
a b c 11 b 11 b 11 b 11 b
explain select * from t1 where a < 2and b <= 1 order by a desc, b desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 4 NULL 12 Using where; Using index
select * from t1 where a < 2and b <= 1 order by a desc, b desc;
a b c 11 b 11 b 11 b 11 b 11 11 10 b 10 b 10 10
select count(*) from t1 where a < 5and b > 0;
count(*) 18
select * from t1 where a < 5and b > 0 order by a desc,b desc;
a b c 23 c 23 c 22 b 22 b 22 a 22 a 21 b 21 b 21 a 21 a 13 b 13 b 11 b 11 b 11 b 11 b 11 11
explain select * from t1 where a between 1and3and b <= 1 order by a desc, b desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 8 NULL 22 Using where; Using index
select * from t1 where a between 1and3and b <= 1 order by a desc, b desc;
a b c 21 b 21 b 21 a 21 a 11 b 11 b 11 b 11 b 11 11 10 b 10 b 10 10
explain select * from t1 where a between 0and1 order by a desc, b desc;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 4 NULL 12 Using where; Using index
select * from t1 where a between 0and1 order by a desc, b desc;
a b c 13 b 13 b 11 b 11 b 11 b 11 b 11 11 10 b 10 b 10 10
drop table t1;
CREATE TABLE t1 (
gid int(10) unsigned NOT NULL auto_increment,
cid smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (gid),
KEY component_id (cid)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (103853,108),(103867,108),(103962,108),(104505,108),(104619,108),(104620,108);
ALTER TABLE t1 add skr int(10) not null;
CREATE TABLE t2 (
gid int(10) unsigned NOT NULL default '0',
uid smallint(5) unsigned NOT NULL default '1',
sid tinyint(3) unsigned NOT NULL default '1',
PRIMARY KEY (gid),
KEY uid (uid),
KEY status_id (sid)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES (103853,250,5),(103867,27,5),(103962,27,5),(104505,117,5),(104619,75,5),(104620,15,5);
CREATE TABLE t3 (
uid smallint(6) NOT NULL auto_increment,
PRIMARY KEY (uid)
) ENGINE=MyISAM;
INSERT INTO t3 VALUES (1),(15),(27),(75),(117),(250);
ALTER TABLE t3 add skr int(10) not null;
select t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
gid sid uid 104620515 103867527 103962527 104619575 1045055117 1038535250
select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
gid sid uid 104620515 103867527 103962527 104619575 1045055117 1038535250
EXPLAIN select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using where; Using index
EXPLAIN SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using where; Using index
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using index condition
drop table t1,t2,t3;
CREATE TABLE t1 (
`titre` char(80) NOT NULL default '',
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`auteur` char(35) NOT NULL default '',
`icone` tinyint(2) unsigned NOT NULL default '0',
`lastauteur` char(35) NOT NULL default '',
`nbrep` smallint(6) unsigned NOT NULL default '0',
`dest` char(35) NOT NULL default '',
`lu` tinyint(1) unsigned NOT NULL default '0',
`vue` mediumint(8) unsigned NOT NULL default '0',
`ludest` tinyint(1) unsigned NOT NULL default '0',
`ouvert` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`numeropost`),
KEY `date` (`date`),
KEY `dest` (`dest`,`ludest`),
KEY `auteur` (`auteur`,`lu`),
KEY `auteur_2` (`auteur`,`date`),
KEY `dest_2` (`dest`,`date`)
) CHECKSUM=1;
CREATE TABLE t2 (
`numeropost` mediumint(8) unsigned NOT NULL default '0',
`pseudo` char(35) NOT NULL default '',
PRIMARY KEY (`numeropost`,`pseudo`),
KEY `pseudo` (`pseudo`)
);
INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug');
INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug');
SELECT titre,t1.numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest
test 1 joce 0000000-00-0000:00:0001 bug
SELECT titre,numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest
test 1 joce 0000000-00-0000:00:0001 bug
SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest
test 1 joce 0000000-00-0000:00:0001 bug
SELECT titre,numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest
test 1 joce 0000000-00-0000:00:0001 bug
drop table t1,t2;
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES (1, 2);
INSERT INTO t1 VALUES (3, 4);
INSERT INTO t1 VALUES (5, NULL);
SELECT * FROM t1 ORDER BY b;
a b 5 NULL 12 34
SELECT * FROM t1 ORDER BY b DESC;
a b 34 12 5 NULL
SELECT * FROM t1 ORDER BY (a + b);
a b 5 NULL 12 34
SELECT * FROM t1 ORDER BY (a + b) DESC;
a b 34 12 5 NULL
DROP TABLE t1;
create table t1(id int not null auto_increment primary key, t char(12));
explain select id,t from t1 order by id;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 Using filesort
explain select id,t from t1 force index (primary) order by id;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 1000
drop table t1;
CREATE TABLE t1 (
FieldKey varchar(36) NOT NULL default '',
LongVal bigint(20) default NULL,
StringVal mediumtext,
KEY FieldKey (FieldKey),
KEY LongField (FieldKey,LongVal),
KEY StringField (FieldKey,StringVal(32))
);
INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3');
EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref FieldKey,LongField,StringField LongField 38 const 3 Using where
SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
FieldKey LongVal StringVal 102 113 121
DS-MRR: use two IGNORE INDEX queries, otherwise we get cost races, because
DS-MRR: records_in_range/read_time return the same numbers for all three indexes
EXPLAIN SELECT * FROM t1 IGNORE INDEX (LongField, StringField) WHERE FieldKey > '2' ORDER BY LongVal;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range FieldKey FieldKey 38 NULL 3 Using index condition; Using filesort
EXPLAIN SELECT * FROM t1 IGNORE INDEX (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range StringField StringField 38 NULL 3 Using where; Using filesort
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
FieldKey LongVal StringVal 312 321 333
EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range FieldKey,LongField,StringField LongField 38 NULL 3 Using where
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
FieldKey LongVal StringVal 312 321 333
DROP TABLE t1;
CREATE TABLE t1 (a INT, b INT); SET @id=0;
UPDATE t1 SET a=0 ORDER BY (a=@id), b;
DROP TABLE t1;
CREATE TABLE t1 ( id smallint(6) unsigned NOT NULL default '0', menu tinyint(4) NOT NULL default '0', KEY id (id), KEY menu (menu)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (11384, 2),(11392, 2);
SELECT id FROM t1 WHERE id <11984AND menu =2 ORDER BY id DESC LIMIT 1;
id 11392
drop table t1;
create table t1(a int, b int, index(b));
insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
insert into t1 values (12, 11), (11, 11), (14, 3), (13, 5), (16, 12), (15, 12);
explain select * from t1 where b=1or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=1or b is null order by a;
a b 11 21 3 NULL 4 NULL
explain select * from t1 where b=2or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=2or b is null order by a;
a b 3 NULL 4 NULL 52 62
drop table t1;
create table t1 (a int not null auto_increment, b int not null, c int not null, d int not null,
key(a,b,d), key(c,b,a));
create table t2 like t1;
insert into t1 values (NULL, 1, 2, 0), (NULL, 2, 1, 1), (NULL, 3, 4, 2), (NULL, 4, 3, 3);
insert into t2 select null, b, c, d from t1;
insert into t1 select null, b, c, d from t2;
insert into t2 select null, b, c, d from t1;
insert into t1 select null, b, c, d from t2;
insert into t2 select null, b, c, d from t1;
insert into t1 select null, b, c, d from t2;
insert into t2 select null, b, c, d from t1;
insert into t1 select null, b, c, d from t2;
insert into t2 select null, b, c, d from t1;
insert into t1 select null, b, c, d from t2;
optimize table t1;
Table Op Msg_type Msg_text
test.t1 optimize status OK set @row=10;
insert into t1 select 1, b, c + (@row:=@row - 1) * 10, d - @row from t2 limit 10;
select * from t1 where a=1and b in (1) order by c, b, a;
a b c d 1120 1112 -1 1152 -5 1192 -9
select * from t1 where a=1and b in (1);
a b c d 1192 -9 1152 -5 1112 -1 1120
drop table t1, t2;
create table t1 (col1 int, col int);
create table t2 (col2 int, col int);
insert into t1 values (1,1),(2,2),(3,3);
insert into t2 values (1,3),(2,2),(3,1);
select t1.* , t2.col as t2_col from t1 left join t2 on (t1.col1=t2.col2)
order by col;
col1 col t2_col 113 222 331
select col1 as col, col from t1 order by col; ERROR23000: Column 'col' in ORDER BY is ambiguous
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
order by col; ERROR23000: Column 'col' in ORDER BY is ambiguous
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
order by col; ERROR23000: Column 'col' in ORDER BY is ambiguous
select col1 from t1, t2 where t1.col1=t2.col2 order by col; ERROR23000: Column 'col' in ORDER BY is ambiguous
select t1.col as t1_col, t2.col2 from t1, t2 where t1.col1=t2.col2
order by col; ERROR23000: Column 'col' in ORDER BY is ambiguous
select t1.col as t1_col, t2.col from t1, t2 where t1.col1=t2.col2
order by col;
t1_col col 31 22 13
select col2 as c, col as c from t2 order by col;
c c 31 22 13
select col2 as col, col as col2 from t2 order by col;
col col2 13 22 31
select t2.col2, t2.col, t2.col from t2 order by col;
col2 col col 311 222 133
select t2.col2 as col from t2 order by t2.col;
col 3 2 1
select t2.col2 as col, t2.col from t2 order by t2.col;
col col 31 22 13
select t2.col2, t2.col, t2.col from t2 order by t2.col;
col2 col col 311 222 133
drop table t1, t2;
create table t1 (a char(70));
insert into t1 set a = repeat('x', 20);
insert into t1 set a = concat(repeat('x', 63), 'z');
insert into t1 set a = concat(repeat('x', 63), 'ab');
insert into t1 set a = concat(repeat('x', 63), 'aa'); set max_sort_length=20;
Warnings:
Warning 1292 Truncated incorrect max_sort_length value: '20'
select a from t1 order by a;
a
xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxab
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxaa
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxz
Warnings:
Warning 42022 values were longer than max_sort_length. Sorting used only the first 64 bytes
drop table t1;
create table t1 (
`sid` decimal(8,0) default null,
`wnid` varchar(11) not null default '',
key `wnid14` (`wnid`(4)),
key `wnid` (`wnid`)
) engine=myisam default charset=latin1;
insert into t1 (`sid`, `wnid`) values
('10100','01019000000'),('37986','01019000000'),('37987','01019010000'),
('37994','01019090000'),('475','02070000000'),('25253','02071100000'),
('25255','02071100000'),('25256','02071110000'),('25258','02071130000'),
('25259','02071190000'),('25260','02071200000'),('25261','02071210000'),
('25262','02071290000'),('25263','02071300000'),('25264','02071310000'),
('25265','02071310000'),('25266','02071320000'),('25267','02071320000'),
('25269','02071330000'),('25270','02071340000'),('25271','02071350000'),
('25272','02071360000'),('25273','02071370000'),('25281','02071391000'),
('25282','02071391000'),('25283','02071399000'),('25284','02071400000'),
('25285','02071410000'),('25286','02071410000'),('25287','02071420000'),
('25288','02071420000'),('25291','02071430000'),('25290','02071440000'),
('25292','02071450000'),('25293','02071460000'),('25294','02071470000'),
('25295','02071491000'),('25296','02071491000'),('25297','02071499000');
explain select * from t1 where wnid like '0101%' order by wnid;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range wnid14,wnid wnid 13 NULL 4 Using where
select * from t1 where wnid like '0101%' order by wnid;
sid wnid 1010001019000000 3798601019000000 3798701019010000 3799401019090000
drop table t1;
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (2), (1), (1), (2), (1);
SELECT a FROM t1 ORDER BY a;
a 1 1 1 2 2
(SELECT a FROM t1) ORDER BY a;
a 1 1 1 2 2
DROP TABLE t1;
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10);
(SELECT b,a FROM t1 ORDER BY a,b) ORDER BY b,a;
b a 101 102 201 202 301 302
(SELECT b FROM t1 ORDER BY b DESC) ORDER BY b ASC;
b 10 10 20 20 30 30
(SELECT b,a FROM t1 ORDER BY b,a) ORDER BY a,b;
b a 101 201 301 102 202 302
(SELECT b,a FROM t1 ORDER by b,a LIMIT 3) ORDER by a,b;
b a 101 201 102
DROP TABLE t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
SELECT a + 1 AS num FROM t1 ORDER BY 30 - num;
num 3 2
SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str);
str
test1
test2
SELECT a + 1 AS num FROM t1 GROUP BY 30 - num;
num 3 2
SELECT a + 1 AS num FROM t1 HAVING 30 - num;
num 2 3
SELECT a + 1 AS num, num + 1 FROM t1; ERROR42S22: Unknown column 'num' in 'SELECT'
SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1;
num (select num + 2 FROM t1 LIMIT 1) 24 35
SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a; ERROR42S22: Unknown column 'num' in 'ON'
DROP TABLE t1;
CREATE TABLE bug25126 (
val int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
);
UPDATE bug25126 SET MissingCol = MissingCol; ERROR42S22: Unknown column 'MissingCol' in 'SET'
UPDATE bug25126 SET val = val ORDER BY MissingCol; ERROR42S22: Unknown column 'MissingCol' in 'ORDER BY'
UPDATE bug25126 SET val = val ORDER BY val;
UPDATE bug25126 SET val = 1 ORDER BY val;
UPDATE bug25126 SET val = 1 ORDER BY MissingCol; ERROR42S22: Unknown column 'MissingCol' in 'ORDER BY'
UPDATE bug25126 SET val = 1 ORDER BY val, MissingCol; ERROR42S22: Unknown column 'MissingCol' in 'ORDER BY'
UPDATE bug25126 SET val = MissingCol ORDER BY MissingCol; ERROR42S22: Unknown column 'MissingCol' in 'ORDER BY'
UPDATE bug25126 SET MissingCol = 1 ORDER BY val, MissingCol; ERROR42S22: Unknown column 'MissingCol' in 'SET'
UPDATE bug25126 SET MissingCol = 1 ORDER BY MissingCol; ERROR42S22: Unknown column 'MissingCol' in 'SET'
UPDATE bug25126 SET MissingCol = val ORDER BY MissingCol; ERROR42S22: Unknown column 'MissingCol' in 'SET'
UPDATE bug25126 SET MissingCol = MissingCol ORDER BY MissingCol; ERROR42S22: Unknown column 'MissingCol' in 'SET'
DROP TABLE bug25126;
CREATE TABLE t1 (a int);
SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1;
val val1
SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val; ERROR23000: Column 'val' in ORDER BY is ambiguous
SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1; ERROR23000: Column 'val' in ORDER BY is ambiguous
DROP TABLE t1;
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (3), (2), (4), (1);
SELECT a, IF(a IN (2,3), a, a+10) FROM t1
ORDER BY IF(a IN (2,3), a, a+10);
a IF(a IN (2,3), a, a+10) 22 33 111 414
SELECT a, IF(a NOT IN (2,3), a, a+10) FROM t1
ORDER BY IF(a NOT IN (2,3), a, a+10);
a IF(a NOT IN (2,3), a, a+10) 11 44 212 313
SELECT a, IF(a IN (2,3), a, a+10) FROM t1
ORDER BY IF(a NOT IN (2,3), a, a+10);
a IF(a IN (2,3), a, a+10) 111 414 22 33
SELECT a, IF(a BETWEEN 2AND3, a, a+10) FROM t1
ORDER BY IF(a BETWEEN 2AND3, a, a+10);
a IF(a BETWEEN 2AND3, a, a+10) 22 33 111 414
SELECT a, IF(a NOT BETWEEN 2AND3, a, a+10) FROM t1
ORDER BY IF(a NOT BETWEEN 2AND3, a, a+10);
a IF(a NOT BETWEEN 2AND3, a, a+10) 11 44 212 313
SELECT a, IF(a BETWEEN 2AND3, a, a+10) FROM t1
ORDER BY IF(a NOT BETWEEN 2AND3, a, a+10);
a IF(a BETWEEN 2AND3, a, a+10) 111 414 22 33
SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2
FROM t1 GROUP BY x1, x2;
x1 x2 3 4 1 2
SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2
FROM t1 GROUP BY x1, IF(a NOT IN (1,2), a, '');
x1 x2 3 4 1 2
SELECT a, a IN (1,2) FROM t1 ORDER BY a IN (1,2);
a a IN (1,2) 30 40 21 11
SELECT a FROM t1 ORDER BY a IN (1,2);
a 3 4 2 1
SELECT a+10 FROM t1 ORDER BY a IN (1,2);
a+10 13 14 12 11
SELECT a, IF(a IN (1,2), a, a+10) FROM t1
ORDER BY IF(a IN (3,4), a, a+10);
a IF(a IN (1,2), a, a+10) 313 414 11 22
DROP TABLE t1;
create table t1 (a int not null, b int not null, c int not null);
insert t1 values (1,1,1),(1,1,2),(1,2,1);
select a, b from t1 group by a, b order by sum(c);
a b 12 11
drop table t1;
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
explain SELECT t1.b as a, t2.b as c FROM
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
ORDER BY c;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where
SELECT t2.b as c FROM
t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
ORDER BY c;
c
NULL
NULL 2
explain SELECT t1.b as a, t2.b as c FROM
t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)
ORDER BY c;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 SELECT * from t1;
CREATE TABLE t3 LIKE t1;
INSERT INTO t3 SELECT * from t1;
CREATE TABLE t4 LIKE t1;
INSERT INTO t4 SELECT * from t1;
INSERT INTO t1 values (0,0),(4,4);
SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b;
b
NULL
NULL 1 2 3
DROP TABLE t1,t2,t3,t4;
create table t1 (a int, b int, c int);
insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9);
select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc;
a ratio 10.5000 191.3333 92.6667
drop table t1;
CREATE TABLE t1 (a INT UNSIGNED NOT NULL, b TIME);
INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10);
UPDATE t1 SET b = SEC_TO_TIME(a);
SELECT a, b FROM t1 ORDER BY b DESC;
a b 1000000277:46:40 10000027:46:40 1000002:46:40 100000:16:40 10000:01:40 1000:00:10 000:00:00
SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC;
a b 1000000277:46:40 10000027:46:40 1000002:46:40 100000:16:40 10000:01:40 1000:00:10 000:00:00
DROP TABLE t1;
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
INSERT INTO t1 VALUES (1,1),(2,2);
CREATE TABLE t2 (a INT, b INT, KEY a (a,b));
INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2);
EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2AND t1.a=t2.a ORDER BY t2.b;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY,b b 5 const 1 1 SIMPLE t2 ref a a 5 const 2 Using where; Using index
DROP TABLE t1,t2;
CREATE TABLE t1(
id int auto_increment PRIMARY KEY, c2 int, c3 int, INDEX k2(c2), INDEX k3(c3));
INSERT INTO t1 (c2,c3) VALUES
(31,34),(35,38),(34,31),(32,35),(31,39),
(11,14),(15,18),(14,11),(12,15),(11,19);
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
INSERT INTO t1 (c2,c3) SELECT c2,c3 FROM t1;
UPDATE t1 SET c2=20 WHERE id%100 = 0;
SELECT COUNT(*) FROM t1;
COUNT(*) 40960
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 SELECT * FROM t1 ORDER BY id;
EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index k2 k3 5 NULL 111 Using where
EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range k2 k2 5 NULL 7341 Using index condition; Using filesort
EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 6000;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL k2 NULL NULL NULL 40960 Using where; Using filesort
EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 10AND12 ORDER BY c3 LIMIT 20;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index k2 k3 5 NULL 73 Using where
EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20AND30 ORDER BY c3 LIMIT 4000;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range k2 k2 5 NULL 386 Using index condition; Using filesort
SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20;
id c3 614 1614 2614 3614 4614 5614 6614 7614 8614 9614 10614 11614 12614 13614 14614 15614 16614 17614 18614 19614
ALTER TABLE t2 DROP INDEX k3, ADD INDEX k3 (c3,c2);
EXPLAIN SELECT c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index k2 k3 10 NULL 22318 Using where; Using index
DROP TABLE t1,t2;
CREATE TABLE t1 (
a INT,
b INT,
PRIMARY KEY (a),
KEY ab(a, b)
);
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4);
INSERT INTO t1 SELECT a + 4, b + 4 FROM t1;
INSERT INTO t1 SELECT a + 8, b + 8 FROM t1;
INSERT INTO t1 SELECT a +16, b +16 FROM t1;
INSERT INTO t1 SELECT a +32, b +32 FROM t1;
INSERT INTO t1 SELECT a +64, b +64 FROM t1;
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using index
SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
a 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
SELECT @tmp_tables_after = @tmp_tables_before ;
@tmp_tables_after = @tmp_tables_before 1
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using index
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
a 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
SELECT @tmp_tables_after = @tmp_tables_before;
@tmp_tables_after = @tmp_tables_before 1
DROP TABLE t1;
#
# Bug#31590: Wrong error message on sort buffer being too small.
#
create table t1(a int, b tinytext);
insert into t1 values (1,2),(3,2); set session sort_buffer_size= 1000;
Warnings:
Warning 1292 Truncated incorrect sort_buffer_size value: '1000' set session max_sort_length= 2180;
CALL mtr.add_suppression("Out of sort memory");
select * from t1 order by b; ERROR HY001: Out of sort memory, consider increasing server sort buffer size
drop table t1; set session sort_buffer_size= 30000;
#
# Bug #39844: Query Crash Mysql Server 5.0.67
#
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE TABLE t2 (a INT PRIMARY KEY, b INT);
CREATE TABLE t3 (c INT);
INSERT INTO t1 (a) VALUES (1), (2);
INSERT INTO t2 (a,b) VALUES (1,2), (2,3);
INSERT INTO t3 (c) VALUES (1), (2);
SELECT
(SELECT t1.a FROM t1, t2 WHERE t1.a = t2.b AND t2.a = t3.c ORDER BY t1.a) as exp
FROM t3;
exp 2
NULL
DROP TABLE t1, t2, t3;
#
# Bug #42760: Select doesn't return desired results when we have null
# values
#
CREATE TABLE t1 (
a INT,
c INT,
UNIQUE KEY a_c (a,c),
KEY (a));
INSERT INTO t1 VALUES (1, 10), (2, NULL);
# Must use ref-or-null on the a_c index
ANALYZE FORMAT=JSON
SELECT 1 AS col FROM t1 WHERE a=2AND (c=10OR c IS NULL) ORDER BY c;
ANALYZE
{ "query_optimization": { "r_total_time_ms": "REPLACED"
}, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [
{ "read_sorted_file": { "r_rows": 1, "filesort": { "sort_key": "t1.c", "r_loops": 1, "r_total_time_ms": "REPLACED", "r_used_priority_queue": false, "r_output_rows": 1, "r_buffer_size": "REPLACED", "r_sort_mode": "sort_key,addon_fields", "table": { "table_name": "t1", "access_type": "ref_or_null", "possible_keys": ["a_c", "a"], "key": "a_c", "key_length": "10", "used_key_parts": ["a", "c"], "ref": ["const", "const"], "loops": 1, "r_loops": 1, "rows": 2, "r_rows": 1, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 50, "r_total_filtered": 100, "attached_condition": "t1.c = 10 or t1.c is null", "r_filtered": 100, "using_index": true
}
}
}
}
]
}
}
EXPLAIN
SELECT 1 AS col FROM t1 WHERE a=2AND (c=10OR c IS NULL) ORDER BY c;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref_or_null a_c,a a_c 10 const,const 2 Using where; Using index; Using filesort
# Must return1 row
SELECT 1 AS col FROM t1 WHERE a=2AND (c=10OR c IS NULL) ORDER BY c;
col 1
# Must use ref-or-null on the a_c index
EXPLAIN
SELECT 1 AS col FROM t1 WHERE a=2AND (c=10OR c IS NULL) ORDER BY c DESC;
id select_type table type possible_keys key key_len ref rows Extra
x x x ref_or_null a_c,a x x x x x
# Must return1 row
SELECT 1 AS col FROM t1 WHERE a=2AND (c=10OR c IS NULL) ORDER BY c DESC;
col 1
DROP TABLE t1;
End of 5.0 tests
CREATE TABLE t2 (a varchar(32), b int(11), c float, d double,
UNIQUE KEY a (a,b,c), KEY b (b), KEY c (c));
CREATE TABLE t1 (a varchar(32), b char(3), UNIQUE KEY a (a,b), KEY b (b));
CREATE TABLE t3 (a varchar(32), b char(3), UNIQUE KEY a (a,b));
INSERT INTO t3 SELECT * FROM t1;
EXPLAIN
SELECT d FROM t1, t2
WHERE t2.b=14AND t2.a=t1.a AND5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using where; Using temporary; Using filesort 1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using index condition
SELECT d FROM t1, t2
WHERE t2.b=14AND t2.a=t1.a AND5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 1;
d 52.5
EXPLAIN
SELECT d FROM t3 AS t1, t2 AS t2
WHERE t2.b=14AND t2.a=t1.a AND5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range a,b,c c 5 NULL 420 Using where 1 SIMPLE t1 eq_ref a a 39 test.t2.a,const 1 Using where; Using index
SELECT d FROM t3 AS t1, t2 AS t2
WHERE t2.b=14AND t2.a=t1.a AND5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 1;
d 52.5
SELECT t1.*,t2.* FROM t1, t2
WHERE t2.b=14AND t2.a=t1.a AND5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 5;
a b a b c d
ppfcz1 DE ppfcz1 14652.5
ppfcz1 DE ppfcz1 14755.5
ppfcz1 DE ppfcz1 14857.5
ppfcz1 DE ppfcz1 14959.5
ppfcz1 DE ppfcz1 141061.5
SELECT t1.*, t2.* FROM t3 AS t1, t2 AS t2
WHERE t2.b=14AND t2.a=t1.a AND5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 5;
a b a b c d
ppfcz1 DE ppfcz1 14652.5
ppfcz1 DE ppfcz1 14755.5
ppfcz1 DE ppfcz1 14857.5
ppfcz1 DE ppfcz1 14959.5
ppfcz1 DE ppfcz1 141061.5
DROP TABLE t1,t2,t3;
#
# WL#1393 - Optimizing filesort with small limit
#
CREATE TABLE t1(f0 int auto_increment primary key, f1 int, f2 varchar(200));
INSERT INTO t1(f1, f2) VALUES
(0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"),
(6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"),
(11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"),
(16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"),
(21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"),
(26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"),
(31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"),
(36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"),
(41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"),
(46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"),
(51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"),
(56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"),
(61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"),
(66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"),
(71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"),
(76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"),
(81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"),
(86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"),
(91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"),
(96,"96"),(97,"97"),(98,"98"),(99,"99");
SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 100;
f0 f1 f2 100 211 322 433 544 655 766 877 988 1099 111010 121111 131212 141313 151414 161515 171616 181717 191818 201919 212020 222121 232222 242323 252424 262525 272626 282727 292828 302929 313030 323131 333232 343333 353434 363535 373636 383737 393838 403939 414040 424141 434242 444343 454444 464545 474646 484747 494848 504949 515050 525151 535252 545353 555454 565555 575656 585757 595858 605959 616060 626161 636262 646363 656464 666565 676666 686767 696868 706969 717070 727171 737272 747373 757474 767575 777676 787777 797878 807979 818080 828181 838282 848383 858484 868585 878686 888787 898888 908989 919090 929191 939292 949393 959494 969595 979696 989797 999898 1009999
SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30;
f0 f1 f2 100 211 322 433 544 655 766 877 988 1099 111010 121111 131212 141313 151414 161515 171616 181717 191818 201919 212020 222121 232222 242323 252424 262525 272626 282727 292828 302929
SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0;
f0 f1 f2
SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30;
f0 f1 f2 1009999 999898 989797 979696 969595 959494 949393 939292 929191 919090 1099 908989 898888 888787 878686 868585 858484 848383 838282 828181 818080 988 807979 797878 787777 777676 767575 757474 747373 737272
SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0;
f0 f1 f2
SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20;
f0 f1 f2 121111 131212 141313 151414 161515 171616 181717 191818 201919 212020 222121 232222 242323 252424 262525 272626 282727 292828 302929 313030
SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0;
f0 f1 f2
SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10;
f0 f1 f2 222121 232222 242323 252424 262525 272626 282727 292828 302929 313030
SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10;
f0 f1 f2 set sort_buffer_size= 32768;
CREATE TEMPORARY TABLE tmp (f1 int, f2 varchar(20));
INSERT INTO tmp SELECT f1, f2 FROM t1;
INSERT INTO t1(f1, f2) SELECT * FROM tmp;
INSERT INTO tmp SELECT f1, f2 FROM t1;
INSERT INTO t1(f1, f2) SELECT * FROM tmp;
SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30;
f0 f1 f2 100 10100 20100 30100 40100 211 10211 20211 30211 40211 322 10322 20322 30322 40322 433 10433 20433 30433 40433 544 10544 20544 30544 40544 655 10655 20655 30655 40655
SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0;
f0 f1 f2
SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30;
f0 f1 f2 1009999 2009999 3009999 4009999 5009999 999898 1999898 2999898 3999898 4999898 989797 1989797 2989797 3989797 4989797 979696 1979696 2979696 3979696 4979696 969595 1969595 2969595 3969595 4969595 959494 1959494 2959494 3959494 4959494
SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0;
f0 f1 f2
SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20;
f0 f1 f2 121111 1121111 2121111 3121111 4121111 131212 1131212 2131212 3131212 4131212 141313 1141313 2141313 3141313 4141313 151414 1151414 2151414 3151414 4151414
SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0;
f0 f1 f2
SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10;
f0 f1 f2 141313 1141313 2141313 3141313 4141313 151414 1151414 2151414 3151414 4151414
SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10;
f0 f1 f2 set sort_buffer_size= 32768;
SELECT SQL_CALC_FOUND_ROWS * FROM t1
ORDER BY f1, f0 LIMIT 30;
f0 f1 f2 100 10100 20100 30100 40100 211 10211 20211 30211 40211 322 10322 20322 30322 40322 433 10433 20433 30433 40433 544 10544 20544 30544 40544 655 10655 20655 30655 40655
SELECT FOUND_ROWS();
FOUND_ROWS() 500
SELECT SQL_CALC_FOUND_ROWS * FROM t1
ORDER BY f1, f0 LIMIT 0;
f0 f1 f2
SELECT FOUND_ROWS();
FOUND_ROWS() 500
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10
ORDER BY f2, f0 LIMIT 20;
f0 f1 f2 121111 1121111 2121111 3121111 4121111 131212 1131212 2131212 3131212 4131212 141313 1141313 2141313 3141313 4141313 151414 1151414 2151414 3151414 4151414
SELECT FOUND_ROWS();
FOUND_ROWS() 445
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10
ORDER BY f2, f0 LIMIT 0;
f0 f1 f2
SELECT FOUND_ROWS();
FOUND_ROWS() 445
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10
ORDER BY f2, f0 LIMIT 10 OFFSET 10;
f0 f1 f2 141313 1141313 2141313 3141313 4141313 151414 1151414 2151414 3151414 4151414
SELECT FOUND_ROWS();
FOUND_ROWS() 445
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10
ORDER BY f2, f0 LIMIT 0 OFFSET 10;
f0 f1 f2
SELECT FOUND_ROWS();
FOUND_ROWS() 445 set sort_buffer_size= 327680;
SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2
ORDER BY tmp.f1, f0 LIMIT 30;
f0 f1 f2 f1 f2 10000 10000 10000 1010000 1010000 1010000 2010000 2010000 2010000 3010000 3010000 3010000 4010000 4010000 4010000 21111 21111 21111 1021111 1021111 1021111 2021111 2021111 2021111 3021111 3021111 3021111 4021111 4021111 4021111
SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2
ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30;
f0 f1 f2 f1 f2 32222 32222 32222 1032222 1032222 1032222 2032222 2032222 2032222 3032222 3032222 3032222 4032222 4032222 4032222 43333 43333 43333 1043333 1043333 1043333 2043333 2043333 2043333 3043333 3043333 3043333 4043333 4043333 4043333
SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2
ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30;
f0 f1 f2 f1 f2 32222 32222 32222 1032222 1032222 1032222 2032222 2032222 2032222 3032222 3032222 3032222 4032222 4032222 4032222 43333 43333 43333 1043333 1043333 1043333 2043333 2043333 2043333 3043333 3043333 3043333 4043333 4043333 4043333
SELECT FOUND_ROWS();
FOUND_ROWS() 1500
SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2
WHERE t1.f2>20
ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30;
f0 f1 f2 f1 f2 2423232323 2423232323 2423232323 12423232323 12423232323 12423232323 22423232323 22423232323 22423232323 32423232323 32423232323 32423232323 42423232323 42423232323 42423232323 2524242424 2524242424 2524242424 12524242424 12524242424 12524242424 22524242424 22524242424 22524242424 32524242424 32524242424 32524242424 42524242424 42524242424 42524242424
SELECT FOUND_ROWS();
FOUND_ROWS() 1185
CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 30;
SELECT * FROM v1;
f0 f1 f2 100 10100 20100 30100 40100 211 10211 20211 30211 40211 322 10322 20322 30322 40322 433 10433 20433 30433 40433 544 10544 20544 30544 40544 655 10655 20655 30655 40655
drop view v1;
CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 100;
SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30;
f0 f1 f2 100 10100 20100 30100 40100 211 10211 20211 30211 40211 111010 1111010 2111010 3111010 4111010 121111 1121111 2121111 3121111 4121111 131212 1131212 2131212 3131212 4131212 141313 1141313 2141313 3141313 4141313
CREATE VIEW v2 as SELECT * FROM t1 ORDER BY f2, f0 LIMIT 100;
SELECT * FROM v1 JOIN v2 on v1.f1=v2.f1 ORDER BY v1.f2,v1.f0,v2.f0
LIMIT 30;
f0 f1 f2 f0 f1 f2 100100 10010100 10020100 10030100 10040100 10100100 1010010100 1010020100 1010030100 1010040100 20100100 2010010100 2010020100 2010030100 2010040100 30100100 3010010100 3010020100 3010030100 3010040100 40100100 4010010100 4010020100 4010030100 4010040100 211211 21110211 21120211 21130211 21140211
SELECT floor(f1/10) f3, count(f2) FROM t1
GROUP BY 1 ORDER BY 2,1 LIMIT 5;
f3 count(f2) 050 150 250 350 450
SELECT floor(f1/10) f3, count(f2) FROM t1
GROUP BY 1 ORDER BY 2,1 LIMIT 0;
f3 count(f2)
CREATE PROCEDURE wl1393_sp_test()
BEGIN
SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 30;
SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 15 OFFSET 15;
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10
ORDER BY f2, f0 LIMIT 15 OFFSET 15;
SELECT FOUND_ROWS();
SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30;
END|
CALL wl1393_sp_test()|
f0 f1 f2 121111 1121111 2121111 3121111 4121111 131212 1131212 2131212 3131212 4131212 141313 1141313 2141313 3141313 4141313 151414 1151414 2151414 3151414 4151414 161515 1161515 2161515 3161515 4161515 171616 1171616 2171616 3171616 4171616
f0 f1 f2 151414 1151414 2151414 3151414 4151414 161515 1161515 2161515 3161515 4161515 171616 1171616 2171616 3171616 4171616
f0 f1 f2 151414 1151414 2151414 3151414 4151414 161515 1161515 2161515 3161515 4161515 171616 1171616 2171616 3171616 4171616
FOUND_ROWS() 445
f0 f1 f2 100 10100 20100 30100 40100 211 10211 20211 30211 40211 111010 1111010 2111010 3111010 4111010 121111 1121111 2121111 3121111 4121111 131212 1131212 2131212 3131212 4131212 141313 1141313 2141313 3141313 4141313
DROP PROCEDURE wl1393_sp_test|
SELECT d1.f1, d1.f2 FROM t1
LEFT JOIN (SELECT * FROM t1 ORDER BY f1 LIMIT 30) d1 on t1.f1=d1.f1
ORDER BY d1.f2 DESC LIMIT 30;
f1 f2 55 55 55 55 55 55 55 55 55 55 55 55 55 55 55 55 55 55 55 55 55 55 55 55 55 44 44 44 44 44
SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 1);
f0 f1 f2 100 10100 20100 30100 40100
SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 2); ERROR21000: Subquery returns more than 1 row
DROP TABLE t1, tmp;
DROP VIEW v1, v2;
# end of WL#1393 - Optimizing filesort with small limit
#
# Bug #58761
# Crash in Field::is_null in field.h on subquery in WHERE clause
#
CREATE TABLE t1 (
pk INT NOT NULL AUTO_INCREMENT,
col_int_key INT DEFAULT NULL,
col_varchar_key VARCHAR(1) DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key,col_int_key)
);
INSERT INTO t1 VALUES (27,7,'x');
INSERT INTO t1 VALUES (28,6,'m');
INSERT INTO t1 VALUES (29,4,'c');
CREATE TABLE where_subselect
SELECT DISTINCT `pk` AS field1 , `pk` AS field2
FROM t1 AS alias1
WHERE alias1 . `col_int_key` > 229 OR alias1 . `col_varchar_key` IS NOT NULL
GROUP BY field1, field2 ;
SELECT *
FROM where_subselect
WHERE (field1, field2) IN (
SELECT DISTINCT `pk` AS field1 , `pk` AS field2
FROM t1 AS alias1
WHERE alias1 . `col_int_key` > 229 OR alias1 . `col_varchar_key` IS NOT NULL
GROUP BY field1, field2
);
field1 field2 2727 2828 2929
DROP TABLE t1;
DROP TABLE where_subselect;
# End of Bug #58761
CREATE TABLE t1 (
id1 INT NULL,
id2 INT NOT NULL,
junk INT NOT NULL,
PRIMARY KEY (id1, id2, junk),
INDEX id2_j_id1 (id2, junk, id1)
);
INSERT INTO t1 VALUES (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 1, 4);
INSERT INTO t1 VALUES (5, 2, 1), (6, 2, 2), (7, 2, 3), (8, 2, 4);
INSERT INTO t1 VALUES (9, 3, 1), (10, 3, 2), (11, 3, 3), (12, 3, 4);
INSERT INTO t1 VALUES (13, 4, 1), (14, 4, 2), (15, 4, 3), (16, 4, 4);
INSERT INTO t1 VALUES (17, 5, 1), (18, 5, 2), (19, 5, 3), (20, 5, 4);
INSERT INTO t1 VALUES (21, 6, 1), (22, 6, 2), (23, 6, 3), (24, 6, 4);
INSERT INTO t1 VALUES (25, 7, 1), (26, 7, 2), (27, 7, 3), (28, 7, 4);
INSERT INTO t1 VALUES (29, 8, 1), (30, 8, 2), (31, 8, 3), (32, 8, 4);
INSERT INTO t1 VALUES (33, 9, 1), (34, 9, 2), (35, 9, 3), (36, 9, 4);
EXPLAIN SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref id2_j_id1 id2_j_id1 4 const 4 Using where; Using index; Using filesort
SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1;
id1 13 14 15 16
DROP TABLE t1;
CREATE TABLE t1 (
a INT,
b INT NOT NULL,
c char(100),
KEY (b, c),
KEY (b, a, c)
)
DEFAULT CHARSET = utf8;
INSERT INTO t1 VALUES
(1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 4, 4),
(5, 5, 5),
(6, 6, 6),
(7, 7, 7),
(8, 8, 8),
(9, 9, 9);
INSERT INTO t1 SELECT a + 10, b, c FROM t1;
INSERT INTO t1 SELECT a + 20, b, c FROM t1;
INSERT INTO t1 SELECT a + 40, b, c FROM t1;
INSERT INTO t1 SELECT a + 80, b, c FROM t1;
INSERT INTO t1 SELECT a + 160, b, c FROM t1;
INSERT INTO t1 SELECT a + 320, b, c FROM t1;
INSERT INTO t1 SELECT a + 640, b, c FROM t1;
INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80;
EXPLAIN
SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where
SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
a 2071 2061 2051 2041 2031 2021 2011 2001 1991
EXPLAIN
SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where; Using temporary
SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9;
a 2071 2061 2051 2041 2031 2021 2011 2001 1991
DROP TABLE t1;
#
# Bug #43029: FORCE INDEX FOR ORDER BY is ignored when join buffering
# is used
#
CREATE TABLE t1 (a INT, b INT, KEY (a));
INSERT INTO t1 VALUES (0, NULL), (1, NULL), (2, NULL), (3, NULL);
INSERT INTO t1 SELECT a+4, b FROM t1;
INSERT INTO t1 SELECT a+8, b FROM t1;
CREATE TABLE t2 (a INT, b INT);
INSERT INTO t2 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL), (4,NULL);
INSERT INTO t2 SELECT a+4, b FROM t2;
# shouldn't have "using filesort"
EXPLAIN
SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using temporary; Using filesort 1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join)
# should have "using filesort"
EXPLAIN
SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using temporary; Using filesort 1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join)
# should have "using filesort"
EXPLAIN
SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using temporary; Using filesort 1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join)
DROP TABLE t1, t2;
#
# Bug #50394: Regression in EXPLAIN with index scan, LIMIT, GROUP BY and
# ORDER BY computed col
#
CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, KEY( a, b ) );
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
INSERT INTO t1 SELECT a + 5, b + 5 FROM t1;
CREATE TABLE t2( a INT PRIMARY KEY, b INT );
INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
INSERT INTO t2 SELECT a + 5, b + 5 FROM t2;
EXPLAIN
SELECT count(*) AS c, t1.a
FROM t1 JOIN t2 ON t1.b = t2.a
WHERE t2.b = 1
GROUP BY t1.a
ORDER by c
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 8 NULL 10 Using index; Using temporary; Using filesort 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where
DROP TABLE t1, t2;
#
# Bug #707848: WHERE condition with OR + ORDER BY + field substitution
#
CREATE TABLE t1 (a int PRIMARY KEY);
INSERT INTO t1 VALUES
(9), (7), (11), (15), (2), (4), (1), (5), (14), (54), (3), (8);
EXPLAIN EXTENDED
SELECT * FROM t1 r JOIN t1 s ON r.a = s.a
WHERE s.a IN (2,9) OR s.a < 100AND s.a != 0
ORDER BY 1 LIMIT 10;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE r range PRIMARY PRIMARY 4 NULL 12100.00 Using where; Using index 1 SIMPLE s eq_ref PRIMARY PRIMARY 4 test.r.a 1100.00 Using index
Warnings:
Note 1003 select `test`.`r`.`a` AS `a`,`test`.`s`.`a` AS `a` from `test`.`t1` `r` join `test`.`t1` `s` where `test`.`s`.`a` = `test`.`r`.`a` and (`test`.`r`.`a` in (2,9) or `test`.`r`.`a` < 100and `test`.`r`.`a` <> 0) order by 1 limit 10
SELECT * FROM t1 r JOIN t1 s ON r.a = s.a
WHERE s.a IN (2,9) OR s.a < 100AND s.a != 0
ORDER BY 1 LIMIT 10;
a a 11 22 33 44 55 77 88 99 1111 1414
DROP TABLE t1;
#
# Bug #59110: Memory leak of QUICK_SELECT_I allocated memory
# and
# Bug #59308: Incorrect result for
SELECT DISTINCT <col>... ORDER BY <col> DESC
# Use Valgrind to detect #59110!
#
CREATE TABLE t1 (a INT,KEY (a));
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
EXPLAIN SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 10 Using where; Using index
SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
a 1 101 91 81 71 61 51 41 31 21
DROP TABLE t1;
#
# Bug#1176525558201:
# VALGRIND/CRASH WHEN ORDERING BY MULTIPLE AGGREGATE FUNCTIONS
#
select 1 order by max(1) + min(1); 1 1
End of 5.1 tests
#
# Bug #38745: MySQL 5.1 optimizer uses filesort for ORDER BY
# when it should use index
#
CREATE TABLE t1 (i1 integer NOT NULL PRIMARY KEY);
CREATE TABLE t2 (i2 integer NOT NULL PRIMARY KEY);
CREATE TABLE t3 (i3 integer);
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12);
INSERT INTO t2 SELECT * FROM t1;
EXPLAIN EXTENDED
SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2
LEFT JOIN t3 ON t2.i2 = t3.i3
ORDER BY t1.i1 LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 system NULL NULL NULL NULL 00.00 Const row not found 1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 5100.00 Using index 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i1 1100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`i2` = `test`.`t1`.`i1` order by `test`.`t1`.`i1` limit 5
SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2
LEFT JOIN t3 ON t2.i2 = t3.i3
ORDER BY t1.i1 LIMIT 5;
i1 i2 11 22 33 44 55
DROP TABLE t1, t2, t3;
#
# Fix of LP BUG#793589 Wrong result with double ORDER BY
#
CREATE TABLE t1 ( b int) ;
INSERT INTO t1 VALUES (8),(9);
CREATE TABLE t2 ( a int, b int, PRIMARY KEY (a)) ;
INSERT INTO t2 VALUES (6,7),(7,7),(8,1),(9,7),(10,1),(11,5),(12,2),(13,0),(14,1),(15,8),(16,1),(17,1),(18,9),(19,1),(20,5);
SELECT t2.b AS field1 FROM t1, t2 WHERE t1.b = t2.a GROUP BY field1 ORDER BY t1.b, field1;
field1 1 7
SELECT t2.b, t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b, t2.b;
b b 18 79
SELECT t2.b,t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b;
b b 18 79
SELECT t2.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b;
b 1 7
# field1 removed from ORDER BY
explain extended
SELECT t2.b AS field1 FROM t1, t2 WHERE t1.b = t2.a GROUP BY field1 ORDER BY t1.b, field1;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2100.00 Using where; Using temporary; Using filesort 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1100.00
Warnings:
Note 1003 select `test`.`t2`.`b` AS `field1` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` group by `test`.`t2`.`b` order by `test`.`t1`.`b`
explain extended
SELECT t2.b, t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b, t2.b;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2100.00 Using where; Using temporary; Using filesort 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1100.00
Warnings:
Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` group by `test`.`t2`.`b` order by `test`.`t1`.`b`
explain extended
SELECT t2.b,t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2100.00 Using where; Using temporary; Using filesort 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1100.00
Warnings:
Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` group by `test`.`t2`.`b` order by `test`.`t1`.`b`
explain extended
SELECT t2.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2100.00 Using where; Using temporary; Using filesort 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1100.00
Warnings:
Note 1003 select `test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` group by `test`.`t2`.`b` order by `test`.`t1`.`b`
drop table t1,t2;
End of 5.2 tests
#
# Bug mdev-449: ORDER BY with small sort_buffer_size
#
CREATE TABLE t1(f0 int auto_increment primary key, f1 int, f2 varchar(200));
INSERT INTO t1(f1, f2) VALUES
(0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"),
(6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"),
(11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"),
(16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"),
(21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"),
(26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"),
(31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"),
(36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"),
(41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"),
(46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"),
(51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"),
(56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"),
(61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"),
(66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"),
(71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"),
(76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"),
(81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"),
(86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"),
(91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"),
(96,"96"),(97,"97"),(98,"98"),(99,"99"); set @save_sort_buffer_size= @@sort_buffer_size; set sort_buffer_size= 2000;
SELECT * FROM t1 ORDER BY f1 DESC, f0;
f0 f1 f2 1009999 999898 989797 979696 969595 959494 949393 939292 929191 919090 908989 898888 888787 878686 868585 858484 848383 838282 828181 818080 807979 797878 787777 777676 767575 757474 747373 737272 727171 717070 706969 696868 686767 676666 666565 656464 646363 636262 626161 616060 605959 595858 585757 575656 565555 555454 545353 535252 525151 515050 504949 494848 484747 474646 464545 454444 444343 434242 424141 414040 403939 393838 383737 373636 363535 353434 343333 333232 323131 313030 302929 292828 282727 272626 262525 252424 242323 232222 222121 212020 201919 191818 181717 171616 161515 151414 141313 131212 121111 111010 1099 988 877 766 655 544 433 322 211 100 set sort_buffer_size= @save_sort_buffer_size;
DROP TABLE t1;
End of 5.3 tests
#
# Bug 54599: discarded fast range scan for query with
# GROUP BY + ORDER BY + LIMIT
#
create table t0 (a int);
insert into t0 values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
create table t1 (a int, b int, index idx1(a,b), index idx2(b,a));
insert into t1
select 1000*s4.a+100*s3.a+10*s2.a + s1.a, 1000*s4.a+100*s3.a+10*s2.a+s1.a
from t0 s1, t0 s2, t0 s3, t0 s4;
analyze table t1;
explain
select b, count(*) num_cnt from t1
where a > 9750 group by b order by num_cnt;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx1 idx1 5 NULL 503 Using where; Using index; Using temporary; Using filesort
flush status;
select b, count(*) num_cnt from t1
where a > 9750 group by b order by num_cnt;
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 250
Handler_read_last 0
Handler_read_next 249
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 249
Handler_read_rnd_deleted 0
Handler_read_rnd_next 250
explain
select b, count(*) num_cnt from t1
where a > 9750 group by b order by num_cnt limit 1;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx1 idx1 5 NULL 503 Using where; Using index; Using temporary; Using filesort
flush status;
select b, count(*) num_cnt from t1
where a > 9750 group by b order by num_cnt limit 1;
show status like '%Handler_read%';
Variable_name Value
Handler_read_first 0
Handler_read_key 250
Handler_read_last 0
Handler_read_next 249
Handler_read_prev 0
Handler_read_retry 0
Handler_read_rnd 1
Handler_read_rnd_deleted 0
Handler_read_rnd_next 250
drop table t0, t1;
#
# LP bug #1002508 : the number of expected rows to be examined is off
# (bug #13528826)
#
CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
INSERT INTO t1 VALUES
(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
INSERT INTO t2 VALUES
(103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
(107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
EXPLAIN
SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index 1 SIMPLE t2 ref i_a i_a 5 test.t1.a 1 Using index
EXPLAIN
SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 6 Using index 1 SIMPLE t2 ref i_a i_a 5 test.t1.a 1 Using index
EXPLAIN
SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 100;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index 1 SIMPLE t2 ref i_a i_a 5 test.t1.a 1 Using index
DROP TABLE t1,t2;
#
# MDEV-4974 memory leak in 5.5.32-MariaDB-1~wheezy-log
# set sort_buffer_size=default; set max_sort_length=default;
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (b int,
col1 varchar(255),
col2 varchar(255)
) character set utf8;
insert into t2 select
A.a+10*B.a,
concat('wow-wow-col1-value-', A.a+10*B.a+100*C.a),
concat('wow-wow-col2-value-', A.a+10*B.a+100*C.a)
from
t1 A, t1 B, t1 C where C.a < 8;
create table t3 as
select distinct A.col1 as XX, B.col1 as YY
from
t2 A, t2 B
where A.b = B.b
order by A.col2, B.col2 limit 10, 1000000;
drop table t1,t2,t3;
#
# mdev-10705 : long order by list that can be skipped
#
SELECT 1
UNION
( SELECT 2
ORDER BY NULL, @a0 := 3, @a1 := 3, @a2 := 3, @a3 := 3, @a4 := 3,
@a5 := 3, @a6 := 3, @a7 := 3, @a8 := 3, @a9 := 3, @a10 := 3 ); 1 1 2
#
# mdev-6706: semi-join with duplicate weedout + ORDER BY
#
CREATE TABLE t1 (f1 VARCHAR(3)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('foo');
CREATE TABLE t2 (f2 VARCHAR(3)) ENGINE=MyISAM;
INSERT INTO t2 VALUES ('bar'),('baz');
CREATE TABLE t3
(i3_key INT, f3_key VARCHAR(3), f3 VARCHAR(3), KEY(f3_key,i3_key))
ENGINE=MyISAM;
INSERT INTO t3 VALUES (0,'qux','qux'),(8,'bar','bar');
SELECT CONCAT( f1, f2 ) AS field FROM t1, t2
WHERE f1 = ANY ( SELECT f1
FROM t1
LEFT JOIN ( t3 AS t3a, t3 AS t3b )
ON ( t3b.f3_key = t3a.f3 )
WHERE t3a.f3 < f1 OR t3b.f3 != f1 );
field
foobar
foobaz
SELECT CONCAT( f1, f2 ) AS field FROM t1, t2
WHERE f1 = ANY ( SELECT f1
FROM t1
LEFT JOIN ( t3 AS t3a, t3 AS t3b )
ON ( t3b.f3_key = t3a.f3 )
WHERE t3a.f3 < f1 OR t3b.f3 != f1 )
ORDER BY field;
field
foobar
foobaz
EXPLAIN EXTENDED SELECT CONCAT( f1, f2 ) AS field FROM t1, t2
WHERE f1 = ANY ( SELECT f1
FROM t1
LEFT JOIN ( t3 AS t3a, t3 AS t3b )
ON ( t3b.f3_key = t3a.f3 )
WHERE t3a.f3 < f1 OR t3b.f3 != f1 )
ORDER BY field;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1100.00 Using temporary; Using filesort 1 PRIMARY t1 system NULL NULL NULL NULL 1100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 2100.00 1 PRIMARY t3a ALL NULL NULL NULL NULL 2100.00 Using where; Start temporary 1 PRIMARY t3b ref f3_key f3_key 6 test.t3a.f3 141.67 Using where; End temporary
Warnings:
Note 1003 select concat('foo',`test`.`t2`.`f2`) AS `field` from `test`.`t2` semi join ((`test`.`t3` `t3a` join `test`.`t3` `t3b`)) where `test`.`t3a`.`f3` < 'foo'or `test`.`t3b`.`f3` <> 'foo' order by concat('foo',`test`.`t2`.`f2`)
DROP TABLE t1,t2,t3;
End of 5.5 tests
#
# MDEV-5884: EXPLAIN UPDATE ... ORDER BY LIMIT shows wrong #rows
#
create table t2(a int);
insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (key1 int, col1 int, key(key1));
insert into t1
select A.a + 10 * B.a + 100 * C.a, 1234 from t2 A, t2 B, t2 C;
# Should show rows=2, not rows=100
explain update t1 set key1=key1+1 where key1 between 10and110 order by key1 limit 2;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range key1 key1 5 NULL 2 Using where; Using buffer
drop table t1,t2;
#
# MDEV-465: Optimizer : wrong index choice, leading to strong performances issues
#
CREATE TABLE t1 (
id1 int(10) unsigned NOT NULL auto_increment,
id2 tinyint(3) unsigned NOT NULL default '0',
id3 tinyint(3) unsigned NOT NULL default '0',
id4 int(10) unsigned NOT NULL default '0',
date timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (id1),
KEY id_234_date (id2,id3,id4,date),
KEY id_23_date (id2,id3,date)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
# t1 has "bad" index declaration order..
CREATE TABLE t2 (
id1 int(10) unsigned NOT NULL auto_increment,
id2 tinyint(3) unsigned NOT NULL default '0',
id3 tinyint(3) unsigned NOT NULL default '0',
id4 int(10) unsigned NOT NULL default '0',
date timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (id1),
KEY id_23_date (id2,id3,date),
KEY id_234_date (id2,id3,id4,date)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
# t2 has a "good" index declaration order
INSERT INTO t1 (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1);
INSERT INTO t1 (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1);
INSERT INTO t2 (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1);
INSERT INTO t2 (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1);
# The following two must both use id_23_date and no "using filesort":
EXPLAIN SELECT id1 FROM t1 WHERE id2=1AND id3=1 ORDER BY date DESC LIMIT 0,4;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range id_234_date,id_23_date id_23_date 2 NULL 8 Using where
# See above query
EXPLAIN SELECT id1 FROM t2 WHERE id2=1AND id3=1 ORDER BY date DESC LIMIT 0,4;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref id_23_date,id_234_date id_23_date 2 const,const 8 Using where
drop table t1,t2;
#
# MDEV-8989: ORDER BY optimizer ignores equality propagation
# set @tmp_8989=@@optimizer_switch; set optimizer_switch='orderby_uses_equalities=on';
create table t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(a int);
insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
create table t2 (
pk int primary key,
a int, b int,
filler char(200),
key(a)
);
insert into t2 select a, 1000-a, 1000-a, repeat('abc-',50) from t1 where a<200 limit 200;
create table t3 (
pk int primary key,
a int, b int,
filler char(200),
key(a)
);
insert into t3 select a, 1000-a, 1000-a, repeat('abc-',50) from t1;
insert into t3 select a+1000, 1000+a, 1000+a, repeat('abc-',50) from t1;
# The optimizer produces an order of 't2,t3' for this join
#
# Case #1 (from the bug report):
# Q1 can take advantage of t2.a to resolve ORDER BY limit w/o sorting
explain
select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b
from t2, t3 where t2.a=t3.a order by t2.a limit 5;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index a a 5 NULL 5 Using where 1 SIMPLE t3 ref a a 5 test.t2.a 1
#
# This is Q2 which used to have "Using temporary; using filesort" but
# has the same query plan as Q1:
#
explain
select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b
from t2, t3 where t2.a=t3.a order by t3.a limit 5;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index a a 5 NULL 5 Using where 1 SIMPLE t3 ref a a 5 test.t2.a 1
select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b
from t2, t3 where t2.a=t3.a order by t2.a limit 5;
pk a b pk a b 199801801199801801 198802802198802802 197803803197803803 196804804196804804 195805805195805805
select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b
from t2, t3 where t2.a=t3.a order by t3.a limit 5;
pk a b pk a b 199801801199801801 198802802198802802 197803803197803803 196804804196804804 195805805195805805
#
# Case #2: here, only "Using temporary" is removed. "Using filesort" remains.
#
explain
select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b
from t2, t3 where t2.a=t3.a order by t2.a limit 25;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index a a 5 NULL 25 Using where 1 SIMPLE t3 ref a a 5 test.t2.a 1
explain
select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b
from t2, t3 where t2.a=t3.a order by t3.a limit 25;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index a a 5 NULL 25 Using where 1 SIMPLE t3 ref a a 5 test.t2.a 1
select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b
from t2, t3 where t2.a=t3.a order by t2.a limit 25;
pk a b pk a b 199801801199801801 198802802198802802 197803803197803803 196804804196804804 195805805195805805 194806806194806806 193807807193807807 192808808192808808 191809809191809809 190810810190810810 189811811189811811 188812812188812812 187813813187813813 186814814186814814 185815815185815815 184816816184816816 183817817183817817 182818818182818818 181819819181819819 180820820180820820 179821821179821821 178822822178822822 177823823177823823 176824824176824824 175825825175825825
select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b
from t2, t3 where t2.a=t3.a order by t3.a limit 25;
pk a b pk a b 199801801199801801 198802802198802802 197803803197803803 196804804196804804 195805805195805805 194806806194806806 193807807193807807 192808808192808808 191809809191809809 190810810190810810 189811811189811811 188812812188812812 187813813187813813 186814814186814814 185815815185815815 184816816184816816 183817817183817817 182818818182818818 181819819181819819 180820820180820820 179821821179821821 178822822178822822 177823823177823823 176824824176824824 175825825175825825
#
# Case #3: single table access (the code that decides whether we need
# "Using temporary" is not invoked)
#
explain select * from t3 where b=a order by a limit 10;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 index NULL a 5 NULL 10 Using where
# This must not use filesort. The query plan should be like the query above:
explain select * from t3 where b=a order by b limit 10;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 index NULL a 5 NULL 10 Using where
drop table t0,t1,t2,t3; set @@optimizer_switch=@tmp_8989; set optimizer_switch='orderby_uses_equalities=on';
#
# MDEV-10880: Assertions `keypart_map' or
# `prebuilt->search_tuple->n_fields > 0' fail on DISTINCT and
# GROUP BY constant
#
CREATE TABLE t1 (pk INT PRIMARY KEY);
INSERT INTO t1 VALUES (1),(2),(3);
SELECT DISTINCT pk FROM t1 GROUP BY 'foo';
pk 1
SELECT DISTINCT pk FROM t1;
pk 1 2 3
DROP TABLE t1;
#
# MDEV-21044: Wrong result when using a smaller size for sort buffer
#
create table t1(a varchar(765),b int);
insert into t1 values ("a",1),("b",2),("c",3),("e",4);
insert into t1 values ("d",5),("f",6),("g",7),("h",8);
insert into t1 values ("k",11),("l",12),("i",9),("j",10);
insert into t1 values ("m",13),("n",14),("o",15),("p",16); set @save_sort_buffer_size= @@sort_buffer_size; set sort_buffer_size=1024;
select * from t1 order by b;
a b
a 1
b 2
c 3
e 4
d 5
f 6
g 7
h 8
i 9
j 10
k 11
l 12
m 13
n 14
o 15
p 16 set @@sort_buffer_size= @save_sort_buffer_size;
drop table t1;
#
# MDEV-22715: SIGSEGV in radixsort_for_str_ptr and in native_compare/my_qsort2 (optimized builds)
# SET @save_sort_buffer_size= @@sort_buffer_size; SET @save_max_sort_length= @@max_sort_length; SET max_sort_length=8;
Warnings:
Warning 1292 Truncated incorrect max_sort_length value: '8' SET sort_buffer_size=1024;
CREATE TABLE t1(a INT, b DECIMAL(65), c BLOB);
INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_25;
INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_25;
SELECT * FROM t1 ORDER BY a,b;
a b c 111 111 222 222 333 333 444 444 555 555 666 666 777 777 888 888 999 999 101010 101010 111111 111111 121212 121212 131313 131313 141414 141414 151515 151515 161616 161616 171717 171717 181818 181818 191919 191919 202020 202020 212121 212121 222222 222222 232323 232323 242424 242424 252525 252525 SET @@sort_buffer_size= @save_sort_buffer_size; SET @@max_sort_length= @save_max_sort_length;
DROP TABLE t1;
#
# MDEV-22390: Assertion `m_next_rec_ptr >= m_rawmem' failed in Filesort_buffer::spaceleft |
# SIGSEGV in __memmove_avx_unaligned_erms from my_b_write (on optimized)
# SET @save_max_sort_length= @@max_sort_length; SET @save_sort_buffer_size= @@sort_buffer_size; SET @save_max_length_for_sort_data= @@max_length_for_sort_data; SET max_sort_length=8;
Warnings:
Warning 1292 Truncated incorrect max_sort_length value: '8' SET sort_buffer_size=1024; SET max_length_for_sort_data=7000;
CREATE TABLE t1(a VARCHAR(64), b VARCHAR(2048))DEFAULT CHARSET=utf8;
INSERT INTO t1 SELECT seq,seq from seq_1_to_100;
ANALYZE FORMAT=JSON SELECT * FROM t1 ORDER BY a LIMIT 5;
ANALYZE
{ "query_optimization": { "r_total_time_ms": "REPLACED"
}, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [
{ "read_sorted_file": { "r_rows": 5, "filesort": { "sort_key": "t1.a", "r_loops": 1, "r_total_time_ms": "REPLACED", "r_limit": 5, "r_used_priority_queue": true, "r_output_rows": 6, "r_sort_mode": "sort_key,rowid", "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 100, "r_rows": 100, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100
}
}
}
}
]
}
}
SELECT * FROM t1 ORDER BY a LIMIT 5;
a b 11 1010 100100 1111 1212 SET max_sort_length= @save_max_sort_length; SET sort_buffer_size= @save_sort_buffer_size; SET max_length_for_sort_data= @save_max_length_for_sort_data;
DROP TABLE t1;
#
# MDEV-23596: Assertion `tab->ref.use_count' failed in join_read_key_unlock_row
#
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY(b));
INSERT INTO t1 VALUES (0, 1),(1, 2);
CREATE TABLE t2 SELECT * FROM t1;
EXPLAIN SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2;
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.t2.b 1 Using where
SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2;
c 1
NULL
DROP TABLE t1,t2;
# end of 10.1 tests
#
# MDEV-13994: Bad join results with orderby_uses_equalities=on
#
CREATE TABLE books (
id int(16) NOT NULL AUTO_INCREMENT,
library_id int(16) NOT NULL DEFAULT 0,
wings_id int(12) NOT NULL DEFAULT 0,
scheduled_for_removal int(1) DEFAULT 0,
PRIMARY KEY (id),
KEY library_idx (library_id)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO books VALUES (32625,8663,707,0),(32624,8663,505,1);
CREATE TABLE wings (
id int(11) NOT NULL AUTO_INCREMENT,
department_id int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO wings VALUES (505,11745),(707,11768); SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='orderby_uses_equalities=off';
SELECT wings.id as wing_id, wings.department_id FROM wings
WHERE wings.id IN ( SELECT books.wings_id FROM books
WHERE books.library_id = 8663AND
books.scheduled_for_removal=0 )
ORDER BY wings.id;
wing_id department_id 70711768 SET optimizer_switch='orderby_uses_equalities=on';
SELECT wings.id as wing_id, wings.department_id FROM wings
WHERE wings.id IN ( SELECT books.wings_id FROM books
WHERE books.library_id = 8663AND
books.scheduled_for_removal=0 )
ORDER BY wings.id;
wing_id department_id 70711768
explain extended SELECT wings.id as wing_id, wings.department_id FROM wings
WHERE wings.id IN ( SELECT books.wings_id FROM books
WHERE books.library_id = 8663AND
books.scheduled_for_removal=0 )
ORDER BY wings.id;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY wings ALL PRIMARY NULL NULL NULL 2100.00 Using filesort 1 PRIMARY books ref library_idx library_idx 4 const 250.00 Using where; FirstMatch(wings)
Warnings:
Note 1003 select `test`.`wings`.`id` AS `wing_id`,`test`.`wings`.`department_id` AS `department_id` from `test`.`wings` semi join (`test`.`books`) where `test`.`books`.`library_id` = 8663and `test`.`books`.`scheduled_for_removal` = 0and `test`.`books`.`wings_id` = `test`.`wings`.`id` order by `test`.`wings`.`id` set optimizer_switch= @save_optimizer_switch;
DROP TABLE books, wings;
#
# MDEV-17796: query with DISTINCT, GROUP BY and ORDER BY
#
CREATE TABLE t1 (id int, gr int, v1 varchar(10));
INSERT INTO t1 VALUES (1,1,'A'), (2,2,'B'), (3,3,NULL), (4,4,'C');
SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), null) FROM t1
WHERE gr in (4,2)
GROUP BY id
ORDER BY id+1 DESC;
NULLIF(GROUP_CONCAT(v1), null)
C
B
DROP TABLE t1;
#
# MDEV-24033: SIGSEGV in __memcmp_avx2_movbe from queue_insert | SIGSEGV in __memcmp_avx2_movbe from native_compare
# SET @save_max_length_for_sort_data=@@max_length_for_sort_data; SET @save_max_sort_length= @@max_sort_length; SET @save_sql_select_limit= @@sql_select_limit;
CREATE TABLE t1 (a DECIMAL(64,0), b INT);
INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); SET max_length_for_sort_data= 30; SET sql_select_limit = 3; SET max_sort_length=8;
Warnings:
Warning 1292 Truncated incorrect max_sort_length value: '8'
SELECT * FROM t1 ORDER BY a+1;
a b 11 22 33 SET max_length_for_sort_data=@save_max_length_for_sort_data; SET max_sort_length= @save_max_sort_length; SET sql_select_limit= @save_sql_select_limit;
DROP TABLE t1;
#
# MDEV-25682: EXPLAIN for SELECT with ORDER BY after [ORDER BY] LIMIT
#
create table t1 (a int);
insert into t1 values (3), (7), (1);
explain (select a from t1 limit 2) order by a desc;
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using filesort 2 DERIVED t1 ALL NULL NULL NULL NULL 3
(select a from t1 limit 2) order by a desc;
a 7 3
create table t2 (a int, b int);
insert into t2 values (3,70), (7,10), (1,40), (4,30);
explain (select b,a from t2 order by a limit 3) order by b desc;
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using filesort 2 DERIVED t2 ALL NULL NULL NULL NULL 4 Using filesort
(select b,a from t2 order by a limit 3) order by b desc;
b a 703 401 304
drop table t1,t2;
#
# MDEV-22256 Assertion `length == pack_length()' failed in Field_timestamp_with_dec::sort_string
# SET sql_mode=''; SET @@SESSION.max_sort_length=4;
Warnings:
Warning 1292 Truncated incorrect max_sort_length value: '4'
CREATE TABLE t1 (c TIMESTAMP(1));
INSERT INTO t1 VALUES(0);
DELETE FROM t1 ORDER BY c;
DROP TABLE t1; SET @@SESSION.max_sort_length=DEFAULT; SET sql_mode=DEFAULT; SET sql_mode=''; SET @@SESSION.max_sort_length=1;
Warnings:
Warning 1292 Truncated incorrect max_sort_length value: '1'
CREATE TEMPORARY TABLE t1 (c DATETIME);
INSERT INTO t1 VALUES(0);
DELETE FROM t1 ORDER BY c;
DROP TABLE t1; SET @@SESSION.max_sort_length=DEFAULT; SET sql_mode=DEFAULT;
#
# MDEV-25994 Crash with union of my_decimal type in ORDER BY clause
#
CREATE TABLE t1 (v1 INTEGER) ;
INSERT INTO t1 (v1) VALUES (8);
UPDATE t1 SET v1 = 1 ORDER BY (SELECT 1.1 UNION SELECT -1); ERROR21000: Subquery returns more than 1 row
# This one must be successful
UPDATE t1 SET v1 = 2 ORDER BY (SELECT 1 UNION SELECT 1);
UPDATE t1 SET v1 = 3 ORDER BY (SELECT 'a' UNION SELECT 'b'); ERROR21000: Subquery returns more than 1 row
# Insert some more data
INSERT INTO t1 (v1) VALUES (8),(9),(100),(-234),(46584),(0);
UPDATE t1 SET v1 = v1+1 ORDER BY (SELECT 100.122 UNION SELECT -189.2); ERROR21000: Subquery returns more than 1 row
# This one must be successful
UPDATE t1 SET v1 = v1-200 ORDER BY (SELECT 1 UNION SELECT 1);
UPDATE t1 SET v1 = v1 ORDER BY (SELECT 'abc' UNION SELECT 'bbb'); ERROR21000: Subquery returns more than 1 row
DROP TABLE t1;
# End of 10.2 tests
#
# MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY
#
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2(
id int primary key,
key1 int,key2 int,
col1 int,
key(key1), key(key2)
);
insert into t2
select
A.a + B.a*10 + C.a*100,
A.a + 10*B.a, A.a + 10*B.a, 123456
from t1 A, t1 B, t1 C;
# here type should show ref not index
explain select
(SELECT concat(id, '-', key1, '-', col1)
FROM t2
WHERE
t2.key1 = t1.a and t2.key1 IS NOT NULL
ORDER BY
t2.key2 ASC
LIMIT 1) as "con"
from t1;
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 2 DEPENDENT SUBQUERY t2 ref key1 key1 5 test.t1.a 10 Using index condition; Using where; Using filesort
select
(SELECT concat(id, '-', key1, '-', col1)
FROM t2
WHERE
t2.key1 = t1.a and t2.key1 IS NOT NULL
ORDER BY
t2.key2 ASC
LIMIT 1) as "con"
from t1;
con 100-0-123456 101-1-123456 102-2-123456 103-3-123456 104-4-123456 105-5-123456 106-6-123456 107-7-123456 108-8-123456 109-9-123456
drop table t1,t2;
# End of 10.3 tests
#
# MDEV-17761: Odd optimizer choice with ORDER BY LIMIT and condition selectivity
#
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2(a int);
insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C;
create table t3(a int);
insert into t3 select A.a + 1000 *B.a from t2 A, t1 B;
create table t4 (
a int,
b int,
c int,
filler1 char(255),
filler2 char(255),
key(a)
);
insert into t4 select a,a,a, a,a from t3; set @tmp_h=@@histogram_size, @tmp_ht=@@histogram_type, @tmp_u=@@use_stat_tables,
@tmp_o=@@optimizer_use_condition_selectivity; set histogram_size=100, histogram_type='single_prec_hb'; set use_stat_tables=preferably; set optimizer_use_condition_selectivity=4;
analyze table t4 persistent for columns(b) indexes ();
Table Op Msg_type Msg_text
test.t4 analyze status Engine-independent statistics collected
test.t4 analyze status Table is already up to date
# rows must be around 1200, not600:
explain extended
select * from t4 where b < 5000 order by a limit 600;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t4 index NULL a 5 NULL 1188100.00 Using where
Warnings:
Note 1003 select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c`,`test`.`t4`.`filler1` AS `filler1`,`test`.`t4`.`filler2` AS `filler2` from `test`.`t4` where `test`.`t4`.`b` < 5000 order by `test`.`t4`.`a` limit 600 set histogram_size=@tmp_h, histogram_type=@tmp_ht, use_stat_tables=@tmp_u,
optimizer_use_condition_selectivity=@tmp_o;
drop table t1,t2,t3,t4;
#
# MDEV-32324: Server crashes inside filesort at my_decimal::to_binary
#
SELECT 1.000000 two UNION SELECT 1 ORDER BY ( SELECT two LIMIT 1 OFFSET 1 ) ;
two 1.000000
# MDEV-32475: test_if_skip_sort_order() should catch the join types
# JT_EQ_REF, JT_CONST and JT_SYSTEM and skip sort order for these
#
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY(b));
CREATE TABLE t2 (a INT, b INT);
INSERT INTO t1 SELECT seq, seq+1 FROM seq_1_to_100;
INSERT INTO t2 VALUES (0, 1),(1, 2);
ANALYZE TABLE t1, t2 PERSISTENT FOR ALL;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status Table is already up to date
test.t2 analyze status Engine-independent statistics collected
test.t2 analyze status OK
# Table t1 must use eq_ref, not index below:
EXPLAIN SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2;
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.t2.b 1 Using where
DROP TABLE t1,t2;
#
# MDEV-29681 Server crashes when optimizing SQL with ORDER BY
#
CREATE TABLE t1 (b INT);
CREATE TABLE t2 (a INT, c INT);
# First test empty tables
EXPLAIN EXTENDED (SELECT * FROM t1 JOIN t2 ON a=b LIMIT 3) ORDER BY a+1;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 00.00 Const row not found 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table
Warnings:
Note 1003/* select#1 */ select NULL AS `b`,NULL AS `a`,NULL AS `c` from ((/* select#2 */ select NULL AS `b`,NULL AS `a`,NULL AS `c` from `test`.`t1` join `test`.`t2` where 0 limit 3)) `__2` order by NULL + 1
EXPLAIN EXTENDED (SELECT * FROM t1 JOIN t2 ON a=b LIMIT 3) ORDER BY a=2;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 00.00 Const row not found 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table
Warnings:
Note 1003/* select#1 */ select NULL AS `b`,NULL AS `a`,NULL AS `c` from ((/* select#2 */ select NULL AS `b`,NULL AS `a`,NULL AS `c` from `test`.`t1` join `test`.`t2` where 0 limit 3)) `__2` order by NULL = 2
EXPLAIN EXTENDED (SELECT * FROM t1 JOIN t2 ON a=b LIMIT 3)
ORDER BY a+1, a-b DESC, c<>a;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 00.00 Const row not found 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table
Warnings:
Note 1003/* select#1 */ select NULL AS `b`,NULL AS `a`,NULL AS `c` from ((/* select#2 */ select NULL AS `b`,NULL AS `a`,NULL AS `c` from `test`.`t1` join `test`.`t2` where 0 limit 3)) `__2` order by NULL + 1,NULL - NULL desc,NULL <> NULL
# Insert some data
INSERT INTO t1 VALUES (1),(2),(3),(4);
INSERT INTO t2 VALUES (1,1),(2,2),(3,3),(4,4);
(SELECT * FROM t1 JOIN t2 ON a=b LIMIT 3) ORDER BY a=b, a-10 DESC, b+a, c+a+a+b;
b a c 111 222 333
(SELECT * FROM t1 JOIN t2 ON a=b LIMIT 3) ORDER BY a=2;
b a c 111 222 333
EXPLAIN EXTENDED (SELECT * FROM t1 JOIN t2 ON a=b LIMIT 3)
ORDER BY a=b, a-10, b+a, c+a+a+b;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3100.00 Using filesort 2 DERIVED t1 ALL NULL NULL NULL NULL 4100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 4100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003/* select#1 */ select `__2`.`b` AS `b`,`__2`.`a` AS `a`,`__2`.`c` AS `c` from ((/* select#2 */ select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` limit 3)) `__2` order by `__2`.`a` = `__2`.`b`,`__2`.`a` - 10,`__2`.`b` + `__2`.`a`,`__2`.`c` + `__2`.`a` + `__2`.`a` + `__2`.`b`
# When there is no LIMIT clause the derived table must be merged
(SELECT * FROM t1 JOIN t2 ON a=b) ORDER BY a+16, b+a, c<>b;
b a c 111 222 333 444
EXPLAIN EXTENDED (SELECT * FROM t1 JOIN t2 ON a=b) ORDER BY a+16 DESC, b+a, c<>b;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4100.00 Using temporary; Using filesort 1 SIMPLE t2 ALL NULL NULL NULL NULL 4100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 (select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` order by `test`.`t2`.`a` + 16 desc,`test`.`t1`.`b` + `test`.`t2`.`a`,`test`.`t2`.`c` <> `test`.`t1`.`b`)
# Test UNIONs:
(SELECT * FROM t1 JOIN t2 ON a=b UNION
SELECT * FROM t1 JOIN t2 ON a!=b
LIMIT 3)
ORDER BY a+16, b+a, c<>b;
b a c 111 222 333
EXPLAIN EXTENDED (SELECT * FROM t1 JOIN t2 ON a=b UNION
SELECT * FROM t1 JOIN t2 ON a!=b
LIMIT 3)
ORDER BY a+16, b+a, c<>b;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 32100.00 Using filesort 2 DERIVED t1 ALL NULL NULL NULL NULL 4100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 4100.00 Using where; Using join buffer (flat, BNL join) 3 UNION t1 ALL NULL NULL NULL NULL 4100.00 3 UNION t2 ALL NULL NULL NULL NULL 4100.00 Using where; Using join buffer (flat, BNL join)
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003/* select#1 */ select `__3`.`b` AS `b`,`__3`.`a` AS `a`,`__3`.`c` AS `c` from (/* select#2 */ select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` union /* select#3 */ select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` <> `test`.`t1`.`b` limit 3) `__3` order by `__3`.`a` + 16,`__3`.`b` + `__3`.`a`,`__3`.`c` <> `__3`.`b`
(SELECT * FROM t1 JOIN t2 ON a=b UNION
SELECT NULL, NULL, NULL
LIMIT 3)
ORDER BY b-a-c;
b a c 111 222 333
EXPLAIN EXTENDED (SELECT * FROM t1 JOIN t2 ON a=b UNION
SELECT NULL, NULL, NULL
LIMIT 3)
ORDER BY b-a-c;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 16100.00 Using filesort 2 DERIVED t1 ALL NULL NULL NULL NULL 4100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 4100.00 Using where; Using join buffer (flat, BNL join) 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003/* select#1 */ select `__3`.`b` AS `b`,`__3`.`a` AS `a`,`__3`.`c` AS `c` from (/* select#2 */ select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` union /* select#3 */ select NULL AS `NULL`,NULL AS `NULL`,NULL AS `NULL` limit 3) `__3` order by `__3`.`b` - `__3`.`a` - `__3`.`c`
(SELECT * FROM t1 JOIN t2 ON a=b UNION
SELECT NULL, NULL, NULL
ORDER BY a LIMIT 3)
ORDER BY b-a-c LIMIT 1;
b a c
NULL NULL NULL
EXPLAIN EXTENDED (SELECT * FROM t1 JOIN t2 ON a=b UNION
SELECT NULL, NULL, NULL
ORDER BY a LIMIT 3)
ORDER BY b-a-c LIMIT 1;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 16100.00 Using filesort 2 DERIVED t1 ALL NULL NULL NULL NULL 4100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 4100.00 Using where; Using join buffer (flat, BNL join) 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Using filesort
Warnings:
Note 1003/* select#1 */ select `__3`.`b` AS `b`,`__3`.`a` AS `a`,`__3`.`c` AS `c` from (/* select#2 */ select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` union /* select#3 */ select NULL AS `NULL`,NULL AS `NULL`,NULL AS `NULL` order by `a` limit 3) `__3` order by `__3`.`b` - `__3`.`a` - `__3`.`c` limit 1
DROP TABLE t1, t2;
# End of 10.4 tests
#
# MDEV-21655: Server crashes in my_qsort2 / Filesort_buffer::sort_buffer
# set @save_sql_mode= @@sql_mode; set sql_mode= 'PAD_CHAR_TO_FULL_LENGTH';
CREATE TABLE t1 ( a CHAR(255), b CHAR(255), c TEXT);
INSERT INTO t1 VALUES
('1','a', 'a'), ('2','b', 'b'), ('3','c', 'c'), ('4','d','d'),
('5','e', 'e'), ('6','f', 'f'), ('7','g','g'), ('8','h','h'),
('9','i', 'i'), ('10','j','j'), ('11','k','k'), ('12','l','l'),
('13','m','m'), ('14','n','n'), ('15','o','o'); set sort_buffer_size=524*15;
select c from t1 order by a,b;
c
a
j
k
l
m
n
o
b
c
d
e
f
g
h
i set sort_buffer_size= default; set sql_mode= @save_sql_mode;
drop table t1;
#
# MDEV-21580: Allow packed sort keys in sort buffer
#
#
# This example should not pack sort keys
# all fields are fixed-size fields in the ORDER BY clause
#
create table t1 (a bigint, b bigint, c bigint);
insert into t1 select seq, seq, seq from seq_1_to_100;
# in r_sort_mode it should show sort_key andnot packed_sort_key
ANALYZE FORMAT=JSON select * from t1 order by a,b,c;
ANALYZE
{ "query_optimization": { "r_total_time_ms": "REPLACED"
}, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [
{ "read_sorted_file": { "r_rows": 100, "filesort": { "sort_key": "t1.a, t1.b, t1.c", "r_loops": 1, "r_total_time_ms": "REPLACED", "r_used_priority_queue": false, "r_output_rows": 100, "r_buffer_size": "REPLACED", "r_sort_mode": "sort_key,packed_addon_fields", "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 100, "r_rows": 100, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100
}
}
}
}
]
}
}
select * from t1 order by a,b,c;
a b c 111 222 333 444 555 666 777 888 999 101010 111111 121212 131313 141414 151515 161616 171717 181818 191919 202020 212121 222222 232323 242424 252525 262626 272727 282828 292929 303030 313131 323232 333333 343434 353535 363636 373737 383838 393939 404040 414141 424242 434343 444444 454545 464646 474747 484848 494949 505050 515151 525252 535353 545454 555555 565656 575757 585858 595959 606060 616161 626262 636363 646464 656565 666666 676767 686868 696969 707070 717171 727272 737373 747474 757575 767676 777777 787878 797979 808080 818181 828282 838383 848484 858585 868686 878787 888888 898989 909090 919191 929292 939393 949494 959595 969696 979797 989898 999999 100100100
drop table t1;
#
# Test with Binary columns (using suffix length to determine ordering)
# Should show packed_sortkey in the r_sort_mode
#
create table t1 (a int, b blob); set @save_max_sort_length= @@max_sort_length;
insert into t1 select 1, CONCAT(repeat('a', @save_max_sort_length), 'A');
insert into t1 select 2, CONCAT(repeat('a', @save_max_sort_length), 'AB');
insert into t1 select 3, CONCAT(repeat('a', @save_max_sort_length), 'ABE');
insert into t1 select 4, CONCAT(repeat('a', @save_max_sort_length), 'APBX');
insert into t1 select 5, CONCAT(repeat('a', @save_max_sort_length), 'ABAAX');
show variables like '%sort_buffer_size';
Variable_name Value
aria_sort_buffer_size 268434432
myisam_sort_buffer_size 134216704
sort_buffer_size 262144
select a, substr(b, @save_max_sort_length+1) from t1 order by b desc;
a substr(b, @save_max_sort_length+1) 5 ABAAX 4 APBX 3 ABE 2 AB 1 A
Warnings:
Warning 42025 values were longer than max_sort_length. Sorting used only the first 1024 bytes
analyze format=json
select a, substr(b, @save_max_sort_length+1) from t1 order by b desc;
ANALYZE
{ "query_optimization": { "r_total_time_ms": "REPLACED"
}, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [
{ "read_sorted_file": { "r_rows": 5, "filesort": { "sort_key": "t1.b desc", "r_loops": 1, "r_total_time_ms": "REPLACED", "r_used_priority_queue": false, "r_output_rows": 5, "r_buffer_size": "REPLACED", "r_sort_mode": "packed_sort_key,rowid", "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 5, "r_rows": 5, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100
}
}
}
}
]
}
}
Warnings:
Warning 42025 values were longer than max_sort_length. Sorting used only the first 1024 bytes
drop table t1;
#
# Packing sort keys with complex collations
#
create table t1(a varchar(255) charset utf8mb3, b int, c decimal);
insert into t1 values ('abc', 1, 1) , ('bcd', 2, 2), ('cde',3, 3);
insert into t1 values ('def', 4, 4) , ('efg', 5, 5), ('fgh', 6, 6);
#
# Should show packed_sortkey in the r_sort_mode
#
ANALYZE FORMAT=JSON select a, b, c from t1 order by a, b;
ANALYZE
{ "query_optimization": { "r_total_time_ms": "REPLACED"
}, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [
{ "read_sorted_file": { "r_rows": 6, "filesort": { "sort_key": "t1.a, t1.b", "r_loops": 1, "r_total_time_ms": "REPLACED", "r_used_priority_queue": false, "r_output_rows": 6, "r_buffer_size": "REPLACED", "r_sort_mode": "packed_sort_key,rowid", "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 6, "r_rows": 6, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100
}
}
}
}
]
}
}
select a, b, c from t1 order by a, b;
a b c
abc 11
bcd 22
cde 33
def 44
efg 55
fgh 66 set @save_max_sort_length= @@max_sort_length; set max_sort_length=5;
Warnings:
Warning 1292 Truncated incorrect max_sort_length value: '5'
#
# should show sortkey in r_sort_mode as the collation is complex and
# truncation is not possible
#
ANALYZE FORMAT=JSON select a, b, c from t1 order by a, b;
ANALYZE
{ "query_optimization": { "r_total_time_ms": "REPLACED"
}, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [
{ "read_sorted_file": { "r_rows": 6, "filesort": { "sort_key": "t1.a, t1.b", "r_loops": 1, "r_total_time_ms": "REPLACED", "r_used_priority_queue": false, "r_output_rows": 6, "r_buffer_size": "REPLACED", "r_sort_mode": "sort_key,packed_addon_fields", "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 6, "r_rows": 6, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100
}
}
}
}
]
}
}
select a, b, c from t1 order by a, b;
a b c
abc 11
bcd 22
cde 33
def 44
efg 55
fgh 66 set max_sort_length= @save_max_sort_length;
drop table t1;
#
# MDEV-21922: Allow packing addon fields even if they don't honour max_length_for_sort_data
#
create table t1 (a varchar(200) character set utf8mb3 collate utf8mb3_general_ci, b int);
insert into t1 select seq, seq from seq_1_to_10;
select * from t1 order by a;
a b 11 1010 22 33 44 55 66 77 88 99
#
# r_sort_mode should show both packed_sort_key and packed_addon_fields
#
analyze format=json select * from t1 order by a;
ANALYZE
{ "query_optimization": { "r_total_time_ms": "REPLACED"
}, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [
{ "read_sorted_file": { "r_rows": 10, "filesort": { "sort_key": "t1.a", "r_loops": 1, "r_total_time_ms": "REPLACED", "r_used_priority_queue": false, "r_output_rows": 10, "r_buffer_size": "REPLACED", "r_sort_mode": "packed_sort_key,packed_addon_fields", "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 10, "r_rows": 10, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100
}
}
}
}
]
}
}
drop table t1;
#
# MDEV-21946: Server crash in store_length upon GROUP BY WITH ROLLUP with geometry field
#
create table t1 ( a longblob);
insert into t1 select repeat('a', 256);
insert into t1 select repeat('b', 256);
insert into t1 select repeat('c', 256);
insert into t1 select repeat('d', 256);
SELECT IF( 0, NULL, a ) AS f FROM t1 GROUP BY f WITH ROLLUP;
f
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
NULL
DROP TABLE t1;
#
# MDEV-22303: Incorrect ordering with REGEXP_REPLACE and OFFSET/LIMIT
#
CREATE TABLE t1 (name VARCHAR(20) CHARACTER SET utf8 NOT NULL);
INSERT INTO t1 (name) VALUES ('Charles Dickens'), ('Roald Dahl');
SELECT name, REGEXP_REPLACE(name, '^(.*) (.*)$', '\\2, \\1') AS surname_first FROM t1
ORDER BY surname_first ASC;
name surname_first
Roald Dahl Dahl, Roald
Charles Dickens Dickens, Charles
SELECT name, REGEXP_REPLACE(name, '^(.*) (.*)$', '\\2, \\1') AS surname_first FROM t1
ORDER BY surname_first ASC LIMIT 1;
name surname_first
Roald Dahl Dahl, Roald
SELECT name, REGEXP_REPLACE(name, '^(.*) (.*)$', '\\2, \\1') AS surname_first FROM t1
ORDER BY surname_first ASC LIMIT 1 OFFSET 1;
name surname_first
Charles Dickens Dickens, Charles
DROP TABLE t1;
#
# MDEV-22836: Server crashes in err_conv / ErrBuff::set_str
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (0),(1);
SELECT * FROM t1 ORDER BY CONVERT(AES_ENCRYPT(1,a), CHAR(4));
a 1 0
DROP TABLE t1;
#
# MDEV-22819:Wrong result or Assertion `ix > 0' failed in read_to_buffer upon select
# with GROUP BY and GROUP_CONCAT
#
CREATE TABLE t1 (a VARCHAR(1000), b CHAR(1));
INSERT INTO t1 VALUES
(REPEAT('a',1000),'a'),(REPEAT('t',932),'t'),('x',NULL),('x',NULL),
(REPEAT('z',298),'z'),(REPEAT('p',1000),'p'),(REPEAT('k',468),'k'),
(REPEAT('c',1000),'c'),(REPEAT('o',648),'o'),('x',NULL),('x',NULL),
(REPEAT('c',258),'c'),(REPEAT('t',414),'t'),(REPEAT('f',966),'f'),
(REPEAT('y',746),'y'),(REPEAT('f',1000),'f');
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1; SET @save_sort_buffer_size= @@sort_buffer_size; SET sort_buffer_size= 16384;
SELECT LEFT(a,1), GROUP_CONCAT(b) FROM t1 GROUP BY a;
LEFT(a,1) GROUP_CONCAT(b)
a a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a
c c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,c
c c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,c
f f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f
f f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f
k k,k,k,k,k,k,k,k,k,k,k,k,k,k,k,k
o o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o
p p,p,p,p,p,p,p,p,p,p,p,p,p,p,p,p
t t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t
t t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t
x NULL
y y,y,y,y,y,y,y,y,y,y,y,y,y,y,y,y
z z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z
SELECT SUBSTR(a,1,1), LENGTH(a), GROUP_CONCAT(b), COUNT(*) FROM t1 GROUP BY a;
SUBSTR(a,1,1) LENGTH(a) GROUP_CONCAT(b) COUNT(*)
a 1000 a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a 16
c 258 c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,c 16
c 1000 c,c,c,c,c,c,c,c,c,c,c,c,c,c,c,c 16
f 966 f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f 16
f 1000 f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f 16
k 468 k,k,k,k,k,k,k,k,k,k,k,k,k,k,k,k 16
o 648 o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o 16
p 1000 p,p,p,p,p,p,p,p,p,p,p,p,p,p,p,p 16
t 414 t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t 16
t 932 t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t 16
x 1 NULL 64
y 746 y,y,y,y,y,y,y,y,y,y,y,y,y,y,y,y 16
z 298 z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z 16 SET @@sort_buffer_size= @save_sort_buffer_size;
DROP TABLE t1;
CREATE TABLE t1(a VARCHAR(1027), b INT);
INSERT INTO t1 SELECT seq, seq from seq_1_to_34; SET @save_tmp_memory_table_size= @@tmp_memory_table_size; SET tmp_memory_table_size= 1056*2;
Warnings:
Warning 1292 Truncated incorrect tmp_memory_table_size value: '2112'
SELECT COUNT(DISTINCT a) FROM t1;
COUNT(DISTINCT a) 34 SET @@tmp_memory_table_size= @save_tmp_memory_table_size;
DROP TABLE t1;
#
# MDEV-23216: LONGTEXT column with collation doesn't sort
#
CREATE TABLE t1 (a LONGTEXT COLLATE utf8mb4_swedish_ci);
INSERT INTO t1 VALUES ('A'),('Z'),('B'),('Y');
SELECT * FROM t1 ORDER BY a;
a
A
B
Y
Z
SELECT * FROM t1 ORDER BY a DESC;
a
Z
Y
B
A
DROP TABLE t1;
#
# MDEV-23414 Assertion `res->charset() == item->collation.collation' failed in Type_handler_string_result::make_packed_sort_key_part
#
CREATE TABLE t1 (a CHAR(3), b BINARY(255));
INSERT t1 VALUES ('foo','bar'),('baz','qux');
SELECT COALESCE(a, b) AS f FROM t1 ORDER BY f;
f
baz
foo
DROP TABLE t1;
#
# MDEV-24015: SQL Error (1038): Out of sort memory when enough memory for the
# sort buffer is provided
#
CREATE TABLE t1 (a VARCHAR(100), b INT);
CREATE TABLE t2 (a VARCHAR(100), b INT);
INSERT INTO t1 SELECT 'abc', seq FROM seq_1_to_50;
INSERT INTO t2 SELECT seq, seq FROM seq_1_to_50; set @save_sort_buffer_size= @@sort_buffer_size; set sort_buffer_size=2000;
ANALYZE FORMAT=JSON
SELECT (SELECT sum(t2.b) FROM t2 WHERE t1.b=t2.b GROUP BY t2.a) FROM t1;
ANALYZE
{ "query_optimization": { "r_total_time_ms": "REPLACED"
}, "query_block": { "select_id": 1, "cost": "REPLACED", "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [
{ "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 50, "r_rows": 50, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100
}
}
], "subqueries": [
{ "subquery_cache": { "r_loops": 50, "r_hit_ratio": 0, "query_block": { "select_id": 2, "cost": "REPLACED", "r_loops": 50, "r_total_time_ms": "REPLACED", "filesort": { "sort_key": "t2.a", "r_loops": 50, "r_total_time_ms": "REPLACED", "r_used_priority_queue": false, "r_output_rows": 1, "r_buffer_size": "REPLACED" across executions)", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [
{ "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "r_loops": 50, "rows": 50, "r_rows": 50, "cost": "REPLACED", "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 100, "r_total_filtered": 2, "attached_condition": "t1.b = t2.b", "r_filtered": 2
}
}
]
}
}
}
}
}
]
}
}
SELECT (SELECT sum(t2.b) FROM t2 WHERE t1.b=t2.b GROUP BY t2.a) FROM t1;
(SELECT sum(t2.b) FROM t2 WHERE t1.b=t2.b GROUP BY t2.a) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 set sort_buffer_size= @save_sort_buffer_size;
DROP TABLE t1,t2;
# End of 10.5 tests
#
# MDEV-13694: Wrong result upon GROUP BY with orderby_uses_equalities=on
#
CREATE TABLE t1 (a INT, b int, primary key(a));
CREATE TABLE t2 (a INT, b INT);
INSERT INTO t1 (a,b) VALUES (58,1),(96,2),(273,3),(23,4),(231,5),(525,6),
(2354,7),(321421,3),(535,2),(4535,3);
INSERT INTO t2 (a,b) VALUES (58,3),(96,3),(273,3),(1000,1000),(2000,2000),(3000,3000);
INSERT INTO t2 select seq,seq from seq_10_to_100;
# Join order should have the SJM scan table as the first table for both
# the queries with GROUP BY and ORDER BY clause.
EXPLAIN SELECT t1.a
FROM t1
WHERE t1.a IN (SELECT a FROM t2 WHERE b=3)
ORDER BY t1.a DESC;
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 10 Using index 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 97 Using where
EXPLAIN FORMAT=JSON SELECT t1.a
FROM t1
WHERE t1.a IN (SELECT a FROM t2 WHERE b=3)
ORDER BY t1.a DESC;
EXPLAIN
{ "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [
{ "table": { "table_name": "t1", "access_type": "index", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["a"], "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100, "using_index": true
}
},
{ "table": { "table_name": "<subquery2>", "access_type": "eq_ref", "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "4", "used_key_parts": ["a"], "ref": ["func"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, "nested_loop": [
{ "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "rows": 97, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.b = 3"
}
}
]
}
}
}
}
]
}
}
SELECT t1.a
FROM t1
WHERE t1.a IN (SELECT a FROM t2 WHERE b=3)
ORDER BY t1.a DESC;
a 273 96 58
EXPLAIN SELECT t1.a, group_concat(t1.b)
FROM t1
WHERE t1.a IN (SELECT a FROM t2 WHERE b=3)
GROUP BY t1.a DESC;
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 10 Using filesort 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 97 Using where
EXPLAIN FORMAT=JSON SELECT t1.a, group_concat(t1.b)
FROM t1
WHERE t1.a IN (SELECT a FROM t2 WHERE b=3)
GROUP BY t1.a DESC;
EXPLAIN
{ "query_block": { "select_id": 1, "cost": "COST_REPLACED", "nested_loop": [
{ "read_sorted_file": { "filesort": { "sort_key": "t1.a desc", "table": { "table_name": "t1", "access_type": "ALL", "possible_keys": ["PRIMARY"], "loops": 1, "rows": 10, "cost": "COST_REPLACED", "filtered": 100
}
}
}
},
{ "table": { "table_name": "<subquery2>", "access_type": "eq_ref", "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "4", "used_key_parts": ["a"], "ref": ["func"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, "nested_loop": [
{ "table": { "table_name": "t2", "access_type": "ALL", "loops": 1, "rows": 97, "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.b = 3"
}
}
]
}
}
}
}
]
}
}
SELECT t1.a, group_concat(t1.b)
FROM t1
WHERE t1.a IN (SELECT a FROM t2 WHERE b=3)
GROUP BY t1.a DESC;
a group_concat(t1.b) 2733 962 581
DROP TABLE t1, t2;
# End of 10.6 tests
#
# MDEV-13756 Implement descending index: KEY (a DESC, b ASC)
#
create table t1 (a int, b int, c int, key r (a desc, b asc));
insert t1 select seq % 10, seq div 10, seq from seq_1_to_55;
insert t1 values (NULL, NULL, NULL), (9, NULL, NULL);
explain select * from t1 force index(r) order by a,b limit 20;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 57 Using filesort
explain select * from t1 force index(r) order by a desc,b limit 20;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL r 10 NULL 20
select * from t1 force index(r) order by a desc,b limit 20;
a b c 9 NULL NULL 909 9119 9229 9339 9449 808 8118 8228 8338 8448 707 7117 7227 7337 7447 606 6116 6226 6336
drop table t1;
#
# MDEV-38087 Unexpected error `Unknown column '???' in 'GROUP BY'`
#
SELECT 1 FROM (select 1) dt GROUP BY -1; ERROR42S22: Unknown column '-1' in 'GROUP BY'
SELECT 1 FROM (select 1) dt order BY -5; ERROR42S22: Unknown column '-5' in 'ORDER BY'
SELECT 1 FROM (select 1) dt order BY 50; ERROR42S22: Unknown column '50' in 'ORDER BY'
# End of 10.11 tests
#
# MDEV-38649: Wrong result (missing rows) upon condition pushdown on table with DESC key
#
CREATE TABLE t1 (a INT, b INT not null, KEY(b));
INSERT INTO t1 VALUES (2,8),(9,1),(4,6);
SELECT a, b FROM t1 WHERE b != 10and b < 30 ORDER BY b DESC;
a b 28 46 91
SELECT a, b FROM t1 use index() WHERE b != 10and b < 30 ORDER BY b DESC;
a b 28 46 91
DROP TABLE t1;
#
# MDEV-38921: Incorrect ORDER BY Result
#
CREATE TABLE t2 (a INT, b INT not null, KEY(b));
INSERT INTO t2 VALUES (2,8),(9,1),(4,6),(11,13),(15,17);
SELECT a, b FROM t2 WHERE b != 10and b < 30 ORDER BY b DESC;
a b 1517 1113 28 46 91
DROP TABLE t2;
#
# MDEV-38934: ICP, Reverse scan: range access will scan whole index for
# range w/o max endpoint
#
CREATE TABLE t1 (
a int,
b int,
INDEX(a)
);
INSERT INTO t1 SELECT seq, seq FROM seq_1_to_10000;
SELECT COUNT(*) FROM t1 WHERE a > 9900AND (a+1 > 9901) ORDER BY a DESC;
COUNT(*) 100
FLUSH STATUS;
SELECT * FROM t1 WHERE a > 9900AND (a+1 > 9901) ORDER BY a DESC;
SELECT * FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME LIKE '%icp%';
VARIABLE_NAME VARIABLE_VALUE
HANDLER_ICP_ATTEMPTS 100
HANDLER_ICP_MATCH 100
SELECT COUNT(*) FROM t1 WHERE a > 9900AND (a+1 > 990000) ORDER BY a DESC;
COUNT(*) 0
FLUSH STATUS;
SELECT * FROM t1 WHERE a > 9900AND (a+1 > 999000) ORDER BY a DESC;
SELECT * FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME LIKE '%icp%';
VARIABLE_NAME VARIABLE_VALUE
HANDLER_ICP_ATTEMPTS 100
HANDLER_ICP_MATCH 0
# Another testcase: check that we correctly handle the case
# where the range starting with +inf has no matches due to ICP,
# but there are other ranges with matches:
select * from t1 where (a > 9990and (a+1)<0) or a=23 order by a desc;
a b 2323
DROP TABLE t1;
#
# End of 12.3 tests
#
ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;
Messung V0.5 in Prozent
¤ Dauer der Verarbeitung: 0.183 Sekunden
(vorverarbeitet am 2026-10-08)
¤
Die Informationen auf dieser Webseite wurden
nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit,
noch Qualität der bereit gestellten Informationen zugesichert.
Bemerkung:
Die farbliche Syntaxdarstellung und die Messung sind noch experimentell.
