github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_cast.test

select CAST(1-2 AS UNSIGNED);
select CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER);
select CAST('10 ' as unsigned integer);
select CAST('10x' 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 cast(5 as unsigned) -6.0;
select cast(NULL as signed);
select cast(1/2 as signed);
#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");
#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';
-- @bvt:issue#3280
select 10.0+cast('a' as decimal);
-- @bvt:issue
-- @bvt:issue#3276
select 10E+0+'a';
-- @bvt:issue

# 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
#
#select hex(cast(_latin1'test' as char character set latin2));
#select hex(cast(_koi8r x'D4C5D3D4' as char character set cp1251));
#create table t1 select cast(_koi8r x'D4C5D3D4' as char character set cp1251) as t;

#Replace default engine value with static engine string 
#show create table t1;
#drop table t1;

#
# CAST to CHAR with/without length
#
# select
#   cast(_latin1'ab'  AS char charset binary)    as c1,
#   cast(_latin1'a '  AS char charset binary)    as c2,
#   cast(_latin1'abc' AS char(2) charset binary) as c3,
#   cast(_latin1'a  ' AS char(2) charset binary) as c4,
#   hex(cast(_latin1'a'   AS char(2) charset binary)) as c5;
# select cast(1000 as CHAR(3) charset binary);
# SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
# create table t1 select
#   cast(_latin1'ab'  AS char charset binary)    as c1,
#   cast(_latin1'a '  AS char charset binary)    as c2,
#   cast(_latin1'abc' AS char(2) charset binary) as c3,
#   cast(_latin1'a  ' AS char(2) charset binary) as c4,
#   cast(_latin1'a'   AS char(2) charset binary) 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 x'C6C7'   AS nchar)    as c1,
#   cast(_koi8r x'C620'   AS nchar)    as c2,
#   cast(_koi8r x'C6C7C8' AS nchar(2)) as c3,
#   cast(_koi8r x'C62020' AS nchar(2)) as c4,
#   cast(_koi8r x'C6'     AS nchar(2)) as c5;
# #enable_ps_protocol

# create table t1 select
#   cast(_koi8r x'C6C7'   AS nchar)    as c1,
#   cast(_koi8r x'C620'   AS nchar)    as c2,
#   cast(_koi8r x'C6C7C8' AS nchar(2)) as c3,
#   cast(_koi8r x'C62020' AS nchar(2)) as c4,
#   cast(_koi8r x'C6'     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 x'D4C5D3D4',_binary x'D4C5D3D4');
# select hex(a),hex(b),hex(cast(a as char character set cp1251)),hex(cast(b as binary)) from t1;
# drop table t1;

#
# 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 charset binary) FROM t1 ORDER BY CAST(a AS UNSIGNED) ;
# SELECT a, CAST(a AS CHAR(3) charset binary) FROM t1 ORDER BY CAST(a AS CHAR(2) charset binary), 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 UNSIGNED) FROM t1 ORDER BY CAST(a AS CHAR charset binary) ;
# SELECT a, CAST(a AS CHAR(2) charset binary ) FROM t1 ORDER BY CAST(a AS CHAR(3) charset binary), 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_ws('184467440','73709551615') as unsigned);
select cast(concat_ws('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;					   

-- @bvt:issue#3257
SELECT CAST(cast('01-01-01' as date) AS UNSIGNED);
SELECT CAST(cast('01-01-01' as date) AS SIGNED);
-- @bvt:issue
#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);

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

create table t1(s1 timestamp);
insert into t1 values ('2020-12-03 11:11:11');
-- @bvt:issue#3257
select cast(s1 as decimal(7,2)) from t1;
-- @bvt:issue
drop table t1;

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

CREATE TABLE t1 (v varchar(10), tt char(255), t char(255),mt char(255), lt char(255));
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');
-- @bvt:issue#3293
SELECT cast(date(d1) as signed) FROM t1;
-- @bvt:issue
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 #
# #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 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 # Bug#31023252: RESULTSET MISMATCH USING STRCMP() WITH DATE AND STRING DATATYPE
# #echo #
#  CREATE TABLE t1(a YEAR, b VARCHAR(10));
#  INSERT INTO t1 VALUES ('1997','random_str');
#  SELECT STRCMP(a, b) FROM t1;
#  DROP TABLE t1;

#echo #
#echo # Bug#30626100: WL13456 RESULTSET DISTINCT DIFFERENCE
#echo #
CREATE TABLE t (col_datetime datetime, col_date date, col_char char);
insert into t values ('2013-03-15 18:35:20', '2013-03-15','L'),('2003-01-10 00:00:23', '2003-01-10', NULL);


#skip_if_hypergraph  # Different warnings.

SELECT CAST(col_char AS DATETIME) FROM t;
#echo
#skip_if_hypergraph  # Different warnings.
SELECT col_char <> col_datetime FROM t;

#echo
#skip_if_hypergraph  # Different warnings.
SELECT CAST(col_char AS DATE) FROM t;


#echo
#skip_if_hypergraph  # Different warnings.
-- @bvt:issue#3253
SELECT col_char <> col_date FROM t;
-- @bvt:issue
#SELECT CAST(col_char as TIME) FROM t;

DROP TABLE t;

#echo #
#echo # Bug#31095719 WL13456 RESULT SET COMPARISON DIFFERENCE WITH JOINS
#echo #
CREATE TABLE `BB` (`col_char_key` char(1));
CREATE TABLE `CC` ( `pk` int, `col_datetime_key` datetime);
INSERT INTO `BB` VALUES ('X');
INSERT INTO `CC` VALUES (1,'2027-03-17 00:10:00'), (2,'2004-11-14 12:46:43');
#skip_if_hypergraph  # Different warnings.
-- @bvt:issue#3254
SELECT COUNT(table1.pk) FROM `CC` table1 JOIN `BB` table3 JOIN `CC` table2
WHERE (table3.col_char_key < table2.col_datetime_key);
-- @bvt:issue
DROP TABLE `BB`;
DROP TABLE `CC`;

#echo #
#echo # CAST AS YEAR
#echo #

# # int values
# SELECT CAST(CAST(-1 AS SIGNED) AS YEAR);
# SELECT CAST(CAST(-99 AS SIGNED) AS YEAR);
# SELECT CAST(CAST(0 AS SIGNED) AS YEAR);
# SELECT CAST(CAST(69 AS SIGNED) AS YEAR);
# SELECT CAST(CAST(70 AS SIGNED) AS YEAR);
# SELECT CAST(CAST(99 AS SIGNED) AS YEAR);
# SELECT CAST(CAST(100 AS SIGNED) AS YEAR);
# SELECT CAST(CAST(2010 AS SIGNED) AS YEAR);

# #floating point values
# SELECT CAST(-1.1 AS YEAR);
# SELECT CAST(1.1 AS YEAR);
# SELECT CAST(0.0 AS YEAR);
# SELECT CAST(69.1 AS YEAR);
# SELECT CAST(70.1 AS YEAR);
# SELECT CAST(100.1 AS YEAR);
# SELECT CAST(2010.9 AS YEAR);

# #decimal values
# SELECT CAST(CAST(-1.1 AS DECIMAL) AS YEAR);
# SELECT CAST(CAST(1.1 AS DECIMAL) AS YEAR);
# SELECT CAST(CAST(0.0 AS DECIMAL) AS YEAR);
# SELECT CAST(CAST(69.1 AS DECIMAL) AS YEAR);
# SELECT CAST(CAST(70.1 AS DECIMAL) AS YEAR);
# SELECT CAST(CAST(100.1 AS DECIMAL) AS YEAR);
# SELECT CAST(CAST(2010.9 AS DECIMAL) AS YEAR);

# #string values
# SELECT CAST("-1" AS YEAR);
# SELECT CAST("-99" AS YEAR);
# SELECT CAST("0" AS YEAR);
# SELECT CAST("69" AS YEAR);
# SELECT CAST("70" AS YEAR);
# SELECT CAST("99" AS YEAR);
# SELECT CAST("100" AS YEAR);
# SELECT CAST("2010" AS YEAR);
# SELECT CAST("extra" AS YEAR);
# SELECT CAST("22extra" AS YEAR);
# SELECT CAST("2020extra" AS YEAR);

# SET timestamp = UNIX_TIMESTAMP('2020-12-22 03:30:00');
# #date[time] values
# SELECT CAST(TIMESTAMP'2010-01-01 00:00' AS YEAR);
# SET SQL_MODE = "";
# SELECT CAST(TIMESTAMP'0000-00-00 00:00' AS YEAR);
# SET SQL_MODE = default;
# SELECT CAST(TIMESTAMP'2010-01-01 08:09:10' AS YEAR);
# SELECT CAST(TIME'08:09:10' AS YEAR);
# SELECT CAST(TIME'00:00:00' AS YEAR);
# SET timestamp = DEFAULT;

# #geometry values
# #error ER_WRONG_ARGUMENTS
# SELECT CAST(ST_PointFromText('POINT(10 10)') AS YEAR);

# #CREATE AS SELECT
# CREATE TABLE t AS SELECT CAST("2010" AS YEAR);
# SHOW CREATE TABLE t;
# DROP TABLE t;

# #json values
# SELECT CAST(JSON_EXTRACT('{"key_year":1934}', '$.key_year') AS YEAR);
# SELECT CAST(CAST('{"_id":"192312412512"}' AS JSON) AS YEAR);

# CREATE TABLE t1 (i INT, j JSON) CHARSET utf8mb4;
# INSERT INTO t1 VALUES (0, NULL);
# INSERT INTO t1 VALUES (1, '"1901"');
# INSERT INTO t1 VALUES (2, 'true');
# INSERT INTO t1 VALUES (3, 'false');
# INSERT INTO t1 VALUES (4, 'null');
# INSERT INTO t1 VALUES (5, '-1');
# INSERT INTO t1 VALUES (6, CAST(CAST(1 AS UNSIGNED) AS JSON));
# INSERT INTO t1 VALUES (7, '1901');
# INSERT INTO t1 VALUES (8, '-1901');
# INSERT INTO t1 VALUES (9, '2147483647');
# INSERT INTO t1 VALUES (10, '2147483648');
# INSERT INTO t1 VALUES (11, '-2147483648');
# INSERT INTO t1 VALUES (12, '-2147483649');
# INSERT INTO t1 VALUES (13, '3.14');
# INSERT INTO t1 VALUES (14, '{}');
# INSERT INTO t1 VALUES (15, '[]');
# INSERT INTO t1 VALUES (16, CAST(CAST('2015-01-15 23:24:25' AS DATETIME) AS JSON));
# INSERT INTO t1 VALUES (17, CAST(CAST('23:24:25' AS TIME) AS JSON));
# INSERT INTO t1 VALUES (18, CAST(CAST('2015-01-15' AS DATE) AS JSON));
# INSERT INTO t1 VALUES (19, CAST(TIMESTAMP'2015-01-15 23:24:25' AS JSON));
# INSERT INTO t1 VALUES (20, CAST(ST_GeomFromText('POINT(1 1)') AS JSON));
# INSERT INTO t1 VALUES (21, CAST('1988' AS CHAR CHARACTER SET 'ascii'));
# INSERT INTO t1 VALUES (22, CAST(x'07C4' AS JSON));
# INSERT INTO t1 VALUES (23, CAST(x'07C407C4' AS JSON));
# SELECT i, CAST(j AS YEAR), CAST(j AS SIGNED) FROM t1 ORDER BY i;
# DROP TABLE t1;

# # enum values
# CREATE TABLE t(numbers ENUM('0','1','2020'), colors ENUM('red', 'green', 'blue'));
# INSERT INTO t values('2020', 'blue');
# SELECT CAST(numbers AS YEAR), CAST(colors AS YEAR) FROM t;
# DROP TABLE t;

# # with/without strict mode
# CREATE TABLE t(y YEAR);
# #error ER_TRUNCATED_WRONG_VALUE
# INSERT INTO t values(CAST("2020extra" AS YEAR));
# #error ER_TRUNCATED_WRONG_VALUE
# INSERT INTO t values(CAST(20201 AS YEAR));

# SET SQL_MODE = "";
# INSERT INTO t values(CAST("2020extra" AS YEAR));
# INSERT INTO t values(CAST(20201 AS YEAR));
# SELECT * FROM t;
# SET SQL_MODE = default;
# DROP TABLE t;

# SELECT CAST(1988 AS YEAR), REPEAT(CAST(1988 AS YEAR), 3) AS c2;
# SELECT CONCAT_WS('x', CAST(1988 AS YEAR));
# SELECT CAST(1988 AS YEAR) + 1.5e0;
# SELECT CAST(CAST(1988 AS YEAR) AS DECIMAL);
# SELECT DATE_ADD(CAST(1988 AS YEAR), INTERVAL 1 DAY);
# SELECT CAST(CAST(1988 AS YEAR) AS SIGNED);
# SELECT CAST(CAST(1988 AS YEAR) AS UNSIGNED);
# SELECT CAST(TIMESTAMP'579-10-10 10:10:10' AS YEAR);

#echo # WL#14109: Implement a consistent comparison type rule matrix

# Check CAST into temporal values and mode settings

create table t1(f1 date, f2 timestamp, f3 datetime);
insert into t1 values ("2006-01-01", "2006-01-01 12:01:01", "2006-01-01 12:01:01");
insert into t1 values ("2006-01-02", "2006-01-02 12:01:02", "2006-01-02 12:01:02");

select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date);
select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date);
select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);
select f3 from t1 where f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
select f1 from t1 where cast("2006-1-1" as date) between f1 and f3;
select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3);
select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date);

drop table t1;



create table t1 (field DATE);
insert into t1 values ('2006-11-06');
-- @bvt:issue#3253
select * from t1 where field < '2006-11-06 04:08:36.0';
select * from t1 where field = '2006-11-06 04:08:36.0';
select * from t1 where field = '2006-11-06';
-- @bvt:issue
-- @bvt:issue#3254
select * from t1 where CAST(field as DATETIME) < '2006-11-06 04:08:36.0';
-- @bvt:issue
-- @bvt:issue#3253
select * from t1 where CAST(field as DATE) < '2006-11-06 04:08:36.0';
-- @bvt:issue
drop table t1;

create table t1 (a int(11) unsigned, b int(11) unsigned);
insert into t1 values (1,0), (1,1), (4294967295,1);
select a-b  from t1 order by 1;
select a-b , (a-b < 0)  from t1 order by 1;
select any_value(a)-b as d, (any_value(a)-b >= 0), b from t1 group by b having d >= 0;
select cast((a - b) as unsigned) from t1 order by 1;
drop table t1;


#SELECT CAST(IFNULL(NULL, NULL) AS DECIMAL);
select if(1, cast(1111111111111111111 as unsigned), 1) i, case when 1 then cast(1111111111111111111 as unsigned) else 1 end c, coalesce(cast(1111111111111111111 as unsigned), 1) co;

#CREATE TABLE t1 (f1 int,
#gc_int int AS (f1 + 1) ,
#gc_date DATE AS (f1 + 1));
#INSERT INTO t1(f1) VALUES
#(030303),(040404),
#(050505),(060606),
#(010101),(020202),
#(030303),(040404),
#(050505),(060606),
#(010101),(020202),
#(090909),(101010),
#(010101),(020202),
#(070707),(080808);
#SELECT * FROM t1 WHERE f1 + 1 > 070707;
#SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE);
#DROP TABLE t1;

create database if not exists test;
use test;
drop table if exists `alarm`;
CREATE TABLE `alarm` (
`alarm_id` INT NOT NULL AUTO_INCREMENT,
`var_name` TEXT DEFAULT NULL,
`address` TEXT DEFAULT NULL,
`var_type` TEXT DEFAULT NULL,
`alarm_value` TEXT DEFAULT NULL,
`current_value` TEXT DEFAULT NULL,
`priority` INT DEFAULT null,
`operator` TEXT DEFAULT NULL,
`insert_time` BLOB DEFAULT NULL,
`note` TEXT DEFAULT NULL,
PRIMARY KEY (`alarm_id`)
);

INSERT INTO `alarm` VALUES (2,'测试','M100.0','Bool','True','True',0,'管理员',_binary '2023-03-01 14:20:29','报警测试'),(3,'测试','M100.0','Bool','True','True',0,'管理员',_binary '2023-03-01 14:20:41','报警测试'),(4,'测试','M100.0','Bool','True','True',0,'管理员',_binary '2023-03-01 14:20:46','报警测试'),(5,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-03 14:29:26.327337','设备急停中'),(6,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-05 11:38:10.6059081','设备急停中'),(7,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-15 07:55:37.1308852','设备急停中'),(8,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-15 08:02:48.7571227','设备急停中'),(9,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-19 03:27:49.5087995','设备急停中'),(10,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-22 00:05:23.1781761','设备急停中'),(11,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-22 16:05:08.6198487','设备急停中'),(12,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-22 16:12:10.8425965','设备急停中'),(13,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-22 16:24:30.269232','设备急停中'),(14,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-22 16:25:16.3121285','设备急停中'),(15,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-22 16:25:23.5447725','设备急停中'),(16,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-22 20:34:57.8534506','设备急停中'),(17,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-22 20:35:20.1639042','设备急停中'),(18,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-22 20:45:27.0464144','设备急停中'),(19,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-22 20:49:59.5979518','设备急停中'),(20,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-23 01:26:46.1155487','设备急停中'),(21,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-03-30 06:33:50.6666203','设备急停中'),(22,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-04-03 05:44:03.5318075','设备急停中'),(23,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-04-03 05:45:01.64952','设备急停中'),(24,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-04-03 05:46:37.8612795','设备急停中'),(25,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-04-03 06:05:06.5081611','设备急停中'),(26,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-04-03 12:21:23.1368163','设备急停中'),(27,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-04-03 12:25:45.5234186','设备急停中'),(28,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-04-03 18:22:29.7438075','设备急停中'),(29,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-04-04 06:52:07.473582','设备急停中'),(30,'Emergency','M1000.4','bool','False','False',1,'Admin',_binary '2023-04-04 08:32:50.2166836','设备急停中'),(31,'测试报警','M100','bool','true','true',1,'Admin',_binary '2023-04-06 21:00:09.7964362','note测试报警');
SELECT * FROM  `alarm`;
drop database test;