func_in.test 8.01 KB
Newer Older
1 2
# Initialise
--disable_warnings
3
drop table if exists t1, t2;
4
--enable_warnings
5 6 7 8
#
# test of IN (NULL)
#

9 10 11 12 13 14 15 16 17 18 19 20
select 1 in (1,2,3);
select 10 in (1,2,3);
select NULL in (1,2,3);
select 1 in (1,NULL,3);
select 3 in (1,NULL,3);
select 10 in (1,NULL,3);
select 1.5 in (1.5,2.5,3.5);
select 10.5 in (1.5,2.5,3.5);
select NULL in (1.5,2.5,3.5);
select 1.5 in (1.5,NULL,3.5);
select 3.5 in (1.5,NULL,3.5);
select 10.5 in (1.5,NULL,3.5);
21

22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
CREATE TABLE t1 (a int, b int, c int);
insert into t1 values (1,2,3), (1,NULL,3);
select 1 in (a,b,c) from t1;
select 3 in (a,b,c) from t1;
select 10 in (a,b,c) from t1;
select NULL in (a,b,c) from t1;
drop table t1;
CREATE TABLE t1 (a float, b float, c float);
insert into t1 values (1.5,2.5,3.5), (1.5,NULL,3.5);
select 1.5 in (a,b,c) from t1;
select 3.5 in (a,b,c) from t1;
select 10.5 in (a,b,c) from t1;
drop table t1;
CREATE TABLE t1 (a varchar(10), b varchar(10), c varchar(10));
insert into t1 values ('A','BC','EFD'), ('A',NULL,'EFD');
select 'A' in (a,b,c) from t1;
select 'EFD' in (a,b,c) from t1;
select 'XSFGGHF' in (a,b,c) from t1;
drop table t1;

42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
CREATE TABLE t1 (field char(1));
INSERT INTO t1 VALUES ('A'),(NULL);
SELECT * from t1 WHERE field IN (NULL);
SELECT * from t1 WHERE field NOT IN (NULL);
SELECT * from t1 where field = field;
SELECT * from t1 where field <=> field;
DELETE FROM t1 WHERE field NOT IN (NULL);
SELECT * FROM t1;
drop table t1;

create table t1 (id int(10) primary key);
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);
select * from t1 where id in (2,5,9);
drop table t1;

57 58 59 60 61 62 63
create table t1 (
a char(1) character set latin1 collate latin1_general_ci,
b char(1) character set latin1 collate latin1_swedish_ci,
c char(1) character set latin1 collate latin1_danish_ci
);
insert into t1 values ('A','B','C');
insert into t1 values ('a','c','c');
unknown's avatar
unknown committed
64
--error 1267
65
select * from t1 where a in (b);
unknown's avatar
unknown committed
66
--error 1270
unknown's avatar
unknown committed
67 68
select * from t1 where a in (b,c);
--error 1271
69 70 71 72 73 74
select * from t1 where 'a' in (a,b,c);
select * from t1 where 'a' in (a);
select * from t1 where a in ('a');
select * from t1 where 'a' collate latin1_general_ci in (a,b,c);
select * from t1 where 'a' collate latin1_bin in (a,b,c);
select * from t1 where 'a' in (a,b,c collate latin1_bin);
75
explain extended select * from t1 where 'a' in (a,b,c collate latin1_bin);
76
drop table t1;
77

78 79 80 81 82
set names utf8;
create table t1 (a char(10) character set utf8 not null);
insert into t1 values ('bbbb'),(_koi8r'ÃÃÃÃ'),(_latin1'ÄÄÄÄ');
select a from t1 where a in ('bbbb',_koi8r'ÃÃÃÃ',_latin1'ÄÄÄÄ') order by a;
drop table t1;
unknown's avatar
unknown committed
83 84 85 86 87
# Bug#7834 Illegal mix of collations in IN operator
create table t1 (a char(10) character set latin1 not null);
insert into t1 values ('a'),('b'),('c');
select a from t1 where a IN ('a','b','c') order by a;
drop table t1;
88 89
set names latin1;

90 91 92 93 94 95 96
select '1.0' in (1,2);
select 1 in ('1.0',2);
select 1 in (1,'2.0');
select 1 in ('1.0',2.0);
select 1 in (1.0,'2.0');
select 1 in ('1.1',2);
select 1 in ('1.1',2.0);
unknown's avatar
unknown committed
97 98 99

# Test case for bug #6365

100
create table t1 (a char(2) character set binary);
unknown's avatar
unknown committed
101 102 103
insert into t1 values ('aa'), ('bb');
select * from t1 where a in (NULL, 'aa');
drop table t1;
unknown's avatar
unknown committed
104

105 106 107 108 109 110 111
# BUG#13419
create table t1 (id int, key(id));
insert into t1 values (1),(2),(3);
select count(*) from t1 where id not in (1);
select count(*) from t1 where id not in (1,2);
drop table t1;

112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132

#
# BUG#17047: CHAR() and IN() can return NULL without signaling NULL
# result
#
# The problem was in the IN() function that ignored maybe_null flags
# of all arguments except the first (the one _before_ the IN
# keyword, '1' in the test case below).
#
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 SELECT 1 IN (2, NULL);
--echo SELECT should return NULL.
SELECT * FROM t1;

DROP TABLE t1;


--echo End of 4.1 tests
133

unknown's avatar
unknown committed
134

unknown's avatar
unknown committed
135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151
#
# Bug #11885: WHERE condition with NOT IN (one element)          
#             

CREATE TABLE t1 (a int PRIMARY KEY);
INSERT INTO t1 VALUES (44), (45), (46);

SELECT * FROM t1 WHERE a IN (45);
SELECT * FROM t1 WHERE a NOT IN (0, 45);
SELECT * FROM t1 WHERE a NOT IN (45);

CREATE VIEW v1 AS SELECT * FROM t1 WHERE a NOT IN (45);
SHOW CREATE VIEW v1;
SELECT * FROM v1; 

DROP VIEW v1;
DROP TABLE t1;
152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243

# BUG#15872: Excessive memory consumption of range analysis of NOT IN
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 
create table t2 (a int, filler char(200), key(a));

insert into t2 select C.a*2,   'no'  from t1 A, t1 B, t1 C;
insert into t2 select C.a*2+1, 'yes' from t1 C;

explain 
select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);
select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18);

explain select * from t2 force index(a) where a NOT IN (2,2,2,2,2,2);
explain select * from t2 force index(a) where a <> 2;

drop table t2;

#
# Repeat the test for DATETIME
#
create table t2 (a datetime, filler char(200), key(a));

insert into t2 select '2006-04-25 10:00:00' + interval C.a minute,
               'no'  from t1 A, t1 B, t1 C where C.a % 2 = 0;

insert into t2 select '2006-04-25 10:00:00' + interval C.a*2+1 minute,
               'yes' from t1 C;

explain 
select * from t2 where a NOT IN (
  '2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', 
  '2006-04-25 10:06:00', '2006-04-25 10:08:00');
select * from t2 where a NOT IN (
  '2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', 
  '2006-04-25 10:06:00', '2006-04-25 10:08:00');
drop table t2;

#
# Repeat the test for CHAR(N)
#
create table t2 (a varchar(10), filler char(200), key(a));

insert into t2 select 'foo', 'no' from t1 A, t1 B;
insert into t2 select 'barbar', 'no' from t1 A, t1 B;
insert into t2 select 'bazbazbaz', 'no' from t1 A, t1 B;

insert into t2 values ('fon', '1'), ('fop','1'), ('barbaq','1'), 
  ('barbas','1'), ('bazbazbay', '1'),('zz','1');

explain select * from t2 where a not in('foo','barbar', 'bazbazbaz');

drop table t2;

#
# Repeat for DECIMAL
#
create table t2 (a decimal(10,5), filler char(200), key(a));

insert into t2 select 345.67890, 'no' from t1 A, t1 B;
insert into t2 select 43245.34, 'no' from t1 A, t1 B;
insert into t2 select 64224.56344, 'no' from t1 A, t1 B;

insert into t2 values (0, '1'), (22334.123,'1'), (33333,'1'), 
  (55555,'1'), (77777, '1');

explain
select * from t2 where a not in (345.67890, 43245.34, 64224.56344);
select * from t2 where a not in (345.67890, 43245.34, 64224.56344);

drop table t2;

# Try a very big IN-list
create table t2 (a int, key(a), b int);
insert into t2 values (1,1),(2,2);

set @cnt= 1; 
set @str="update t2 set b=1 where a not in (";
select count(*) from (
  select @str:=concat(@str, @cnt:=@cnt+1, ",") 
  from t1 A, t1 B, t1 C, t1 D) Z;

set @str:=concat(@str, "10000)");
select substr(@str, 1, 50);
prepare s from @str;
execute s;
deallocate prepare s;
set @str=NULL;

drop table t2;
drop table t1;

244 245 246 247 248 249 250 251 252 253 254 255
# BUG#19618: Crash in range optimizer for 
#   "unsigned_keypart NOT IN(negative_number,...)" 
#   (introduced in fix BUG#15872) 
create table t1 (
  some_id smallint(5) unsigned,
  key (some_id)
);
insert into t1 values (1),(2);
select some_id from t1 where some_id not in(2,-1);
select some_id from t1 where some_id not in(-4,-1,-4);
select some_id from t1 where some_id not in(-4,-1,3423534,2342342);
drop table t1;
256

257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292
#
# BUG#20420: optimizer reports wrong keys on left join with IN
#
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);

CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
INSERT INTO t2 VALUES (3,2),(4,2);

CREATE TABLE t3 (a int PRIMARY KEY);
INSERT INTO t3 VALUES (1),(2),(3),(4);

CREATE TABLE t4 (a int PRIMARY KEY);
INSERT INTO t4 VALUES (1),(2);

EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 
  JOIN t1 ON t3.a=t1.a 
  JOIN t2 ON t3.a=t2.a
  JOIN t4 WHERE t4.a IN (t1.b, t2.b);

SELECT STRAIGHT_JOIN * FROM t3 
  JOIN t1 ON t3.a=t1.a 
  JOIN t2 ON t3.a=t2.a
  JOIN t4 WHERE t4.a IN (t1.b, t2.b);

EXPLAIN SELECT STRAIGHT_JOIN 
   (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
  FROM t3, t1, t2
  WHERE t3.a=t1.a AND t3.a=t2.a;

SELECT STRAIGHT_JOIN 
   (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
  FROM t3, t1, t2
  WHERE t3.a=t1.a AND t3.a=t2.a;

DROP TABLE t1,t2,t3,t4;  
293 294

--echo End of 5.0 tests