# # 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; --echo End of 5.0 tests