~drizzle-trunk/drizzle/development

# Description

# -----------

# Testing string functions

--disable_warnings

drop table if exists t1,t2;

--enable_warnings

set names latin1;

select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo';

select 'hello' 'monty';

select length('\n\t\r\b\0\_\%\\');

select bit_length('\n\t\r\b\0\_\%\\');

select char_length('\n\t\r\b\0\_\%\\');

select length(_latin1'\n\t\n\b\0\\_\\%\\');

select concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h');

select hex(char(256));

select locate('he','hello'),locate('he','hello',2),locate('lo','hello',2) ;

select instr('hello','HE'), instr('hello',binary 'HE'), instr(binary 'hello','HE'); 

select position(binary 'll' in 'hello'),position('a' in binary 'hello');

#

# Bug#11728 string function LEFT,

# strange undocumented behaviour, strict mode

#

select left('hello',null), right('hello',null);

select left('hello',2),right('hello',2),substring('hello',2,2),mid('hello',1,5) ;

select concat('',left(right(concat('what ',concat('is ','happening')),9),4),'',substring('monty',5,1)) ;

select substring_index('www.tcx.se','.',-2),substring_index('www.tcx.se','.',1);

select substring_index('www.tcx.se','tcx',1),substring_index('www.tcx.se','tcx',-1);

select substring_index('.tcx.se','.',-2),substring_index('.tcx.se','.tcx',-1);

select substring_index('aaaaaaaaa1','a',1);

select substring_index('aaaaaaaaa1','aa',1);

select substring_index('aaaaaaaaa1','aa',2);

select substring_index('aaaaaaaaa1','aa',3);

select substring_index('aaaaaaaaa1','aa',4);

select substring_index('aaaaaaaaa1','aa',5);

select substring_index('aaaaaaaaa1','aaa',1);

select substring_index('aaaaaaaaa1','aaa',2);

select substring_index('aaaaaaaaa1','aaa',3);

select substring_index('aaaaaaaaa1','aaa',4);

select substring_index('aaaaaaaaa1','aaaa',1);

select substring_index('aaaaaaaaa1','aaaa',2);

select substring_index('aaaaaaaaa1','1',1);

select substring_index('aaaaaaaaa1','a',-1);

select substring_index('aaaaaaaaa1','aa',-1);

select substring_index('aaaaaaaaa1','aa',-2);

select substring_index('aaaaaaaaa1','aa',-3);

select substring_index('aaaaaaaaa1','aa',-4);

select substring_index('aaaaaaaaa1','aa',-5);

select substring_index('aaaaaaaaa1','aaa',-1);

select substring_index('aaaaaaaaa1','aaa',-2);

select substring_index('aaaaaaaaa1','aaa',-3);

select substring_index('aaaaaaaaa1','aaa',-4);

select substring_index('the king of thethe hill','the',-2);

select substring_index('the king of the the hill','the',-2);

select substring_index('the king of the  the hill','the',-2);

select substring_index('the king of the  the hill',' the ',-1);

select substring_index('the king of the  the hill',' the ',-2);

select substring_index('the king of the  the hill',' ',-1);

select substring_index('the king of the  the hill',' ',-2);

select substring_index('the king of the  the hill',' ',-3);

select substring_index('the king of the  the hill',' ',-4);

select substring_index('the king of the  the hill',' ',-5);

select substring_index('the king of the.the hill','the',-2);

select substring_index('the king of thethethe.the hill','the',-3);

select substring_index('the king of thethethe.the hill','the',-1);

select substring_index('the king of the the hill','the',1);

select substring_index('the king of the the hill','the',2);

select substring_index('the king of the the hill','the',3);

select concat(':',ltrim('  left  '),':',rtrim('  right  '),':');

select concat(':',trim(leading from '  left  '),':',trim(trailing from '  right  '),':');

select concat(':',trim(LEADING FROM ' left'),':',trim(TRAILING FROM ' right '),':');

select concat(':',trim(' m '),':',trim(BOTH FROM ' y '),':',trim('*' FROM '*s*'),':');

select concat(':',trim(BOTH 'ab' FROM 'ababmyabab'),':',trim(BOTH '*' FROM '***sql'),':');

select concat(':',trim(LEADING '.*' FROM '.*my'),':',trim(TRAILING '.*' FROM 'sql.*.*'),':');

select TRIM("foo" FROM "foo"), TRIM("foo" FROM "foook"), TRIM("foo" FROM "okfoo");

select concat_ws(', ','monty','was here','again');

select concat_ws(NULL,'a'),concat_ws(',',NULL,'');

select concat_ws(',','',NULL,'a');

SELECT CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('d',100)), '"');

select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');

select replace('aaaa','a','b'),replace('aaaa','aa','b'),replace('aaaa','a','bb'),replace('aaaa','','b'),replace('bbbb','a','c');

select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ;

select md5('hello');

select crc32("123");

select aes_decrypt(aes_encrypt('abc','1'),'1');

select aes_decrypt(aes_encrypt('abc','1'),1);

select aes_encrypt(NULL,"a");

select aes_encrypt("a",NULL);

select aes_decrypt(NULL,"a");

select aes_decrypt("a",NULL);

select aes_decrypt("a","a");

select aes_decrypt(aes_encrypt("","a"),"a");

select repeat('monty',5),concat('*',space(5),'*');

select reverse('abc'),reverse('abcd');

select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'), rpad(11, 10 , 22), rpad("ab", 10, 22);

select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12'), lpad(11, 10 , 22);

select rpad(741653838,17,'0'),lpad(741653838,17,'0');

select rpad('abcd',7,'ab'),lpad('abcd',7,'ab');

select rpad('abcd',1,'ab'),lpad('abcd',1,'ab');

select rpad('STRING', 20, CONCAT('p','a','d') );

select lpad('STRING', 20, CONCAT('p','a','d') );

select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD');

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");

select decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000);

select decode(encode("abcdef","monty"),"monty")="abcdef";

select quote('\'\"\\test');

select quote(concat('abc\'', '\\cba'));

select quote(1/0), quote('\0\Z');

select length(quote(concat(char(0),"test")));

select hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235))));

select unhex(hex("foobar")), hex(unhex("1234567890ABCDEF")), unhex("345678"), unhex(NULL);

select hex(unhex("1")), hex(unhex("12")), hex(unhex("123")), hex(unhex("1234")), hex(unhex("12345")), hex(unhex("123456"));

select length(unhex(md5("abrakadabra")));

#

# Bug #6564: QUOTE(NULL

#

select concat('a', quote(NULL));

#

# Wrong usage of functions

#

select reverse("");

select insert("aa",100,1,"b"),insert("aa",1,3,"b"),left("aa",-1),substring("a",1,2);

select elt(2,1),field(NULL,"a","b","c"),reverse("");

select locate("a","b",2),locate("","a",1);

select ltrim("a"),rtrim("a"),trim(BOTH "" from "a"),trim(BOTH " " from "a");

select concat("1","2")|0,concat("1",".5")+0.0;

select substring_index("www.tcx.se","",3);

select length(repeat("a",100000000)),length(repeat("a",1000*64));

select position("0" in "baaa" in (1)),position("0" in "1" in (1,2,3)),position("sql" in ("mysql"));

select position(("1" in (1,2,3)) in "01");

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)));

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)));

#

# Problem med concat

#

create table t1 ( domain char(50) );

insert into t1 VALUES ("hello.de" ), ("test.de" );

select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@hello.de';

select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@test.de';

drop table t1;

#

# Test bug in concat_ws

#

CREATE TABLE t1 (

  id int(10) unsigned NOT NULL,

  title varchar(255) default NULL,

  prio int(10) unsigned default NULL,

  category int(10) unsigned default NULL,

  program int(10) unsigned default NULL,

  bugdesc text,

  created datetime default NULL,

  modified timestamp NOT NULL,

  bugstatus int(10) unsigned default NULL,

  submitter int(10) unsigned default NULL

) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1,'Link',1,1,1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','2001-02-28 08:40:16',20010228084016,0,4);

SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter), '"') FROM t1;

SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"') FROM t1;

SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter) FROM t1;

SELECT bugdesc, REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb') from t1 group by bugdesc;

drop table t1;

#

# Test bug in AES_DECRYPT() when called with wrong argument

#

CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf');

SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password");

DROP TABLE t1;

CREATE TABLE t1 (

  wid int(10) unsigned NOT NULL auto_increment,

  data_podp date default NULL,

  status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy',

  PRIMARY KEY(wid)

);

INSERT INTO t1 VALUES (8,NULL,'real');

INSERT INTO t1 VALUES (9,NULL,'nowy');

SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid;

DROP TABLE t1;

#

# test for #739

CREATE TABLE t1 (title text) ENGINE=MyISAM;

INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education');

INSERT INTO t1 VALUES ('House passes the CAREERS bill');

SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1;

DROP TABLE t1;

#

# test for Bug #2290 "output truncated with ELT when using DISTINCT"

#

CREATE TABLE t1 (i int, j int);

INSERT INTO t1 VALUES (1,1),(2,2);

SELECT DISTINCT i, ELT(j, '345', '34') FROM t1;

DROP TABLE t1;

#

# bug #3756: quote and NULL

#

create table t1(a char(4));

insert into t1 values ('one'),(NULL),('two'),('four');

select a, quote(a), isnull(quote(a)), quote(a) is null, ifnull(quote(a), 'n') from t1;

drop table t1;

#

# Bug #5498: TRIM fails with LEADING or TRAILING if remstr = str

#

select trim(trailing 'foo' from 'foo');

select trim(leading 'foo' from 'foo');

#

# crashing bug with QUOTE() and LTRIM() or TRIM() fixed

# Bug #7495

#

select quote(ltrim(concat('    ', 'a')));

select quote(trim(concat('    ', 'a')));

# Bad results from QUOTE(). Bug #8248

CREATE TABLE t1 SELECT 1 UNION SELECT 2 UNION SELECT 3;

SELECT QUOTE('A') FROM t1;

DROP TABLE t1;

# Test collation and coercibility

#

select 1=_latin1'1';

select _latin1'1'=1;

select _latin2'1'=1;

select 1=_latin2'1';

--error 1267

select _latin1'1'=_latin2'1';

select row('a','b','c') = row('a','b','c');

select row('A','b','c') = row('a','b','c');

select row('A' COLLATE latin1_bin,'b','c') = row('a','b','c');

select row('A','b','c') = row('a' COLLATE latin1_bin,'b','c');

--error 1267

select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c');

--error 1267

select concat(_latin1'a',_latin2'a');

--error 1270

select concat(_latin1'a',_latin2'a',_latin5'a');

--error 1271

select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a');

--error 1267

select concat_ws(_latin1'a',_latin2'a');

#

# Test FIELD() and collations

#

select FIELD('b','A','B');

select FIELD('B','A','B');

select FIELD('b' COLLATE latin1_bin,'A','B');

select FIELD('b','A' COLLATE latin1_bin,'B');

--error 1270

select FIELD(_latin2'b','A','B');

--error 1270

select FIELD('b',_latin2'A','B');

select FIELD('1',_latin2'3','2',1);

select POSITION(_latin1'B' IN _latin1'abcd');

select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin);

select POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd');

--error 1267

select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin);

--error 1267

select POSITION(_latin1'B' IN _latin2'abcd');

select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d');

# fix this:

--disable_parsing

select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d' COLLATE latin1_bin);

select FIND_IN_SET(_latin1'B' COLLATE latin1_bin,_latin1'a,b,c,d');

--enable_parsing

--error 1267

select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin);

--error 1267

select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d');

select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2);

# fix this:

--disable_parsing

select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_bin,_latin1'd',2);

select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd' COLLATE latin1_bin,2);

--enable_parsing

--error 1267

select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2);

--error 1267

select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2);

select _latin1'B' between _latin1'a' and _latin1'c';

select _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c';

select _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c';

select _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin;

--error 1270

select _latin2'B' between _latin1'a' and _latin1'b';

--error 1270

select _latin1'B' between _latin2'a' and _latin1'b';

--error 1270

select _latin1'B' between _latin1'a' and _latin2'b';

--error 1270

select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b';

select _latin1'B' in (_latin1'a',_latin1'b');

select _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b');

select _latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b');

select _latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin);

--error 1270

select _latin2'B' in (_latin1'a',_latin1'b');

--error 1270

select _latin1'B' in (_latin2'a',_latin1'b');

--error 1270

select _latin1'B' in (_latin1'a',_latin2'b');

--error 1270

select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b');

--error 1270

select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin);

select collation(bin(130)), coercibility(bin(130));

select collation(oct(130)), coercibility(oct(130));

select collation(conv(130,16,10)), coercibility(conv(130,16,10));

select collation(hex(130)), coercibility(hex(130));

select collation(char(130)), coercibility(hex(130));

select collation(format(130,10)), coercibility(format(130,10));

select collation(lcase(_latin2'a')), coercibility(lcase(_latin2'a'));

select collation(ucase(_latin2'a')), coercibility(ucase(_latin2'a'));

select collation(left(_latin2'a',1)), coercibility(left(_latin2'a',1));

select collation(right(_latin2'a',1)), coercibility(right(_latin2'a',1));

select collation(substring(_latin2'a',1,1)), coercibility(substring(_latin2'a',1,1));

select collation(concat(_latin2'a',_latin2'b')), coercibility(concat(_latin2'a',_latin2'b'));

select collation(lpad(_latin2'a',4,_latin2'b')), coercibility(lpad(_latin2'a',4,_latin2'b'));

select collation(rpad(_latin2'a',4,_latin2'b')), coercibility(rpad(_latin2'a',4,_latin2'b'));

select collation(concat_ws(_latin2'a',_latin2'b')), coercibility(concat_ws(_latin2'a',_latin2'b'));

select collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')), coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c'));

select collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')), coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' '));

select collation(trim(_latin2' a ')), coercibility(trim(_latin2' a '));

select collation(ltrim(_latin2' a ')), coercibility(ltrim(_latin2' a '));

select collation(rtrim(_latin2' a ')), coercibility(rtrim(_latin2' a '));

select collation(trim(LEADING _latin2' ' FROM _latin2'a')), coercibility(trim(LEADING _latin2'a' FROM _latin2'a'));

select collation(trim(TRAILING _latin2' ' FROM _latin2'a')), coercibility(trim(TRAILING _latin2'a' FROM _latin2'a'));

select collation(trim(BOTH _latin2' ' FROM _latin2'a')), coercibility(trim(BOTH _latin2'a' FROM _latin2'a'));

select collation(repeat(_latin2'a',10)), coercibility(repeat(_latin2'a',10));

select collation(reverse(_latin2'ab')), coercibility(reverse(_latin2'ab'));

select collation(quote(_latin2'ab')), coercibility(quote(_latin2'ab'));

select collation(substring(_latin2'ab',1)), coercibility(substring(_latin2'ab',1));

select collation(insert(_latin2'abcd',2,3,_latin2'ef')), coercibility(insert(_latin2'abcd',2,3,_latin2'ef'));

select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B'));

select collation(encode('abcd','ab')), coercibility(encode('abcd','ab'));

create table t1 

select

  bin(130),

  oct(130),

  conv(130,16,10),

  hex(130),

  char(130),

  left(_latin2'a',1),

  right(_latin2'a',1), 

  lcase(_latin2'a'), 

  ucase(_latin2'a'),

  substring(_latin2'a',1,1),

  concat(_latin2'a',_latin2'b'),

  lpad(_latin2'a',4,_latin2'b'),

  rpad(_latin2'a',4,_latin2'b'),

  concat_ws(_latin2'a',_latin2'b'),

  make_set(255,_latin2'a',_latin2'b',_latin2'c'),

  export_set(255,_latin2'y',_latin2'n',_latin2' '),

  trim(_latin2' a '),

  ltrim(_latin2' a '),

  rtrim(_latin2' a '),

  trim(LEADING _latin2' ' FROM _latin2' a '),

  trim(TRAILING _latin2' ' FROM _latin2' a '),

  trim(BOTH _latin2' ' FROM _latin2' a '),

  repeat(_latin2'a',10),

  reverse(_latin2'ab'),

  quote(_latin2'ab'),

  substring(_latin2'ab',1),

  insert(_latin2'abcd',2,3,_latin2'ef'),

  replace(_latin2'abcd',_latin2'b',_latin2'B'),

  encode('abcd','ab')

;

show create table t1;

drop table t1;

#

# Bug#9129

#

create table t1 (a char character set latin2);

insert into t1 values (null);

select charset(a), collation(a), coercibility(a) from t1;

drop table t1;

select charset(null), collation(null), coercibility(null);

#

# Make sure OUTER JOIN is not replaced with a regular joun

#

CREATE TABLE t1 (a int, b int);

CREATE TABLE t2 (a int, b int);

INSERT INTO t1 VALUES (1,1),(2,2);

INSERT INTO t2 VALUES (2,2),(3,3);

select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b)

where collation(t2.a) = _utf8'binary' order by t1.a,t2.a;

select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b)

where charset(t2.a) = _utf8'binary' order by t1.a,t2.a;

select t1.*,t2.* from t1 left join t2 on (t1.b=t2.b)

where coercibility(t2.a) = 2 order by t1.a,t2.a;

DROP TABLE t1, t2;

#

# test for SUBSTR

#

select SUBSTR('abcdefg',3,2);

select SUBSTRING('abcdefg',3,2);

select SUBSTR('abcdefg',-3,2);

select SUBSTR('abcdefg',-1,5);

select SUBSTR('abcdefg',0,0);

select SUBSTR('abcdefg',-1,-1);

select SUBSTR('abcdefg',1,-1);

#

# Test that fix_fields doesn't follow to upper level (to comparison)

# when an error on a lower level (in concat) has accured:

#

create table t7 (s1 char);

--error 1267

select * from t7

where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA';

drop table t7;

select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2);

explain extended select md5('hello');

explain extended select aes_decrypt(aes_encrypt('abc','1'),'1');

explain extended select concat('*',space(5),'*');

explain extended select reverse('abc');

explain extended select rpad('a',4,'1');

explain extended select lpad('a',4,'1');

explain extended select concat_ws(',','',NULL,'a');

explain extended select make_set(255,_latin2'a', _latin2'b', _latin2'c');

explain extended select elt(2,1);

explain extended select locate("a","b",2);

explain extended select char(0);

explain extended select conv(130,16,10);

explain extended select hex(130);

explain extended select binary 'HE';

explain extended select export_set(255,_latin2'y', _latin2'n', _latin2' ');

explain extended select FIELD('b' COLLATE latin1_bin,'A','B');

explain extended select FIND_IN_SET(_latin1'B', _latin1'a,b,c,d');

explain extended select collation(conv(130,16,10));

explain extended select coercibility(conv(130,16,10));

explain extended select length('\n\t\r\b\0\_\%\\');

explain extended select bit_length('\n\t\r\b\0\_\%\\');

explain extended select bit_length('\n\t\r\b\0\_\%\\');

explain extended select concat('monty',' was here ','again');

explain extended select length('hello');

explain extended select char(ascii('h'));

explain extended select ord('h');

explain extended select quote(1/0);

explain extended select crc32("123");

explain extended select replace('aaaa','a','b');

explain extended select insert('txs',2,1,'hi');

explain extended select left(_latin2'a',1);

explain extended select right(_latin2'a',1);

explain extended select lcase(_latin2'a');

explain extended select ucase(_latin2'a');

explain extended select SUBSTR('abcdefg',3,2);

explain extended select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2);

explain extended select trim(_latin2' a ');

explain extended select ltrim(_latin2' a ');

explain extended select rtrim(_latin2' a ');

explain extended select decode(encode(repeat("a",100000),"monty"),"monty");

#

# lpad returns incorrect result (Bug #2182)

#

SELECT lpad(12345, 5, "#");

#

# Problem the the CONV() function (Bug #2972)

#

SELECT conv(71, 10, 36), conv('1Z', 36, 10);

SELECT conv(71, 10, 37), conv('1Z', 37, 10), conv(0,1,10),conv(0,0,10), conv(0,-1,10);

#

# Bug in SUBSTRING when mixed with CONCAT and ORDER BY (Bug #3089)

#

create table t1 (id int(1), str varchar(10)) DEFAULT CHARSET=utf8;

insert into t1 values (1,'aaaaaaaaaa'), (2,'bbbbbbbbbb');

create table t2 (id int(1), str varchar(10)) DEFAULT CHARSET=utf8;

insert into t2 values (1,'cccccccccc'), (2,'dddddddddd');

select substring(concat(t1.str, t2.str), 1, 15) "name" from t1, t2 

where t2.id=t1.id order by name;

drop table t1, t2;

#

# Test case for conversion of long string value to integer (Bug #3472)

#

create table t1 (c1 INT, c2 INT UNSIGNED);

insert into t1 values ('21474836461','21474836461');

insert into t1 values ('-21474836461','-21474836461');

show warnings;

select * from t1;

drop table t1;

#

# Bug #4878: LEFT() in integer/float context

#

select left(1234, 3) + 0;

#

# Bug #7101: bug with LEFT() when used as a field in GROUP BY aggregation

#

create table t1 (a int not null primary key, b varchar(40), c datetime);

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'); 

select count(*) as total, left(c,10) as reg from t1 group by reg order by reg desc limit 0,12;

drop table t1;

#

# Bug#7455 unexpected result: TRIM(<NULL> FROM <whatever>) gives NOT NULL

# According to ANSI if one of the TRIM arguments is NULL, then the result

# must be NULL too.

#

select trim(null from 'kate') as "must_be_null";

select trim('xyz' from null) as "must_be_null";

select trim(leading NULL from 'kate') as "must_be_null";

select trim(trailing NULL from 'xyz') as "must_be_null";

#

# Bug #7751 - conversion for a bigint unsigned constant 

#

CREATE TABLE t1 (

  id int(11) NOT NULL auto_increment,

  a bigint(20) unsigned default NULL,

  PRIMARY KEY  (id)

) ENGINE=MyISAM;

INSERT INTO t1 VALUES

('0','16307858876001849059');

SELECT CONV('e251273eb74a8ee3', 16, 10);

EXPLAIN 

SELECT id

  FROM t1

  WHERE a = 16307858876001849059;

EXPLAIN 

  SELECT id

  FROM t1

  WHERE a = CONV('e251273eb74a8ee3', 16, 10);

DROP TABLE t1;

#

# Bug #6317: string function CHAR, parameter is NULL, wrong result

#

SELECT CHAR(NULL,121,83,81,'76') as my_column;

SELECT CHAR_LENGTH(CHAR(NULL,121,83,81,'76')) as my_column;

#

# Test case for bug #8669: null aes_decrypt result in order by query

#

CREATE TABLE t1 (id int PRIMARY KEY, str char(255) NOT NULL);

CREATE TABLE t2 (id int NOT NULL UNIQUE);

INSERT INTO t2 VALUES (1),(2);

INSERT INTO t1 VALUES (1, aes_encrypt('foo', 'bar'));

INSERT INTO t1 VALUES (2, 'not valid');

SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id;

SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id

 ORDER BY t1.id;

DROP TABLE t1, t2;

#

# Bug #10944: Mishandling of NULL arguments in FIELD()

#

select field(0,NULL,1,0), field("",NULL,"bar",""), field(0.0,NULL,1.0,0.0);

select field(NULL,1,2,NULL), field(NULL,1,2,0);

#

# Bug #10124: access by integer index with a string key that is not a number  

#

CREATE TABLE t1 (str varchar(20) PRIMARY KEY);

CREATE TABLE t2 (num int primary key);

INSERT INTO t1 VALUES ('notnumber');

INSERT INTO t2 VALUES (0), (1); 

SELECT * FROM t1, t2 WHERE num=str;

SELECT * FROM t1, t2 WHERE num=substring(str from 1 for 6);

DROP TABLE t1,t2;

#

# Bug #11469: NOT NULL optimization wrongly used for arguments of CONCAT_WS  

#

CREATE TABLE t1(

  id int(11) NOT NULL auto_increment,

  pc int(11) NOT NULL default '0',

  title varchar(20) default NULL,

  PRIMARY KEY (id)

);

INSERT INTO t1 VALUES

  (1, 0, 'Main'),

  (2, 1, 'Toys'),

  (3, 1, 'Games');

SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1

  FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id

          LEFT JOIN t1 AS t3 ON t2.pc=t3.id;

SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1

  FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id

          LEFT JOIN t1 AS t3 ON t2.pc=t3.id

    WHERE CONCAT_WS('->', t3.title, t2.title, t1.title) LIKE '%Toys%';

DROP TABLE t1;

CREATE TABLE t1(

  trackid     int(10) unsigned NOT NULL auto_increment,

  trackname   varchar(100) NOT NULL default '',

  PRIMARY KEY (trackid)

);

CREATE TABLE t2(

  artistid    int(10) unsigned NOT NULL auto_increment,

  artistname  varchar(100) NOT NULL default '',

  PRIMARY KEY (artistid)

);

CREATE TABLE t3(

  trackid     int(10) unsigned NOT NULL,

  artistid    int(10) unsigned NOT NULL,

  PRIMARY KEY (trackid,artistid)

);

INSERT INTO t1 VALUES (1, 'April In Paris'), (2, 'Autumn In New York');

INSERT INTO t2 VALUES (1, 'Vernon Duke');

INSERT INTO t3 VALUES (1,1);

SELECT CONCAT_WS(' ', trackname, artistname) trackname, artistname

  FROM t1 LEFT JOIN t3 ON t1.trackid=t3.trackid

          LEFT JOIN t2 ON t2.artistid=t3.artistid

    WHERE CONCAT_WS(' ', trackname, artistname) LIKE '%In%';

DROP TABLE t1,t2,t3;

#

# Correct length reporting from substring() (BUG#10269)

#

create table t1 (b varchar(5));

insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde');

select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1;

select * from (select *,substring(b,1),substring(b,-1),substring(b,-2),substring(b,-3),substring(b,-4),substring(b,-5) from t1) t;

drop table t1;

#

# Bug #9854  hex() and out of range handling