~drizzle-trunk/drizzle/development

select cast("A" as binary) = "a", cast(BINARY "a" as CHAR) = "A";

cast("A" as binary) = "a"	cast(BINARY "a" as CHAR) = "A"

0	1

select cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME);

cast("2001-1-1" as DATE)	cast("2001-1-1" as DATETIME)

2001-01-01	2001-01-01 00:00:00

select CONVERT("2004-01-22 21:45:33",DATE);

CONVERT("2004-01-22 21:45:33",DATE)

2004-01-22

select 10+'10';

10+'10'

20

select 10.0+'10';

10.0+'10'

20

select 10E+0+'10';

10E+0+'10'

20

select CONVERT(DATE "2004-01-22 21:45:33",CHAR);

CONVERT(DATE "2004-01-22 21:45:33",CHAR)

2004-01-22 21:45:33

select CONVERT(DATE "2004-01-22 21:45:33",CHAR(4));

CONVERT(DATE "2004-01-22 21:45:33",CHAR(4))

2004

Warnings:

Warning	1292	Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33'

select CONVERT(DATE "2004-01-22 21:45:33",BINARY(4));

CONVERT(DATE "2004-01-22 21:45:33",BINARY(4))

2004

Warnings:

Warning	1292	Truncated incorrect BINARY(4) value: '2004-01-22 21:45:33'

select CAST(DATE "2004-01-22 21:45:33" AS BINARY(4));

CAST(DATE "2004-01-22 21:45:33" AS BINARY(4))

2004

Warnings:

Warning	1292	Truncated incorrect BINARY(4) value: '2004-01-22 21:45:33'

select 10+'a';

10+'a'

10

Warnings:

Warning	1292	Truncated incorrect DOUBLE value: 'a'

select 10.0+cast('a' as decimal);

10.0+cast('a' as decimal)

10.0

Warnings:

Warning	1292	Truncated incorrect DECIMAL value: 'a'

select 10E+0+'a';

10E+0+'a'

10

Warnings:

Warning	1292	Truncated incorrect DOUBLE value: 'a'

select

cast('ab'  AS char)    as c1,

cast('a '  AS char)    as c2,

cast('abc' AS char(2)) as c3,

cast('a  ' AS char(2)) as c4,

hex(cast('a'   AS char(2))) as c5;

c1	c2	c3	c4	c5

ab	a 	ab	a 	61

Warnings:

Warning	1292	Truncated incorrect CHAR(2) value: 'abc'

Warning	1292	Truncated incorrect CHAR(2) value: 'a  '

select cast(1000 as CHAR(3));

cast(1000 as CHAR(3))

100

Warnings:

Warning	1292	Truncated incorrect CHAR(3) value: '1000'

create table t1 select

cast('ab'  AS char)    as c1,

cast('a '  AS char)    as c2,

cast('abc' AS char(2)) as c3,

cast('a  ' AS char(2)) as c4,

cast('a'   AS char(2)) as c5;

ERROR 22007: Truncated incorrect CHAR(2) value: 'abc'

select

cast('ÆÇ'  AS char)    as c1,

cast('Æ '  AS char)    as c2,

cast('ÆÇÈ' AS char(2)) as c3,

cast('Æ  ' AS char(2)) as c4,

cast('Æ'   AS char(2)) as c5;

c1	c2	c3	c4	c5

ÆÇ	Æ 	ÆÇ	Æ 	Æ

Warnings:

Warning	1292	Truncated incorrect CHAR(2) value: 'ÆÇÈ'

Warning	1292	Truncated incorrect CHAR(2) value: 'Æ  '

select cast("2001-1-1" as date) = "2001-01-01";

cast("2001-1-1" as date) = "2001-01-01"

1

select cast("2001-1-1" as datetime) = "2001-01-01 00:00:00";

cast("2001-1-1" as datetime) = "2001-01-01 00:00:00"

1

select cast(NULL as DATE);

cast(NULL as DATE)

NULL

select cast(NULL as BINARY);

cast(NULL as BINARY)

NULL

CREATE TABLE t1 (a enum ('aac','aab','aaa') not null);

INSERT INTO t1 VALUES ('aaa'),('aab'),('aac');

SELECT a, CAST(a AS CHAR(3)) FROM t1 ORDER BY CAST(a AS CHAR(2)), a;

a	CAST(a AS CHAR(3))

aac	aac

aab	aab

aaa	aaa

Warnings:

Warning	1292	Truncated incorrect CHAR(2) value: 'aaa'

Warning	1292	Truncated incorrect CHAR(2) value: 'aab'

Warning	1292	Truncated incorrect CHAR(2) value: 'aac'

SELECT a, CAST(a AS CHAR(2)) FROM t1 ORDER BY CAST(a AS CHAR(3)), a;

a	CAST(a AS CHAR(2))

aaa	aa

aab	aa

aac	aa

Warnings:

Warning	1292	Truncated incorrect CHAR(2) value: 'aaa'

Warning	1292	Truncated incorrect CHAR(2) value: 'aab'

Warning	1292	Truncated incorrect CHAR(2) value: 'aac'

DROP TABLE t1;

select date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour);

date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour)

2004-12-30 00:00:00

select isnull(date(NULL)), isnull(cast(NULL as DATE));

isnull(date(NULL))	isnull(cast(NULL as DATE))

1	1

select cast('1.2' as decimal(3,2));

cast('1.2' as decimal(3,2))

1.20

select 1e18 * cast('1.2' as decimal(3,2));

1e18 * cast('1.2' as decimal(3,2))

1.2e18

set @v1=1e18;

select cast(@v1 as decimal(22, 2));

cast(@v1 as decimal(22, 2))

1000000000000000000.00

select cast(-1e18 as decimal(22,2));

cast(-1e18 as decimal(22,2))

-1000000000000000000.00

CREATE TABLE t1 (v varchar(10), tt tinytext, t text,

mt mediumtext, lt longtext);

INSERT INTO t1 VALUES ('1.01', '2.02', '3.03', '4.04', '5.05');

SELECT CAST(v AS DECIMAL), CAST(tt AS DECIMAL), CAST(t AS DECIMAL),

CAST(mt AS DECIMAL), CAST(lt AS DECIMAL) from t1;

CAST(v AS DECIMAL)	CAST(tt AS DECIMAL)	CAST(t AS DECIMAL)	CAST(mt AS DECIMAL)	CAST(lt AS DECIMAL)

1	2	3	4	5

DROP TABLE t1;

select cast(NULL as decimal(6)) as t1;

t1

NULL

select hex(cast('a' as char(2) binary));

hex(cast('a' as char(2) binary))

61

select hex(cast('a' as binary(2)));

hex(cast('a' as binary(2)))

6100

select hex(cast('a' as char(2) binary));

hex(cast('a' as char(2) binary))

6100

CREATE TABLE t1 (f1 DATE);

INSERT INTO t1 VALUES ('2007-07-19'), (NULL);

SELECT HOUR(f1),

MINUTE(f1),

SECOND(f1) FROM t1;

HOUR(f1)	MINUTE(f1)	SECOND(f1)

0	0	0

NULL	NULL	NULL

SELECT HOUR(CAST('2007-07-19' AS DATE)),

MINUTE(CAST('2007-07-19' AS DATE)),

SECOND(CAST('2007-07-19' AS DATE));

HOUR(CAST('2007-07-19' AS DATE))	MINUTE(CAST('2007-07-19' AS DATE))	SECOND(CAST('2007-07-19' AS DATE))

0	0	0

SELECT HOUR(CAST(NULL AS DATE)),

MINUTE(CAST(NULL AS DATE)),

SECOND(CAST(NULL AS DATE));

HOUR(CAST(NULL AS DATE))	MINUTE(CAST(NULL AS DATE))	SECOND(CAST(NULL AS DATE))

NULL	NULL	NULL

SELECT HOUR(NULL),

MINUTE(NULL),

SECOND(NULL);

HOUR(NULL)	MINUTE(NULL)	SECOND(NULL)

NULL	NULL	NULL

DROP TABLE t1;

End of 5.0 tests