mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-10-31 19:06:14 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/func_extract.test
Alexander Barkov 20eba06d9b MDEV-35489 Assertion `!ldate->neg' or unexpected result upon extracting unit from invalid value 
The patch for MDEV-23687 easlier fixed this problem. Adding tests only.
2024-11-25 16:13:16 +04:00

543 lines
18 KiB
Text
Raw Permalink Blame History

--echo #
--echo # MDEV-17385 MICROSECOND() returns confusing results with an out-of-range TIME-alike argument
--echo #
CREATE TABLE t1 (v VARCHAR(64), ll BIGINT, t TIME, dt DATETIME, d DATE);
CREATE TABLE t2 AS SELECT
EXTRACT(DAY FROM t),
EXTRACT(DAY_HOUR FROM t),
EXTRACT(DAY_MINUTE FROM t),
EXTRACT(DAY_SECOND FROM t),
EXTRACT(DAY_MICROSECOND FROM t),
EXTRACT(DAY FROM d),
EXTRACT(DAY_HOUR FROM d),
EXTRACT(DAY_MINUTE FROM d),
EXTRACT(DAY_SECOND FROM d),
EXTRACT(DAY_MICROSECOND FROM d),
EXTRACT(DAY FROM v),
EXTRACT(DAY_HOUR FROM v),
EXTRACT(DAY_MINUTE FROM v),
EXTRACT(DAY_SECOND FROM v),
EXTRACT(DAY_MICROSECOND FROM v),
EXTRACT(DAY FROM ll),
EXTRACT(DAY_HOUR FROM ll),
EXTRACT(DAY_MINUTE FROM ll),
EXTRACT(DAY_SECOND FROM ll),
EXTRACT(DAY_MICROSECOND FROM ll)
FROM t1;
SHOW CREATE TABLE t2;
DROP TABLE t2;
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(64), b DECIMAL(32,9));
INSERT INTO t1 VALUES
('9999-12-31 23:59:59.123456', 99991231235959.123456),
('2001-01-01 10:20:30.123456', 20010101102030.123456),
('4294967296:59:59.123456', 42949672965959.123456),
('4294967295:59:59.123456', 42949672955959.123456),
('87649416:59:59.123456', 876494165959.123456),
('87649415:59:59.123456', 876494155959.123456),
('87649414:59:59.123456', 876494145959.123456),
('9999:59:59.123456', 99995959.123456),
('9999:01:01.123456', 99990101.123456),
('9999:01:01', 99990101),
('0.999999', 0.999999),
('0.99999', 0.99999),
('0.9999', 0.9999),
('0.999', 0.999),
('0.99', 0.99),
('0.9', 0.9),
('000000',0);
--echo # Summary:
--echo # Check that FUNC(varchar) and FUNC(decimal) give equal results
--echo # Expect empty sets
--disable_warnings
SELECT a, b, EXTRACT(DAY_HOUR FROM a), EXTRACT(DAY_HOUR FROM b) FROM t1 WHERE NOT (EXTRACT(DAY_HOUR FROM a)<=>EXTRACT(DAY_HOUR FROM b));
SELECT a, b, EXTRACT(DAY FROM a), EXTRACT(DAY FROM b) FROM t1 WHERE NOT (EXTRACT(DAY FROM a)<=>EXTRACT(DAY FROM b));
SELECT a, b, EXTRACT(HOUR FROM a), EXTRACT(HOUR FROM b) FROM t1 WHERE NOT (EXTRACT(HOUR FROM a)<=>EXTRACT(HOUR FROM b));
SELECT a, b, EXTRACT(MINUTE FROM a), EXTRACT(MINUTE FROM b) FROM t1 WHERE NOT (EXTRACT(MINUTE FROM a)<=>EXTRACT(MINUTE FROM b));
SELECT a, b, EXTRACT(SECOND FROM a), EXTRACT(SECOND FROM b) FROM t1 WHERE NOT (EXTRACT(SECOND FROM a)<=>EXTRACT(SECOND FROM b));
SELECT a, b, EXTRACT(MICROSECOND FROM a), EXTRACT(MICROSECOND FROM b) FROM t1 WHERE NOT (EXTRACT(MICROSECOND FROM a)<=>EXTRACT(MICROSECOND FROM b));
--enable_warnings
#enable after fix MDEV-29525
--disable_view_protocol
--echo # Detailed results
SELECT
a,
CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm,
EXTRACT(DAY FROM a) * 24 + EXTRACT(HOUR FROM a) AS dh,
EXTRACT(DAY_HOUR FROM a),
EXTRACT(DAY FROM a),
EXTRACT(HOUR FROM a),
EXTRACT(MINUTE FROM a),
EXTRACT(SECOND FROM a),
EXTRACT(MICROSECOND FROM a)
FROM t1;
SELECT
b,
CAST(b AS INTERVAL DAY_SECOND(6)) AS cidm,
EXTRACT(DAY FROM b) * 24 + EXTRACT(HOUR FROM b) AS dh,
EXTRACT(DAY_HOUR FROM b),
EXTRACT(DAY FROM b),
EXTRACT(HOUR FROM b),
EXTRACT(MINUTE FROM b),
EXTRACT(SECOND FROM b),
EXTRACT(MICROSECOND FROM b)
FROM t1;
DROP TABLE t1;
--enable_view_protocol
--echo # Special case: DAY + TIME
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES ('9999-01-01');
SELECT a,
EXTRACT(DAY_HOUR FROM a),
EXTRACT(DAY_MINUTE FROM a),
EXTRACT(DAY_SECOND FROM a),
EXTRACT(DAY_MICROSECOND FROM a),
EXTRACT(DAY FROM a),
EXTRACT(HOUR FROM a),
EXTRACT(MINUTE FROM a),
EXTRACT(SECOND FROM a),
EXTRACT(MICROSECOND FROM a)
FROM t1;
DROP TABLE t1;
#enable after fix MDEV-29525
--disable_view_protocol
--echo # Bad values
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES ('');
SELECT a,
CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm,
EXTRACT(DAY_HOUR FROM a),
EXTRACT(DAY_MINUTE FROM a),
EXTRACT(DAY_SECOND FROM a),
EXTRACT(DAY_MICROSECOND FROM a),
EXTRACT(DAY FROM a),
EXTRACT(HOUR FROM a),
EXTRACT(MINUTE FROM a),
EXTRACT(SECOND FROM a),
EXTRACT(MICROSECOND FROM a)
FROM t1;
DROP TABLE t1;
--enable_view_protocol
--echo # Backward compatibility
--echo # This still parses as DATETIME
SELECT EXTRACT(YEAR FROM '2001/02/03 10:20:30');
SELECT EXTRACT(MONTH FROM '2001/02/03 10:20:30');
SELECT EXTRACT(DAY FROM '2001/02/03 10:20:30');
SELECT EXTRACT(YEAR FROM '01/02/03 10:20:30');
SELECT EXTRACT(MONTH FROM '01/02/03 10:20:30');
SELECT EXTRACT(DAY FROM '01/02/03 10:20:30');
SELECT EXTRACT(YEAR FROM '01:02:03 10:20:30');
SELECT EXTRACT(MONTH FROM '01:02:03 10:20:30');
SELECT EXTRACT(DAY FROM '01:02:03 10:20:30');
--echo # This still parses as DATETIME and returns NULL
SELECT EXTRACT(YEAR FROM "2011-02-32 8:46:06.23434");
SELECT EXTRACT(MONTH FROM "2011-02-32 8:46:06.23434");
SELECT EXTRACT(DAY FROM "2011-02-32 8:46:06.23434");
SELECT EXTRACT(HOUR FROM "2011-02-32 8:46:06.23434");
--echo # This still parses as DATE
SELECT EXTRACT(YEAR FROM '2001/02/03');
SELECT EXTRACT(MONTH FROM '2001/02/03');
SELECT EXTRACT(DAY FROM '2001/02/03');
SELECT EXTRACT(YEAR FROM '01/02/03');
SELECT EXTRACT(MONTH FROM '01/02/03');
SELECT EXTRACT(DAY FROM '01/02/03');
SELECT EXTRACT(YEAR FROM '01-02-03');
SELECT EXTRACT(MONTH FROM '01-02-03');
SELECT EXTRACT(DAY FROM '01-02-03');
SELECT EXTRACT(YEAR FROM '1-2-3');
SELECT EXTRACT(MONTH FROM '1-2-3');
SELECT EXTRACT(DAY FROM '1-2-3');
SELECT EXTRACT(HOUR FROM '1-2-3');
SELECT EXTRACT(DAY FROM '2024-01-03 garbage /////');
SELECT EXTRACT(DAY FROM '24-01-03 garbage /////');
SELECT EXTRACT(DAY FROM '01-02-03');
SELECT EXTRACT(DAY FROM '24:02:03T');
SELECT EXTRACT(DAY FROM '24-02-03');
SELECT EXTRACT(DAY FROM '24/02/03');
SELECT EXTRACT(DAY FROM '11111');
SELECT TIME('2001-01-01T'), TIME('2001-01-01T ');
SELECT TIME('2001/01/01T'), TIME('2001/01/01T ');
SELECT TIME('2001:01:01T'), TIME('2001:01:01T ');
SELECT EXTRACT(DAY FROM '2001-01-01T'), EXTRACT(DAY FROM '2001-01-01T ');
SELECT EXTRACT(DAY FROM '2001/01/01T'), EXTRACT(DAY FROM '2001/01/01T ');
SELECT EXTRACT(DAY FROM '2001:01:01T'), EXTRACT(DAY FROM '2001:01:01T ');
SELECT TIME('2001:01:01T'), TIME('2001:01:01T ');
SELECT EXTRACT(HOUR FROM '2001-01-01T'), EXTRACT(HOUR FROM '2001-01-01T ');
SELECT EXTRACT(HOUR FROM '2001/01/01T'), EXTRACT(HOUR FROM '2001/01/01T ');
SELECT EXTRACT(HOUR FROM '2001:01:01T'), EXTRACT(HOUR FROM '2001:01:01T ');
--echo # This still parses as DATE and returns NULL (without trying TIME)
SELECT EXTRACT(DAY FROM '100000:02:03T');
SELECT EXTRACT(DAY FROM '100000/02/03');
SELECT EXTRACT(DAY FROM '100000-02-03');
SELECT EXTRACT(DAY FROM '1111');
SELECT EXTRACT(DAY FROM '111');
SELECT EXTRACT(DAY FROM '11');
SELECT EXTRACT(DAY FROM '1');
--echo # This still parses as TIME
SELECT EXTRACT(HOUR FROM '11111');
SELECT EXTRACT(HOUR FROM '1111');
SELECT EXTRACT(HOUR FROM '111');
SELECT EXTRACT(HOUR FROM '11');
SELECT EXTRACT(HOUR FROM '1');
SELECT TIME('01:02:03:');
SELECT TIME('01:02:03-');
SELECT TIME('01:02:03;');
SELECT TIME('01:02:03/');
SELECT EXTRACT(HOUR FROM '01:02:03:');
SELECT EXTRACT(HOUR FROM '01:02:03-');
SELECT EXTRACT(HOUR FROM '01:02:03;');
SELECT EXTRACT(HOUR FROM '01:02:03/');
--echo # Backward compatibility preserved for YEAR and MONTH only
--echo # (behavior has changed for DAY, see below)
SELECT EXTRACT(YEAR FROM '01:02:03');
SELECT EXTRACT(MONTH FROM '01:02:03');
SELECT EXTRACT(YEAR FROM '24:01:03 garbage /////');
SELECT EXTRACT(MONTH FROM '24:01:03 garbage /////');
--echo # This still parses as TIME 00:20:01
SELECT TIME('2001/01/01');
SELECT TIME('2001-01-01');
--echo # This still parses as TIME and overflows to '838:59:59'
SELECT TIME('2001:01:01');
--echo # This used to parse as DATE, now parses as TIME interval
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES
('2024:01:03 garbage /////'),
('24:01:03 garbage /////'),
('01:01:03 garbage /////'),
('2024:02:03'),
('100000:02:03'),
('24:02:03'),
('01:02:03'),
('01:02:03:'),
('01:02:03-'),
('01:02:03;'),
('01:02:03/'),
('20 10:20:30');
#enable after fix MDEV-29525
--disable_view_protocol
SELECT
EXTRACT(DAY FROM a),
EXTRACT(DAY_SECOND FROM a), a,
CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm
FROM t1;
DROP TABLE t1;
--enable_view_protocol
--echo #
--echo # Start of 10.5 tests
--echo #
--echo #
--echo # MDEV-33496 Out of range error in AVG(YEAR(datetime)) due to a wrong data type
--echo #
let select01=SELECT ?, CAST(? AS UNSIGNED), CAST(? AS SIGNED), ABS(?), ROUND(?), -?, ROUND(?,-1), ?+0, ?+0.0, CONCAT(?), LEAST(?,?), COALESCE(?), COALESCE(?,CAST(1 AS SIGNED)), COALESCE(?,CAST(1 AS UNSIGNED)), @a:=?;
let pcount01=16;
let select02=SELECT AVG(?), MIN(?), MAX(?), GROUP_CONCAT(?);
let pcount02=4;
let ts=TIMESTAMP'2001-12-13 10:20:30.999999';
eval CREATE FUNCTION select01() RETURNS TEXT RETURN '$select01';
eval CREATE FUNCTION select02() RETURNS TEXT RETURN '$select02';
CREATE TABLE t1 (a DATETIME(6));
INSERT INTO t1 VALUES ('2001-12-31 10:20:30.999999');
DELIMITER $$;
CREATE FUNCTION params(expr TEXT, count INT) RETURNS TEXT
BEGIN
RETURN CONCAT(expr, REPEAT(CONCAT(', ', expr), count-1));
END;
$$
CREATE PROCEDURE show_drop()
BEGIN
SELECT TABLE_NAME, COLUMN_TYPE, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='test'
AND TABLE_NAME IN ('t1e_nm','t2e_nm','t1f_nm','t2f_nm',
't1e_ps','t1f_ps','t2e_ps','t2f_ps')
ORDER BY LEFT(TABLE_NAME, 2), ORDINAL_POSITION, TABLE_NAME;
FOR rec IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='test'
AND TABLE_NAME IN ('t1e_nm','t2e_nm','t1f_nm','t2f_nm',
't1e_ps','t1f_ps','t2e_ps','t2f_ps'))
DO
EXECUTE IMMEDIATE CONCAT('DROP TABLE ', rec.TABLE_NAME);
END FOR;
END;
$$
CREATE PROCEDURE p1(unit VARCHAR(32))
BEGIN
DECLARE do_extract BOOL DEFAULT unit NOT IN('DAYOFYEAR');
DECLARE query01 TEXT DEFAULT
CONCAT('CREATE TABLE t2 AS ', select01(), ' FROM t1');
DECLARE query02 TEXT DEFAULT
CONCAT('CREATE TABLE t2 AS ', select02(), ' FROM t1');
IF (do_extract)
THEN
EXECUTE IMMEDIATE REPLACE(REPLACE(query01,'t2','t1e_nm'),'?', CONCAT('EXTRACT(',unit,' FROM a)'));
EXECUTE IMMEDIATE REPLACE(REPLACE(query02,'t2','t2e_nm'),'?', CONCAT('EXTRACT(',unit,' FROM a)'));
END IF;
EXECUTE IMMEDIATE REPLACE(REPLACE(query01,'t2','t1f_nm'),'?', CONCAT(unit,'(a)'));
EXECUTE IMMEDIATE REPLACE(REPLACE(query02,'t2','t2f_nm'),'?', CONCAT(unit,'(a)'));
END;
$$
DELIMITER ;$$
--echo
--echo
--echo # EXTRACT(YEAR FROM expr) and YEAR(expr) are equivalent
CALL p1('YEAR');
let extr=EXTRACT(YEAR FROM $ts);
let func=YEAR($ts);
let extr01=`SELECT params("$extr", $pcount01) AS p`;
let func01=`SELECT params("$func", $pcount01) AS p`;
let extr02=`SELECT params("$extr", $pcount02) AS p`;
let func02=`SELECT params("$func", $pcount02) AS p`;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS $select01' USING $extr01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS $select01' USING $func01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS $select02' USING $extr02;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS $select02' USING $func02;
CALL show_drop;
--echo
--echo
--echo # EXTRACT(QUARTER FROM expr) and QUARTER(expr) are equavalent
CALL p1('QUARTER');
let extr=EXTRACT(QUARTER FROM $ts);
let func=QUARTER($ts);
let extr01=`SELECT params("$extr", $pcount01) AS p`;
let func01=`SELECT params("$func", $pcount01) AS p`;
let extr02=`SELECT params("$extr", $pcount02) AS p`;
let func02=`SELECT params("$func", $pcount02) AS p`;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS $select01' USING $extr01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS $select01' USING $func01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS $select02' USING $extr02;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS $select02' USING $func02;
CALL show_drop;
--echo
--echo
--echo # EXTRACT(MONTH FROM expr) and MONTH(expr) are equavalent
CALL p1('MONTH');
let extr=EXTRACT(MONTH FROM $ts);
let func=MONTH($ts);
let extr01=`SELECT params("$extr", $pcount01) AS p`;
let func01=`SELECT params("$func", $pcount01) AS p`;
let extr02=`SELECT params("$extr", $pcount02) AS p`;
let func02=`SELECT params("$func", $pcount02) AS p`;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS $select01' USING $extr01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS $select01' USING $func01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS $select02' USING $extr02;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS $select02' USING $func02;
CALL show_drop;
--echo
--echo
--echo # EXTRACT(WEEK FROM expr) and WEEK(expr) are equavalent
CALL p1('WEEK');
let extr=EXTRACT(WEEK FROM $ts);
let func=WEEK($ts);
let extr01=`SELECT params("$extr", $pcount01) AS p`;
let func01=`SELECT params("$func", $pcount01) AS p`;
let extr02=`SELECT params("$extr", $pcount02) AS p`;
let func02=`SELECT params("$func", $pcount02) AS p`;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS $select01' USING $extr01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS $select01' USING $func01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS $select02' USING $extr02;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS $select02' USING $func02;
CALL show_drop;
--echo
--echo
--echo # EXTRACT(DAY FROM expr) returns hours/24 and includes the sign for TIME
--echo # DAY(expr) returns the DD part of CAST(expr AS DATETIME)
CALL p1('DAY');
let extr=EXTRACT(DAY FROM $ts);
let func=DAY($ts);
let extr01=`SELECT params("$extr", $pcount01) AS p`;
let func01=`SELECT params("$func", $pcount01) AS p`;
let extr02=`SELECT params("$extr", $pcount02) AS p`;
let func02=`SELECT params("$func", $pcount02) AS p`;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS $select01' USING $extr01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS $select01' USING $func01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS $select02' USING $extr02;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS $select02' USING $func02;
CALL show_drop;
--echo
--echo
--echo # EXTRACT(HOUR FROM expr) returns hours%24 and includes the sign for TIME
--echo # HOUR(expr) returns the hh part of CAST(expr AS DATETIME)
CALL p1('HOUR');
let extr=EXTRACT(HOUR FROM $ts);
let func=HOUR($ts);
let extr01=`SELECT params("$extr", $pcount01) AS p`;
let func01=`SELECT params("$func", $pcount01) AS p`;
let extr02=`SELECT params("$extr", $pcount02) AS p`;
let func02=`SELECT params("$func", $pcount02) AS p`;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS $select01' USING $extr01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS $select01' USING $func01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS $select02' USING $extr02;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS $select02' USING $func02;
CALL show_drop;
--echo
--echo
--echo # EXTRACT(MINUTE FROM expr) includes the sign for TIME
--echo # MINUTE(expr) returns the absolute value
CALL p1('MINUTE');
let extr=EXTRACT(MINUTE FROM $ts);
let func=MINUTE($ts);
let extr01=`SELECT params("$extr", $pcount01) AS p`;
let func01=`SELECT params("$func", $pcount01) AS p`;
let extr02=`SELECT params("$extr", $pcount02) AS p`;
let func02=`SELECT params("$func", $pcount02) AS p`;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS $select01' USING $extr01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS $select01' USING $func01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS $select02' USING $extr02;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS $select02' USING $func02;
CALL show_drop;
--echo
--echo
--echo # EXTRACT(SECONDS FROM expr) includes the sign for TIME
--echo # SECONDS(expr) returns the absolute value
CALL p1('SECOND');
let extr=EXTRACT(SECOND FROM $ts);
let func=SECOND($ts);
let extr01=`SELECT params("$extr", $pcount01) AS p`;
let func01=`SELECT params("$func", $pcount01) AS p`;
let extr02=`SELECT params("$extr", $pcount02) AS p`;
let func02=`SELECT params("$func", $pcount02) AS p`;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS $select01' USING $extr01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS $select01' USING $func01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS $select02' USING $extr02;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS $select02' USING $func02;
CALL show_drop;
--echo
--echo
--echo # EXTRACT(MICROSECONDS FROM expr) includes the sign for TIME
--echo # MICROSECONDS(expr) returns the absolute value
CALL p1('MICROSECOND');
let extr=EXTRACT(MICROSECOND FROM $ts);
let func=MICROSECOND($ts);
let extr01=`SELECT params("$extr", $pcount01) AS p`;
let func01=`SELECT params("$func", $pcount01) AS p`;
let extr02=`SELECT params("$extr", $pcount02) AS p`;
let func02=`SELECT params("$func", $pcount02) AS p`;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1e_ps AS $select01' USING $extr01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS $select01' USING $func01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2e_ps AS $select02' USING $extr02;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS $select02' USING $func02;
CALL show_drop;
--echo
--echo
--echo # DAYOFYEAR
CALL p1('DAYOFYEAR');
let func=DAYOFYEAR($ts);
let func01=`SELECT params("$func", $pcount01) AS p`;
let func02=`SELECT params("$func", $pcount02) AS p`;
eval EXECUTE IMMEDIATE 'CREATE TABLE t1f_ps AS $select01' USING $func01;
eval EXECUTE IMMEDIATE 'CREATE TABLE t2f_ps AS $select02' USING $func02;
CALL show_drop;
DROP TABLE t1;
DROP PROCEDURE p1;
DROP PROCEDURE show_drop;
DROP FUNCTION params;
DROP FUNCTION select01;
DROP FUNCTION select02;
--echo #
--echo # MDEV-32891 Assertion `value <= ((ulonglong) 0xFFFFFFFFL) * 10000ULL' failed in str_to_DDhhmmssff_internal
--echo #
SELECT EXTRACT(HOUR_MICROSECOND FROM '42949672955000x1');
--echo #
--echo # MDEV-23687 Assertion `is_valid_value_slow()' failed in Datetime::Datetime upon EXTRACT under mode ZERO_DATE_TIME_CAST
--echo #
SET SESSION old_mode='ZERO_DATE_TIME_CAST';
SELECT CAST('100000:00:00' AS DATETIME);
SELECT EXTRACT(DAY FROM CAST('100000:00:00' AS DATETIME));
SELECT CAST('100000:00:00' AS DATE);
SELECT EXTRACT(DAY FROM CAST('100000:00:00' AS DATE));
SET SESSION old_mode=DEFAULT;
--echo #
--echo # MDEV-35489 Assertion `!ldate->neg' or unexpected result upon extracting unit from invalid value
--echo #
SELECT EXTRACT(DAY FROM TIMESTAMP(-177498480000));
SELECT EXTRACT(DAY FROM TIMESTAMP(-177498480001));
SELECT EXTRACT(DAY FROM TIMESTAMP(-200000000000));
SELECT EXTRACT(DAY FROM TIMESTAMP(-221938034527));
--echo #
--echo # End of 10.5 tests
--echo #
Reference in a new issue View git blame Copy permalink