1 drop table if exists t1,t2;
3 select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo';
4 hello 'hello' ""hello"" 'h'e'l'l'o' hel"lo hel'lo
5 hello 'hello' ""hello"" 'h'e'l'l'o' hel"lo hel'lo
6 select 'hello' 'monty';
9 select length('\n\t\r\b\0\_\%\\');
10 length('\n\t\r\b\0\_\%\\')
12 select bit_length('\n\t\r\b\0\_\%\\');
13 bit_length('\n\t\r\b\0\_\%\\')
15 select char_length('\n\t\r\b\0\_\%\\');
16 char_length('\n\t\r\b\0\_\%\\')
18 select length(_latin1'\n\t\n\b\0\\_\\%\\');
19 length(_latin1'\n\t\n\b\0\\_\\%\\')
21 select concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h');
22 concat('monty',' was here ','again') length('hello') char(ascii('h')) ord('h')
23 monty was here again 5 h 104
24 select hex(char(256));
27 select locate('he','hello'),locate('he','hello',2),locate('lo','hello',2) ;
28 locate('he','hello') locate('he','hello',2) locate('lo','hello',2)
30 select instr('hello','HE'), instr('hello',binary 'HE'), instr(binary 'hello','HE');
31 instr('hello','HE') instr('hello',binary 'HE') instr(binary 'hello','HE')
33 select position(binary 'll' in 'hello'),position('a' in binary 'hello');
34 position(binary 'll' in 'hello') position('a' in binary 'hello')
36 select left('hello',null), right('hello',null);
37 left('hello',null) right('hello',null)
39 select left('hello',2),right('hello',2),substring('hello',2,2),mid('hello',1,5) ;
40 left('hello',2) right('hello',2) substring('hello',2,2) mid('hello',1,5)
42 select concat('',left(right(concat('what ',concat('is ','happening')),9),4),'',substring('monty',5,1)) ;
43 concat('',left(right(concat('what ',concat('is ','happening')),9),4),'',substring('monty',5,1))
45 select substring_index('www.tcx.se','.',-2),substring_index('www.tcx.se','.',1);
46 substring_index('www.tcx.se','.',-2) substring_index('www.tcx.se','.',1)
48 select substring_index('www.tcx.se','tcx',1),substring_index('www.tcx.se','tcx',-1);
49 substring_index('www.tcx.se','tcx',1) substring_index('www.tcx.se','tcx',-1)
51 select substring_index('.tcx.se','.',-2),substring_index('.tcx.se','.tcx',-1);
52 substring_index('.tcx.se','.',-2) substring_index('.tcx.se','.tcx',-1)
54 select substring_index('aaaaaaaaa1','a',1);
55 substring_index('aaaaaaaaa1','a',1)
57 select substring_index('aaaaaaaaa1','aa',1);
58 substring_index('aaaaaaaaa1','aa',1)
60 select substring_index('aaaaaaaaa1','aa',2);
61 substring_index('aaaaaaaaa1','aa',2)
63 select substring_index('aaaaaaaaa1','aa',3);
64 substring_index('aaaaaaaaa1','aa',3)
66 select substring_index('aaaaaaaaa1','aa',4);
67 substring_index('aaaaaaaaa1','aa',4)
69 select substring_index('aaaaaaaaa1','aa',5);
70 substring_index('aaaaaaaaa1','aa',5)
72 select substring_index('aaaaaaaaa1','aaa',1);
73 substring_index('aaaaaaaaa1','aaa',1)
75 select substring_index('aaaaaaaaa1','aaa',2);
76 substring_index('aaaaaaaaa1','aaa',2)
78 select substring_index('aaaaaaaaa1','aaa',3);
79 substring_index('aaaaaaaaa1','aaa',3)
81 select substring_index('aaaaaaaaa1','aaa',4);
82 substring_index('aaaaaaaaa1','aaa',4)
84 select substring_index('aaaaaaaaa1','aaaa',1);
85 substring_index('aaaaaaaaa1','aaaa',1)
87 select substring_index('aaaaaaaaa1','aaaa',2);
88 substring_index('aaaaaaaaa1','aaaa',2)
90 select substring_index('aaaaaaaaa1','1',1);
91 substring_index('aaaaaaaaa1','1',1)
93 select substring_index('aaaaaaaaa1','a',-1);
94 substring_index('aaaaaaaaa1','a',-1)
96 select substring_index('aaaaaaaaa1','aa',-1);
97 substring_index('aaaaaaaaa1','aa',-1)
99 select substring_index('aaaaaaaaa1','aa',-2);
100 substring_index('aaaaaaaaa1','aa',-2)
102 select substring_index('aaaaaaaaa1','aa',-3);
103 substring_index('aaaaaaaaa1','aa',-3)
105 select substring_index('aaaaaaaaa1','aa',-4);
106 substring_index('aaaaaaaaa1','aa',-4)
108 select substring_index('aaaaaaaaa1','aa',-5);
109 substring_index('aaaaaaaaa1','aa',-5)
111 select substring_index('aaaaaaaaa1','aaa',-1);
112 substring_index('aaaaaaaaa1','aaa',-1)
114 select substring_index('aaaaaaaaa1','aaa',-2);
115 substring_index('aaaaaaaaa1','aaa',-2)
117 select substring_index('aaaaaaaaa1','aaa',-3);
118 substring_index('aaaaaaaaa1','aaa',-3)
120 select substring_index('aaaaaaaaa1','aaa',-4);
121 substring_index('aaaaaaaaa1','aaa',-4)
123 select substring_index('the king of thethe hill','the',-2);
124 substring_index('the king of thethe hill','the',-2)
126 select substring_index('the king of the the hill','the',-2);
127 substring_index('the king of the the hill','the',-2)
129 select substring_index('the king of the the hill','the',-2);
130 substring_index('the king of the the hill','the',-2)
132 select substring_index('the king of the the hill',' the ',-1);
133 substring_index('the king of the the hill',' the ',-1)
135 select substring_index('the king of the the hill',' the ',-2);
136 substring_index('the king of the the hill',' the ',-2)
138 select substring_index('the king of the the hill',' ',-1);
139 substring_index('the king of the the hill',' ',-1)
141 select substring_index('the king of the the hill',' ',-2);
142 substring_index('the king of the the hill',' ',-2)
144 select substring_index('the king of the the hill',' ',-3);
145 substring_index('the king of the the hill',' ',-3)
147 select substring_index('the king of the the hill',' ',-4);
148 substring_index('the king of the the hill',' ',-4)
150 select substring_index('the king of the the hill',' ',-5);
151 substring_index('the king of the the hill',' ',-5)
153 select substring_index('the king of the.the hill','the',-2);
154 substring_index('the king of the.the hill','the',-2)
156 select substring_index('the king of thethethe.the hill','the',-3);
157 substring_index('the king of thethethe.the hill','the',-3)
159 select substring_index('the king of thethethe.the hill','the',-1);
160 substring_index('the king of thethethe.the hill','the',-1)
162 select substring_index('the king of the the hill','the',1);
163 substring_index('the king of the the hill','the',1)
165 select substring_index('the king of the the hill','the',2);
166 substring_index('the king of the the hill','the',2)
168 select substring_index('the king of the the hill','the',3);
169 substring_index('the king of the the hill','the',3)
171 select concat(':',ltrim(' left '),':',rtrim(' right '),':');
172 concat(':',ltrim(' left '),':',rtrim(' right '),':')
174 select concat(':',trim(leading from ' left '),':',trim(trailing from ' right '),':');
175 concat(':',trim(leading from ' left '),':',trim(trailing from ' right '),':')
177 select concat(':',trim(LEADING FROM ' left'),':',trim(TRAILING FROM ' right '),':');
178 concat(':',trim(LEADING FROM ' left'),':',trim(TRAILING FROM ' right '),':')
180 select concat(':',trim(' m '),':',trim(BOTH FROM ' y '),':',trim('*' FROM '*s*'),':');
181 concat(':',trim(' m '),':',trim(BOTH FROM ' y '),':',trim('*' FROM '*s*'),':')
183 select concat(':',trim(BOTH 'ab' FROM 'ababmyabab'),':',trim(BOTH '*' FROM '***sql'),':');
184 concat(':',trim(BOTH 'ab' FROM 'ababmyabab'),':',trim(BOTH '*' FROM '***sql'),':')
186 select concat(':',trim(LEADING '.*' FROM '.*my'),':',trim(TRAILING '.*' FROM 'sql.*.*'),':');
187 concat(':',trim(LEADING '.*' FROM '.*my'),':',trim(TRAILING '.*' FROM 'sql.*.*'),':')
189 select TRIM("foo" FROM "foo"), TRIM("foo" FROM "foook"), TRIM("foo" FROM "okfoo");
190 TRIM("foo" FROM "foo") TRIM("foo" FROM "foook") TRIM("foo" FROM "okfoo")
192 select concat_ws(', ','monty','was here','again');
193 concat_ws(', ','monty','was here','again')
194 monty, was here, again
195 select concat_ws(NULL,'a'),concat_ws(',',NULL,'');
196 concat_ws(NULL,'a') concat_ws(',',NULL,'')
198 select concat_ws(',','',NULL,'a');
199 concat_ws(',','',NULL,'a')
201 SELECT CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('d',100)), '"');
202 CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('d',100)), '"')
203 "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb";"cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc";"dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd"
204 select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
205 insert('txs',2,1,'hi') insert('is ',4,0,'a') insert('txxxxt',2,4,'es')
207 select replace('aaaa','a','b'),replace('aaaa','aa','b'),replace('aaaa','a','bb'),replace('aaaa','','b'),replace('bbbb','a','c');
208 replace('aaaa','a','b') replace('aaaa','aa','b') replace('aaaa','a','bb') replace('aaaa','','b') replace('bbbb','a','c')
209 bbbb bb bbbbbbbb aaaa bbbb
210 select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ;
211 replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL')
213 select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb');
214 soundex('') soundex('he') soundex('hello all folks') soundex('#3556 in bugdb')
216 select 'mood' sounds like 'mud';
217 'mood' sounds like 'mud'
219 select 'Glazgo' sounds like 'Liverpool';
220 'Glazgo' sounds like 'Liverpool'
222 select null sounds like 'null';
223 null sounds like 'null'
225 select 'null' sounds like null;
226 'null' sounds like null
228 select null sounds like null;
229 null sounds like null
233 5d41402abc4b2a76b9719d911017c592
239 a9993e364706816aba3e25717850c26c9cd0d89d
242 a9993e364706816aba3e25717850c26c9cd0d89d
243 select aes_decrypt(aes_encrypt('abc','1'),'1');
244 aes_decrypt(aes_encrypt('abc','1'),'1')
246 select aes_decrypt(aes_encrypt('abc','1'),1);
247 aes_decrypt(aes_encrypt('abc','1'),1)
249 select aes_encrypt(NULL,"a");
250 aes_encrypt(NULL,"a")
252 select aes_encrypt("a",NULL);
253 aes_encrypt("a",NULL)
255 select aes_decrypt(NULL,"a");
256 aes_decrypt(NULL,"a")
258 select aes_decrypt("a",NULL);
259 aes_decrypt("a",NULL)
261 select aes_decrypt("a","a");
264 select aes_decrypt(aes_encrypt("","a"),"a");
265 aes_decrypt(aes_encrypt("","a"),"a")
267 select repeat('monty',5),concat('*',space(5),'*');
268 repeat('monty',5) concat('*',space(5),'*')
269 montymontymontymontymonty * *
270 select reverse('abc'),reverse('abcd');
271 reverse('abc') reverse('abcd')
273 select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'), rpad(11, 10 , 22), rpad("ab", 10, 22);
274 rpad('a',4,'1') rpad('a',4,'12') rpad('abcd',3,'12') rpad(11, 10 , 22) rpad("ab", 10, 22)
275 a111 a121 abc 1122222222 ab22222222
276 select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12'), lpad(11, 10 , 22);
277 lpad('a',4,'1') lpad('a',4,'12') lpad('abcd',3,'12') lpad(11, 10 , 22)
278 111a 121a abc 2222222211
279 select rpad(741653838,17,'0'),lpad(741653838,17,'0');
280 rpad(741653838,17,'0') lpad(741653838,17,'0')
281 74165383800000000 00000000741653838
282 select rpad('abcd',7,'ab'),lpad('abcd',7,'ab');
283 rpad('abcd',7,'ab') lpad('abcd',7,'ab')
285 select rpad('abcd',1,'ab'),lpad('abcd',1,'ab');
286 rpad('abcd',1,'ab') lpad('abcd',1,'ab')
288 select rpad('STRING', 20, CONCAT('p','a','d') );
289 rpad('STRING', 20, CONCAT('p','a','d') )
291 select lpad('STRING', 20, CONCAT('p','a','d') );
292 lpad('STRING', 20, CONCAT('p','a','d') )
294 select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD');
295 LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD') GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD')
297 select least(1,2,3) | greatest(16,32,8), least(5,4)*1,greatest(-1.0,1.0)*1,least(3,2,1)*1.0,greatest(1,1.1,1.0),least("10",9),greatest("A","B","0");
298 least(1,2,3) | greatest(16,32,8) least(5,4)*1 greatest(-1.0,1.0)*1 least(3,2,1)*1.0 greatest(1,1.1,1.0) least("10",9) greatest("A","B","0")
300 select decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000);
301 decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000)
303 select decode(encode("abcdef","monty"),"monty")="abcdef";
304 decode(encode("abcdef","monty"),"monty")="abcdef"
306 select quote('\'\"\\test');
309 select quote(concat('abc\'', '\\cba'));
310 quote(concat('abc\'', '\\cba'))
312 select quote(1/0), quote('\0\Z');
313 quote(1/0) quote('\0\Z')
315 select length(quote(concat(char(0),"test")));
316 length(quote(concat(char(0),"test")))
318 select hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235))));
319 hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235))))
321 select unhex(hex("foobar")), hex(unhex("1234567890ABCDEF")), unhex("345678"), unhex(NULL);
322 unhex(hex("foobar")) hex(unhex("1234567890ABCDEF")) unhex("345678") unhex(NULL)
323 foobar 1234567890ABCDEF 4Vx NULL
324 select hex(unhex("1")), hex(unhex("12")), hex(unhex("123")), hex(unhex("1234")), hex(unhex("12345")), hex(unhex("123456"));
325 hex(unhex("1")) hex(unhex("12")) hex(unhex("123")) hex(unhex("1234")) hex(unhex("12345")) hex(unhex("123456"))
326 01 12 0123 1234 012345 123456
327 select length(unhex(md5("abrakadabra")));
328 length(unhex(md5("abrakadabra")))
330 select concat('a', quote(NULL));
331 concat('a', quote(NULL))
336 select insert("aa",100,1,"b"),insert("aa",1,3,"b"),left("aa",-1),substring("a",1,2);
337 insert("aa",100,1,"b") insert("aa",1,3,"b") left("aa",-1) substring("a",1,2)
339 select elt(2,1),field(NULL,"a","b","c"),reverse("");
340 elt(2,1) field(NULL,"a","b","c") reverse("")
342 select locate("a","b",2),locate("","a",1);
343 locate("a","b",2) locate("","a",1)
345 select ltrim("a"),rtrim("a"),trim(BOTH "" from "a"),trim(BOTH " " from "a");
346 ltrim("a") rtrim("a") trim(BOTH "" from "a") trim(BOTH " " from "a")
348 select concat("1","2")|0,concat("1",".5")+0.0;
349 concat("1","2")|0 concat("1",".5")+0.0
351 select substring_index("www.tcx.se","",3);
352 substring_index("www.tcx.se","",3)
354 select length(repeat("a",100000000)),length(repeat("a",1000*64));
355 length(repeat("a",100000000)) length(repeat("a",1000*64))
358 Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated
359 select position("0" in "baaa" in (1)),position("0" in "1" in (1,2,3)),position("sql" in ("mysql"));
360 position("0" in "baaa" in (1)) position("0" in "1" in (1,2,3)) position("sql" in ("mysql"))
363 Warning 1292 Truncated incorrect DOUBLE value: 'baaa'
364 select position(("1" in (1,2,3)) in "01");
365 position(("1" in (1,2,3)) in "01")
367 select length(repeat("a",65500)),length(concat(repeat("a",32000),repeat("a",32000))),length(replace("aaaaa","a",concat(repeat("a",10000)))),length(insert(repeat("a",40000),1,30000,repeat("b",50000)));
368 length(repeat("a",65500)) length(concat(repeat("a",32000),repeat("a",32000))) length(replace("aaaaa","a",concat(repeat("a",10000)))) length(insert(repeat("a",40000),1,30000,repeat("b",50000)))
369 65500 64000 50000 60000
370 select length(repeat("a",1000000)),length(concat(repeat("a",32000),repeat("a",32000),repeat("a",32000))),length(replace("aaaaa","a",concat(repeat("a",32000)))),length(insert(repeat("a",48000),1,1000,repeat("a",48000)));
371 length(repeat("a",1000000)) length(concat(repeat("a",32000),repeat("a",32000),repeat("a",32000))) length(replace("aaaaa","a",concat(repeat("a",32000)))) length(insert(repeat("a",48000),1,1000,repeat("a",48000)))
372 1000000 96000 160000 95000
373 create table t1 ( domain char(50) );
374 insert into t1 VALUES ("hello.de" ), ("test.de" );
375 select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@hello.de';
378 select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@test.de';
383 id int(10) unsigned NOT NULL,
384 title varchar(255) default NULL,
385 prio int(10) unsigned default NULL,
386 category int(10) unsigned default NULL,
387 program int(10) unsigned default NULL,
389 created datetime default NULL,
390 modified timestamp NOT NULL,
391 bugstatus int(10) unsigned default NULL,
392 submitter int(10) unsigned default NULL
394 INSERT INTO t1 VALUES (1,'Link',1,1,1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','2001-02-28 08:40:16',20010228084016,0,4);
395 SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter), '"') FROM t1;
396 CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter), '"')
397 "Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4"
398 SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"') FROM t1;
399 CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"')
400 "Link";"1";"1";"1";"0";"4"
401 SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter) FROM t1;
402 CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter)
403 Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4
404 SELECT bugdesc, REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb') from t1 group by bugdesc;
405 bugdesc REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb')
406 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
408 CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) ENGINE=MyISAM;
409 INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf');
410 SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password");
414 wid int(10) unsigned NOT NULL auto_increment,
415 data_podp date default NULL,
416 status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy',
419 INSERT INTO t1 VALUES (8,NULL,'real');
420 INSERT INTO t1 VALUES (9,NULL,'nowy');
421 SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid;
422 elt(status_wnio,data_podp)
426 CREATE TABLE t1 (title text) ENGINE=MyISAM;
427 INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education');
428 INSERT INTO t1 VALUES ('House passes the CAREERS bill');
429 SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1;
430 CONCAT("</a>",RPAD("",(55 - LENGTH(title)),"."))
432 </a>..........................
434 CREATE TABLE t1 (i int, j int);
435 INSERT INTO t1 VALUES (1,1),(2,2);
436 SELECT DISTINCT i, ELT(j, '345', '34') FROM t1;
437 i ELT(j, '345', '34')
441 create table t1(a char(4));
442 insert into t1 values ('one'),(NULL),('two'),('four');
443 select a, quote(a), isnull(quote(a)), quote(a) is null, ifnull(quote(a), 'n') from t1;
444 a quote(a) isnull(quote(a)) quote(a) is null ifnull(quote(a), 'n')
448 four 'four' 0 0 'four'
450 select trim(trailing 'foo' from 'foo');
451 trim(trailing 'foo' from 'foo')
453 select trim(leading 'foo' from 'foo');
454 trim(leading 'foo' from 'foo')
456 select quote(ltrim(concat(' ', 'a')));
457 quote(ltrim(concat(' ', 'a')))
459 select quote(trim(concat(' ', 'a')));
460 quote(trim(concat(' ', 'a')))
462 CREATE TABLE t1 SELECT 1 UNION SELECT 2 UNION SELECT 3;
463 SELECT QUOTE('A') FROM t1;
481 select _latin1'1'=_latin2'1';
482 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '='
483 select row('a','b','c') = row('a','b','c');
484 row('a','b','c') = row('a','b','c')
486 select row('A','b','c') = row('a','b','c');
487 row('A','b','c') = row('a','b','c')
489 select row('A' COLLATE latin1_bin,'b','c') = row('a','b','c');
490 row('A' COLLATE latin1_bin,'b','c') = row('a','b','c')
492 select row('A','b','c') = row('a' COLLATE latin1_bin,'b','c');
493 row('A','b','c') = row('a' COLLATE latin1_bin,'b','c')
495 select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c');
496 ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation '='
497 select concat(_latin1'a',_latin2'a');
498 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat'
499 select concat(_latin1'a',_latin2'a',_latin5'a');
500 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin5_turkish_ci,COERCIBLE) for operation 'concat'
501 select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a');
502 ERROR HY000: Illegal mix of collations for operation 'concat'
503 select concat_ws(_latin1'a',_latin2'a');
504 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat_ws'
505 select FIELD('b','A','B');
508 select FIELD('B','A','B');
511 select FIELD('b' COLLATE latin1_bin,'A','B');
512 FIELD('b' COLLATE latin1_bin,'A','B')
514 select FIELD('b','A' COLLATE latin1_bin,'B');
515 FIELD('b','A' COLLATE latin1_bin,'B')
517 select FIELD(_latin2'b','A','B');
518 ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field'
519 select FIELD('b',_latin2'A','B');
520 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field'
521 select FIELD('1',_latin2'3','2',1);
522 FIELD('1',_latin2'3','2',1)
524 select POSITION(_latin1'B' IN _latin1'abcd');
525 POSITION(_latin1'B' IN _latin1'abcd')
527 select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin);
528 POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin)
530 select POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd');
531 POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd')
533 select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin);
534 ERROR HY000: Illegal mix of collations (latin1_bin,EXPLICIT) and (latin1_general_ci,EXPLICIT) for operation 'locate'
535 select POSITION(_latin1'B' IN _latin2'abcd');
536 ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation 'locate'
537 select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d');
538 FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')
540 select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin);
541 ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'find_in_set'
542 select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d');
543 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'find_in_set'
544 select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2);
545 SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2)
547 select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2);
548 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'substring_index'
549 select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2);
550 ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'substring_index'
551 select _latin1'B' between _latin1'a' and _latin1'c';
552 _latin1'B' between _latin1'a' and _latin1'c'
554 select _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c';
555 _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c'
557 select _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c';
558 _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c'
560 select _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin;
561 _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin
563 select _latin2'B' between _latin1'a' and _latin1'b';
564 ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between'
565 select _latin1'B' between _latin2'a' and _latin1'b';
566 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between'
567 select _latin1'B' between _latin1'a' and _latin2'b';
568 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation 'between'
569 select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b';
570 ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation 'between'
571 select _latin1'B' in (_latin1'a',_latin1'b');
572 _latin1'B' in (_latin1'a',_latin1'b')
574 select _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b');
575 _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b')
577 select _latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b');
578 _latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b')
580 select _latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin);
581 _latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin)
583 select _latin2'B' in (_latin1'a',_latin1'b');
584 ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN '
585 select _latin1'B' in (_latin2'a',_latin1'b');
586 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN '
587 select _latin1'B' in (_latin1'a',_latin2'b');
588 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation ' IN '
589 select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b');
590 ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation ' IN '
591 select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin);
592 ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_swedish_ci,COERCIBLE), (latin1_bin,EXPLICIT) for operation ' IN '
593 select collation(bin(130)), coercibility(bin(130));
594 collation(bin(130)) coercibility(bin(130))
596 select collation(oct(130)), coercibility(oct(130));
597 collation(oct(130)) coercibility(oct(130))
599 select collation(conv(130,16,10)), coercibility(conv(130,16,10));
600 collation(conv(130,16,10)) coercibility(conv(130,16,10))
602 select collation(hex(130)), coercibility(hex(130));
603 collation(hex(130)) coercibility(hex(130))
605 select collation(char(130)), coercibility(hex(130));
606 collation(char(130)) coercibility(hex(130))
608 select collation(format(130,10)), coercibility(format(130,10));
609 collation(format(130,10)) coercibility(format(130,10))
611 select collation(lcase(_latin2'a')), coercibility(lcase(_latin2'a'));
612 collation(lcase(_latin2'a')) coercibility(lcase(_latin2'a'))
614 select collation(ucase(_latin2'a')), coercibility(ucase(_latin2'a'));
615 collation(ucase(_latin2'a')) coercibility(ucase(_latin2'a'))
617 select collation(left(_latin2'a',1)), coercibility(left(_latin2'a',1));
618 collation(left(_latin2'a',1)) coercibility(left(_latin2'a',1))
620 select collation(right(_latin2'a',1)), coercibility(right(_latin2'a',1));
621 collation(right(_latin2'a',1)) coercibility(right(_latin2'a',1))
623 select collation(substring(_latin2'a',1,1)), coercibility(substring(_latin2'a',1,1));
624 collation(substring(_latin2'a',1,1)) coercibility(substring(_latin2'a',1,1))
626 select collation(concat(_latin2'a',_latin2'b')), coercibility(concat(_latin2'a',_latin2'b'));
627 collation(concat(_latin2'a',_latin2'b')) coercibility(concat(_latin2'a',_latin2'b'))
629 select collation(lpad(_latin2'a',4,_latin2'b')), coercibility(lpad(_latin2'a',4,_latin2'b'));
630 collation(lpad(_latin2'a',4,_latin2'b')) coercibility(lpad(_latin2'a',4,_latin2'b'))
632 select collation(rpad(_latin2'a',4,_latin2'b')), coercibility(rpad(_latin2'a',4,_latin2'b'));
633 collation(rpad(_latin2'a',4,_latin2'b')) coercibility(rpad(_latin2'a',4,_latin2'b'))
635 select collation(concat_ws(_latin2'a',_latin2'b')), coercibility(concat_ws(_latin2'a',_latin2'b'));
636 collation(concat_ws(_latin2'a',_latin2'b')) coercibility(concat_ws(_latin2'a',_latin2'b'))
638 select collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')), coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c'));
639 collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')) coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c'))
641 select collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')), coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' '));
642 collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')) coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' '))
644 select collation(trim(_latin2' a ')), coercibility(trim(_latin2' a '));
645 collation(trim(_latin2' a ')) coercibility(trim(_latin2' a '))
647 select collation(ltrim(_latin2' a ')), coercibility(ltrim(_latin2' a '));
648 collation(ltrim(_latin2' a ')) coercibility(ltrim(_latin2' a '))
650 select collation(rtrim(_latin2' a ')), coercibility(rtrim(_latin2' a '));
651 collation(rtrim(_latin2' a ')) coercibility(rtrim(_latin2' a '))
653 select collation(trim(LEADING _latin2' ' FROM _latin2'a')), coercibility(trim(LEADING _latin2'a' FROM _latin2'a'));
654 collation(trim(LEADING _latin2' ' FROM _latin2'a')) coercibility(trim(LEADING _latin2'a' FROM _latin2'a'))
656 select collation(trim(TRAILING _latin2' ' FROM _latin2'a')), coercibility(trim(TRAILING _latin2'a' FROM _latin2'a'));
657 collation(trim(TRAILING _latin2' ' FROM _latin2'a')) coercibility(trim(TRAILING _latin2'a' FROM _latin2'a'))
659 select collation(trim(BOTH _latin2' ' FROM _latin2'a')), coercibility(trim(BOTH _latin2'a' FROM _latin2'a'));
660 collation(trim(BOTH _latin2' ' FROM _latin2'a')) coercibility(trim(BOTH _latin2'a' FROM _latin2'a'))
662 select collation(repeat(_latin2'a',10)), coercibility(repeat(_latin2'a',10));
663 collation(repeat(_latin2'a',10)) coercibility(repeat(_latin2'a',10))
665 select collation(reverse(_latin2'ab')), coercibility(reverse(_latin2'ab'));
666 collation(reverse(_latin2'ab')) coercibility(reverse(_latin2'ab'))
668 select collation(quote(_latin2'ab')), coercibility(quote(_latin2'ab'));
669 collation(quote(_latin2'ab')) coercibility(quote(_latin2'ab'))
671 select collation(soundex(_latin2'ab')), coercibility(soundex(_latin2'ab'));
672 collation(soundex(_latin2'ab')) coercibility(soundex(_latin2'ab'))
674 select collation(substring(_latin2'ab',1)), coercibility(substring(_latin2'ab',1));
675 collation(substring(_latin2'ab',1)) coercibility(substring(_latin2'ab',1))
677 select collation(insert(_latin2'abcd',2,3,_latin2'ef')), coercibility(insert(_latin2'abcd',2,3,_latin2'ef'));
678 collation(insert(_latin2'abcd',2,3,_latin2'ef')) coercibility(insert(_latin2'abcd',2,3,_latin2'ef'))
680 select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B'));
681 collation(replace(_latin2'abcd',_latin2'b',_latin2'B')) coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B'))
683 select collation(encode('abcd','ab')), coercibility(encode('abcd','ab'));
684 collation(encode('abcd','ab')) coercibility(encode('abcd','ab'))
698 substring(_latin2'a',1,1),
699 concat(_latin2'a',_latin2'b'),
700 lpad(_latin2'a',4,_latin2'b'),
701 rpad(_latin2'a',4,_latin2'b'),
702 concat_ws(_latin2'a',_latin2'b'),
703 make_set(255,_latin2'a',_latin2'b',_latin2'c'),
704 export_set(255,_latin2'y',_latin2'n',_latin2' '),
708 trim(LEADING _latin2' ' FROM _latin2' a '),
709 trim(TRAILING _latin2' ' FROM _latin2' a '),
710 trim(BOTH _latin2' ' FROM _latin2' a '),
711 repeat(_latin2'a',10),
712 reverse(_latin2'ab'),
714 soundex(_latin2'ab'),
715 substring(_latin2'ab',1),
716 insert(_latin2'abcd',2,3,_latin2'ef'),
717 replace(_latin2'abcd',_latin2'b',_latin2'B'),
720 show create table t1;
722 t1 CREATE TABLE `t1` (
723 `bin(130)` varchar(64) DEFAULT NULL,
724 `oct(130)` varchar(64) DEFAULT NULL,
725 `conv(130,16,10)` varchar(64) DEFAULT NULL,
726 `hex(130)` varchar(6) NOT NULL DEFAULT '',
727 `char(130)` varbinary(4) NOT NULL DEFAULT '',
728 `format(130,10)` varchar(37) NOT NULL DEFAULT '',
729 `left(_latin2'a',1)` varchar(1) CHARACTER SET latin2 NOT NULL DEFAULT '',
730 `right(_latin2'a',1)` varchar(1) CHARACTER SET latin2 NOT NULL DEFAULT '',
731 `lcase(_latin2'a')` varchar(1) CHARACTER SET latin2 NOT NULL DEFAULT '',
732 `ucase(_latin2'a')` varchar(1) CHARACTER SET latin2 NOT NULL DEFAULT '',
733 `substring(_latin2'a',1,1)` varchar(1) CHARACTER SET latin2 NOT NULL DEFAULT '',
734 `concat(_latin2'a',_latin2'b')` varchar(2) CHARACTER SET latin2 NOT NULL DEFAULT '',
735 `lpad(_latin2'a',4,_latin2'b')` varchar(4) CHARACTER SET latin2 NOT NULL DEFAULT '',
736 `rpad(_latin2'a',4,_latin2'b')` varchar(4) CHARACTER SET latin2 NOT NULL DEFAULT '',
737 `concat_ws(_latin2'a',_latin2'b')` varchar(1) CHARACTER SET latin2 NOT NULL DEFAULT '',
738 `make_set(255,_latin2'a',_latin2'b',_latin2'c')` varchar(5) CHARACTER SET latin2 NOT NULL DEFAULT '',
739 `export_set(255,_latin2'y',_latin2'n',_latin2' ')` varchar(127) CHARACTER SET latin2 NOT NULL DEFAULT '',
740 `trim(_latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL DEFAULT '',
741 `ltrim(_latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL DEFAULT '',
742 `rtrim(_latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL DEFAULT '',
743 `trim(LEADING _latin2' ' FROM _latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL DEFAULT '',
744 `trim(TRAILING _latin2' ' FROM _latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL DEFAULT '',
745 `trim(BOTH _latin2' ' FROM _latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL DEFAULT '',
746 `repeat(_latin2'a',10)` varchar(10) CHARACTER SET latin2 NOT NULL DEFAULT '',
747 `reverse(_latin2'ab')` varchar(2) CHARACTER SET latin2 NOT NULL DEFAULT '',
748 `quote(_latin2'ab')` varchar(6) CHARACTER SET latin2 NOT NULL DEFAULT '',
749 `soundex(_latin2'ab')` varchar(4) CHARACTER SET latin2 NOT NULL DEFAULT '',
750 `substring(_latin2'ab',1)` varchar(2) CHARACTER SET latin2 NOT NULL DEFAULT '',
751 `insert(_latin2'abcd',2,3,_latin2'ef')` varchar(6) CHARACTER SET latin2 NOT NULL DEFAULT '',
752 `replace(_latin2'abcd',_latin2'b',_latin2'B')` varchar(4) CHARACTER SET latin2 NOT NULL DEFAULT '',
753 `encode('abcd','ab')` varbinary(4) NOT NULL DEFAULT ''
754 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
756 create table t1 (a char character set latin2);
757 insert into t1 values (null);
758 select charset(a), collation(a), coercibility(a) from t1;
759 charset(a) collation(a) coercibility(a)
760 latin2 latin2_general_ci 2
762 select charset(null), collation(null), coercibility(null);
763 charset(null) collation(null) coercibility(null)
765 CREATE TABLE t1 (a int, b int);
766 CREATE TABLE t2 (a int, b int);
767 INSERT INTO t1 VALUES (1,1),(2,2);
768 INSERT INTO t2 VALUES (2,2),(3,3);
769 select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b)
770 where collation(t2.a) = _utf8'binary' order by t1.a,t2.a;
774 select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b)
775 where charset(t2.a) = _utf8'binary' order by t1.a,t2.a;
779 select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b)
780 where coercibility(t2.a) = 2 order by t1.a,t2.a;
785 select SUBSTR('abcdefg',3,2);
786 SUBSTR('abcdefg',3,2)
788 select SUBSTRING('abcdefg',3,2);
789 SUBSTRING('abcdefg',3,2)
791 select SUBSTR('abcdefg',-3,2) FROM DUAL;
792 SUBSTR('abcdefg',-3,2)
794 select SUBSTR('abcdefg',-1,5) FROM DUAL;
795 SUBSTR('abcdefg',-1,5)
797 select SUBSTR('abcdefg',0,0) FROM DUAL;
798 SUBSTR('abcdefg',0,0)
800 select SUBSTR('abcdefg',-1,-1) FROM DUAL;
801 SUBSTR('abcdefg',-1,-1)
803 select SUBSTR('abcdefg',1,-1) FROM DUAL;
804 SUBSTR('abcdefg',1,-1)
806 create table t7 (s1 char);
808 where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA';
809 ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'concat'
811 select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2);
812 substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2) substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2)
813 1abcd;2abcd 3abcd;4abcd
814 explain extended select md5('hello');
815 id select_type table type possible_keys key key_len ref rows filtered Extra
816 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
818 Note 1003 select md5('hello') AS `md5('hello')`
819 explain extended select sha('abc');
820 id select_type table type possible_keys key key_len ref rows filtered Extra
821 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
823 Note 1003 select sha('abc') AS `sha('abc')`
824 explain extended select sha1('abc');
825 id select_type table type possible_keys key key_len ref rows filtered Extra
826 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
828 Note 1003 select sha('abc') AS `sha1('abc')`
829 explain extended select soundex('');
830 id select_type table type possible_keys key key_len ref rows filtered Extra
831 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
833 Note 1003 select soundex('') AS `soundex('')`
834 explain extended select 'mood' sounds like 'mud';
835 id select_type table type possible_keys key key_len ref rows filtered Extra
836 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
838 Note 1003 select (soundex('mood') = soundex('mud')) AS `'mood' sounds like 'mud'`
839 explain extended select aes_decrypt(aes_encrypt('abc','1'),'1');
840 id select_type table type possible_keys key key_len ref rows filtered Extra
841 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
843 Note 1003 select aes_decrypt(aes_encrypt('abc','1'),'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`
844 explain extended select concat('*',space(5),'*');
845 id select_type table type possible_keys key key_len ref rows filtered Extra
846 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
848 Note 1003 select concat('*',repeat(' ',5),'*') AS `concat('*',space(5),'*')`
849 explain extended select reverse('abc');
850 id select_type table type possible_keys key key_len ref rows filtered Extra
851 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
853 Note 1003 select reverse('abc') AS `reverse('abc')`
854 explain extended select rpad('a',4,'1');
855 id select_type table type possible_keys key key_len ref rows filtered Extra
856 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
858 Note 1003 select rpad('a',4,'1') AS `rpad('a',4,'1')`
859 explain extended select lpad('a',4,'1');
860 id select_type table type possible_keys key key_len ref rows filtered Extra
861 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
863 Note 1003 select lpad('a',4,'1') AS `lpad('a',4,'1')`
864 explain extended select concat_ws(',','',NULL,'a');
865 id select_type table type possible_keys key key_len ref rows filtered Extra
866 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
868 Note 1003 select concat_ws(',','',NULL,'a') AS `concat_ws(',','',NULL,'a')`
869 explain extended select make_set(255,_latin2'a', _latin2'b', _latin2'c');
870 id select_type table type possible_keys key key_len ref rows filtered Extra
871 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
873 Note 1003 select make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a', _latin2'b', _latin2'c')`
874 explain extended select elt(2,1);
875 id select_type table type possible_keys key key_len ref rows filtered Extra
876 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
878 Note 1003 select elt(2,1) AS `elt(2,1)`
879 explain extended select locate("a","b",2);
880 id select_type table type possible_keys key key_len ref rows filtered Extra
881 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
883 Note 1003 select locate('a','b',2) AS `locate("a","b",2)`
884 explain extended select format(130,10);
885 id select_type table type possible_keys key key_len ref rows filtered Extra
886 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
888 Note 1003 select format(130,10) AS `format(130,10)`
889 explain extended select char(0);
890 id select_type table type possible_keys key key_len ref rows filtered Extra
891 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
893 Note 1003 select char(0) AS `char(0)`
894 explain extended select conv(130,16,10);
895 id select_type table type possible_keys key key_len ref rows filtered Extra
896 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
898 Note 1003 select conv(130,16,10) AS `conv(130,16,10)`
899 explain extended select hex(130);
900 id select_type table type possible_keys key key_len ref rows filtered Extra
901 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
903 Note 1003 select hex(130) AS `hex(130)`
904 explain extended select binary 'HE';
905 id select_type table type possible_keys key key_len ref rows filtered Extra
906 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
908 Note 1003 select cast('HE' as char charset binary) AS `binary 'HE'`
909 explain extended select export_set(255,_latin2'y', _latin2'n', _latin2' ');
910 id select_type table type possible_keys key key_len ref rows filtered Extra
911 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
913 Note 1003 select export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y', _latin2'n', _latin2' ')`
914 explain extended select FIELD('b' COLLATE latin1_bin,'A','B');
915 id select_type table type possible_keys key key_len ref rows filtered Extra
916 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
918 Note 1003 select field(('b' collate latin1_bin),'A','B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`
919 explain extended select FIND_IN_SET(_latin1'B', _latin1'a,b,c,d');
920 id select_type table type possible_keys key key_len ref rows filtered Extra
921 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
923 Note 1003 select find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B', _latin1'a,b,c,d')`
924 explain extended select collation(conv(130,16,10));
925 id select_type table type possible_keys key key_len ref rows filtered Extra
926 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
928 Note 1003 select collation(conv(130,16,10)) AS `collation(conv(130,16,10))`
929 explain extended select coercibility(conv(130,16,10));
930 id select_type table type possible_keys key key_len ref rows filtered Extra
931 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
933 Note 1003 select coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`
934 explain extended select length('\n\t\r\b\0\_\%\\');
935 id select_type table type possible_keys key key_len ref rows filtered Extra
936 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
938 Note 1003 select length('\n \r
\b\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`
939 explain extended select bit_length('\n\t\r\b\0\_\%\\');
940 id select_type table type possible_keys key key_len ref rows filtered Extra
941 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
943 Note 1003 select bit_length('\n \r
\b\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`
944 explain extended select bit_length('\n\t\r\b\0\_\%\\');
945 id select_type table type possible_keys key key_len ref rows filtered Extra
946 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
948 Note 1003 select bit_length('\n \r
\b\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`
949 explain extended select concat('monty',' was here ','again');
950 id select_type table type possible_keys key key_len ref rows filtered Extra
951 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
953 Note 1003 select concat('monty',' was here ','again') AS `concat('monty',' was here ','again')`
954 explain extended select length('hello');
955 id select_type table type possible_keys key key_len ref rows filtered Extra
956 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
958 Note 1003 select length('hello') AS `length('hello')`
959 explain extended select char(ascii('h'));
960 id select_type table type possible_keys key key_len ref rows filtered Extra
961 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
963 Note 1003 select char(ascii('h')) AS `char(ascii('h'))`
964 explain extended select ord('h');
965 id select_type table type possible_keys key key_len ref rows filtered Extra
966 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
968 Note 1003 select ord('h') AS `ord('h')`
969 explain extended select quote(1/0);
970 id select_type table type possible_keys key key_len ref rows filtered Extra
971 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
973 Note 1003 select quote((1 / 0)) AS `quote(1/0)`
974 explain extended select crc32("123");
975 id select_type table type possible_keys key key_len ref rows filtered Extra
976 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
978 Note 1003 select crc32('123') AS `crc32("123")`
979 explain extended select replace('aaaa','a','b');
980 id select_type table type possible_keys key key_len ref rows filtered Extra
981 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
983 Note 1003 select replace('aaaa','a','b') AS `replace('aaaa','a','b')`
984 explain extended select insert('txs',2,1,'hi');
985 id select_type table type possible_keys key key_len ref rows filtered Extra
986 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
988 Note 1003 select insert('txs',2,1,'hi') AS `insert('txs',2,1,'hi')`
989 explain extended select left(_latin2'a',1);
990 id select_type table type possible_keys key key_len ref rows filtered Extra
991 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
993 Note 1003 select left(_latin2'a',1) AS `left(_latin2'a',1)`
994 explain extended select right(_latin2'a',1);
995 id select_type table type possible_keys key key_len ref rows filtered Extra
996 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
998 Note 1003 select right(_latin2'a',1) AS `right(_latin2'a',1)`
999 explain extended select lcase(_latin2'a');
1000 id select_type table type possible_keys key key_len ref rows filtered Extra
1001 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1003 Note 1003 select lcase(_latin2'a') AS `lcase(_latin2'a')`
1004 explain extended select ucase(_latin2'a');
1005 id select_type table type possible_keys key key_len ref rows filtered Extra
1006 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1008 Note 1003 select ucase(_latin2'a') AS `ucase(_latin2'a')`
1009 explain extended select SUBSTR('abcdefg',3,2);
1010 id select_type table type possible_keys key key_len ref rows filtered Extra
1011 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1013 Note 1003 select substr('abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`
1014 explain extended select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2);
1015 id select_type table type possible_keys key key_len ref rows filtered Extra
1016 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1018 Note 1003 select substring_index('1abcd;2abcd;3abcd;4abcd',';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`
1019 explain extended select trim(_latin2' a ');
1020 id select_type table type possible_keys key key_len ref rows filtered Extra
1021 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1023 Note 1003 select trim(_latin2' a ') AS `trim(_latin2' a ')`
1024 explain extended select ltrim(_latin2' a ');
1025 id select_type table type possible_keys key key_len ref rows filtered Extra
1026 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1028 Note 1003 select ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`
1029 explain extended select rtrim(_latin2' a ');
1030 id select_type table type possible_keys key key_len ref rows filtered Extra
1031 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1033 Note 1003 select rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`
1034 explain extended select decode(encode(repeat("a",100000),"monty"),"monty");
1035 id select_type table type possible_keys key key_len ref rows filtered Extra
1036 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1038 Note 1003 select decode(encode(repeat('a',100000),'monty'),'monty') AS `decode(encode(repeat("a",100000),"monty"),"monty")`
1039 SELECT lpad(12345, 5, "#");
1042 SELECT conv(71, 10, 36), conv('1Z', 36, 10);
1043 conv(71, 10, 36) conv('1Z', 36, 10)
1045 SELECT conv(71, 10, 37), conv('1Z', 37, 10), conv(0,1,10),conv(0,0,10), conv(0,-1,10);
1046 conv(71, 10, 37) conv('1Z', 37, 10) conv(0,1,10) conv(0,0,10) conv(0,-1,10)
1047 NULL NULL NULL NULL NULL
1048 create table t1 (id int(1), str varchar(10)) DEFAULT CHARSET=utf8;
1049 insert into t1 values (1,'aaaaaaaaaa'), (2,'bbbbbbbbbb');
1050 create table t2 (id int(1), str varchar(10)) DEFAULT CHARSET=utf8;
1051 insert into t2 values (1,'cccccccccc'), (2,'dddddddddd');
1052 select substring(concat(t1.str, t2.str), 1, 15) "name" from t1, t2
1053 where t2.id=t1.id order by name;
1058 create table t1 (c1 INT, c2 INT UNSIGNED);
1059 insert into t1 values ('21474836461','21474836461');
1061 Warning 1264 Out of range value for column 'c1' at row 1
1062 Warning 1264 Out of range value for column 'c2' at row 1
1063 insert into t1 values ('-21474836461','-21474836461');
1065 Warning 1264 Out of range value for column 'c1' at row 1
1066 Warning 1264 Out of range value for column 'c2' at row 1
1069 Warning 1264 Out of range value for column 'c1' at row 1
1070 Warning 1264 Out of range value for column 'c2' at row 1
1073 2147483647 4294967295
1076 select left(1234, 3) + 0;
1079 create table t1 (a int not null primary key, b varchar(40), c datetime);
1080 insert into t1 (a,b,c) values (1,'Tom','2004-12-10 12:13:14'),(2,'ball games','2004-12-10 12:13:14'), (3,'Basil','2004-12-10 12:13:14'), (4,'Dean','2004-12-10 12:13:14'),(5,'Ellis','2004-12-10 12:13:14'), (6,'Serg','2004-12-10 12:13:14'), (7,'Sergei','2004-12-10 12:13:14'),(8,'Georg','2004-12-10 12:13:14'),(9,'Salle','2004-12-10 12:13:14'),(10,'Sinisa','2004-12-10 12:13:14');
1081 select count(*) as total, left(c,10) as reg from t1 group by reg order by reg desc limit 0,12;
1085 select trim(null from 'kate') as "must_be_null";
1088 select trim('xyz' from null) as "must_be_null";
1091 select trim(leading NULL from 'kate') as "must_be_null";
1094 select trim(trailing NULL from 'xyz') as "must_be_null";
1098 id int(11) NOT NULL auto_increment,
1099 a bigint(20) unsigned default NULL,
1102 INSERT INTO t1 VALUES
1103 ('0','16307858876001849059');
1104 SELECT CONV('e251273eb74a8ee3', 16, 10);
1105 CONV('e251273eb74a8ee3', 16, 10)
1106 16307858876001849059
1110 WHERE a = 16307858876001849059;
1111 id select_type table type possible_keys key key_len ref rows Extra
1112 1 SIMPLE t1 system NULL NULL NULL NULL 1
1116 WHERE a = CONV('e251273eb74a8ee3', 16, 10);
1117 id select_type table type possible_keys key key_len ref rows Extra
1118 1 SIMPLE t1 system NULL NULL NULL NULL 1
1120 SELECT CHAR(NULL,121,83,81,'76') as my_column;
1123 SELECT CHAR_LENGTH(CHAR(NULL,121,83,81,'76')) as my_column;
1126 CREATE TABLE t1 (id int PRIMARY KEY, str char(255) NOT NULL);
1127 CREATE TABLE t2 (id int NOT NULL UNIQUE);
1128 INSERT INTO t2 VALUES (1),(2);
1129 INSERT INTO t1 VALUES (1, aes_encrypt('foo', 'bar'));
1130 INSERT INTO t1 VALUES (2, 'not valid');
1131 SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id;
1132 id aes_decrypt(str, 'bar')
1135 SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id
1137 id aes_decrypt(str, 'bar')
1141 select field(0,NULL,1,0), field("",NULL,"bar",""), field(0.0,NULL,1.0,0.0);
1142 field(0,NULL,1,0) field("",NULL,"bar","") field(0.0,NULL,1.0,0.0)
1144 select field(NULL,1,2,NULL), field(NULL,1,2,0);
1145 field(NULL,1,2,NULL) field(NULL,1,2,0)
1147 CREATE TABLE t1 (str varchar(20) PRIMARY KEY);
1148 CREATE TABLE t2 (num int primary key);
1149 INSERT INTO t1 VALUES ('notnumber');
1150 INSERT INTO t2 VALUES (0), (1);
1151 SELECT * FROM t1, t2 WHERE num=str;
1154 SELECT * FROM t1, t2 WHERE num=substring(str from 1 for 6);
1159 id int(11) NOT NULL auto_increment,
1160 pc int(11) NOT NULL default '0',
1161 title varchar(20) default NULL,
1164 INSERT INTO t1 VALUES
1168 SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1
1169 FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id
1170 LEFT JOIN t1 AS t3 ON t2.pc=t3.id;
1175 SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1
1176 FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id
1177 LEFT JOIN t1 AS t3 ON t2.pc=t3.id
1178 WHERE CONCAT_WS('->', t3.title, t2.title, t1.title) LIKE '%Toys%';
1183 trackid int(10) unsigned NOT NULL auto_increment,
1184 trackname varchar(100) NOT NULL default '',
1185 PRIMARY KEY (trackid)
1188 artistid int(10) unsigned NOT NULL auto_increment,
1189 artistname varchar(100) NOT NULL default '',
1190 PRIMARY KEY (artistid)
1193 trackid int(10) unsigned NOT NULL,
1194 artistid int(10) unsigned NOT NULL,
1195 PRIMARY KEY (trackid,artistid)
1197 INSERT INTO t1 VALUES (1, 'April In Paris'), (2, 'Autumn In New York');
1198 INSERT INTO t2 VALUES (1, 'Vernon Duke');
1199 INSERT INTO t3 VALUES (1,1);
1200 SELECT CONCAT_WS(' ', trackname, artistname) trackname, artistname
1201 FROM t1 LEFT JOIN t3 ON t1.trackid=t3.trackid
1202 LEFT JOIN t2 ON t2.artistid=t3.artistid
1203 WHERE CONCAT_WS(' ', trackname, artistname) LIKE '%In%';
1204 trackname artistname
1205 April In Paris Vernon Duke Vernon Duke
1206 Autumn In New York NULL
1207 DROP TABLE t1,t2,t3;
1208 create table t1 (b varchar(5));
1209 insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde');
1210 select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1;
1211 b substring(b,1) substring(b,-1) substring(b,-2) substring(b,-3) substring(b,-4) substring(b,-5)
1214 abcd abcd d cd bcd abcd
1215 abcde abcde e de cde bcde abcde
1216 select * from (select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1) t;
1217 b substring(b,1) substring(b,-1) substring(b,-2) substring(b,-3) substring(b,-4) substring(b,-5)
1220 abcd abcd d cd bcd abcd
1221 abcde abcde e de cde bcde abcde
1223 select hex(29223372036854775809), hex(-29223372036854775809);
1224 hex(29223372036854775809) hex(-29223372036854775809)
1225 FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
1226 create table t1 (i int);
1227 insert into t1 values (1000000000),(1);
1228 select lpad(i, 7, ' ') as t from t1;
1229 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1230 def t 253 7 7 Y 128 31 63
1234 select rpad(i, 7, ' ') as t from t1;
1235 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1236 def t 253 7 7 Y 128 31 63
1241 select load_file("lkjlkj");
1244 select ifnull(load_file("lkjlkj"),"it's null");
1245 ifnull(load_file("lkjlkj"),"it's null")
1247 create table t1 (f1 varchar(4), f2 varchar(64), unique key k1 (f1,f2));
1248 insert into t1 values ( 'test',md5('test')), ('test', sha('test'));
1249 select * from t1 where f1='test' and (f2= md5("test") or f2= md5("TEST"));
1251 test 098f6bcd4621d373cade4e832627b4f6
1252 select * from t1 where f1='test' and (f2= md5("TEST") or f2= md5("test"));
1254 test 098f6bcd4621d373cade4e832627b4f6
1255 select * from t1 where f1='test' and (f2= sha("test") or f2= sha("TEST"));
1257 test a94a8fe5ccb19ba61c4c0873d391e987982fbbd3
1258 select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test"));
1260 test a94a8fe5ccb19ba61c4c0873d391e987982fbbd3
1262 CREATE TABLE t1 (a varchar(10));
1263 INSERT INTO t1 VALUES ('abc'), ('xyz');
1264 SELECT a, CONCAT(a,' ',a) AS c FROM t1
1265 HAVING LEFT(c,LENGTH(c)-INSTR(REVERSE(c)," ")) = a;
1269 SELECT a, CONCAT(a,' ',a) AS c FROM t1
1270 HAVING LEFT(CONCAT(a,' ',a),
1271 LENGTH(CONCAT(a,' ',a))-
1272 INSTR(REVERSE(CONCAT(a,' ',a))," ")) = a;
1277 CREATE TABLE t1 (s varchar(10));
1278 INSERT INTO t1 VALUES ('yadda'), ('yaddy');
1279 EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(s) > 'ab';
1280 id select_type table type possible_keys key key_len ref rows filtered Extra
1281 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
1283 Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(`test`.`t1`.`s`) > 'ab')
1284 EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM('y' FROM s) > 'ab';
1285 id select_type table type possible_keys key key_len ref rows filtered Extra
1286 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
1288 Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both 'y' from `test`.`t1`.`s`) > 'ab')
1289 EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(LEADING 'y' FROM s) > 'ab';
1290 id select_type table type possible_keys key key_len ref rows filtered Extra
1291 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
1293 Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(leading 'y' from `test`.`t1`.`s`) > 'ab')
1294 EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(TRAILING 'y' FROM s) > 'ab';
1295 id select_type table type possible_keys key key_len ref rows filtered Extra
1296 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
1298 Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(trailing 'y' from `test`.`t1`.`s`) > 'ab')
1299 EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(BOTH 'y' FROM s) > 'ab';
1300 id select_type table type possible_keys key key_len ref rows filtered Extra
1301 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
1303 Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both 'y' from `test`.`t1`.`s`) > 'ab')
1305 create table t1(f1 varchar(4));
1306 explain extended select encode(f1,'zxcv') as 'enc' from t1;
1307 id select_type table type possible_keys key key_len ref rows filtered Extra
1308 1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found
1310 Note 1003 select encode(NULL,'zxcv') AS `enc` from `test`.`t1`
1311 explain extended select decode(f1,'zxcv') as 'enc' from t1;
1312 id select_type table type possible_keys key key_len ref rows filtered Extra
1313 1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found
1315 Note 1003 select decode(NULL,'zxcv') AS `enc` from `test`.`t1`
1317 create table t1 (a bigint not null)engine=myisam;
1318 insert into t1 set a = 1024*1024*1024*4;
1319 delete from t1 order by (inet_ntoa(a)) desc limit 10;
1321 create table t1 (a char(36) not null)engine=myisam;
1322 insert ignore into t1 set a = ' ';
1323 insert ignore into t1 set a = ' ';
1324 select * from t1 order by (oct(a));
1330 create table t1 (d decimal default null);
1331 insert into t1 values (null);
1332 select format(d, 2) from t1;
1336 create table t1 (c varchar(40));
1337 insert into t1 values ('y,abc'),('y,abc');
1338 select c, substring_index(lcase(c), @q:=',', -1) as res from t1;
1343 select cast(rtrim(' 20.06 ') as decimal(19,2));
1344 cast(rtrim(' 20.06 ') as decimal(19,2))
1346 select cast(ltrim(' 20.06 ') as decimal(19,2));
1347 cast(ltrim(' 20.06 ') as decimal(19,2))
1349 select cast(rtrim(ltrim(' 20.06 ')) as decimal(19,2));
1350 cast(rtrim(ltrim(' 20.06 ')) as decimal(19,2))
1352 select conv("18383815659218730760",10,10) + 0;
1353 conv("18383815659218730760",10,10) + 0
1354 1.83838156592187e+19
1355 select "18383815659218730760" + 0;
1356 "18383815659218730760" + 0
1357 1.83838156592187e+19
1358 CREATE TABLE t1 (code varchar(10));
1359 INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13');
1360 SELECT ASCII(code), code FROM t1 WHERE code='A12';
1364 SELECT ASCII(code), code FROM t1 WHERE code='A12' AND ASCII(code)=65;
1367 INSERT INTO t1 VALUES ('a12 '), ('A12 ');
1368 SELECT LENGTH(code), code FROM t1 WHERE code='A12';
1374 SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5;
1377 ALTER TABLE t1 ADD INDEX (code);
1378 CREATE TABLE t2 (id varchar(10) PRIMARY KEY);
1379 INSERT INTO t2 VALUES ('a11'), ('a12'), ('a13'), ('a14');
1380 SELECT * FROM t1 INNER JOIN t2 ON t1.code=t2.id
1381 WHERE t2.id='a12' AND (LENGTH(code)=5 OR code < 'a00');
1385 SELECT * FROM t1 INNER JOIN t2 ON code=id
1386 WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00');
1387 id select_type table type possible_keys key key_len ref rows filtered Extra
1388 1 SIMPLE t2 const PRIMARY PRIMARY 12 const 1 100.00 Using index
1389 1 SIMPLE t1 ref code code 13 const 3 100.00 Using where; Using index
1391 Note 1003 select `test`.`t1`.`code` AS `code`,'a12' AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = 'a12') and (length(`test`.`t1`.`code`) = 5))
1393 select encode(NULL, NULL);
1396 select encode("data", NULL);
1397 encode("data", NULL)
1399 select encode(NULL, "password");
1400 encode(NULL, "password")
1402 select decode(NULL, NULL);
1405 select decode("data", NULL);
1406 decode("data", NULL)
1408 select decode(NULL, "password");
1409 decode(NULL, "password")
1411 select format(NULL, NULL);
1414 select format(pi(), NULL);
1417 select format(NULL, 2);
1420 select benchmark(NULL, NULL);
1421 benchmark(NULL, NULL)
1423 select benchmark(0, NULL);
1426 select benchmark(100, NULL);
1427 benchmark(100, NULL)
1429 select benchmark(NULL, 1+1);
1430 benchmark(NULL, 1+1)
1432 select benchmark(-1, 1);
1436 Error 1411 Incorrect count value: '-1' for function benchmark
1437 set @password="password";
1438 set @my_data="clear text to encode";
1439 select md5(encode(@my_data, "password"));
1440 md5(encode(@my_data, "password"))
1441 44320fd2b4a0ec92faa2da2122def917
1442 select md5(encode(@my_data, _utf8 "password"));
1443 md5(encode(@my_data, _utf8 "password"))
1444 44320fd2b4a0ec92faa2da2122def917
1445 select md5(encode(@my_data, binary "password"));
1446 md5(encode(@my_data, binary "password"))
1447 44320fd2b4a0ec92faa2da2122def917
1448 select md5(encode(@my_data, _latin1 "password"));
1449 md5(encode(@my_data, _latin1 "password"))
1450 44320fd2b4a0ec92faa2da2122def917
1451 select md5(encode(@my_data, _koi8r "password"));
1452 md5(encode(@my_data, _koi8r "password"))
1453 44320fd2b4a0ec92faa2da2122def917
1454 select md5(encode(@my_data, (select "password" from dual)));
1455 md5(encode(@my_data, (select "password" from dual)))
1456 44320fd2b4a0ec92faa2da2122def917
1457 select md5(encode(@my_data, concat("pass", "word")));
1458 md5(encode(@my_data, concat("pass", "word")))
1459 44320fd2b4a0ec92faa2da2122def917
1460 select md5(encode(@my_data, @password));
1461 md5(encode(@my_data, @password))
1462 44320fd2b4a0ec92faa2da2122def917
1463 set @my_data="binary encoded data";
1464 select md5(decode(@my_data, "password"));
1465 md5(decode(@my_data, "password"))
1466 5bea8c394368dbc03b76684483b7756b
1467 select md5(decode(@my_data, _utf8 "password"));
1468 md5(decode(@my_data, _utf8 "password"))
1469 5bea8c394368dbc03b76684483b7756b
1470 select md5(decode(@my_data, binary "password"));
1471 md5(decode(@my_data, binary "password"))
1472 5bea8c394368dbc03b76684483b7756b
1473 select md5(decode(@my_data, _latin1 "password"));
1474 md5(decode(@my_data, _latin1 "password"))
1475 5bea8c394368dbc03b76684483b7756b
1476 select md5(decode(@my_data, _koi8r "password"));
1477 md5(decode(@my_data, _koi8r "password"))
1478 5bea8c394368dbc03b76684483b7756b
1479 select md5(decode(@my_data, (select "password" from dual)));
1480 md5(decode(@my_data, (select "password" from dual)))
1481 5bea8c394368dbc03b76684483b7756b
1482 select md5(decode(@my_data, concat("pass", "word")));
1483 md5(decode(@my_data, concat("pass", "word")))
1484 5bea8c394368dbc03b76684483b7756b
1485 select md5(decode(@my_data, @password));
1486 md5(decode(@my_data, @password))
1487 5bea8c394368dbc03b76684483b7756b
1489 select format(pi(), (1+1));
1492 select format(pi(), (select 3 from dual));
1493 format(pi(), (select 3 from dual))
1495 select format(pi(), @dec);
1498 set @bench_count=10;
1499 select benchmark(10, pi());
1502 select benchmark(5+5, pi());
1503 benchmark(5+5, pi())
1505 select benchmark((select 10 from dual), pi());
1506 benchmark((select 10 from dual), pi())
1508 select benchmark(@bench_count, pi());
1509 benchmark(@bench_count, pi())
1511 select locate('he','hello',-2);
1512 locate('he','hello',-2)
1514 select locate('lo','hello',-4294967295);
1515 locate('lo','hello',-4294967295)
1517 select locate('lo','hello',4294967295);
1518 locate('lo','hello',4294967295)
1520 select locate('lo','hello',-4294967296);
1521 locate('lo','hello',-4294967296)
1523 select locate('lo','hello',4294967296);
1524 locate('lo','hello',4294967296)
1526 select locate('lo','hello',-4294967297);
1527 locate('lo','hello',-4294967297)
1529 select locate('lo','hello',4294967297);
1530 locate('lo','hello',4294967297)
1532 select locate('lo','hello',-18446744073709551615);
1533 locate('lo','hello',-18446744073709551615)
1536 Error 1292 Truncated incorrect DECIMAL value: ''
1537 select locate('lo','hello',18446744073709551615);
1538 locate('lo','hello',18446744073709551615)
1540 select locate('lo','hello',-18446744073709551616);
1541 locate('lo','hello',-18446744073709551616)
1544 Error 1292 Truncated incorrect DECIMAL value: ''
1545 select locate('lo','hello',18446744073709551616);
1546 locate('lo','hello',18446744073709551616)
1549 Error 1292 Truncated incorrect DECIMAL value: ''
1550 select locate('lo','hello',-18446744073709551617);
1551 locate('lo','hello',-18446744073709551617)
1554 Error 1292 Truncated incorrect DECIMAL value: ''
1555 select locate('lo','hello',18446744073709551617);
1556 locate('lo','hello',18446744073709551617)
1559 Error 1292 Truncated incorrect DECIMAL value: ''
1560 select left('hello', 10);
1563 select left('hello', 0);
1566 select left('hello', -1);
1569 select left('hello', -4294967295);
1570 left('hello', -4294967295)
1572 select left('hello', 4294967295);
1573 left('hello', 4294967295)
1575 select left('hello', -4294967296);
1576 left('hello', -4294967296)
1578 select left('hello', 4294967296);
1579 left('hello', 4294967296)
1581 select left('hello', -4294967297);
1582 left('hello', -4294967297)
1584 select left('hello', 4294967297);
1585 left('hello', 4294967297)
1587 select left('hello', -18446744073709551615);
1588 left('hello', -18446744073709551615)
1591 Error 1292 Truncated incorrect DECIMAL value: ''
1592 Error 1292 Truncated incorrect DECIMAL value: ''
1593 select left('hello', 18446744073709551615);
1594 left('hello', 18446744073709551615)
1596 select left('hello', -18446744073709551616);
1597 left('hello', -18446744073709551616)
1600 Error 1292 Truncated incorrect DECIMAL value: ''
1601 Error 1292 Truncated incorrect DECIMAL value: ''
1602 select left('hello', 18446744073709551616);
1603 left('hello', 18446744073709551616)
1606 Error 1292 Truncated incorrect DECIMAL value: ''
1607 Error 1292 Truncated incorrect DECIMAL value: ''
1608 select left('hello', -18446744073709551617);
1609 left('hello', -18446744073709551617)
1612 Error 1292 Truncated incorrect DECIMAL value: ''
1613 Error 1292 Truncated incorrect DECIMAL value: ''
1614 select left('hello', 18446744073709551617);
1615 left('hello', 18446744073709551617)
1618 Error 1292 Truncated incorrect DECIMAL value: ''
1619 Error 1292 Truncated incorrect DECIMAL value: ''
1620 select right('hello', 10);
1623 select right('hello', 0);
1626 select right('hello', -1);
1629 select right('hello', -4294967295);
1630 right('hello', -4294967295)
1632 select right('hello', 4294967295);
1633 right('hello', 4294967295)
1635 select right('hello', -4294967296);
1636 right('hello', -4294967296)
1638 select right('hello', 4294967296);
1639 right('hello', 4294967296)
1641 select right('hello', -4294967297);
1642 right('hello', -4294967297)
1644 select right('hello', 4294967297);
1645 right('hello', 4294967297)
1647 select right('hello', -18446744073709551615);
1648 right('hello', -18446744073709551615)
1651 Error 1292 Truncated incorrect DECIMAL value: ''
1652 Error 1292 Truncated incorrect DECIMAL value: ''
1653 select right('hello', 18446744073709551615);
1654 right('hello', 18446744073709551615)
1656 select right('hello', -18446744073709551616);
1657 right('hello', -18446744073709551616)
1660 Error 1292 Truncated incorrect DECIMAL value: ''
1661 Error 1292 Truncated incorrect DECIMAL value: ''
1662 select right('hello', 18446744073709551616);
1663 right('hello', 18446744073709551616)
1666 Error 1292 Truncated incorrect DECIMAL value: ''
1667 Error 1292 Truncated incorrect DECIMAL value: ''
1668 select right('hello', -18446744073709551617);
1669 right('hello', -18446744073709551617)
1672 Error 1292 Truncated incorrect DECIMAL value: ''
1673 Error 1292 Truncated incorrect DECIMAL value: ''
1674 select right('hello', 18446744073709551617);
1675 right('hello', 18446744073709551617)
1678 Error 1292 Truncated incorrect DECIMAL value: ''
1679 Error 1292 Truncated incorrect DECIMAL value: ''
1680 select substring('hello', 2, -1);
1681 substring('hello', 2, -1)
1683 select substring('hello', -1, 1);
1684 substring('hello', -1, 1)
1686 select substring('hello', -2, 1);
1687 substring('hello', -2, 1)
1689 select substring('hello', -4294967295, 1);
1690 substring('hello', -4294967295, 1)
1692 select substring('hello', 4294967295, 1);
1693 substring('hello', 4294967295, 1)
1695 select substring('hello', -4294967296, 1);
1696 substring('hello', -4294967296, 1)
1698 select substring('hello', 4294967296, 1);
1699 substring('hello', 4294967296, 1)
1701 select substring('hello', -4294967297, 1);
1702 substring('hello', -4294967297, 1)
1704 select substring('hello', 4294967297, 1);
1705 substring('hello', 4294967297, 1)
1707 select substring('hello', -18446744073709551615, 1);
1708 substring('hello', -18446744073709551615, 1)
1711 Error 1292 Truncated incorrect DECIMAL value: ''
1712 Error 1292 Truncated incorrect DECIMAL value: ''
1713 select substring('hello', 18446744073709551615, 1);
1714 substring('hello', 18446744073709551615, 1)
1716 select substring('hello', -18446744073709551616, 1);
1717 substring('hello', -18446744073709551616, 1)
1720 Error 1292 Truncated incorrect DECIMAL value: ''
1721 Error 1292 Truncated incorrect DECIMAL value: ''
1722 select substring('hello', 18446744073709551616, 1);
1723 substring('hello', 18446744073709551616, 1)
1726 Error 1292 Truncated incorrect DECIMAL value: ''
1727 Error 1292 Truncated incorrect DECIMAL value: ''
1728 select substring('hello', -18446744073709551617, 1);
1729 substring('hello', -18446744073709551617, 1)
1732 Error 1292 Truncated incorrect DECIMAL value: ''
1733 Error 1292 Truncated incorrect DECIMAL value: ''
1734 select substring('hello', 18446744073709551617, 1);
1735 substring('hello', 18446744073709551617, 1)
1738 Error 1292 Truncated incorrect DECIMAL value: ''
1739 Error 1292 Truncated incorrect DECIMAL value: ''
1740 select substring('hello', 1, -1);
1741 substring('hello', 1, -1)
1743 select substring('hello', 1, -4294967295);
1744 substring('hello', 1, -4294967295)
1746 select substring('hello', 1, 4294967295);
1747 substring('hello', 1, 4294967295)
1749 select substring('hello', 1, -4294967296);
1750 substring('hello', 1, -4294967296)
1752 select substring('hello', 1, 4294967296);
1753 substring('hello', 1, 4294967296)
1755 select substring('hello', 1, -4294967297);
1756 substring('hello', 1, -4294967297)
1758 select substring('hello', 1, 4294967297);
1759 substring('hello', 1, 4294967297)
1761 select substring('hello', 1, -18446744073709551615);
1762 substring('hello', 1, -18446744073709551615)
1765 Error 1292 Truncated incorrect DECIMAL value: ''
1766 Error 1292 Truncated incorrect DECIMAL value: ''
1767 select substring('hello', 1, 18446744073709551615);
1768 substring('hello', 1, 18446744073709551615)
1770 select substring('hello', 1, -18446744073709551616);
1771 substring('hello', 1, -18446744073709551616)
1774 Error 1292 Truncated incorrect DECIMAL value: ''
1775 Error 1292 Truncated incorrect DECIMAL value: ''
1776 select substring('hello', 1, 18446744073709551616);
1777 substring('hello', 1, 18446744073709551616)
1780 Error 1292 Truncated incorrect DECIMAL value: ''
1781 Error 1292 Truncated incorrect DECIMAL value: ''
1782 select substring('hello', 1, -18446744073709551617);
1783 substring('hello', 1, -18446744073709551617)
1786 Error 1292 Truncated incorrect DECIMAL value: ''
1787 Error 1292 Truncated incorrect DECIMAL value: ''
1788 select substring('hello', 1, 18446744073709551617);
1789 substring('hello', 1, 18446744073709551617)
1792 Error 1292 Truncated incorrect DECIMAL value: ''
1793 Error 1292 Truncated incorrect DECIMAL value: ''
1794 select substring('hello', -1, -1);
1795 substring('hello', -1, -1)
1797 select substring('hello', -4294967295, -4294967295);
1798 substring('hello', -4294967295, -4294967295)
1800 select substring('hello', 4294967295, 4294967295);
1801 substring('hello', 4294967295, 4294967295)
1803 select substring('hello', -4294967296, -4294967296);
1804 substring('hello', -4294967296, -4294967296)
1806 select substring('hello', 4294967296, 4294967296);
1807 substring('hello', 4294967296, 4294967296)
1809 select substring('hello', -4294967297, -4294967297);
1810 substring('hello', -4294967297, -4294967297)
1812 select substring('hello', 4294967297, 4294967297);
1813 substring('hello', 4294967297, 4294967297)
1815 select substring('hello', -18446744073709551615, -18446744073709551615);
1816 substring('hello', -18446744073709551615, -18446744073709551615)
1819 Error 1292 Truncated incorrect DECIMAL value: ''
1820 Error 1292 Truncated incorrect DECIMAL value: ''
1821 Error 1292 Truncated incorrect DECIMAL value: ''
1822 Error 1292 Truncated incorrect DECIMAL value: ''
1823 select substring('hello', 18446744073709551615, 18446744073709551615);
1824 substring('hello', 18446744073709551615, 18446744073709551615)
1826 select substring('hello', -18446744073709551616, -18446744073709551616);
1827 substring('hello', -18446744073709551616, -18446744073709551616)
1830 Error 1292 Truncated incorrect DECIMAL value: ''
1831 Error 1292 Truncated incorrect DECIMAL value: ''
1832 Error 1292 Truncated incorrect DECIMAL value: ''
1833 Error 1292 Truncated incorrect DECIMAL value: ''
1834 select substring('hello', 18446744073709551616, 18446744073709551616);
1835 substring('hello', 18446744073709551616, 18446744073709551616)
1838 Error 1292 Truncated incorrect DECIMAL value: ''
1839 Error 1292 Truncated incorrect DECIMAL value: ''
1840 Error 1292 Truncated incorrect DECIMAL value: ''
1841 Error 1292 Truncated incorrect DECIMAL value: ''
1842 select substring('hello', -18446744073709551617, -18446744073709551617);
1843 substring('hello', -18446744073709551617, -18446744073709551617)
1846 Error 1292 Truncated incorrect DECIMAL value: ''
1847 Error 1292 Truncated incorrect DECIMAL value: ''
1848 Error 1292 Truncated incorrect DECIMAL value: ''
1849 Error 1292 Truncated incorrect DECIMAL value: ''
1850 select substring('hello', 18446744073709551617, 18446744073709551617);
1851 substring('hello', 18446744073709551617, 18446744073709551617)
1854 Error 1292 Truncated incorrect DECIMAL value: ''
1855 Error 1292 Truncated incorrect DECIMAL value: ''
1856 Error 1292 Truncated incorrect DECIMAL value: ''
1857 Error 1292 Truncated incorrect DECIMAL value: ''
1858 select insert('hello', -1, 1, 'hi');
1859 insert('hello', -1, 1, 'hi')
1861 select insert('hello', -4294967295, 1, 'hi');
1862 insert('hello', -4294967295, 1, 'hi')
1864 select insert('hello', 4294967295, 1, 'hi');
1865 insert('hello', 4294967295, 1, 'hi')
1867 select insert('hello', -4294967296, 1, 'hi');
1868 insert('hello', -4294967296, 1, 'hi')
1870 select insert('hello', 4294967296, 1, 'hi');
1871 insert('hello', 4294967296, 1, 'hi')
1873 select insert('hello', -4294967297, 1, 'hi');
1874 insert('hello', -4294967297, 1, 'hi')
1876 select insert('hello', 4294967297, 1, 'hi');
1877 insert('hello', 4294967297, 1, 'hi')
1879 select insert('hello', -18446744073709551615, 1, 'hi');
1880 insert('hello', -18446744073709551615, 1, 'hi')
1883 Error 1292 Truncated incorrect DECIMAL value: ''
1884 select insert('hello', 18446744073709551615, 1, 'hi');
1885 insert('hello', 18446744073709551615, 1, 'hi')
1887 select insert('hello', -18446744073709551616, 1, 'hi');
1888 insert('hello', -18446744073709551616, 1, 'hi')
1891 Error 1292 Truncated incorrect DECIMAL value: ''
1892 select insert('hello', 18446744073709551616, 1, 'hi');
1893 insert('hello', 18446744073709551616, 1, 'hi')
1896 Error 1292 Truncated incorrect DECIMAL value: ''
1897 select insert('hello', -18446744073709551617, 1, 'hi');
1898 insert('hello', -18446744073709551617, 1, 'hi')
1901 Error 1292 Truncated incorrect DECIMAL value: ''
1902 select insert('hello', 18446744073709551617, 1, 'hi');
1903 insert('hello', 18446744073709551617, 1, 'hi')
1906 Error 1292 Truncated incorrect DECIMAL value: ''
1907 select insert('hello', 1, -1, 'hi');
1908 insert('hello', 1, -1, 'hi')
1910 select insert('hello', 1, -4294967295, 'hi');
1911 insert('hello', 1, -4294967295, 'hi')
1913 select insert('hello', 1, 4294967295, 'hi');
1914 insert('hello', 1, 4294967295, 'hi')
1916 select insert('hello', 1, -4294967296, 'hi');
1917 insert('hello', 1, -4294967296, 'hi')
1919 select insert('hello', 1, 4294967296, 'hi');
1920 insert('hello', 1, 4294967296, 'hi')
1922 select insert('hello', 1, -4294967297, 'hi');
1923 insert('hello', 1, -4294967297, 'hi')
1925 select insert('hello', 1, 4294967297, 'hi');
1926 insert('hello', 1, 4294967297, 'hi')
1928 select insert('hello', 1, -18446744073709551615, 'hi');
1929 insert('hello', 1, -18446744073709551615, 'hi')
1932 Error 1292 Truncated incorrect DECIMAL value: ''
1933 select insert('hello', 1, 18446744073709551615, 'hi');
1934 insert('hello', 1, 18446744073709551615, 'hi')
1936 select insert('hello', 1, -18446744073709551616, 'hi');
1937 insert('hello', 1, -18446744073709551616, 'hi')
1940 Error 1292 Truncated incorrect DECIMAL value: ''
1941 select insert('hello', 1, 18446744073709551616, 'hi');
1942 insert('hello', 1, 18446744073709551616, 'hi')
1945 Error 1292 Truncated incorrect DECIMAL value: ''
1946 select insert('hello', 1, -18446744073709551617, 'hi');
1947 insert('hello', 1, -18446744073709551617, 'hi')
1950 Error 1292 Truncated incorrect DECIMAL value: ''
1951 select insert('hello', 1, 18446744073709551617, 'hi');
1952 insert('hello', 1, 18446744073709551617, 'hi')
1955 Error 1292 Truncated incorrect DECIMAL value: ''
1956 select insert('hello', -1, -1, 'hi');
1957 insert('hello', -1, -1, 'hi')
1959 select insert('hello', -4294967295, -4294967295, 'hi');
1960 insert('hello', -4294967295, -4294967295, 'hi')
1962 select insert('hello', 4294967295, 4294967295, 'hi');
1963 insert('hello', 4294967295, 4294967295, 'hi')
1965 select insert('hello', -4294967296, -4294967296, 'hi');
1966 insert('hello', -4294967296, -4294967296, 'hi')
1968 select insert('hello', 4294967296, 4294967296, 'hi');
1969 insert('hello', 4294967296, 4294967296, 'hi')
1971 select insert('hello', -4294967297, -4294967297, 'hi');
1972 insert('hello', -4294967297, -4294967297, 'hi')
1974 select insert('hello', 4294967297, 4294967297, 'hi');
1975 insert('hello', 4294967297, 4294967297, 'hi')
1977 select insert('hello', -18446744073709551615, -18446744073709551615, 'hi');
1978 insert('hello', -18446744073709551615, -18446744073709551615, 'hi')
1981 Error 1292 Truncated incorrect DECIMAL value: ''
1982 Error 1292 Truncated incorrect DECIMAL value: ''
1983 select insert('hello', 18446744073709551615, 18446744073709551615, 'hi');
1984 insert('hello', 18446744073709551615, 18446744073709551615, 'hi')
1986 select insert('hello', -18446744073709551616, -18446744073709551616, 'hi');
1987 insert('hello', -18446744073709551616, -18446744073709551616, 'hi')
1990 Error 1292 Truncated incorrect DECIMAL value: ''
1991 Error 1292 Truncated incorrect DECIMAL value: ''
1992 select insert('hello', 18446744073709551616, 18446744073709551616, 'hi');
1993 insert('hello', 18446744073709551616, 18446744073709551616, 'hi')
1996 Error 1292 Truncated incorrect DECIMAL value: ''
1997 Error 1292 Truncated incorrect DECIMAL value: ''
1998 select insert('hello', -18446744073709551617, -18446744073709551617, 'hi');
1999 insert('hello', -18446744073709551617, -18446744073709551617, 'hi')
2002 Error 1292 Truncated incorrect DECIMAL value: ''
2003 Error 1292 Truncated incorrect DECIMAL value: ''
2004 select insert('hello', 18446744073709551617, 18446744073709551617, 'hi');
2005 insert('hello', 18446744073709551617, 18446744073709551617, 'hi')
2008 Error 1292 Truncated incorrect DECIMAL value: ''
2009 Error 1292 Truncated incorrect DECIMAL value: ''
2010 select repeat('hello', -1);
2013 select repeat('hello', -4294967295);
2014 repeat('hello', -4294967295)
2016 select repeat('hello', 4294967295);
2017 repeat('hello', 4294967295)
2020 Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated
2021 select repeat('hello', -4294967296);
2022 repeat('hello', -4294967296)
2024 select repeat('hello', 4294967296);
2025 repeat('hello', 4294967296)
2028 Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated
2029 select repeat('hello', -4294967297);
2030 repeat('hello', -4294967297)
2032 select repeat('hello', 4294967297);
2033 repeat('hello', 4294967297)
2036 Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated
2037 select repeat('hello', -18446744073709551615);
2038 repeat('hello', -18446744073709551615)
2041 Error 1292 Truncated incorrect DECIMAL value: ''
2042 Error 1292 Truncated incorrect DECIMAL value: ''
2043 select repeat('hello', 18446744073709551615);
2044 repeat('hello', 18446744073709551615)
2047 Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated
2048 select repeat('hello', -18446744073709551616);
2049 repeat('hello', -18446744073709551616)
2052 Error 1292 Truncated incorrect DECIMAL value: ''
2053 Error 1292 Truncated incorrect DECIMAL value: ''
2054 select repeat('hello', 18446744073709551616);
2055 repeat('hello', 18446744073709551616)
2058 Error 1292 Truncated incorrect DECIMAL value: ''
2059 Error 1292 Truncated incorrect DECIMAL value: ''
2060 Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated
2061 select repeat('hello', -18446744073709551617);
2062 repeat('hello', -18446744073709551617)
2065 Error 1292 Truncated incorrect DECIMAL value: ''
2066 Error 1292 Truncated incorrect DECIMAL value: ''
2067 select repeat('hello', 18446744073709551617);
2068 repeat('hello', 18446744073709551617)
2071 Error 1292 Truncated incorrect DECIMAL value: ''
2072 Error 1292 Truncated incorrect DECIMAL value: ''
2073 Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated
2077 select space(-4294967295);
2080 select space(4294967295);
2084 Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated
2085 select space(-4294967296);
2088 select space(4294967296);
2092 Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated
2093 select space(-4294967297);
2096 select space(4294967297);
2100 Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated
2101 select space(-18446744073709551615);
2102 space(-18446744073709551615)
2105 Error 1292 Truncated incorrect DECIMAL value: ''
2106 Error 1292 Truncated incorrect DECIMAL value: ''
2107 select space(18446744073709551615);
2108 space(18446744073709551615)
2111 Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated
2112 select space(-18446744073709551616);
2113 space(-18446744073709551616)
2116 Error 1292 Truncated incorrect DECIMAL value: ''
2117 Error 1292 Truncated incorrect DECIMAL value: ''
2118 select space(18446744073709551616);
2119 space(18446744073709551616)
2122 Error 1292 Truncated incorrect DECIMAL value: ''
2123 Error 1292 Truncated incorrect DECIMAL value: ''
2124 Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated
2125 select space(-18446744073709551617);
2126 space(-18446744073709551617)
2129 Error 1292 Truncated incorrect DECIMAL value: ''
2130 Error 1292 Truncated incorrect DECIMAL value: ''
2131 select space(18446744073709551617);
2132 space(18446744073709551617)
2135 Error 1292 Truncated incorrect DECIMAL value: ''
2136 Error 1292 Truncated incorrect DECIMAL value: ''
2137 Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - truncated
2138 select rpad('hello', -1, '1');
2139 rpad('hello', -1, '1')
2141 select rpad('hello', -4294967295, '1');
2142 rpad('hello', -4294967295, '1')
2144 select rpad('hello', 4294967295, '1');
2145 rpad('hello', 4294967295, '1')
2148 Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated
2149 select rpad('hello', -4294967296, '1');
2150 rpad('hello', -4294967296, '1')
2152 select rpad('hello', 4294967296, '1');
2153 rpad('hello', 4294967296, '1')
2156 Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated
2157 select rpad('hello', -4294967297, '1');
2158 rpad('hello', -4294967297, '1')
2160 select rpad('hello', 4294967297, '1');
2161 rpad('hello', 4294967297, '1')
2164 Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated
2165 select rpad('hello', -18446744073709551615, '1');
2166 rpad('hello', -18446744073709551615, '1')
2169 Error 1292 Truncated incorrect DECIMAL value: ''
2170 Error 1292 Truncated incorrect DECIMAL value: ''
2171 select rpad('hello', 18446744073709551615, '1');
2172 rpad('hello', 18446744073709551615, '1')
2175 Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated
2176 select rpad('hello', -18446744073709551616, '1');
2177 rpad('hello', -18446744073709551616, '1')
2180 Error 1292 Truncated incorrect DECIMAL value: ''
2181 Error 1292 Truncated incorrect DECIMAL value: ''
2182 select rpad('hello', 18446744073709551616, '1');
2183 rpad('hello', 18446744073709551616, '1')
2186 Error 1292 Truncated incorrect DECIMAL value: ''
2187 Error 1292 Truncated incorrect DECIMAL value: ''
2188 Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated
2189 select rpad('hello', -18446744073709551617, '1');
2190 rpad('hello', -18446744073709551617, '1')
2193 Error 1292 Truncated incorrect DECIMAL value: ''
2194 Error 1292 Truncated incorrect DECIMAL value: ''
2195 select rpad('hello', 18446744073709551617, '1');
2196 rpad('hello', 18446744073709551617, '1')
2199 Error 1292 Truncated incorrect DECIMAL value: ''
2200 Error 1292 Truncated incorrect DECIMAL value: ''
2201 Warning 1301 Result of rpad() was larger than max_allowed_packet (1048576) - truncated
2202 select lpad('hello', -1, '1');
2203 lpad('hello', -1, '1')
2205 select lpad('hello', -4294967295, '1');
2206 lpad('hello', -4294967295, '1')
2208 select lpad('hello', 4294967295, '1');
2209 lpad('hello', 4294967295, '1')
2212 Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated
2213 select lpad('hello', -4294967296, '1');
2214 lpad('hello', -4294967296, '1')
2216 select lpad('hello', 4294967296, '1');
2217 lpad('hello', 4294967296, '1')
2220 Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated
2221 select lpad('hello', -4294967297, '1');
2222 lpad('hello', -4294967297, '1')
2224 select lpad('hello', 4294967297, '1');
2225 lpad('hello', 4294967297, '1')
2228 Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated
2229 select lpad('hello', -18446744073709551615, '1');
2230 lpad('hello', -18446744073709551615, '1')
2233 Error 1292 Truncated incorrect DECIMAL value: ''
2234 Error 1292 Truncated incorrect DECIMAL value: ''
2235 select lpad('hello', 18446744073709551615, '1');
2236 lpad('hello', 18446744073709551615, '1')
2239 Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated
2240 select lpad('hello', -18446744073709551616, '1');
2241 lpad('hello', -18446744073709551616, '1')
2244 Error 1292 Truncated incorrect DECIMAL value: ''
2245 Error 1292 Truncated incorrect DECIMAL value: ''
2246 select lpad('hello', 18446744073709551616, '1');
2247 lpad('hello', 18446744073709551616, '1')
2250 Error 1292 Truncated incorrect DECIMAL value: ''
2251 Error 1292 Truncated incorrect DECIMAL value: ''
2252 Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated
2253 select lpad('hello', -18446744073709551617, '1');
2254 lpad('hello', -18446744073709551617, '1')
2257 Error 1292 Truncated incorrect DECIMAL value: ''
2258 Error 1292 Truncated incorrect DECIMAL value: ''
2259 select lpad('hello', 18446744073709551617, '1');
2260 lpad('hello', 18446744073709551617, '1')
2263 Error 1292 Truncated incorrect DECIMAL value: ''
2264 Error 1292 Truncated incorrect DECIMAL value: ''
2265 Warning 1301 Result of lpad() was larger than max_allowed_packet (1048576) - truncated
2266 SET @orig_sql_mode = @@SQL_MODE;
2267 SET SQL_MODE=traditional;
2268 SELECT CHAR(0xff,0x8f USING utf8);
2269 CHAR(0xff,0x8f USING utf8)
2272 Error 1300 Invalid utf8 character string: 'FF8F'
2273 SELECT CHAR(0xff,0x8f USING utf8) IS NULL;
2274 CHAR(0xff,0x8f USING utf8) IS NULL
2277 Error 1300 Invalid utf8 character string: 'FF8F'
2278 SET SQL_MODE=@orig_sql_mode;
2279 select substring('abc', cast(2 as unsigned int));
2280 substring('abc', cast(2 as unsigned int))
2282 select repeat('a', cast(2 as unsigned int));
2283 repeat('a', cast(2 as unsigned int))
2285 select rpad('abc', cast(5 as unsigned integer), 'x');
2286 rpad('abc', cast(5 as unsigned integer), 'x')
2288 select lpad('abc', cast(5 as unsigned integer), 'x');
2289 lpad('abc', cast(5 as unsigned integer), 'x')
2291 create table t1(f1 longtext);
2292 insert into t1 values ("123"),("456");
2293 select substring(f1,1,1) from t1 group by 1;
2297 create table t2(f1 varchar(3));
2298 insert into t1 values ("123"),("456");
2299 select substring(f1,4,1), substring(f1,-4,1) from t2;
2300 substring(f1,4,1) substring(f1,-4,1)
2302 DROP TABLE IF EXISTS t1;
2304 `id` varchar(20) NOT NULL,
2305 `tire` tinyint(3) unsigned NOT NULL,
2308 INSERT INTO `t1` (`id`, `tire`) VALUES ('A', 0), ('B', 1),('C', 2);
2309 SELECT REPEAT( '#', tire ) AS A,
2310 REPEAT( '#', tire % 999 ) AS B, tire FROM `t1`;
2315 SELECT REPEAT('0', CAST(0 AS UNSIGNED));
2316 REPEAT('0', CAST(0 AS UNSIGNED))
2318 SELECT REPEAT('0', -2);
2321 SELECT REPEAT('0', 2);
2328 SELECT UNHEX('G') IS NULL;
2331 SELECT INSERT('abc', 3, 3, '1234');
2332 INSERT('abc', 3, 3, '1234')
2334 SELECT INSERT('abc', 4, 3, '1234');
2335 INSERT('abc', 4, 3, '1234')
2337 SELECT INSERT('abc', 5, 3, '1234');
2338 INSERT('abc', 5, 3, '1234')
2340 SELECT INSERT('abc', 6, 3, '1234');
2341 INSERT('abc', 6, 3, '1234')
2343 CREATE TABLE t1 (a INT);
2344 CREATE VIEW v1 AS SELECT CRC32(a) AS C FROM t1;
2345 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
2346 SELECT CRC32(a), COUNT(*) FROM t1 GROUP BY 1;
2358 SELECT CRC32(a), COUNT(*) FROM t1 GROUP BY 1 ORDER BY 1;
2370 SELECT * FROM (SELECT CRC32(a) FROM t1) t2;
2382 CREATE TABLE t2 SELECT CRC32(a) FROM t1;
2384 Field Type Null Key Default Extra
2385 CRC32(a) int(10) unsigned YES NULL
2398 SELECT * FROM (SELECT * FROM v1) x;
2412 SELECT LOCATE('foo', NULL) FROM DUAL;
2415 SELECT LOCATE(NULL, 'o') FROM DUAL;
2418 SELECT LOCATE(NULL, NULL) FROM DUAL;
2421 SELECT LOCATE('foo', NULL) IS NULL FROM DUAL;
2422 LOCATE('foo', NULL) IS NULL
2424 SELECT LOCATE(NULL, 'o') IS NULL FROM DUAL;
2425 LOCATE(NULL, 'o') IS NULL
2427 SELECT LOCATE(NULL, NULL) IS NULL FROM DUAL;
2428 LOCATE(NULL, NULL) IS NULL
2430 SELECT ISNULL(LOCATE('foo', NULL)) FROM DUAL;
2431 ISNULL(LOCATE('foo', NULL))
2433 SELECT ISNULL(LOCATE(NULL, 'o')) FROM DUAL;
2434 ISNULL(LOCATE(NULL, 'o'))
2436 SELECT ISNULL(LOCATE(NULL, NULL)) FROM DUAL;
2437 ISNULL(LOCATE(NULL, NULL))
2439 SELECT LOCATE('foo', NULL) <=> NULL FROM DUAL;
2440 LOCATE('foo', NULL) <=> NULL
2442 SELECT LOCATE(NULL, 'o') <=> NULL FROM DUAL;
2443 LOCATE(NULL, 'o') <=> NULL
2445 SELECT LOCATE(NULL, NULL) <=> NULL FROM DUAL;
2446 LOCATE(NULL, NULL) <=> NULL
2448 CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a varchar(10), p varchar(10));
2449 INSERT INTO t1 VALUES (1, 'foo', 'o');
2450 INSERT INTO t1 VALUES (2, 'foo', NULL);
2451 INSERT INTO t1 VALUES (3, NULL, 'o');
2452 INSERT INTO t1 VALUES (4, NULL, NULL);
2453 SELECT id, LOCATE(a,p) FROM t1;
2459 SELECT id, LOCATE(a,p) IS NULL FROM t1;
2460 id LOCATE(a,p) IS NULL
2465 SELECT id, ISNULL(LOCATE(a,p)) FROM t1;
2466 id ISNULL(LOCATE(a,p))
2471 SELECT id, LOCATE(a,p) <=> NULL FROM t1;
2472 id LOCATE(a,p) <=> NULL
2477 SELECT id FROM t1 WHERE LOCATE(a,p) IS NULL;
2482 SELECT id FROM t1 WHERE LOCATE(a,p) <=> NULL;
2488 SELECT SUBSTR('foo',1,0) FROM DUAL;
2491 SELECT SUBSTR('foo',1,CAST(0 AS SIGNED)) FROM DUAL;
2492 SUBSTR('foo',1,CAST(0 AS SIGNED))
2494 SELECT SUBSTR('foo',1,CAST(0 AS UNSIGNED)) FROM DUAL;
2495 SUBSTR('foo',1,CAST(0 AS UNSIGNED))
2497 CREATE TABLE t1 (a varchar(10), len int unsigned);
2498 INSERT INTO t1 VALUES ('bar', 2), ('foo', 0);
2499 SELECT SUBSTR(a,1,len) FROM t1;
2504 CREATE TABLE t1 AS SELECT CHAR(0x414243) as c1;
2505 SELECT HEX(c1) from t1;
2509 CREATE VIEW v1 AS SELECT CHAR(0x414243) as c1;
2510 SELECT HEX(c1) from v1;
2514 create table t1(a float);
2515 insert into t1 values (1.33);
2516 select format(a, 2) from t1;
2517 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
2518 def format(a, 2) 253 49 4 Y 0 31 8
2522 CREATE TABLE t1 (c DATE, aa VARCHAR(30));
2523 INSERT INTO t1 VALUES ('2008-12-31','aaaaaa');
2524 SELECT DATE_FORMAT(c, GET_FORMAT(DATE, 'eur')) h, CONCAT(UPPER(aa),', ', aa) i FROM t1;
2526 31.12.2008 AAAAAA, aaaaaa
2529 # BUG#44774: load_file function produces valgrind warnings
2531 CREATE TABLE t1 (a TINYBLOB);
2532 INSERT INTO t1 VALUES ('aaaaaaaa');
2533 SELECT LOAD_FILE(a) FROM t1;
2537 CREATE TABLE t1 (f2 VARCHAR(20));
2538 CREATE TABLE t2 (f2 VARCHAR(20));
2539 INSERT INTO t1 VALUES ('MIN'),('MAX');
2540 INSERT INTO t2 VALUES ('LOAD');
2541 SELECT CONCAT_WS('_', (SELECT t2.f2 FROM t2), t1.f2) AS concat_name FROM t1;
2547 drop table if exists t1;
2548 create table t1(f1 tinyint default null)engine=myisam;
2549 insert into t1 values (-1),(null);
2550 explain select 1 as a from t1,(select decode(f1,f1) as b from t1) a;
2551 id select_type table type possible_keys key key_len ref rows Extra
2552 1 PRIMARY t1 ALL NULL NULL NULL NULL 2
2553 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using join buffer
2554 2 DERIVED t1 ALL NULL NULL NULL NULL 2
2555 explain select 1 as a from t1,(select encode(f1,f1) as b from t1) a;
2556 id select_type table type possible_keys key key_len ref rows Extra
2557 1 PRIMARY t1 ALL NULL NULL NULL NULL 2
2558 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using join buffer
2559 2 DERIVED t1 ALL NULL NULL NULL NULL 2
2562 # Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0
2564 DROP TABLE IF EXISTS t1, t2;
2565 CREATE TABLE t1 (a VARCHAR(20), b INT);
2566 CREATE TABLE t2 (a VARCHAR(20), b INT);
2567 INSERT INTO t1 VALUES ('ABC', 1);
2568 INSERT INTO t2 VALUES ('ABC', 1);
2569 SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a)
2570 FROM t1,t2 WHERE t1.b = t1.b > 0 GROUP BY t2.b;
2571 DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a)
2573 SELECT DECODE((SELECT ENCODE('secret', 'ABC') FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a)
2574 FROM t1,t2 WHERE t1.b = t1.b > 0 GROUP BY t2.b;
2575 DECODE((SELECT ENCODE('secret', 'ABC') FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), t2.a)
2577 SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), 'ABC')
2578 FROM t1,t2 WHERE t1.b = t1.b > 0 GROUP BY t2.b;
2579 DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b), 'ABC')
2583 INSERT INTO t1 VALUES ('EDF', 3), ('BCD', 2), ('ABC', 1);
2584 INSERT INTO t2 VALUES ('EDF', 3), ('BCD', 2), ('ABC', 1);
2585 SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b LIMIT 1), t2.a)
2586 FROM t2 WHERE t2.b = 1 GROUP BY t2.b;
2587 DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.b LIMIT 1), t2.a)
2591 # Bug#52164 Assertion failed: param.sort_length, file .\filesort.cc, line 149
2593 CREATE TABLE t1 (a LONGBLOB NOT NULL);
2594 INSERT INTO t1 VALUES (''),('');
2595 SELECT 1 FROM t1, t1 t2
2596 ORDER BY QUOTE(t1.a);
2604 # Bug#57913 large negative number to string conversion functions crash
2605 # Bug#57810 case/when/then : Assertion failed: length || !scale
2607 SELECT '1' IN ('1', SUBSTRING(-9223372036854775809, 1));
2608 '1' IN ('1', SUBSTRING(-9223372036854775809, 1))
2610 SELECT CONVERT(('' IN (REVERSE(CAST(('') AS DECIMAL)), '')), CHAR(3));
2611 CONVERT(('' IN (REVERSE(CAST(('') AS DECIMAL)), '')), CHAR(3))
2614 Warning 1292 Truncated incorrect DECIMAL value: ''
2616 # Bug#58165: "my_empty_string" gets modified and causes LOAD DATA to fail
2619 CREATE TABLE t1 ( a TEXT );
2620 SELECT 'aaaaaaaaaaaaaa' INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug58165.txt';;
2621 SELECT insert( substring_index( 'a', 'a', 'b' ), 1, 0, 'x' );
2622 insert( substring_index( 'a', 'a', 'b' ), 1, 0, 'x' )
2625 Warning 1292 Truncated incorrect INTEGER value: 'b'
2626 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug58165.txt' INTO TABLE t1;;