10
10
event_date date DEFAULT '0000-00-00' NOT NULL,
11
type int(11) DEFAULT '0' NOT NULL,
12
event_id int(11) DEFAULT '0' NOT NULL,
11
type int DEFAULT '0' NOT NULL,
12
event_id int DEFAULT '0' NOT NULL,
13
13
PRIMARY KEY (event_date,type,event_id)
41
PAPER_ID int(6) DEFAULT '0' NOT NULL,
42
YEAR int(6) DEFAULT '0' NOT NULL,
43
ISSUE int(6) DEFAULT '0' NOT NULL,
44
CLOSED int(4) DEFAULT '0' NOT NULL,
41
PAPER_ID int DEFAULT '0' NOT NULL,
42
YEAR int DEFAULT '0' NOT NULL,
43
ISSUE int DEFAULT '0' NOT NULL,
44
CLOSED int DEFAULT '0' NOT NULL,
45
45
ISS_DATE date DEFAULT '0000-00-00' NOT NULL,
46
46
PRIMARY KEY (PAPER_ID,YEAR,ISSUE)
69
id int(11) NOT NULL auto_increment,
70
parent_id int(11) DEFAULT '0' NOT NULL,
71
level int(4) DEFAULT '0' NOT NULL,
69
id int NOT NULL auto_increment,
70
parent_id int DEFAULT '0' NOT NULL,
71
level int DEFAULT '0' NOT NULL,
73
73
KEY parent_id (parent_id),
119
119
CREATE TABLE t1 (
120
t1ID int(10) NOT NULL auto_increment,
121
art binary(1) NOT NULL default '',
120
t1ID int NOT NULL auto_increment,
121
art varbinary(1) NOT NULL default '',
122
122
KNR char(5) NOT NULL default '',
123
123
RECHNR char(6) NOT NULL default '',
124
124
POSNR char(2) NOT NULL default '',
198
198
# bug #1172: "Force index" option caused server crash
200
CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1));
200
CREATE TABLE t1 (key1 int NOT NULL default '0', KEY i1 (key1));
201
201
INSERT INTO t1 VALUES (0),(0),(0),(0),(0),(1),(1);
202
CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya));
202
CREATE TABLE t2 (keya int NOT NULL default '0', KEY j1 (keya));
203
203
INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2);
204
204
explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;
205
205
explain select * from t1 force index(i1), t2 force index(j1) where
264
264
CREATE TABLE t1 (
265
id int( 11 ) NOT NULL AUTO_INCREMENT ,
266
line int( 5 ) NOT NULL default '0',
267
columnid int( 3 ) NOT NULL default '0',
268
owner int( 3 ) NOT NULL default '0',
269
ordinal int( 3 ) NOT NULL default '0',
270
showid int( 6 ) NOT NULL default '1',
271
tableid int( 1 ) NOT NULL default '1',
272
content int( 5 ) NOT NULL default '188',
265
id int NOT NULL AUTO_INCREMENT ,
266
line int NOT NULL default '0',
267
columnid int NOT NULL default '0',
268
owner int NOT NULL default '0',
269
ordinal int NOT NULL default '0',
270
showid int NOT NULL default '1',
271
tableid int NOT NULL default '1',
272
content int NOT NULL default '188',
273
273
PRIMARY KEY ( owner, id ) ,
274
274
KEY menu( owner, showid, columnid ) ,
275
275
KEY `COLUMN` ( owner, columnid, line ) ,
286
286
# test for a bug with in() and unique key
289
create table t1 (id int(10) primary key);
289
create table t1 (id int primary key);
290
290
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);
292
292
select id from t1 where id in (2,5,9) ;
393
393
# Fix for bug#4488
395
395
create table t1 (x bigint not null);
396
insert into t1(x) values (0xfffffffffffffff0);
397
insert into t1(x) values (0xfffffffffffffff1);
396
insert into t1(x) values (0x0ffffffffffffff0);
397
insert into t1(x) values (0x0ffffffffffffff1);
398
398
select * from t1;
399
399
select count(*) from t1 where x>0;
400
400
select count(*) from t1 where x=0;
421
421
--disable_warnings
422
422
create table t1 (x bigint not null primary key) engine=innodb;
423
423
--enable_warnings
424
insert into t1(x) values (0xfffffffffffffff0);
425
insert into t1(x) values (0xfffffffffffffff1);
424
insert into t1(x) values (0x0ffffffffffffff0);
425
insert into t1(x) values (0x0ffffffffffffff1);
426
426
select * from t1;
427
427
select count(*) from t1 where x>0;
428
428
select count(*) from t1 where x=0;
457
457
# Bug #6045: Binary Comparison regression in MySQL 4.1
458
458
# Binary searches didn't use a case insensitive index.
461
create table t1 (a char(10), b text, key (a)) character set latin1;
460
create table t1 (a char(10), b text, key (a));
462
461
INSERT INTO t1 (a) VALUES
463
462
('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb');
464
463
# all these three can be optimized
465
464
explain select * from t1 where a='aaa';
466
465
explain select * from t1 where a=binary 'aaa';
467
explain select * from t1 where a='aaa' collate latin1_bin;
469
explain select * from t1 where a='aaa' collate latin1_german1_ci;
466
explain select * from t1 where a='aaa' collate utf8_bin;
472
469
# Test for BUG#9348 "result for WHERE A AND (B OR C) differs from WHERE a AND (C OR B)"
473
470
--disable_warnings
474
471
CREATE TABLE t1 (
475
`CLIENT` char(3) character set latin1 collate latin1_bin NOT NULL default '000',
476
`ARG1` char(3) character set latin1 collate latin1_bin NOT NULL default '',
477
`ARG2` char(3) character set latin1 collate latin1_bin NOT NULL default '',
478
`FUNCTION` varchar(10) character set latin1 collate latin1_bin NOT NULL default '',
479
`FUNCTINT` int(11) NOT NULL default '0',
472
`CLIENT` char(3) collate utf8_bin NOT NULL default '000',
473
`ARG1` char(3) collate utf8_bin NOT NULL default '',
474
`ARG2` char(3) collate utf8_bin NOT NULL default '',
475
`FUNCTION` varchar(10) collate utf8_bin NOT NULL default '',
476
`FUNCTINT` int NOT NULL default '0',
480
477
KEY `VERI_CLNT~2` (`ARG1`)
481
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
482
479
--enable_warnings
484
481
INSERT INTO t1 VALUES ('000',' 0',' 0','Text 001',0), ('000',' 0',' 1','Text 002',0),
496
493
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
498
495
CREATE TABLE t2 (
499
pk1 int(11) NOT NULL,
500
pk2 int(11) NOT NULL,
501
pk3 int(11) NOT NULL,
502
pk4 int(11) NOT NULL,
504
501
PRIMARY KEY (pk1,pk2,pk3,pk4)
505
) DEFAULT CHARSET=latin1;
507
504
insert into t2 select 1, A.a+10*B.a, 432, 44, 'fillerZ' from t1 A, t1 B;
508
505
INSERT INTO t2 VALUES (2621, 2635, 0, 0,'filler'), (2621, 2635, 1, 0,'filler'),
532
529
CREATE TABLE t1 (
533
OXID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
534
OXPARENTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT 'oxrootid',
530
OXID varchar(32) NOT NULL DEFAULT '',
531
OXPARENTID varchar(32) NOT NULL DEFAULT 'oxrootid',
535
532
OXLEFT int NOT NULL DEFAULT '0',
536
533
OXRIGHT int NOT NULL DEFAULT '0',
537
OXROOTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
534
OXROOTID varchar(32) NOT NULL DEFAULT '',
538
535
PRIMARY KEY (OXID),
539
536
KEY OXNID (OXID),
540
537
KEY OXLEFT (OXLEFT),
541
538
KEY OXRIGHT (OXRIGHT),
542
539
KEY OXROOTID (OXROOTID)
543
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
545
542
INSERT INTO t1 VALUES
546
543
('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'),
758
# Test for bug #10031: range to be used over a view
761
CREATE TABLE t1 (a int, b int, primary key(a,b));
763
INSERT INTO t1 VALUES
764
(1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,1),(3,2),(3,3),(4,1),(4,2),(4,3);
766
CREATE VIEW v1 as SELECT a,b FROM t1 WHERE b=3;
768
EXPLAIN SELECT a,b FROM t1 WHERE a < 2 and b=3;
769
EXPLAIN SELECT a,b FROM v1 WHERE a < 2 and b=3;
771
EXPLAIN SELECT a,b FROM t1 WHERE a < 2;
772
EXPLAIN SELECT a,b FROM v1 WHERE a < 2;
774
SELECT a,b FROM t1 WHERE a < 2 and b=3;
775
SELECT a,b FROM v1 WHERE a < 2 and b=3;
781
755
# Bug #11853: DELETE statement with a NOT (LIKE/<=>) where condition
782
756
# for an indexed attribute
804
# BUG#13317: range optimization doesn't work for IN over VIEW.
806
create table t1 (a int, b int, primary key(a,b));
807
create view v1 as select a, b from t1;
809
INSERT INTO `t1` VALUES
810
(0,0),(1,0),(2,0),(3,0),(4,0),(5,1),(6,1),(7,1),(8,1),(9,1),(10,2),(11,2),(12,2)
811
,(13,2),(14,2),(15,3),(16,3),(17,3),(18,3),(19,3);
814
explain select * from t1 where a in (3,4) and b in (1,2,3);
816
explain select * from v1 where a in (3,4) and b in (1,2,3);
818
explain select * from t1 where a between 3 and 4 and b between 1 and 2;
820
explain select * from v1 where a between 3 and 4 and b between 1 and 2;
826
778
create table t3 (a int);
827
779
insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
829
create table t1 (a varchar(10), filler char(200), key(a)) charset=binary;
781
create table t1 (a varchar(10), filler char(200), key(a));
830
782
insert into t1 values ('a','');
831
783
insert into t1 values ('a ','');
832
784
insert into t1 values ('a ', '');
854
806
drop table t1,t2,t3;
859
create table t1 (a int);
860
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
861
create table t2 (a int, key(a));
862
insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C;
864
set @a="select * from t2 force index (a) where a NOT IN(0";
865
select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z;
866
set @a=concat(@a, ')');
868
insert into t2 values (11),(13),(15);
870
set @b= concat("explain ", @a);
872
prepare stmt1 from @b;
875
prepare stmt1 from @a;
881
809
# Bug #18165: range access for BETWEEN with a constant for the first argument
939
867
--echo BUG#32198 "Comparison of DATE with DATETIME still not using indexes correctly"
941
869
CREATE TABLE t1 (
942
id int(11) NOT NULL auto_increment,
870
id int NOT NULL auto_increment,
943
871
dateval date default NULL,
944
872
PRIMARY KEY (id),
945
873
KEY dateval (dateval)
963
891
CREATE TABLE t1 (
964
892
a varchar(32), index (a)
965
) DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
893
) DEFAULT COLLATE=utf8_bin;
967
895
INSERT INTO t1 VALUES
968
896
('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A');
979
907
# test UNSIGNED. only occurs when indexed.
980
CREATE TABLE t1 (f1 int(11) NOT NULL, PRIMARY KEY (f1));
908
CREATE TABLE t1 (f1 int NOT NULL, PRIMARY KEY (f1));
982
910
INSERT INTO t1 VALUES (127),(254),(0),(1),(255);
984
912
# test upper bound
986
SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256;
987
SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256.0;
914
SELECT COUNT(*) FROM t1 WHERE f1 < 256;
915
SELECT COUNT(*) FROM t1 WHERE f1 < 256.0;
989
SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 255;
917
SELECT COUNT(*) FROM t1 WHERE f1 < 255;
991
919
# show we don't fiddle with lower bound on UNSIGNED
993
SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < -1;
921
SELECT COUNT(*) FROM t1 WHERE f1 < -1;
995
SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -1;
923
SELECT COUNT(*) FROM t1 WHERE f1 > -1;
1000
928
# test signed. only occurs when index.
1001
CREATE TABLE t1 ( f1 int(11) NOT NULL, PRIMARY KEY (f1));
929
CREATE TABLE t1 ( f1 int NOT NULL, PRIMARY KEY (f1));
1003
931
INSERT INTO t1 VALUES (127),(126),(0),(-128),(-127);
1005
933
# test upper bound
1007
SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128;
1008
SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128.0;
935
SELECT COUNT(*) FROM t1 WHERE f1 < 128;
936
SELECT COUNT(*) FROM t1 WHERE f1 < 128.0;
1010
SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 127;
938
SELECT COUNT(*) FROM t1 WHERE f1 < 127;
1012
940
# test lower bound
1014
SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129;
1015
SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129.0;
942
SELECT COUNT(*) FROM t1 WHERE f1 > -129;
943
SELECT COUNT(*) FROM t1 WHERE f1 > -129.0;
1017
SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -128;
945
SELECT COUNT(*) FROM t1 WHERE f1 > -128;