mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-20 09:07:44 +03:00
104 lines
4.8 KiB
Plaintext
104 lines
4.8 KiB
Plaintext
#
|
|
# Test MODA Function
|
|
# Author: dhall, david.hall@mariadb.com
|
|
#
|
|
# Test MODA with various numeric types
|
|
-- source ../include/have_columnstore.inc
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mcs98_db;
|
|
--enable_warnings
|
|
CREATE DATABASE mcs98_db;
|
|
USE mcs98_db;
|
|
CREATE TABLE t1 (t TINYINT, s SMALLINT, m MEDIUMINT, i INT, bi BIGINT, d1 DECIMAL(5,2), d2 DECIMAL(36,12), rl FLOAT, dbl DOUBLE)ENGINE=Columnstore;
|
|
INSERT INTO t1 VALUES(NULL, NULL, 1234, -1000012898, 700000012898, 34.21, 90000000000000000009.124312000091, 14.01, 3900000000000001.23),
|
|
(12, 345, 1234, -1000012899, 70000001289, 34.21, 90000000000000000009.124312000091, 14.01, 3900000000000001.23),
|
|
(12, 345, 1234, -1000012898, 700000012899, 34.22, 90000000000000000009.124312000092, 14.02, 3900000000000001.24),
|
|
(13, 346, 1235, NULL, NULL, NULL, NULL, NULL, NULL),
|
|
(28, 1345, 11234, -2000012898, 1700000012899, 134.22, 190000000000000000009.124312000092, 114.02, 13900000000000001.24);
|
|
|
|
SELECT moda(t) FROM t1;
|
|
SELECT moda(s) FROM t1;
|
|
SELECT moda(m) FROM t1;
|
|
SELECT moda(i) FROM t1;
|
|
SELECT moda(bi) FROM t1;
|
|
SELECT moda(d1) FROM t1;
|
|
SELECT moda(d2) FROM t1;
|
|
SELECT moda(rl) FROM t1;
|
|
SELECT moda(dbl) FROM t1;
|
|
CREATE TABLE t2 (
|
|
t tinyint,
|
|
tu tinyint unsigned,
|
|
s smallint,
|
|
su smallint unsigned,
|
|
m mediumint,
|
|
mu mediumint unsigned,
|
|
i int,
|
|
iu int unsigned,
|
|
b bigint,
|
|
bu bigint unsigned,
|
|
d1 decimal(5,2),
|
|
du1 decimal(5,2) unsigned,
|
|
d2 decimal(36,12),
|
|
du2 decimal(36,12) unsigned,
|
|
rl float,
|
|
dbl double,
|
|
v char(4),
|
|
vc varchar(10)
|
|
) engine=columnstore;
|
|
INSERT INTO t2 VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
|
|
(10, 10, 2010, 2010, 1237567, 1237567, 74836460, 74836460, 2223372036854775816,
|
|
2223372036854775816, 22.34, 22.34, 70000000605040302018.124312000091,
|
|
70000000605040302019.124312000091, 2344.32, 12345678.91011, 'five', 'five123456'),
|
|
(-10, 10, -2010, 2010, -1237567, 1237567, -74836460, 74836460,
|
|
-2223372036854775816, 2223372036854775816, -22.34, 22.34,
|
|
-70000000605040302019.124312000091, 70000000605040302029.124312000091,
|
|
-2344.32, -123456789.1011, 'four', 'five654321'),
|
|
(1, 1, 201, 201, 1234867, 1234867, 7483646, 7483646, 2223372036854775807,
|
|
2223372036854775807, 13.34, 13.34, 70000000605040302019.124312000091,
|
|
70000000605040302029.124312000091, 234.432, 12345678.91011, 'five', 'five123456'),
|
|
(-1, 1, -201, 201, -1234867, 1234867, -7483646, 7483646, -2223372036854775807,
|
|
2223372036854775807, -13.34, 13.34, -70000000605040302019.124312000091,
|
|
70000000605040302019.124312000091, -234.432, -1234567.891011, 'four', 'four123456'),
|
|
(10, 10, 2010, 2010, 1237567, 1237567, 74836460, 74836460, 2223372036854775816,
|
|
2223372036854775816, 22.34, 22.34, 70000000605040302019.124312000091,
|
|
70000000605040302019.124312000091, 2344.32, 1234567.891011, 'five', 'five123456'),
|
|
(-10, 10, -2010, 2010, -1237567, 1237567, -74836460, 74836460,
|
|
-2223372036854775816, 2223372036854775816, -22.34, 22.34,
|
|
-70000000605040302019.124312000091, 70000000605040302029.124312000091,
|
|
-2344.32, -12345678.91011, 'four', 'five654321'),
|
|
(1, 1, 201, 201, 1234867, 1234867, 7483646, 7483646, 2223372036854775807,
|
|
2223372036854775807, 13.34, 13.34, 70000000605040302018.124312000091,
|
|
70000000605040302029.124312000091, 234.432, 12345678.91011, 'six', 'six1234567');
|
|
SELECT moda(t) FROM t2;
|
|
SELECT moda(tu) FROM t2;
|
|
SELECT moda(s) FROM t2;
|
|
SELECT moda(su) FROM t2;
|
|
SELECT moda(m) FROM t2;
|
|
SELECT moda(mu) FROM t2;
|
|
SELECT moda(i) FROM t2;
|
|
SELECT moda(iu) FROM t2;
|
|
SELECT moda(b) FROM t2;
|
|
SELECT moda(bu) FROM t2;
|
|
SELECT moda(d1) FROM t2;
|
|
SELECT moda(du1) FROM t2;
|
|
SELECT moda(d2) FROM t2;
|
|
SELECT moda(du2) FROM t2;
|
|
SELECT moda(rl) FROM t2;
|
|
SELECT moda(dbl) FROM t2;
|
|
SELECT moda(v) FROM t2;
|
|
SELECT moda(vc) FROM t2;
|
|
--sorted_result
|
|
SELECT i FROM t2 WHERE i >= (SELECT moda(i) FROM t2) order by i;
|
|
--sorted_result
|
|
SELECT d2 FROM t2 WHERE d2 < (SELECT moda(d2) FROM t2);
|
|
--sorted_result
|
|
SELECT tu, moda(i) FROM t2 GROUP BY tu;
|
|
SELECT floor(moda(rl)) FROM t2;
|
|
SELECT ceiling(moda(dbl)) FROM t2;
|
|
SELECT moda(floor(rl)) FROM t2;
|
|
--sorted_result
|
|
SELECT t, moda(tu) 'q1' FROM t2 GROUP BY t HAVING moda(tu) > 5;
|
|
# Clean UP
|
|
DROP DATABASE mcs98_db;
|
|
|