test-example-projects/c++_projects/280w-mysql-8.0.18/mysql-test/t/cast.test

 #Get deafult engine value
--let $DEFAULT_ENGINE = `select @@global.default_storage_engine`

#
# 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 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(TIMESTAMP "2004-01-22 21:45:33" USING latin1);
SELECT CONVERT(TIMESTAMP "2004-01-22 21:45:33", CHAR);
SELECT CONVERT(TIMESTAMP "2004-01-22 21:45:33", CHAR(4));
SELECT CONVERT(TIMESTAMP "2004-01-22 21:45:33", BINARY(4));
select CAST(TIMESTAMP "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'<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' as char character set cp1251);
create table t1 select cast(_koi8r'<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' as char character set cp1251) as t;

#Replace default engine value with static engine string 
--replace_result $DEFAULT_ENGINE ENGINE
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));
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
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;

#Replace default engine value with static engine string 
--replace_result $DEFAULT_ENGINE ENGINE
show create table t1;
drop table t1;

#
# CAST to NCHAR with/without length
#

# Different number of warnings with ps protocol.
--disable_ps_protocol
select
  cast(_koi8r'<EFBFBD><EFBFBD>'  AS nchar)    as c1,
  cast(_koi8r'<EFBFBD> '  AS nchar)    as c2,
  cast(_koi8r'<EFBFBD><EFBFBD><EFBFBD>' AS nchar(2)) as c3,
  cast(_koi8r'<EFBFBD>  ' AS nchar(2)) as c4,
  cast(_koi8r'<EFBFBD>'   AS nchar(2)) as c5;
--enable_ps_protocol

create table t1 select
  cast(_koi8r'<EFBFBD><EFBFBD>'  AS nchar)    as c1,
  cast(_koi8r'<EFBFBD> '  AS nchar)    as c2,
  cast(_koi8r'<EFBFBD><EFBFBD><EFBFBD>' AS nchar(2)) as c3,
  cast(_koi8r'<EFBFBD>  ' AS nchar(2)) as c4,
  cast(_koi8r'<EFBFBD>'   AS nchar(2)) as c5;
select * from t1;

#Replace default engine value with static engine string 
--replace_result $DEFAULT_ENGINE ENGINE
show create table t1;
drop table t1;

--echo #
--echo # Bug #24934161: FAILURE OF SYNONYMY OF NCHAR AND NATIONAL CHAR
--echo #

# Different number of warnings with ps protocol.
--disable_ps_protocol
SELECT
  CAST(_gb2312 x'CAFDBEDD'     AS NATIONAL CHAR)    AS c1,
  CAST(_gb2312 x'CAFD20'       AS NATIONAL CHAR)    AS c2,
  CAST(_gb2312 x'CAFDBEDDBFE2' AS NATIONAL CHAR(2)) AS c3,
  CAST(_gb2312 x'CAFD2020'     AS NATIONAL CHAR(2)) AS c4,
  CAST(_gb2312 x'CAFD'         AS NATIONAL CHAR(2)) AS c5;
--enable_ps_protocol

CREATE TABLE t1 SELECT
  CAST(_gb2312 x'CAFDBEDD'     AS NATIONAL CHAR)    AS c1,
  CAST(_gb2312 x'CAFD20'       AS NATIONAL CHAR)    AS c2,
  CAST(_gb2312 x'CAFDBEDDBFE2' AS NATIONAL CHAR(2)) AS c3,
  CAST(_gb2312 x'CAFD2020'     AS NATIONAL CHAR(2)) AS c4,
  CAST(_gb2312 x'CAFD'         AS NATIONAL CHAR(2)) AS c5;
SELECT * FROM t1;
SHOW CREATE TABLE t1;
DROP TABLE t1;

SET sql_mode = default;
#
# 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'<EFBFBD><EFBFBD><EFBFBD><EFBFBD>',_binary'<EFBFBD><EFBFBD><EFBFBD><EFBFBD>');
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

--echo #
--echo #  Bug #44766: valgrind error when using convert() in a subquery
--echo #

CREATE TABLE t1(a tinyint);
INSERT INTO t1 VALUES (127);
SELECT 1 FROM
(
 SELECT CONVERT(t2.a USING UTF8) FROM t1, t1 t2 LIMIT 1
) AS s LIMIT 1;
DROP TABLE t1;

--echo #
--echo # Bug #11765023: 57934: DOS POSSIBLE SINCE BINARY CASTING 
--echo #   DOESN'T ADHERE TO MAX_ALLOWED_PACKET

SET @@GLOBAL.max_allowed_packet=2048;
# reconnect to make the new max packet size take effect
--connect (newconn, localhost, root,,)

SELECT CONVERT('a', BINARY(2049));  
SELECT CONVERT('a', CHAR(2049));  

connection default;
disconnect newconn;
SET @@GLOBAL.max_allowed_packet=default;

--echo #
--echo # Bug#13519724 63793: CRASH IN DTCOLLATION::SET(DTCOLLATION &SET)
--echo #

CREATE TABLE t1 (a VARCHAR(50));

SELECT a FROM t1 
WHERE CAST(a as BINARY)=x'62736D697468' 
  AND CAST(a AS BINARY)=x'65736D697468';

DROP TABLE t1;

--echo End of 5.1 tests

--echo #
--echo # Bug#22885819: CAST( .. AS BINARY(N)) GETS UNEXPECTED NULL
--echo #
SELECT CAST( 'a' AS BINARY(429496729));
SELECT CAST( 'a' AS BINARY(4294967294));
SELECT CAST( 'a' AS BINARY(4294967295));
--error ER_TOO_BIG_DISPLAYWIDTH
SELECT CAST( 'a' AS BINARY(4294967296));
--error ER_TOO_BIG_DISPLAYWIDTH
SELECT CAST( 'a' AS BINARY(4294967296784564));

--echo #
--echo # Bug#13581962 HIGH MEMORY USAGE ATTEMPT, THEN CRASH WITH LONGTEXT, UNION, USER VARIABLE
--echo #
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1 AS SELECT CONCAT(CAST(REPEAT('9', 1000) AS SIGNED)),
                          CONCAT(CAST(REPEAT('9', 1000) AS UNSIGNED));

#Replace default engine value with static engine string 
--replace_result $DEFAULT_ENGINE ENGINE
SHOW CREATE TABLE t1;
DROP TABLE t1;
SET sql_mode = default;
--echo End of 5.5 tests

--echo #
--echo # Bug#28547906 ENUM TYPE CASTING WORKS WRONG WITH SUBQUERIES
--echo #
CREATE TABLE t (c1 ENUM('a','b','c'));
INSERT INTO t VALUES ('a'), ('b'), ('c');
SELECT CAST(c1 AS UNSIGNED) AS c5 FROM t;
SELECT CAST(c1 AS UNSIGNED) AS c5 FROM (SELECT c1 FROM t) t;
DROP TABLE t;

--echo #
--echo # CAST as DOUBLE/FLOAT/REAL
--echo #
SELECT CAST(1/3 AS FLOAT) as float_col,
       CAST(1/3 AS DOUBLE) as double_col,
       CAST(1/3 AS DOUBLE PRECISION) as double_prec_col,
       CAST(1/3 AS REAL) as real_col;

SELECT CAST(1/3 AS FLOAT(10)), CAST(1/3 AS FLOAT(53));
--error ER_PARSE_ERROR
SELECT CAST(1/3 AS FLOAT(-1));
--error ER_TOO_BIG_PRECISION
SELECT CAST(1/3 AS FLOAT(54));
--error ER_PARSE_ERROR
SELECT CAST(1/3 AS DOUBLE(52));
--error ER_PARSE_ERROR
SELECT CAST(1/3 AS REAL(34));

--error ER_PARSE_ERROR
SELECT CAST(999.00009 AS FLOAT(7,4)) as float_col;
--error ER_PARSE_ERROR
SELECT CAST(999.00009 AS DOUBLE(7,4)) as double_col;
--error ER_PARSE_ERROR
SELECT CAST(999.00009 AS REAL(7,4)) as real_col;

SELECT ADDDATE(CAST("20010101235959.9" AS DOUBLE), INTERVAL 1 DAY);
SELECT TIMEDIFF(CAST("101112" AS DOUBLE), TIME'101010');

SELECT CAST(DATE'2000-01-01' AS FLOAT), CAST(DATE'2000-01-01' AS DOUBLE);
SELECT CAST(TIME'23:59:59' AS FLOAT), CAST(TIME'23:59:59' AS DOUBLE);
SELECT CAST(TIME'23:59:59.123456' AS FLOAT),
       CAST(TIME'23:59:59.123456' AS DOUBLE);
SELECT CAST(TIMESTAMP'2000-01-01 23:59:59' AS FLOAT),
       CAST(TIMESTAMP'2000-01-01 23:59:59' AS DOUBLE);
SELECT CAST(TIMESTAMP'2000-01-01 23:59:59.123456' AS FLOAT),
       CAST(TIMESTAMP'2000-01-01 23:59:59.123456' AS DOUBLE);

CREATE TABLE t1 as SELECT CAST(1/3 AS FLOAT) as float_col,
                          CAST(1/3 AS DOUBLE) as double_col,
                          CAST(CAST(999.00009 AS DECIMAL(7,4)) AS DOUBLE) as d2;
SHOW CREATE TABLE t1;
DROP TABLE t1;

# Function that forces Item_typecast_real::val_int() to be called to generate overflow
--error ER_DATA_OUT_OF_RANGE
SELECT PERIOD_ADD(200905, CAST(3.14e19 AS DOUBLE));
SELECT -1.0 *  CAST(3.14e19 AS DOUBLE);
--error ER_DATA_OUT_OF_RANGE
SELECT CAST("3.14e100" AS FLOAT);
--error ER_DATA_OUT_OF_RANGE
SELECT CAST(-1e308 as FLOAT);
SELECT CONCAT("value=", CAST("3.4e5" AS FLOAT));

CREATE VIEW v1 AS SELECT CAST(1/3 AS REAL), CAST(1/3 AS FLOAT(2)), CAST(1/3 AS FLOAT(50));
SHOW CREATE VIEW v1;
DROP VIEW v1;


SELECT CAST(NULL AS REAL), CAST(NULL AS FLOAT), CAST(NULL AS DOUBLE);

SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=REAL_AS_FLOAT;
CREATE TABLE t AS SELECT CAST(34 AS REAL);
SHOW CREATE TABLE t;
DROP TABLE t;
SET @@SQL_MODE=@OLD_SQL_MODE;
CREATE TABLE t AS SELECT CAST(34 AS REAL);
SHOW CREATE TABLE t;
DROP TABLE t;

SELECT MAKETIME(1, 2, CAST("1.6" AS FLOAT));

--echo #
--echo # WL#12108: Inject type cast nodes into the item tree to avoid data
--echo # type mismatches.
--echo #

CREATE TABLE dt_t (dt DATETIME, d DATE, t TIME);
CREATE TABLE n_t (i INT, d DECIMAL, f FLOAT, dc DECIMAL);

--echo #
--echo # DATETIME + NUMERICS
--echo #
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.dt = n_t.i;
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.dt = n_t.d;
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.dt = n_t.f;

--echo #
--echo # DATE + NUMERICS
--echo #
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.d = n_t.i;
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.d = n_t.d;
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.d = n_t.f;
EXPLAIN SELECT * from dt_t JOIN n_t on dt_t.d = n_t.dc;

--echo #
--echo # TIME + NUMERICS
--echo #
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.t = n_t.i;
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.t = n_t.d;
EXPLAIN SELECT * from dt_t JOIN n_t ON dt_t.t = n_t.f;

--echo #
--echo # DATETIME + DATE
--echo #
EXPLAIN SELECT * from dt_t dt1 JOIN dt_t dt2 ON dt1.dt = dt2.d;

--echo #
--echo # DATETIME + TIME
--echo #
EXPLAIN SELECT * from dt_t dt1 JOIN dt_t dt2 ON dt1.dt = dt2.t;

--echo #
--echo # DATE + TIME
--echo #
EXPLAIN SELECT * from dt_t dt1 JOIN dt_t dt2 ON dt1.d = dt2.t;

# No CAST is expected when the data type is the same
EXPLAIN SELECT * FROM dt_t dt1 JOIN dt_t dt2 ON dt1.d = dt2.d;

# In the SELECT list
EXPLAIN SELECT dt_t.dt = n_t.i from dt_t, n_t;
# In HAVING expression
EXPLAIN SELECT MAX(dt_t.d) AS max_d, MAX(n_t.i) AS max_i
FROM dt_t, n_t HAVING max_d = max_i;
# In ORDER BY clause
EXPLAIN SELECT dt=d from dt_t ORDER BY dt = d;
EXPLAIN SELECT * from dt_t ORDER BY dt = d;
# In WINDOW ORDER BY clause
EXPLAIN SELECT d=t, LEAD(d,1) OVER w FROM dt_t WINDOW w AS (ORDER BY d=t);
EXPLAIN SELECT LEAD(d,1) OVER w FROM dt_t WINDOW w AS (ORDER BY d=t);

DROP TABLE dt_t, n_t;

# When an argument is a grouped or windowed aggregate function
CREATE TABLE t1 (spID int, userID int, date date);

INSERT INTO t1 VALUES (1,1,'1998-01-01');
INSERT INTO t1 VALUES (2,2,'2001-02-03');
INSERT INTO t1 VALUES (3,1,'1988-12-20');
INSERT INTO t1 VALUES (4,2,'1972-12-12');

EXPLAIN SELECT MIN(t1.userID) = MIN(date) FROM t1  GROUP BY userid;
EXPLAIN SELECT FIRST_VALUE(date) OVER (ORDER BY spID = date) FROM t1;
EXPLAIN SELECT date, spid = FIRST_VALUE(date) OVER (ORDER BY date ) FROM t1;

--echo #
--echo # No casts when constants or constant functions are involved
--echo #
EXPLAIN SELECT * from t1 WHERE userID = DATE'2012-02-20';
EXPLAIN SELECT * FROM t1 WHERE date = NULL;
EXPLAIN SELECT * FROM t1 WHERE date = CAST('20:21:22' AS TIME);

DROP TABLE t1;