Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 467 insertions, 0 deletions

diff --git a/mysql-test/main/func_time_round.test b/mysql-test/main/func_time_round.test
new file mode 100644
index 00000000..79f9ec28
--- /dev/null
+++ b/mysql-test/main/func_time_round.test
@@ -0,0 +1,467 @@
+SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
+SET @default_sql_mode=@@sql_mode;
+
+CREATE TABLE t1_datetime_in_varchar (id SERIAL, a VARCHAR(64));
+INSERT INTO t1_datetime_in_varchar (a) VALUES
+('2000-12-31 23:59:59'),
+('2000-12-31 23:59:59.9'),
+('2000-12-31 23:59:59.99'),
+('2000-12-31 23:59:59.999'),
+('2000-12-31 23:59:59.9999'),
+('2000-12-31 23:59:59.99999'),
+('2000-12-31 23:59:59.999999'),
+('2000-12-31 23:59:59.9999999');
+
+CREATE TABLE t1_datetime_in_decimal (id SERIAL, a DECIMAL(38,10));
+INSERT INTO t1_datetime_in_decimal (a) VALUES
+(20001231235959),
+(20001231235959.9),
+(20001231235959.99),
+(20001231235959.999),
+(20001231235959.9999),
+(20001231235959.99999),
+(20001231235959.999999),
+(20001231235959.9999999);
+
+
+CREATE TABLE t1_time_in_varchar (id SERIAL, a VARCHAR(64));
+INSERT INTO t1_time_in_varchar (a) VALUES
+('00:00:00'),
+('00:00:00.9'),
+('00:00:00.99'),
+('00:00:00.999'),
+('00:00:00.9999'),
+('00:00:00.99999'),
+('00:00:00.999999'),
+('00:00:00.9999999');
+INSERT INTO t1_time_in_varchar (a) VALUES
+('837:59:59.9999999'),
+('838:59:59'),
+('838:59:59.9'),
+('838:59:59.99'),
+('838:59:59.999'),
+('838:59:59.9999'),
+('838:59:59.99999'),
+('838:59:59.999999'),
+('838:59:59.9999999'),
+('839:59:59.9999999'),
+('87649414:59:59.999999'),
+('87649414:59:59.9999999'),
+('87649415:59:59.999999'),
+('87649415:59:59.9999999');
+
+
+CREATE TABLE t1_time_in_decimal (id SERIAL, a DECIMAL(38,10));
+INSERT INTO t1_time_in_decimal (a) VALUES
+(0),
+(0.9),
+(0.99),
+(0.999),
+(0.9999),
+(0.99999),
+(0.999999),
+(0.9999999);
+INSERT INTO t1_time_in_decimal (a) VALUES
+(8375959.9999999),
+(8385959),
+(8385959.9),
+(8385959.99),
+(8385959.999),
+(8385959.9999),
+(8385959.99999),
+(8385959.999999),
+(8385959.9999999),
+(8395959.9999999),
+(876494145959.999999),
+(876494145959.9999999),
+(876494155959.999999),
+(876494155959.9999999);
+
+--echo #
+--echo # TIME: LEAST/GREATEST
+--echo #
+
+SELECT GREATEST(TIME'00:00:00', a) FROM t1_time_in_varchar;
+SELECT GREATEST(TIME'00:00:00', a) FROM t1_time_in_decimal;
+
+SELECT GREATEST(TIME'00:00:00', '00:00:00.0000004');
+SELECT GREATEST(TIME'00:00:00', 0.0000004);
+
+SELECT GREATEST(TIME'00:00:00', '00:00:00.0000005');
+SELECT GREATEST(TIME'00:00:00', 0.0000005);
+
+
+--echo #
+--echo # Functions with a single TIME input, conversion from DATETIME-in-VARCHAR
+--echo #
+
+SELECT SECOND(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT MINUTE(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT HOUR(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+
+SELECT EXTRACT(SECOND FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT EXTRACT(MINUTE FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT EXTRACT(HOUR FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+
+SELECT TIME_TO_SEC(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+
+
+--echo #
+--echo # Functions with a single TIME input, conversion from DATETIME-in-DECIMAL
+--echo #
+
+
+SELECT SECOND(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT MINUTE(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT HOUR(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+
+SELECT EXTRACT(SECOND FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT EXTRACT(MINUTE FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT EXTRACT(HOUR FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+
+SELECT TIME_TO_SEC(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+
+
+--echo #
+--echo # Functions with a single TIME interval input, conversion from TIME-interval-in-VARCHAR
+--echo #
+
+#enable after fix MDEV-29525
+--disable_view_protocol
+SELECT
+  EXTRACT(DAY FROM a),
+  EXTRACT(HOUR FROM a),
+  EXTRACT(MINUTE FROM a),
+  EXTRACT(SECOND FROM a),
+  EXTRACT(MICROSECOND FROM a),
+  CAST(a AS INTERVAL DAY_SECOND(6)),
+  a
+FROM t1_time_in_varchar ORDER BY id;
+--enable_view_protocol
+
+SELECT
+  TIME_TO_SEC(a),
+  CAST(a AS TIME(6)),
+  a
+FROM t1_time_in_varchar ORDER BY id;
+
+--echo #
+--echo # Functions with a single TIME interval input, conversion from TIME-interval-in-DECIMAL
+--echo #
+
+#enable after fix MDEV-29525
+--disable_view_protocol
+SELECT
+  EXTRACT(DAY FROM a),
+  EXTRACT(HOUR FROM a),
+  EXTRACT(MINUTE FROM a),
+  EXTRACT(SECOND FROM a),
+  EXTRACT(MICROSECOND FROM a),
+  CAST(a AS INTERVAL DAY_SECOND(6)),
+  a
+FROM t1_time_in_decimal ORDER BY id;
+--enable_view_protocol
+
+SELECT
+  TIME_TO_SEC(a),
+  CAST(a AS TIME(6)),
+  a
+FROM t1_time_in_decimal ORDER BY id;
+
+
+--echo #
+--echo # Functions with a single DATE input, conversion from DATETIME-in-VARCHAR
+--echo #
+
+SELECT QUARTER(a), CAST(a AS DATE),  a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT MONTH(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT YEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+
+SELECT DAYNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT MONTHNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+
+SELECT LAST_DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT TO_DAYS(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+SELECT DAYOFYEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
+
+
+CREATE TABLE t1 (a VARCHAR(32));
+INSERT INTO t1 VALUES
+('2002-01-05 23:59:59'),
+('2002-01-05 23:59:59.999999'),
+('2002-01-05 23:59:59.9999999');
+SELECT YEARWEEK(a), a FROM t1;
+SELECT WEEK(a), a FROM t1;
+SELECT WEEKDAY(a), a FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(32,9));
+INSERT INTO t1 VALUES
+(20020105235959),
+(20020105235959.999999),
+(20020105235959.9999999);
+SELECT YEARWEEK(a), a FROM t1;
+SELECT WEEK(a), a FROM t1;
+SELECT WEEKDAY(a), a FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Functions with a single DATE input, conversion from DATETIME-in-DECIMAL
+--echo #
+
+SELECT QUARTER(a), CAST(a AS DATE),  a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT MONTH(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT YEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+
+SELECT DAYNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT MONTHNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT YEARWEEK(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+
+SELECT LAST_DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT TO_DAYS(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT DAYOFYEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+SELECT DAYOFMONTH(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
+
+
+--echo #
+--echo # Functions with a single DATETIME input, conversion from DATETIME-in-VARCHAR
+--echo #
+
+SELECT TO_SECONDS(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+
+SET time_zone='+00:00';
+SELECT UNIX_TIMESTAMP(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+SET time_zone=DEFAULT;
+
+SELECT CONVERT_TZ(a, '+00:00','+00:00'), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
+
+
+--echo #
+--echo # Functions with a single DATETIME input, conversion from DATETIME-in-DECIMAL
+--echo #
+
+SELECT TO_SECONDS(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+
+SET time_zone='+00:00';
+SELECT UNIX_TIMESTAMP(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+SET time_zone=DEFAULT;
+
+SELECT CONVERT_TZ(a, '+00:00','+00:00'), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
+
+DROP TABLE t1_datetime_in_varchar;
+DROP TABLE t1_datetime_in_decimal;
+DROP TABLE t1_time_in_varchar;
+DROP TABLE t1_time_in_decimal;
+
+
+--echo #
+--echo # Functions that construct DATETIME
+--echo #
+
+SET time_zone='+00:00';
+CREATE TABLE t1_unix_timestamp (id SERIAL, a DECIMAL(30,10));
+INSERT INTO t1_unix_timestamp (a) VALUES
+(980639999),
+(980639999.9),
+(980639999.999999),
+(980639999.9999999),
+(2147483647),
+(2147483647.9),
+(2147483647.999999),
+(2147483647.9999999);
+SELECT a, FROM_UNIXTIME(a) FROM t1_unix_timestamp ORDER BY id;
+DROP TABLE t1_unix_timestamp;
+SET time_zone=DEFAULT;
+
+
+--echo #
+--echo # Functions that construct TIME
+--echo #
+
+CREATE TABLE t1_sec (id SERIAL, a DECIMAL(38,10));
+INSERT INTO t1_sec (a) VALUES
+(59),
+(59.9),
+(59.999999),
+(59.9999999),
+(3020398),
+(3020398.999999),
+(3020398.9999999),
+(3020399),
+(3020399.999999),
+(3020399.9999999),
+(9223372036854775807),
+(9223372036854775807.9),
+(9223372036854775807.999999),
+(9223372036854775807.9999999),
+(18446744073709551615),
+(18446744073709551615.9),
+(18446744073709551615.999999),
+(18446744073709551615.9999999);
+SELECT a, SEC_TO_TIME(a) FROM t1_sec ORDER BY id;
+DROP TABLE t1_sec;
+
+
+CREATE TABLE t1_sec (id SERIAL, a DECIMAL(38,10));
+INSERT INTO t1_sec (a) VALUES
+(0),
+(0.9),
+(0.999999),
+(0.9999999);
+SELECT a, MAKETIME(0, 0, a) FROM t1_sec ORDER BY id;
+DROP TABLE t1_sec;
+
+
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('2017-12-31 23:59:59'),
+('2017-12-31 23:59:59.9'),
+('2017-12-31 23:59:59.999999'),
+('2017-12-31 23:59:59.9999999');
+--vertical_results
+SELECT
+  '----',
+  a,
+  DATE_FORMAT(a, '%Y') AS yyyy,
+  DATE_FORMAT(a, '%Y-%m-%d') AS d,
+  DATE_FORMAT(a, '%H:%i:%s') AS t0,
+  DATE_FORMAT(a, '%H:%i:%s.%f') AS t6,
+  DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s') AS dt0,
+  DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s.%f') AS dt6
+FROM t1;
+--horizontal_results
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(32,9));
+INSERT INTO t1 VALUES
+(20171231235959),
+(20171231235959.9),
+(20171231235959.999999),
+(20171231235959.9999999);
+--vertical_results
+SELECT
+  '----',
+  a,
+  DATE_FORMAT(a, '%Y') AS yyyy,
+  DATE_FORMAT(a, '%Y-%m-%d') AS d,
+  DATE_FORMAT(a, '%H:%i:%s') AS t0,
+  DATE_FORMAT(a, '%H:%i:%s.%f') AS t6,
+  DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s') AS dt0,
+  DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s.%f') AS dt6
+FROM t1;
+--horizontal_results
+DROP TABLE t1;
+
+
+--echo #
+--echo # Functions with two temporal parameters that round nanoseconds in both parameters in MySQL
+--echo #
+
+CREATE TABLE t1 (a VARCHAR(64));
+CREATE TABLE t2 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('2017-12-31 23:59:59'),
+('2017-12-31 23:59:59.9'),
+('2017-12-31 23:59:59.999999'),
+('2017-12-31 23:59:59.9999999');
+INSERT INTO t2 VALUES
+('00:00:00'),
+('00:00:00.9'),
+('00:00:00.999999'),
+('00:00:00.9999999');
+
+SELECT TIMESTAMP(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
+SELECT ADDTIME(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
+
+DROP TABLE t1, t2;
+
+
+CREATE TABLE t1 (a VARCHAR(64));
+CREATE TABLE t2 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('23:59:59'),
+('23:59:59.9'),
+('23:59:59.999999'),
+('23:59:59.9999999');
+INSERT INTO t2 VALUES
+('00:00:00'),
+('00:00:00.9'),
+('00:00:00.999999'),
+('00:00:00.9999999');
+SELECT TIMEDIFF(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
+DROP TABLE t1, t2;
+
+
+CREATE TABLE t1 (a VARCHAR(64));
+CREATE TABLE t2 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('2001-12-31 23:59:59'),
+('2001-12-31 23:59:59.9'),
+('2001-12-31 23:59:59.999999'),
+('2001-12-31 23:59:59.9999999');
+INSERT INTO t2 VALUES
+('2001-12-31 23:59:59'),
+('2001-12-31 23:59:59.9'),
+('2001-12-31 23:59:59.999999'),
+('2001-12-31 23:59:59.9999999');
+SELECT TIMESTAMPDIFF(MICROSECOND,t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (a VARCHAR(64));
+CREATE TABLE t2 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('23:59:59'),
+('23:59:59.9'),
+('23:59:59.999999'),
+('23:59:59.9999999');
+INSERT INTO t2 VALUES
+('00:00:00'),
+('00:00:00.9'),
+('00:00:00.999999'),
+('00:00:00.9999999');
+SELECT TIMEDIFF(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
+DROP TABLE t1, t2;
+
+
+--echo #
+--echo # STR_TO_DATE behaviour is questionable in MySQL 5.6 (MySQL Bug #92474)
+--echo #
+
+--echo # It truncates nanoseconds, but this may change in the future.
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('2017-12-31 23:59:59'),
+('2017-12-31 23:59:59.9'),
+('2017-12-31 23:59:59.999999'),
+('2017-12-31 23:59:59.9999999');
+
+SELECT
+  a,
+  STR_TO_DATE(a, '%Y-%m-%d %H:%i:%s') AS c0,
+  STR_TO_DATE(a, '%Y-%m-%d %H:%i:%s.%f') AS c6
+FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # DATE_ADD behaviour is questionable in MySQL 5.6 (MySQL Bug#92473)
+--echo # It rounds nanoseconds in the first argument, but truncates nanoseconds in the second argument.
+--echo # This may change in the future, to round both arguments.
+--echo #
+
+CREATE TABLE t1 (a VARCHAR(64));
+INSERT INTO t1 VALUES
+('2017-12-31 23:59:59'),
+('2017-12-31 23:59:59.9'),
+('2017-12-31 23:59:59.999999'),
+('2017-12-31 23:59:59.9999999');
+CREATE TABLE t2 (b DECIMAL(32,9));
+INSERT INTO t2 VALUES
+(0),
+(0.9),
+(0.999999),
+(0.9999999);
+SELECT a, b, DATE_ADD(a, INTERVAL b SECOND) FROM t1,t2 ORDER BY a,b;
+DROP TABLE t1, t2;