database/mariadb-columnstore-engine
1
0
Fork 0
mirror of https://github.com/mariadb-corporation/mariadb-columnstore-engine.git synced 2025-04-20 09:07:44 +03:00
Code
mariadb-columnstore-engine/mysql-test/columnstore/basic/r/mcs236_extract_function.result
Leonid Fedorov 77eedd1756
MCOL-5503: Fix broken quarter (#2855) 
* Fix broken quarter function
2023-06-02 18:06:58 +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 -558149696
11:11:11 -558149696
12:12:12 952915712
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 -558149696
11:11:11 -558149696
12:12:12 952915712
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