~drizzle-trunk/drizzle/development

#

# Test of cast function

#

select CAST(1-2 AS UNSIGNED);

select CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER);

select CAST('10 ' as unsigned integer);

select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1;

select cast(-5 as unsigned) -1, cast(-5 as unsigned) + 1;

select ~5, cast(~5 as signed);

explain extended select ~5, cast(~5 as signed);

select cast(5 as unsigned) -6.0;

select cast(NULL as signed), cast(1/0 as signed);

#

# 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(NULL as unsigned), cast(1/0 as unsigned); 

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

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 CAST(0xb3 as signed);

select CAST(0x8fffffffffffffff as signed);

select CAST(0xffffffffffffffff as unsigned);

select CAST(0xfffffffffffffffe as signed);

select cast('-10a' as signed integer);

select cast('a10' as unsigned integer);

select 10+'a';

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

select 10E+0+'a';

# out-of-range cases

select cast('18446744073709551616' as unsigned);

select cast('18446744073709551616' as signed);

select cast('9223372036854775809' as signed);

select cast('-1' as unsigned);

select cast('abc' as signed);

select cast('1a' as signed);

select cast('' as signed);

#

# Character set conversion

#

set names binary;

select cast(_latin1'test' as char character set latin2);

select cast(_koi8r'ÔÅÓÔ' as char character set cp1251);

create table t1 select cast(_koi8r'ÔÅÓÔ' as char character set cp1251) as t;

show create table t1;

drop table t1;

#

# CAST to CHAR with/without length

#

select

  cast(_latin1'ab'  AS char)    as c1,

  cast(_latin1'a '  AS char)    as c2,

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

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

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

select cast(1000 as CHAR(3));

create table t1 select

  cast(_latin1'ab'  AS char)    as c1,

  cast(_latin1'a '  AS char)    as c2,

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

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

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

select c1,c2,c3,c4,hex(c5) from t1;

show create table t1;

drop table t1;

#

# CAST to NCHAR with/without length

#

select

  cast(_koi8r'ÆÇ'  AS nchar)    as c1,

  cast(_koi8r'Æ '  AS nchar)    as c2,

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

  cast(_koi8r'Æ  ' AS nchar(2)) as c4,

  cast(_koi8r'Æ'   AS nchar(2)) as c5;

create table t1 select

  cast(_koi8r'ÆÇ'  AS nchar)    as c1,

  cast(_koi8r'Æ '  AS nchar)    as c2,

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

  cast(_koi8r'Æ  ' AS nchar(2)) as c4,

  cast(_koi8r'Æ'   AS nchar(2)) as c5;

select * from t1;

show create table t1;

drop table t1;

#

# Bug 2202

# CAST from BINARY to non-BINARY and from non-BINARY to BINARY

#

create table t1 (a binary(4), b char(4) character set koi8r);

insert into t1 values (_binary'ÔÅÓÔ',_binary'ÔÅÓÔ');

select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;

set names koi8r;

select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;

set names cp1251;

select a,b,cast(a as char character set cp1251),cast(b as binary) from t1;

drop table t1;

set names binary;

#

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

# these two should be in enum order

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

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

# these two should be in alphabetic order

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

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 #7036: Casting from string to unsigned would cap value of result at

# maximum signed value instead of maximum unsigned value

#

select cast(18446744073709551615 as unsigned);

select cast(18446744073709551615 as signed);

select cast('18446744073709551615' as unsigned);

select cast('18446744073709551615' as signed);

select cast('9223372036854775807' as signed);

select cast(concat('184467440','73709551615') as unsigned);

select cast(concat('184467440','73709551615') as signed);

select cast(repeat('1',20) as unsigned);

select cast(repeat('1',20) as signed);

#

# Bug #13344: cast of large decimal to signed int not handled correctly

#

select cast(1.0e+300 as signed int);

#

# Bugs: #15098: CAST(column double TO signed int), wrong result

#

CREATE TABLE t1 (f1 double);

INSERT INTO t1 SET f1 = -1.0e+30 ;

INSERT INTO t1 SET f1 = +1.0e+30 ;

SELECT f1 AS double_val, CAST(f1 AS SIGNED INT) AS cast_val FROM t1;

DROP TABLE t1;					   

#

# Bug #23938: cast(NULL as DATE)

#

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

#

# Bug#23656: Wrong result of CAST from DATE to int

#

SELECT CAST(cast('01-01-01' as date) AS UNSIGNED);

SELECT CAST(cast('01-01-01' as date) AS SIGNED);

--echo End of 4.1 tests

#decimal-related additions

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

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

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

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

#

set names latin1;

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

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

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

#

# Bug#29898: Item_date_typecast::val_int doesn't reset the null_value flag.

#

CREATE TABLE t1 (d1 datetime);

INSERT INTO t1(d1) VALUES ('2007-07-19 08:30:00'), (NULL),

  ('2007-07-19 08:34:00'), (NULL), ('2007-07-19 08:36:00');

SELECT cast(date(d1) as signed) FROM t1;

drop table t1;

#

# 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