~drizzle-trunk/drizzle/development

drop table if exists t1,t2;

set names latin1;

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

hello	'hello'	""hello""	'h'e'l'l'o'	hel"lo	hel'lo

hello	'hello'	""hello""	'h'e'l'l'o'	hel"lo	hel'lo

select 'hello' 'monty';

hello

hellomonty

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

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

10

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

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

80

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

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

10

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

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

10

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

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

monty was here again	5	h	104

select hex(char(256));

hex(char(256))

0100

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

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

1	0	4

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

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

1	0	0

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

position(binary 'll' in 'hello')	position('a' in binary 'hello')

3	0

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

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

NULL	NULL

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

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

he	lo	el	hello

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

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

happy

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

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

tcx.se	www

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

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

www.	.se

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

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

tcx.se	.se

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

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

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

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

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

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

aa

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

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

aaaa

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

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

aaaaaa

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

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

aaaaaaaaa1

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

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

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

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

aaa

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

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

aaaaaa

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

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

aaaaaaaaa1

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

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

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

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

aaaa

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

substring_index('aaaaaaaaa1','1',1)

aaaaaaaaa

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

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

1

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

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

1

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

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

aa1

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

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

aaaa1

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

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

aaaaaa1

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

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

aaaaaaaaa1

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

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

1

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

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

aaa1

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

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

aaaaaa1

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

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

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

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

the hill

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

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

 the hill

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

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

  the hill

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

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

hill

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

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

 the hill

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

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

hill

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

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

the hill

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

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

 the hill

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

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

the  the hill

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

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

of the  the hill

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

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

.the hill

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

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

the.the hill

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

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

 hill

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

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

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

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

the king of 

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

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

the king of the 

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

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

:left  :  right:

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

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

:left  :  right:

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

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

:left: right:

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

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

:m:y:s:

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

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

:my:sql:

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

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

:my:sql:

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

TRIM("foo" FROM "foo")	TRIM("foo" FROM "foook")	TRIM("foo" FROM "okfoo")

	ok	ok

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

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

monty, was here, again

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

concat_ws(NULL,'a')	concat_ws(',',NULL,'')

NULL	

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

concat_ws(',','',NULL,'a')

,a

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

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

"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb";"cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc";"dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd"

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

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

this	is a	test

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

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

bbbb	bb	bbbbbbbb	aaaa	bbbb

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

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

this is a REAL test

select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb');

soundex('')	soundex('he')	soundex('hello all folks')	soundex('#3556 in bugdb')

	H000	H4142	I51231

select 'mood' sounds like 'mud';

'mood' sounds like 'mud'

1

select 'Glazgo' sounds like 'Liverpool';

'Glazgo' sounds like 'Liverpool'

0

select null sounds like 'null';

null sounds like 'null'

NULL

select 'null' sounds like null;

'null' sounds like null

NULL

select null sounds like null;

null sounds like null

NULL

select md5('hello');

md5('hello')

5d41402abc4b2a76b9719d911017c592

select crc32("123");

crc32("123")

2286445522

select sha('abc');

sha('abc')

a9993e364706816aba3e25717850c26c9cd0d89d

select sha1('abc');

sha1('abc')

a9993e364706816aba3e25717850c26c9cd0d89d

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

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

abc

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

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

abc

select aes_encrypt(NULL,"a");

aes_encrypt(NULL,"a")

NULL

select aes_encrypt("a",NULL);

aes_encrypt("a",NULL)

NULL

select aes_decrypt(NULL,"a");

aes_decrypt(NULL,"a")

NULL

select aes_decrypt("a",NULL);

aes_decrypt("a",NULL)

NULL

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

aes_decrypt("a","a")

NULL

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

aes_decrypt(aes_encrypt("","a"),"a")

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

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

montymontymontymontymonty	*     *

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

reverse('abc')	reverse('abcd')

cba	dcba

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

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

a111	a121	abc	1122222222	ab22222222

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

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

111a	121a	abc	2222222211

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

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

74165383800000000	00000000741653838

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

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

abcdaba	abaabcd

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

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

a	a

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

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

STRINGpadpadpadpadpa

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

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

padpadpadpadpaSTRING

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

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

NULL	NULL

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

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

33	4	1.0	1.0	1.1	9	B

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

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

1

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

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

1

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

quote('\'\"\\test')

'\'"\\test'

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

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

'abc\'\\cba'

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

quote(1/0)	quote('\0\Z')

NULL	'\0\Z'

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

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

8

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

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

27E0E3E6E7E8EAEB27

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

unhex(hex("foobar"))	hex(unhex("1234567890ABCDEF"))	unhex("345678")	unhex(NULL)

foobar	1234567890ABCDEF	4Vx	NULL

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

hex(unhex("1"))	hex(unhex("12"))	hex(unhex("123"))	hex(unhex("1234"))	hex(unhex("12345"))	hex(unhex("123456"))

01	12	0123	1234	012345	123456

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

length(unhex(md5("abrakadabra")))

16

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

concat('a', quote(NULL))

aNULL

select reverse("");

reverse("")

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

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

aa	b		a

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

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

NULL	0	

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

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

0	1

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

ltrim("a")	rtrim("a")	trim(BOTH "" from "a")	trim(BOTH " " from "a")

a	a	a	a

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

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

12	1.5

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

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

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

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

NULL	64000

Warnings:

Warning	1301	Result of repeat() was larger than max_allowed_packet (1048576) - truncated

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

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

1	0	3

Warnings:

Warning	1292	Truncated incorrect DOUBLE value: 'baaa'

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

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

2

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

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

65500	64000	50000	60000

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

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

1000000	96000	160000	95000

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

domain

hello.de

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

domain

test.de

drop table t1;

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;

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

"Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4"

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

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

"Link";"1";"1";"1";"0";"4"

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

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

Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4

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

bugdesc	REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb')

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

drop table t1;

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

1

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;

elt(status_wnio,data_podp)

NULL

NULL

DROP TABLE t1;

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;

CONCAT("</a>",RPAD("",(55 - LENGTH(title)),"."))

NULL

</a>..........................

DROP TABLE t1;

CREATE TABLE t1 (i int, j int);

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

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

i	ELT(j, '345', '34')

1	345

2	34

DROP TABLE t1;

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;

a	quote(a)	isnull(quote(a))	quote(a) is null	ifnull(quote(a), 'n')

one	'one'	0	0	'one'

NULL	NULL	0	0	NULL

two	'two'	0	0	'two'

four	'four'	0	0	'four'

drop table t1;

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

trim(trailing 'foo' from 'foo')

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

trim(leading 'foo' from 'foo')

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

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

'a'

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

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

'a'

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

SELECT QUOTE('A') FROM t1;

QUOTE('A')

'A'

'A'

'A'

DROP TABLE t1;

select 1=_latin1'1';

1=_latin1'1'

1

select _latin1'1'=1;

_latin1'1'=1

1

select _latin2'1'=1;

_latin2'1'=1

1

select 1=_latin2'1';

1=_latin2'1'

1

select _latin1'1'=_latin2'1';

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '='

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

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

1

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

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

1

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

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

0

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

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

0

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

ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation '='

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

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat'

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

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin5_turkish_ci,COERCIBLE) for operation 'concat'

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

ERROR HY000: Illegal mix of collations for operation 'concat'

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

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat_ws'

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

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

2

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

FIELD('B','A','B')

2

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

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

0

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

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

0

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

ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field'

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

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field'

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

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

3

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

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

2

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

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

0

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

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

0

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

ERROR HY000: Illegal mix of collations (latin1_bin,EXPLICIT) and (latin1_general_ci,EXPLICIT) for operation 'locate'

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

ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation 'locate'

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

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

2

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

ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'find_in_set'

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

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'find_in_set'

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

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

abcdabc

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

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'substring_index'

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

ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'substring_index'

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

_latin1'B' between _latin1'a' and _latin1'c'

1

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

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

0

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

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

0

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

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

0

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

ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between'

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

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between'

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

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation 'between'

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

ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation 'between'

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

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

1

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

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

0

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

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

0

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

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

0

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

ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN '

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

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN '

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

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation ' IN '

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

ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation ' IN '

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

ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_swedish_ci,COERCIBLE), (latin1_bin,EXPLICIT) for operation ' IN '

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

collation(bin(130))	coercibility(bin(130))

latin1_swedish_ci	4

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

collation(oct(130))	coercibility(oct(130))

latin1_swedish_ci	4

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

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

latin1_swedish_ci	4

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

collation(hex(130))	coercibility(hex(130))

latin1_swedish_ci	4

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

collation(char(130))	coercibility(hex(130))

binary	4

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

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

latin1_swedish_ci	4

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

collation(lcase(_latin2'a'))	coercibility(lcase(_latin2'a'))

latin2_general_ci	4

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

collation(ucase(_latin2'a'))	coercibility(ucase(_latin2'a'))

latin2_general_ci	4

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

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

latin2_general_ci	4

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

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

latin2_general_ci	4

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

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

latin2_general_ci	4

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

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

latin2_general_ci	4

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

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

latin2_general_ci	4

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

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

latin2_general_ci	4

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

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

latin2_general_ci	4

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

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

latin2_general_ci	4

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

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

latin2_general_ci	4

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

collation(trim(_latin2' a '))	coercibility(trim(_latin2' a '))

latin2_general_ci	4

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

collation(ltrim(_latin2' a '))	coercibility(ltrim(_latin2' a '))

latin2_general_ci	4

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

collation(rtrim(_latin2' a '))	coercibility(rtrim(_latin2' a '))

latin2_general_ci	4

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

collation(trim(LEADING _latin2' ' FROM _latin2'a'))	coercibility(trim(LEADING _latin2'a' FROM _latin2'a'))

latin2_general_ci	4

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

collation(trim(TRAILING _latin2' ' FROM _latin2'a'))	coercibility(trim(TRAILING _latin2'a' FROM _latin2'a'))

latin2_general_ci	4

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

collation(trim(BOTH _latin2' ' FROM _latin2'a'))	coercibility(trim(BOTH _latin2'a' FROM _latin2'a'))

latin2_general_ci	4

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

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

latin2_general_ci	4

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

collation(reverse(_latin2'ab'))	coercibility(reverse(_latin2'ab'))

latin2_general_ci	4

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

collation(quote(_latin2'ab'))	coercibility(quote(_latin2'ab'))

latin2_general_ci	4

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

collation(soundex(_latin2'ab'))	coercibility(soundex(_latin2'ab'))

latin2_general_ci	4

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

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

latin2_general_ci	4

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

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

latin2_general_ci	4

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

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

latin2_general_ci	4

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

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

binary	4

create table t1 

select

bin(130),

oct(130),

conv(130,16,10),

hex(130),

char(130),

format(130,10),

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

soundex(_latin2'ab'),

substring(_latin2'ab',1),

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

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

encode('abcd','ab')

;

Warnings:

Warning	1265	Data truncated for column 'format(130,10)' at row 1

show create table t1;

Table	Create Table

t1	CREATE TABLE `t1` (

  `bin(130)` varchar(64) DEFAULT NULL,

  `oct(130)` varchar(64) DEFAULT NULL,

  `conv(130,16,10)` varchar(64) DEFAULT NULL,

  `hex(130)` varchar(6) NOT NULL DEFAULT '',

  `char(130)` varbinary(4) NOT NULL DEFAULT '',

  `format(130,10)` varchar(4) NOT NULL DEFAULT '',

  `left(_latin2'a',1)` varchar(1) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `right(_latin2'a',1)` varchar(1) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `lcase(_latin2'a')` varchar(1) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `ucase(_latin2'a')` varchar(1) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `substring(_latin2'a',1,1)` varchar(1) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `concat(_latin2'a',_latin2'b')` varchar(2) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `lpad(_latin2'a',4,_latin2'b')` varchar(4) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `rpad(_latin2'a',4,_latin2'b')` varchar(4) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `concat_ws(_latin2'a',_latin2'b')` varchar(1) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `make_set(255,_latin2'a',_latin2'b',_latin2'c')` varchar(5) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `export_set(255,_latin2'y',_latin2'n',_latin2' ')` varchar(127) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `trim(_latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `ltrim(_latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `rtrim(_latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `trim(LEADING _latin2' ' FROM _latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `trim(TRAILING _latin2' ' FROM _latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `trim(BOTH _latin2' ' FROM _latin2' a ')` varchar(3) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `repeat(_latin2'a',10)` varchar(10) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `reverse(_latin2'ab')` varchar(2) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `quote(_latin2'ab')` varchar(6) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `soundex(_latin2'ab')` varchar(4) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `substring(_latin2'ab',1)` varchar(2) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `insert(_latin2'abcd',2,3,_latin2'ef')` varchar(6) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `replace(_latin2'abcd',_latin2'b',_latin2'B')` varchar(4) CHARACTER SET latin2 NOT NULL DEFAULT '',

  `encode('abcd','ab')` varbinary(4) NOT NULL DEFAULT ''

) ENGINE=MyISAM DEFAULT CHARSET=latin1

drop table t1;

create table t1 (a char character set latin2);

insert into t1 values (null);

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

charset(a)	collation(a)	coercibility(a)

latin2	latin2_general_ci	2

drop table t1;

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

charset(null)	collation(null)	coercibility(null)

binary	binary	5

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;

a	b	a	b

1	1	NULL	NULL

2	2	2	2

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;

a	b	a	b

1	1	NULL	NULL

2	2	2	2

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

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

a	b	a	b

1	1	NULL	NULL

2	2	2	2

DROP TABLE t1, t2;

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

SUBSTR('abcdefg',3,2)

cd

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

SUBSTRING('abcdefg',3,2)

cd

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

SUBSTR('abcdefg',-3,2)

ef

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

SUBSTR('abcdefg',-1,5)

g

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

SUBSTR('abcdefg',0,0)

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

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

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

SUBSTR('abcdefg',1,-1)

create table t7 (s1 char);

select * from t7

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

ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'concat'

drop table t7;

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

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

1abcd;2abcd	3abcd;4abcd

explain extended select md5('hello');

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select md5('hello') AS `md5('hello')`

explain extended select sha('abc');

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select sha('abc') AS `sha('abc')`

explain extended select sha1('abc');

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select sha('abc') AS `sha1('abc')`

explain extended select soundex('');

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select soundex('') AS `soundex('')`

explain extended select 'mood' sounds like 'mud';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select (soundex('mood') = soundex('mud')) AS `'mood' sounds like 'mud'`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select aes_decrypt(aes_encrypt('abc','1'),'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select concat('*',repeat(' ',5),'*') AS `concat('*',space(5),'*')`

explain extended select reverse('abc');

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select reverse('abc') AS `reverse('abc')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select rpad('a',4,'1') AS `rpad('a',4,'1')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select lpad('a',4,'1') AS `lpad('a',4,'1')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select concat_ws(',','',NULL,'a') AS `concat_ws(',','',NULL,'a')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a', _latin2'b', _latin2'c')`

explain extended select elt(2,1);

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select elt(2,1) AS `elt(2,1)`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select locate('a','b',2) AS `locate("a","b",2)`

explain extended select format(130,10);

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select format(130,10) AS `format(130,10)`

explain extended select char(0);

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select char(0) AS `char(0)`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select conv(130,16,10) AS `conv(130,16,10)`

explain extended select hex(130);

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select hex(130) AS `hex(130)`

explain extended select binary 'HE';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select cast('HE' as char charset binary) AS `binary 'HE'`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y', _latin2'n', _latin2' ')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select field(('b' collate latin1_bin),'A','B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B', _latin1'a,b,c,d')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select collation(conv(130,16,10)) AS `collation(conv(130,16,10))`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select length('\n	\r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select bit_length('\n	\r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select bit_length('\n	\r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select concat('monty',' was here ','again') AS `concat('monty',' was here ','again')`

explain extended select length('hello');

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select length('hello') AS `length('hello')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select char(ascii('h')) AS `char(ascii('h'))`

explain extended select ord('h');

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select ord('h') AS `ord('h')`

explain extended select quote(1/0);

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select quote((1 / 0)) AS `quote(1/0)`

explain extended select crc32("123");

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select crc32('123') AS `crc32("123")`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select replace('aaaa','a','b') AS `replace('aaaa','a','b')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select insert('txs',2,1,'hi') AS `insert('txs',2,1,'hi')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select left(_latin2'a',1) AS `left(_latin2'a',1)`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select right(_latin2'a',1) AS `right(_latin2'a',1)`

explain extended select lcase(_latin2'a');

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select lcase(_latin2'a') AS `lcase(_latin2'a')`

explain extended select ucase(_latin2'a');

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select ucase(_latin2'a') AS `ucase(_latin2'a')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select substr('abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select substring_index('1abcd;2abcd;3abcd;4abcd',';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`

explain extended select trim(_latin2' a ');

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select trim(_latin2' a ') AS `trim(_latin2' a ')`

explain extended select ltrim(_latin2' a ');

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`

explain extended select rtrim(_latin2' a ');

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used

Warnings:

Note	1003	select decode(encode(repeat('a',100000),'monty'),'monty') AS `decode(encode(repeat("a",100000),"monty"),"monty")`

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

lpad(12345, 5, "#")

12345

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

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

1Z	71

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

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

NULL	NULL	NULL	NULL	NULL

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;

name

aaaaaaaaaaccccc

bbbbbbbbbbddddd

drop table t1, t2;

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

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

Warnings:

Warning	1264	Out of range value for column 'c1' at row 1

Warning	1264	Out of range value for column 'c2' at row 1

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

Warnings:

Warning	1264	Out of range value for column 'c1' at row 1

Warning	1264	Out of range value for column 'c2' at row 1

show warnings;

Level	Code	Message

Warning	1264	Out of range value for column 'c1' at row 1

Warning	1264	Out of range value for column 'c2' at row 1

select * from t1;

c1	c2

2147483647	4294967295

-2147483648	0

drop table t1;

select left(1234, 3) + 0;

left(1234, 3) + 0

123

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;

total	reg

10	2004-12-10

drop table t1;

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

must_be_null

NULL

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

must_be_null

NULL

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

must_be_null

NULL

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

must_be_null

NULL

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

CONV('e251273eb74a8ee3', 16, 10)

16307858876001849059

EXPLAIN 

SELECT id

FROM t1

WHERE a = 16307858876001849059;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	

EXPLAIN 

SELECT id

FROM t1

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	

DROP TABLE t1;

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

my_column

ySQL

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

my_column

4

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;

id	aes_decrypt(str, 'bar')

1	foo

2	NULL

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

ORDER BY t1.id;

id	aes_decrypt(str, 'bar')

1	foo

2	NULL

DROP TABLE t1, t2;

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

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

3	3	3

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

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

0	0

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;

str	num

notnumber	0

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

str	num

notnumber	0

DROP TABLE t1,t2;

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;

id	col1

1	Main

2	Main->Toys

3	Main->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

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

id	col1

2	Main->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%';

trackname	artistname

April In Paris Vernon Duke	Vernon Duke

Autumn In New York	NULL

DROP TABLE t1,t2,t3;

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;

b	substring(b,1)	substring(b,-1)	substring(b,-2)	substring(b,-3)	substring(b,-4)	substring(b,-5)

ab	ab	b	ab			

abc	abc	c	bc	abc		

abcd	abcd	d	cd	bcd	abcd	

abcde	abcde	e	de	cde	bcde	abcde

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

b	substring(b,1)	substring(b,-1)	substring(b,-2)	substring(b,-3)	substring(b,-4)	substring(b,-5)

ab	ab	b	ab			

abc	abc	c	bc	abc		

abcd	abcd	d	cd	bcd	abcd	

abcde	abcde	e	de	cde	bcde	abcde

drop table t1;

select hex(29223372036854775809), hex(-29223372036854775809);

hex(29223372036854775809)	hex(-29223372036854775809)

FFFFFFFFFFFFFFFF	FFFFFFFFFFFFFFFF

create table t1 (i int);

insert into t1 values (1000000000),(1);

select lpad(i, 7, ' ') as t from t1;

Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr

def					t	253	7	7	Y	128	31	63

t

1000000

      1

select rpad(i, 7, ' ') as t from t1;

Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr

def					t	253	7	7	Y	128	31	63

t

1000000

1      

drop table t1;

select load_file("lkjlkj");

load_file("lkjlkj")

NULL

select ifnull(load_file("lkjlkj"),"it's null");

ifnull(load_file("lkjlkj"),"it's null")

it's null

create table t1 (f1 varchar(4), f2 varchar(64), unique key k1 (f1,f2));

insert into t1 values ( 'test',md5('test')), ('test', sha('test'));

select * from t1 where f1='test' and (f2= md5("test") or f2= md5("TEST"));

f1	f2

test	098f6bcd4621d373cade4e832627b4f6

select * from t1 where f1='test' and (f2= md5("TEST") or f2= md5("test"));

f1	f2

test	098f6bcd4621d373cade4e832627b4f6

select * from t1 where f1='test' and (f2= sha("test") or f2= sha("TEST"));

f1	f2

test	a94a8fe5ccb19ba61c4c0873d391e987982fbbd3

select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test"));

f1	f2

test	a94a8fe5ccb19ba61c4c0873d391e987982fbbd3

drop table t1;

CREATE TABLE t1 (a varchar(10));

INSERT INTO t1 VALUES ('abc'), ('xyz');

SELECT a, CONCAT(a,' ',a) AS c FROM t1

HAVING LEFT(c,LENGTH(c)-INSTR(REVERSE(c)," ")) = a;

a	c

abc	abc abc

xyz	xyz xyz

SELECT a, CONCAT(a,' ',a) AS c FROM t1

HAVING LEFT(CONCAT(a,' ',a),

LENGTH(CONCAT(a,' ',a))-

INSTR(REVERSE(CONCAT(a,' ',a))," ")) = a;

a	c

abc	abc abc

xyz	xyz xyz

DROP TABLE t1;

CREATE TABLE t1 (s varchar(10));

INSERT INTO t1 VALUES ('yadda'), ('yaddy');

EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(s) > 'ab';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where

Warnings:

Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(`test`.`t1`.`s`) > 'ab')

EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM('y' FROM s) > 'ab';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where

Warnings:

Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both 'y' from `test`.`t1`.`s`) > 'ab')

EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(LEADING 'y' FROM s) > 'ab';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where

Warnings:

Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(leading 'y' from `test`.`t1`.`s`) > 'ab')

EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(TRAILING 'y' FROM s) > 'ab';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where

Warnings:

Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(trailing 'y' from `test`.`t1`.`s`) > 'ab')

EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM(BOTH 'y' FROM s) > 'ab';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where

Warnings:

Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both 'y' from `test`.`t1`.`s`) > 'ab')

DROP TABLE t1;

create table t1(f1 varchar(4));

explain extended select encode(f1,'zxcv') as 'enc' from t1;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found

Warnings:

Note	1003	select encode('','zxcv') AS `enc` from `test`.`t1`

explain extended select decode(f1,'zxcv') as 'enc' from t1;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found

Warnings:

Note	1003	select decode('','zxcv') AS `enc` from `test`.`t1`

drop table t1;