409 lines
22 KiB
Plaintext
409 lines
22 KiB
Plaintext
# TestDaynameArithmetic
|
|
select dayname("1962-03-01")+0;
|
|
select dayname("1962-03-02")+0;
|
|
select dayname("1962-03-03")+0;
|
|
select dayname("1962-03-04")+0;
|
|
select dayname("1962-03-05")+0;
|
|
select dayname("1962-03-06")+0;
|
|
select dayname("1962-03-07")+0;
|
|
select dayname("1962-03-08")+0;
|
|
select dayname("1962-03-01")+1;
|
|
select dayname("1962-03-01")+2;
|
|
select dayname("1962-03-01")+3;
|
|
select dayname("1962-03-01")+4;
|
|
select dayname("1962-03-01")+5;
|
|
select dayname("1962-03-01")+6;
|
|
select dayname("1962-03-01")+7;
|
|
select dayname("1962-03-01")+2333;
|
|
select dayname("1962-03-01")+2.333;
|
|
select dayname("1962-03-01")>2;
|
|
select dayname("1962-03-01")<2;
|
|
select dayname("1962-03-01")=3;
|
|
select dayname("1962-03-01")!=3;
|
|
select dayname("1962-03-01")<4;
|
|
select dayname("1962-03-01")>4;
|
|
select !dayname("1962-03-01");
|
|
select dayname("1962-03-01")&1;
|
|
select dayname("1962-03-01")&3;
|
|
select dayname("1962-03-01")&7;
|
|
select dayname("1962-03-01")|1;
|
|
select dayname("1962-03-01")|3;
|
|
select dayname("1962-03-01")|7;
|
|
select dayname("1962-03-01")^1;
|
|
select dayname("1962-03-01")^3;
|
|
select dayname("1962-03-01")^7;
|
|
|
|
# TestTimestampDatumEncode
|
|
drop table if exists t;
|
|
create table t (a bigint primary key, b timestamp);
|
|
insert into t values (1, "2019-04-29 11:56:12");
|
|
explain format = 'plan_tree' select * from t where b = (select max(b) from t);
|
|
select * from t where b = (select max(b) from t);
|
|
|
|
# TestDateTimeAddReal
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL 1.123456789e3 SECOND;
|
|
SELECT 19000101000000 + INTERVAL 1.123456789e3 SECOND;
|
|
select date("1900-01-01") + interval 1.123456789e3 second;
|
|
SELECT "1900-01-01 00:18:43.456789" - INTERVAL 1.123456789e3 SECOND;
|
|
SELECT 19000101001843.456789 - INTERVAL 1.123456789e3 SECOND;
|
|
SELECT 19000101000000.0005 + INTERVAL 0.0005 SECOND;
|
|
select date("1900-01-01") - interval 1.123456789e3 second;
|
|
select 19000101000000 - interval 1.123456789e3 second;
|
|
|
|
# TestDateTimeAddString
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL "2" HOUR;
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL "-2" HOUR;
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL "128" HOUR;
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL "1e+3" HOUR;
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL "1+1" HOUR;
|
|
drop table if exists t;
|
|
create table t (id int primary key auto_increment, a varchar(32));
|
|
insert into t (a) values(''), ('+1'), ('+1+2'), ('-1'), ('2.2'), ('2.9'), ('2.2+1'), ('2+2.2'), ('5-2'), ('1e2'),
|
|
('true'), ('false'), ('xxx'), ('xxx+1'), ('xxx1'), (' 1 '), ('xxx-1'),
|
|
('9223372036854775808'), ('-9223372036854775809'), ('9223372036854775808-02'), ('-9223372036854775809-02');
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a MICROSECOND as result from t order by id ASC;
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a SECOND as result from t order by id ASC;
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a MINUTE as result from t order by id ASC;
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a HOUR as result from t order by id ASC;
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a DAY as result from t order by id ASC;
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a WEEK as result from t order by id ASC;
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a MONTH as result from t order by id ASC;
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a QUARTER as result from t order by id ASC;
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a YEAR as result from t order by id ASC;
|
|
|
|
# TestAddIntervalSpecialCase
|
|
select "1900-01-01 00:00:00" + INTERVAL true MICROSECOND;
|
|
select "1900-01-01 00:00:00" + INTERVAL "1.2" MICROSECOND;
|
|
select "1900-01-01 00:00:00" + INTERVAL "1.9" MINUTE;
|
|
select "1900-01-01 00:00:00" + INTERVAL 1.2 MICROSECOND;
|
|
select "1900-01-01 00:00:00" + INTERVAL 1.9 MICROSECOND;
|
|
select "1900-01-01 00:00:00" + INTERVAL true SECOND;
|
|
select "1900-01-01 00:00:00" + INTERVAL 1.2 SECOND;
|
|
select "1900-01-01 00:00:00" + INTERVAL "1+2" SECOND;
|
|
select "1900-01-01 00:00:00" + INTERVAL "1.2+2" SECOND;
|
|
select "1900-01-01 00:00:00" + INTERVAL "1+2.2" SECOND;
|
|
select "1900-01-01 00:00:00" + INTERVAL "0.000001" SECOND;
|
|
select "1900-01-01 00:00:00" + INTERVAL "0.0000009" SECOND;
|
|
select "1900-01-01 00:00:00" + INTERVAL true MINUTE;
|
|
select "1900-01-01 00:00:00" + INTERVAL "1.2" MINUTE;
|
|
select "1900-01-01 00:00:00" + INTERVAL "1.9" MINUTE;
|
|
select "1900-01-01 00:00:00" + INTERVAL 1.2 MINUTE;
|
|
select "1900-01-01 00:00:00" + INTERVAL 1.9 MINUTE;
|
|
|
|
# TestDecimalConvertToTime
|
|
# for issue #9770
|
|
drop table if exists t;
|
|
create table t(a datetime(6), b timestamp);
|
|
insert t values (20010101100000.123456, 20110707101112.123456);
|
|
select * from t;
|
|
|
|
# TestDateAddForNonExistingTimestamp
|
|
-- enable_warnings
|
|
set time_zone = 'CET';
|
|
drop table if exists t;
|
|
create table t(ts timestamp);
|
|
set time_zone = 'UTC';
|
|
insert into t values('2022-03-27 00:30:00');
|
|
insert into t values('2022-10-30 00:30:00');
|
|
insert into t values('2022-10-30 01:30:00');
|
|
set time_zone = 'Europe/Amsterdam';
|
|
-- error 1292
|
|
insert into t values('2022-03-27 02:30:00');
|
|
select date_add(ts, interval 1 hour) from t order by ts;
|
|
set time_zone = default;
|
|
-- disable_warnings
|
|
|
|
# TestTimestampAddWithFractionalSecond
|
|
drop table if exists t;
|
|
create table t(a date);
|
|
insert into t values ('2021-08-20');
|
|
select timestampadd(microsecond, 1, a) from t;
|
|
select timestampadd(second, 6/4, a) from t;
|
|
select timestampadd(second, 9.9999e2, a) from t;
|
|
select timestampadd(second, 1, '2021-08-20 00:00:01.0001');
|
|
select timestampadd(minute, 1.5, '2021-08-20 00:00:00');
|
|
select timestampadd(minute, 1.5, '2021-08-20 00:00:00.0001');
|
|
--enable_warnings
|
|
SELECT timestampadd(year,1.212208e+308,'1995-01-05 06:32:20.859724') as result;
|
|
--disable_warnings
|
|
|
|
# TestDatetimeMicrosecond
|
|
# for int
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2 SECOND_MICROSECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2 MINUTE_MICROSECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2 HOUR_MICROSECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2 DAY_MICROSECOND);
|
|
# for decimal
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 HOUR_MINUTE);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MINUTE_SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 YEAR_MONTH);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_HOUR);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_MINUTE);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 HOUR_SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 YEAR);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 QUARTER);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MONTH);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 WEEK);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 HOUR);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MINUTE);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MICROSECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 HOUR_MINUTE);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MINUTE_SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 YEAR_MONTH);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_HOUR);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_MINUTE);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 HOUR_SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 YEAR);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 QUARTER);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MONTH);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 WEEK);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 HOUR);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MINUTE);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MICROSECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" HOUR_MINUTE);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" MINUTE_SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" YEAR_MONTH);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" DAY_HOUR);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" DAY_MINUTE);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" DAY_SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" HOUR_SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" YEAR);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" QUARTER);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" MONTH);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" WEEK);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" DAY);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" HOUR);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" MINUTE);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" MICROSECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" HOUR_MINUTE);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" MINUTE_SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" YEAR_MONTH);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" DAY_HOUR);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" DAY_MINUTE);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" DAY_SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" HOUR_SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.+2" SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.*2" SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2./2" SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.a2" SECOND);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" YEAR);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" QUARTER);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" MONTH);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" WEEK);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" DAY);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" HOUR);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" MINUTE);
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" MICROSECOND);
|
|
# select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 SECOND);
|
|
|
|
# TestExprDateTimeOnDST
|
|
set @@session.time_zone = 'Europe/Amsterdam';
|
|
drop table if exists t;
|
|
create table t (id int, dt datetime, primary key (id, dt));
|
|
insert into t values (1, date_add('2023-03-26 00:00:00', interval 2 hour));
|
|
insert into t values (4,'2023-03-26 02:00:00');
|
|
select * from t;
|
|
set time_zone = default;
|
|
|
|
# TestGreatestTimeType
|
|
drop table if exists t1;
|
|
create table t1(c_time time(5), c_dt datetime(4), c_ts timestamp(3), c_d date, c_str varchar(100));
|
|
insert into t1 values('-800:10:10', '2021-10-10 10:10:10.1234', '2021-10-10 10:10:10.1234', '2021-10-11', '2021-10-10 10:10:10.1234');
|
|
set @@tidb_enable_vectorized_expression = off;
|
|
select greatest(c_time, c_time) from t1;
|
|
select greatest(c_dt, c_dt) from t1;
|
|
select greatest(c_ts, c_ts) from t1;
|
|
select greatest(c_d, c_d) from t1;
|
|
select greatest(c_str, c_str) from t1;
|
|
select least(c_time, c_time) from t1;
|
|
select least(c_dt, c_dt) from t1;
|
|
select least(c_ts, c_ts) from t1;
|
|
select least(c_d, c_d) from t1;
|
|
select least(c_str, c_str) from t1;
|
|
select greatest(c_time, cast('10:01:01' as time)) from t1;
|
|
select least(c_time, cast('10:01:01' as time)) from t1;
|
|
select greatest(c_d, cast('1999-10-10' as date)) from t1;
|
|
select least(c_d, cast('1999-10-10' as date)) from t1;
|
|
select greatest(c_dt, cast('1999-10-10 10:10:10.1234' as datetime)) from t1;
|
|
select least(c_dt, cast('1999-10-10 10:10:10.1234' as datetime)) from t1;
|
|
set @@tidb_enable_vectorized_expression = on;
|
|
select greatest(c_time, c_time) from t1;
|
|
select greatest(c_dt, c_dt) from t1;
|
|
select greatest(c_ts, c_ts) from t1;
|
|
select greatest(c_d, c_d) from t1;
|
|
select greatest(c_str, c_str) from t1;
|
|
select least(c_time, c_time) from t1;
|
|
select least(c_dt, c_dt) from t1;
|
|
select least(c_ts, c_ts) from t1;
|
|
select least(c_d, c_d) from t1;
|
|
select least(c_str, c_str) from t1;
|
|
select greatest(c_time, cast('10:01:01' as time)) from t1;
|
|
select least(c_time, cast('10:01:01' as time)) from t1;
|
|
select greatest(c_d, cast('1999-10-10' as date)) from t1;
|
|
select least(c_d, cast('1999-10-10' as date)) from t1;
|
|
select greatest(c_dt, cast('1999-10-10 10:10:10.1234' as datetime)) from t1;
|
|
select least(c_dt, cast('1999-10-10 10:10:10.1234' as datetime)) from t1;
|
|
set @@tidb_enable_vectorized_expression = default;
|
|
|
|
# TestDatetimeOverflow
|
|
drop table if exists t1;
|
|
create table t1 (d date);
|
|
set sql_mode='traditional';
|
|
-- error 1441
|
|
insert into t1 (d) select date_add('2000-01-01',interval 8000 year);
|
|
-- error 1441
|
|
insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR);
|
|
-- error 1441
|
|
insert into t1 (d) select date_add('9999-12-31',interval 1 year);
|
|
-- error 1441
|
|
insert into t1 (d) select date_add('9999-12-31',interval 1 day);
|
|
set sql_mode='';
|
|
insert into t1 (d) select date_add('2000-01-01',interval 8000 year);
|
|
insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR);
|
|
insert into t1 (d) select date_add('9999-12-31',interval 1 year);
|
|
insert into t1 (d) select date_add('9999-12-31',interval 1 day);
|
|
select * from t1;
|
|
set sql_mode=default;
|
|
|
|
# Test date add interval overflow
|
|
select "1000-01-01 00:00:00" + INTERVAL 9223372036854775808 day;
|
|
select "1000-01-01 00:00:00" + INTERVAL 18446744073709551616 day;
|
|
|
|
drop table if exists t1;
|
|
create table t1(a decimal(65, 2));
|
|
insert into t1 (a) values (1), (1.4), (1.5), (1.6), (-1), (-1.4), (-1.5), (-1.6), (-1000.5);
|
|
|
|
insert into t1 (a) values (315600000000000000), (9223372036854775808), (18446744073709551615), (18446744073709551616), (-9223372036854775808), (-9223372036854775809);
|
|
set @@tidb_enable_vectorized_expression=0;
|
|
select a, "1000-01-01 00:00:00" + INTERVAL a YEAR from t1 order by a ASC;
|
|
select a, "1000-01-01 00:00:00" + INTERVAL a MINUTE from t1 order by a ASC;
|
|
select a, "1000-01-01 00:00:00" + INTERVAL a MICROSECOND from t1 order by a ASC;
|
|
select a, "1000-01-01 00:00:00" + INTERVAL cast(a as char) DAY from t1 order by a ASC;
|
|
select a, "1000-01-01 00:00:00" + INTERVAL cast(a as signed) DAY from t1 order by a ASC;
|
|
select a, "1000-01-01 00:00:00" + INTERVAL cast(a as unsigned) DAY from t1 order by a ASC;
|
|
|
|
set @@tidb_enable_vectorized_expression=1;
|
|
select a, "1000-01-01 00:00:00" + INTERVAL a YEAR from t1 order by a ASC;
|
|
select a, "1000-01-01 00:00:00" + INTERVAL a MINUTE from t1 order by a ASC;
|
|
select a, "1000-01-01 00:00:00" + INTERVAL a MICROSECOND from t1 order by a ASC;
|
|
select a, "1000-01-01 00:00:00" + INTERVAL cast(a as char) DAY from t1 order by a ASC;
|
|
select a, "1000-01-01 00:00:00" + INTERVAL cast(a as signed) DAY from t1 order by a ASC;
|
|
select a, "1000-01-01 00:00:00" + INTERVAL cast(a as unsigned) DAY from t1 order by a ASC;
|
|
|
|
create table t2(a decimal(65, 2), d datetime);
|
|
set @old_sql_mode=@@sql_mode;
|
|
set @@sql_mode='';
|
|
insert into t2 values('1', "1000-01-01 00:00:00" + INTERVAL "+1" YEAR);
|
|
insert into t2 values('-1', "1000-01-01 00:00:00" + INTERVAL "-1" YEAR);
|
|
insert into t2 values('0', "1000-01-01 00:00:00" + INTERVAL "XXX" YEAR);
|
|
insert into t2 values('99999', "1000-01-01 00:00:00" + INTERVAL 99999 YEAR);
|
|
insert into t2 values('116777216', "1000-01-01 00:00:00" + INTERVAL 116777216 YEAR);
|
|
insert into t2 values('9223372036854775809', "1000-01-01 00:00:00" + INTERVAL 9223372036854775808 YEAR);
|
|
insert into t2 values('18446744073709551616', "1000-01-01 00:00:00" + INTERVAL 18446744073709551616 YEAR);
|
|
insert into t2 values('-9223372036854775809', "1000-01-01 00:00:00" + INTERVAL -9223372036854775809 YEAR);
|
|
select a, d from t2 order by a ASC;
|
|
|
|
truncate table t2;
|
|
set @@sql_mode=@old_sql_mode;
|
|
insert into t2 values('1', "1000-01-01 00:00:00" + INTERVAL "+1" YEAR);
|
|
insert into t2 values('-1', "1000-01-01 00:00:00" + INTERVAL "-1" YEAR);
|
|
--error 1292
|
|
insert into t2 values('0', "1000-01-01 00:00:00" + INTERVAL "XXX" YEAR);
|
|
--error 1441
|
|
insert into t2 values('99999', "1000-01-01 00:00:00" + INTERVAL 99999 YEAR);
|
|
--error 1441
|
|
insert into t2 values('116777216', "1000-01-01 00:00:00" + INTERVAL 116777216 YEAR);
|
|
--error 1292
|
|
insert into t2 values('9223372036854775809', "1000-01-01 00:00:00" + INTERVAL 9223372036854775808 YEAR);
|
|
--error 1292
|
|
insert into t2 values('18446744073709551616', "1000-01-01 00:00:00" + INTERVAL 18446744073709551616 YEAR);
|
|
--error 1292
|
|
insert into t2 values('-9223372036854775809', "1000-01-01 00:00:00" + INTERVAL -9223372036854775809 YEAR);
|
|
select a, d from t2 order by a ASC;
|
|
drop table if exists t1;
|
|
|
|
drop table if exists t;
|
|
create table t(col0 date, col1 time, col2 varchar(30));
|
|
insert into t values('2024-11-01', '12:00:01.341300', '12:00:01.341300'), ('2024-11-01', '1 12:00:01.341300', '1 12:00:01.341300'), ('2024-11-01', '-1 12:00:01.341300', '-1 12:00:01.341300'),('1000-01-01', '12:00:01.341300', '12:00:01.341300'), ('9999-12-31', '12:00:01.341300', '12:00:01.341300'), (null, '12:00:01.341300', '12:00:01.341300'), ('2020-11-01', null, null);
|
|
select addtime(date '2024-11-01', '12:00:01.341300');
|
|
select subtime(date '2024-11-01', '12:00:01.341300');
|
|
select addtime(date '2024-11-01', '1 12:00:01.341300');
|
|
select subtime(date '2024-11-01', '1 12:00:01.341300');
|
|
select addtime(date '2024-11-01', '-1 12:00:01.341300');
|
|
select subtime(date '2024-11-01', '-1 12:00:01.341300');
|
|
select addtime(date '2024-11-01', time '12:00:01.341300');
|
|
select subtime(date '2024-11-01', time '12:00:01.341300');
|
|
select addtime(date '2024-11-01', time '1 12:00:01.341300');
|
|
select subtime(date '2024-11-01', time '1 12:00:01.341300');
|
|
select addtime(date '2024-11-01', time '-1 12:00:01.341300');
|
|
select subtime(date '2024-11-01', time '-1 12:00:01.341300');
|
|
select addtime(col0, '12:00:01.341300') from t order by col0, col1 asc;
|
|
select subtime(col0, '12:00:01.341300') from t order by col0, col1 asc;
|
|
select addtime(col0, '1 12:00:01.341300') from t order by col0, col1 asc;
|
|
select subtime(col0, '1 12:00:01.341300') from t order by col0, col1 asc;
|
|
select addtime(col0, '-1 12:00:01.341300') from t order by col0, col1 asc;
|
|
select subtime(col0, '-1 12:00:01.341300') from t order by col0, col1 asc;
|
|
select addtime(col0, time '12:00:01.341300') from t order by col0, col1 asc;
|
|
select subtime(col0, time '12:00:01.341300') from t order by col0, col1 asc;
|
|
select addtime(col0, time '1 12:00:01.341300') from t order by col0, col1 asc;
|
|
select subtime(col0, time '1 12:00:01.341300') from t order by col0, col1 asc;
|
|
select addtime(col0, time '-1 12:00:01.341300') from t order by col0, col1 asc;
|
|
select subtime(col0, time '-1 12:00:01.341300') from t order by col0, col1 asc;
|
|
select addtime(col0, col1) from t order by col0, col1 asc;
|
|
select subtime(col0, col1) from t order by col0, col1 asc;
|
|
select addtime(col0, col2) from t order by col0, col1 asc;
|
|
select subtime(col0, col2) from t order by col0, col1 asc;
|
|
select addtime(col0, null) from t order by col0, col1 asc;
|
|
select subtime(col0, null) from t order by col0, col1 asc;
|
|
select addtime(null, col1) from t order by col0, col1 asc;
|
|
select subtime(null, col1) from t order by col0, col1 asc;
|
|
select addtime(null, col2) from t order by col0, col1 asc;
|
|
select subtime(null, col2) from t order by col0, col1 asc;
|
|
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
select addtime(date '0-0-0', '12:00:01.341300');
|
|
select addtime(date '0-0-0', time '12:00:01.341300');
|
|
select addtime(date '0-0-0', '1 12:00:01.341300');
|
|
select addtime(date '0-0-0', time '1 12:00:01.341300');
|
|
select subtime(date '0-0-0', '12:00:01.341300');
|
|
select subtime(date '0-0-0', time '12:00:01.341300');
|
|
select subtime(date '0-0-0', '1 12:00:01.341300');
|
|
select subtime(date '0-0-0', time '1 12:00:01.341300');
|
|
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
drop table if exists t;
|
|
create table t(col0 date, col1 time, col2 varchar(30));
|
|
insert into t values ('0-0-0', '1:1:1', '1:1:1'), ('0-0-0', '0:0:0', '0:0:0'), ('2024-0-1', '1:1:1', '1:1:1'), ('2020-1-1', '-1 1:1:1', '-1 1:1:1'), ('2024-1-0', '1:1:1', '1:1:1'), ('2024-0-1', '1:1:1', '1:1:1');
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
select addtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
select subtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
select addtime(col0, col2) from t order by col0, col1, col2 asc;
|
|
select subtime(col0, col2) from t order by col0, col1, col2 asc;
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
select addtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
select subtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
select addtime(col0, col2) from t order by col0, col1, col2 asc;
|
|
select subtime(col0, col2) from t order by col0, col1, col2 asc;
|
|
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
drop table if exists t;
|
|
create table t(col0 datetime, col1 time, col2 varchar(30));
|
|
insert into t values ('0-0-0', '1:1:1', '1:1:1'), ('0-0-0', '0:0:0', '0:0:0'), ('2024-0-1', '1:1:1', '1:1:1'), ('2020-1-1', '-1 1:1:1', '-1 1:1:1'), ('2024-1-0', '1:1:1', '1:1:1'), ('2024-0-1', '1:1:1', '1:1:1');
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
select addtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
select subtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
select addtime(col0, col2) from t order by col0, col1, col2 asc;
|
|
select subtime(col0, col2) from t order by col0, col1, col2 asc;
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
select addtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
select subtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
select addtime(col0, col2) from t order by col0, col1, col2 asc;
|
|
select subtime(col0, col2) from t order by col0, col1, col2 asc;
|