database/mariadb-columnstore-engine
1
0
Fork 0
mirror of https://github.com/mariadb-corporation/mariadb-columnstore-engine.git synced 2025-04-18 21:44:02 +03:00
Code
mariadb-columnstore-engine/mysql-test/columnstore/basic/r/mcs236_extract_function.result
Serguey Zefirov 0bc384d5f0 fix(ubsan): MCOL-5844 - iron out UBSAN reports 
The most important fix here is the fix of possible buffer overrun in
DATEFORMAT() function. A "%W" format, repeated enough times, would
overflow the 256-bytes buffer for result. Now we use ostringstream to
construct result and we are safe.

Changes in date/time projection functions made me fix difference between
us and server behavior. The new, better behavior is reflected in changes
in tests' results.

Also, there was incorrect logic in TRUNCATE() and ROUND() functions in
computing the decimal "shift."
2024-12-02 20:18:13 +03:00

370 lines
10 KiB
Plaintext
Raw Permalink Blame History

DROP DATABASE IF EXISTS mcs236_db;
CREATE DATABASE mcs236_db;
USE mcs236_db;
CREATE TABLE t1(t1_d DATE, t1_t TIME, t1_dt DATETIME)ENGINE=Columnstore;
INSERT INTO t1 VALUES(NULL, NULL, NULL);
INSERT INTO t1 VALUES('1177-01-21', '12:12:12.282', '2020-11-11 12:12:12');
INSERT INTO t1 VALUES('2020-01-01', '11:11:11', '1561-12-31 12:34:56');
INSERT INTO t1 VALUES('2016-02-01', '11:11:11', '2017-02-01 12:12:12.07109');
SELECT t1_d, EXTRACT(MICROSECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(MICROSECOND FROM t1_d)
NULL NULL
1177-01-21 0
2016-02-01 0
2020-01-01 0
SELECT t1_d, EXTRACT(SECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(SECOND FROM t1_d)
NULL NULL
1177-01-21 0
2016-02-01 0
2020-01-01 0
SELECT t1_d, EXTRACT(MINUTE FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(MINUTE FROM t1_d)
NULL NULL
1177-01-21 0
2016-02-01 0
2020-01-01 0
SELECT t1_d, EXTRACT(HOUR FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(HOUR FROM t1_d)
NULL NULL
1177-01-21 0
2016-02-01 0
2020-01-01 0
SELECT t1_d, EXTRACT(DAY FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(DAY FROM t1_d)
NULL NULL
1177-01-21 21
2016-02-01 1
2020-01-01 1
SELECT t1_d, EXTRACT(WEEK FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(WEEK FROM t1_d)
NULL NULL
1177-01-21 3
2016-02-01 5
2020-01-01 1
SELECT t1_d, EXTRACT(MONTH FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(MONTH FROM t1_d)
NULL NULL
1177-01-21 1
2016-02-01 2
2020-01-01 1
SELECT t1_d, EXTRACT(QUARTER FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(QUARTER FROM t1_d)
NULL NULL
1177-01-21 1
2016-02-01 1
2020-01-01 1
SELECT t1_d, EXTRACT(YEAR FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(YEAR FROM t1_d)
NULL NULL
1177-01-21 1177
2016-02-01 2016
2020-01-01 2020
SELECT t1_d, EXTRACT(SECOND_MICROSECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(SECOND_MICROSECOND FROM t1_d)
NULL NULL
1177-01-21 0
2016-02-01 0
2020-01-01 0
SELECT t1_d, EXTRACT(MINUTE_MICROSECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(MINUTE_MICROSECOND FROM t1_d)
NULL NULL
1177-01-21 0
2016-02-01 0
2020-01-01 0
SELECT t1_d, EXTRACT(MINUTE_SECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(MINUTE_SECOND FROM t1_d)
NULL NULL
1177-01-21 0
2016-02-01 0
2020-01-01 0
SELECT t1_d, EXTRACT(HOUR_MICROSECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(HOUR_MICROSECOND FROM t1_d)
NULL NULL
1177-01-21 0
2016-02-01 0
2020-01-01 0
SELECT t1_d, EXTRACT(HOUR_SECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(HOUR_SECOND FROM t1_d)
NULL NULL
1177-01-21 0
2016-02-01 0
2020-01-01 0
SELECT t1_d, EXTRACT(HOUR_MINUTE FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(HOUR_MINUTE FROM t1_d)
NULL NULL
1177-01-21 0
2016-02-01 0
2020-01-01 0
SELECT t1_d, EXTRACT(DAY_MICROSECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(DAY_MICROSECOND FROM t1_d)
NULL NULL
1177-01-21 21000000000000
2016-02-01 1000000000000
2020-01-01 1000000000000
SELECT t1_d, EXTRACT(DAY_SECOND FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(DAY_SECOND FROM t1_d)
NULL NULL
1177-01-21 21000000
2016-02-01 1000000
2020-01-01 1000000
SELECT t1_d, EXTRACT(DAY_MINUTE FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(DAY_MINUTE FROM t1_d)
NULL NULL
1177-01-21 210000
2016-02-01 10000
2020-01-01 10000
SELECT t1_d, EXTRACT(DAY_HOUR FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(DAY_HOUR FROM t1_d)
NULL NULL
1177-01-21 2100
2016-02-01 100
2020-01-01 100
SELECT t1_d, EXTRACT(YEAR_MONTH FROM t1_d) FROM t1 ORDER BY 1;
t1_d EXTRACT(YEAR_MONTH FROM t1_d)
NULL NULL
1177-01-21 117701
2016-02-01 201602
2020-01-01 202001
SELECT t1_dt, EXTRACT(MICROSECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(MICROSECOND FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 0
2017-02-01 12:12:12 0
2020-11-11 12:12:12 0
SELECT t1_dt, EXTRACT(SECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(SECOND FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 56
2017-02-01 12:12:12 12
2020-11-11 12:12:12 12
SELECT t1_dt, EXTRACT(MINUTE FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(MINUTE FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 34
2017-02-01 12:12:12 12
2020-11-11 12:12:12 12
SELECT t1_dt, EXTRACT(HOUR FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(HOUR FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 12
2017-02-01 12:12:12 12
2020-11-11 12:12:12 12
SELECT t1_dt, EXTRACT(DAY FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(DAY FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 31
2017-02-01 12:12:12 1
2020-11-11 12:12:12 11
SELECT t1_dt, EXTRACT(WEEK FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(WEEK FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 53
2017-02-01 12:12:12 5
2020-11-11 12:12:12 46
SELECT t1_dt, EXTRACT(MONTH FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(MONTH FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 12
2017-02-01 12:12:12 2
2020-11-11 12:12:12 11
SELECT t1_dt, EXTRACT(QUARTER FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(QUARTER FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 4
2017-02-01 12:12:12 1
2020-11-11 12:12:12 4
SELECT t1_dt, EXTRACT(YEAR FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(YEAR FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 1561
2017-02-01 12:12:12 2017
2020-11-11 12:12:12 2020
SELECT t1_dt, EXTRACT(SECOND_MICROSECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(SECOND_MICROSECOND FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 56000000
2017-02-01 12:12:12 12000000
2020-11-11 12:12:12 12000000
SELECT t1_dt, EXTRACT(MINUTE_MICROSECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(MINUTE_MICROSECOND FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 3456000000
2017-02-01 12:12:12 1212000000
2020-11-11 12:12:12 1212000000
SELECT t1_dt, EXTRACT(MINUTE_SECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(MINUTE_SECOND FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 3456
2017-02-01 12:12:12 1212
2020-11-11 12:12:12 1212
SELECT t1_dt, EXTRACT(HOUR_MICROSECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(HOUR_MICROSECOND FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 123456000000
2017-02-01 12:12:12 121212000000
2020-11-11 12:12:12 121212000000
SELECT t1_dt, EXTRACT(HOUR_SECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(HOUR_SECOND FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 123456
2017-02-01 12:12:12 121212
2020-11-11 12:12:12 121212
SELECT t1_dt, EXTRACT(HOUR_MINUTE FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(HOUR_MINUTE FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 1234
2017-02-01 12:12:12 1212
2020-11-11 12:12:12 1212
SELECT t1_dt, EXTRACT(DAY_MICROSECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(DAY_MICROSECOND FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 31123456000000
2017-02-01 12:12:12 1121212000000
2020-11-11 12:12:12 11121212000000
SELECT t1_dt, EXTRACT(DAY_SECOND FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(DAY_SECOND FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 31123456
2017-02-01 12:12:12 1121212
2020-11-11 12:12:12 11121212
SELECT t1_dt, EXTRACT(DAY_MINUTE FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(DAY_MINUTE FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 311234
2017-02-01 12:12:12 11212
2020-11-11 12:12:12 111212
SELECT t1_dt, EXTRACT(DAY_HOUR FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(DAY_HOUR FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 3112
2017-02-01 12:12:12 112
2020-11-11 12:12:12 1112
SELECT t1_dt, EXTRACT(YEAR_MONTH FROM t1_dt) FROM t1 ORDER BY 1;
t1_dt EXTRACT(YEAR_MONTH FROM t1_dt)
NULL NULL
1561-12-31 12:34:56 156112
2017-02-01 12:12:12 201702
2020-11-11 12:12:12 202011
SELECT t1_t, EXTRACT(MICROSECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(MICROSECOND FROM t1_t)
NULL NULL
11:11:11 0
11:11:11 0
12:12:12 0
SELECT t1_t, EXTRACT(SECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(SECOND FROM t1_t)
NULL NULL
11:11:11 11
11:11:11 11
12:12:12 12
SELECT t1_t, EXTRACT(MINUTE FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(MINUTE FROM t1_t)
NULL NULL
11:11:11 11
11:11:11 11
12:12:12 12
SELECT t1_t, EXTRACT(HOUR FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(HOUR FROM t1_t)
NULL NULL
11:11:11 11
11:11:11 11
12:12:12 12
SELECT t1_t, EXTRACT(DAY FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(DAY FROM t1_t)
NULL NULL
11:11:11 0
11:11:11 0
12:12:12 0
SELECT t1_t, EXTRACT(WEEK FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(WEEK FROM t1_t)
NULL NULL
11:11:11 0
11:11:11 0
12:12:12 0
SELECT t1_t, EXTRACT(MONTH FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(MONTH FROM t1_t)
NULL NULL
11:11:11 0
11:11:11 0
12:12:12 0
SELECT t1_t, EXTRACT(QUARTER FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(QUARTER FROM t1_t)
NULL NULL
11:11:11 0
11:11:11 0
12:12:12 0
SELECT t1_t, EXTRACT(YEAR FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(YEAR FROM t1_t)
NULL NULL
11:11:11 0
11:11:11 0
12:12:12 0
SELECT t1_t, EXTRACT(SECOND_MICROSECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(SECOND_MICROSECOND FROM t1_t)
NULL NULL
11:11:11 11000000
11:11:11 11000000
12:12:12 12000000
SELECT t1_t, EXTRACT(MINUTE_MICROSECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(MINUTE_MICROSECOND FROM t1_t)
NULL NULL
11:11:11 1111000000
11:11:11 1111000000
12:12:12 1212000000
SELECT t1_t, EXTRACT(MINUTE_SECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(MINUTE_SECOND FROM t1_t)
NULL NULL
11:11:11 1111
11:11:11 1111
12:12:12 1212
SELECT t1_t, EXTRACT(HOUR_MICROSECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(HOUR_MICROSECOND FROM t1_t)
NULL NULL
11:11:11 111111000000
11:11:11 111111000000
12:12:12 121212000000
SELECT t1_t, EXTRACT(HOUR_SECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(HOUR_SECOND FROM t1_t)
NULL NULL
11:11:11 111111
11:11:11 111111
12:12:12 121212
SELECT t1_t, EXTRACT(HOUR_MINUTE FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(HOUR_MINUTE FROM t1_t)
NULL NULL
11:11:11 1111
11:11:11 1111
12:12:12 1212
SELECT t1_t, EXTRACT(DAY_MICROSECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(DAY_MICROSECOND FROM t1_t)
NULL NULL
11:11:11 111111000000
11:11:11 111111000000
12:12:12 121212000000
SELECT t1_t, EXTRACT(DAY_SECOND FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(DAY_SECOND FROM t1_t)
NULL NULL
11:11:11 111111
11:11:11 111111
12:12:12 121212
SELECT t1_t, EXTRACT(DAY_MINUTE FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(DAY_MINUTE FROM t1_t)
NULL NULL
11:11:11 1111
11:11:11 1111
12:12:12 1212
SELECT t1_t, EXTRACT(DAY_HOUR FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(DAY_HOUR FROM t1_t)
NULL NULL
11:11:11 11
11:11:11 11
12:12:12 12
SELECT t1_t, EXTRACT(YEAR_MONTH FROM t1_t) FROM t1 ORDER BY 1;
t1_t EXTRACT(YEAR_MONTH FROM t1_t)
NULL NULL
11:11:11 0
11:11:11 0
12:12:12 0
DROP DATABASE mcs236_db;
View Git Blame Copy Permalink