#
# 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;