~drizzle-trunk/drizzle/development

#

# Test of cast function

#

#

# Bug #28250: Run-Time Check Failure #3 - The variable 'value' is being used 

# without being def

# 

# The following line causes Run-Time Check Failure on 

# binaries built with Visual C++ 2005

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

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

select cast("1:2:3" as TIME);

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

select 10+'10';

select 10.0+'10';

select 10E+0+'10';

# The following cast creates warnings

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

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

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

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

select 10+'a';

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

select 10E+0+'a';

#

# CAST to CHAR with/without length

#

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;

select cast(1000 as CHAR(3));

# Should throw an error about 'abc' being too large for a char(2)

--error 1292

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;

#

# CAST to NCHAR with/without length

#

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;

# BUG in drizzletest - can't handle these chars right

# # Should throw an error about incorrect

#--error 1292

#create table t1 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;

#

# The following should be fixed in 4.1

#

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

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

select cast("1:2:3" as TIME) = "1:02:03";

select cast(NULL as DATE);

select cast(NULL as BINARY);

#

# Bug #5228 ORDER BY CAST(enumcol) sorts incorrectly under certain conditions

#

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

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

# should be in enum order

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

# should be in alphabetic order

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

DROP TABLE t1;

#

# Test for bug #6914 "Problems using time()/date() output in expressions".

# When we are casting datetime value to DATE/TIME we should throw away

# time/date parts (correspondingly).

#

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

select timediff(cast('2004-12-30 12:00:00' as time), '12:00:00');

# Still we should not throw away "days" part of time value

select timediff(cast('1 12:00:00' as time), '12:00:00');

#

# Bug #23938: cast(NULL as DATE)

#

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

#decimal-related additions

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

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

set @v1=1e18;

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

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

create table t1(s1 time);

insert into t1 values ('11:11:11');

select cast(s1 as decimal(7,2)) from t1;

drop table t1;

#

# Test for bug #11283: field conversion from varchar, and text types to decimal

#

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;

DROP TABLE t1;

#

# Bug #10237 (CAST(NULL DECIMAL) crashes server)

#

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

#

# Bug #17903: cast to char results in binary

#

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

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

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

#

# Bug #31990: MINUTE() and SECOND() return bogus results when used on a DATE

#

# Show that HH:MM:SS of a DATE are 0, and that it's the same for columns

# and typecasts (NULL in, NULL out).

CREATE TABLE t1 (f1 DATE);

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

SELECT HOUR(f1),

       MINUTE(f1),

       SECOND(f1) FROM t1;

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

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

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

SELECT HOUR(CAST(NULL AS DATE)),

       MINUTE(CAST(NULL AS DATE)),

       SECOND(CAST(NULL AS DATE));

SELECT HOUR(NULL),

       MINUTE(NULL),

       SECOND(NULL);

DROP TABLE t1;

--echo End of 5.0 tests