database/mariadb-columnstore-engine
1
0
Fork 0
mirror of https://github.com/mariadb-corporation/mariadb-columnstore-engine.git synced 2025-11-03 17:13:17 +03:00
Code Activity
Files
cmapi-json-in-out-by-default
mariadb-columnstore-engine/mysql-test/columnstore/basic/r/mcol641-aggregate.result
aleksei.bukhalov ebde9b5648 fix(tests): add deterministic ordering for failing mtrs
2025-09-05 16:33:04 +04:00

501 lines
43 KiB
Plaintext
Raw Permalink Blame History

DROP DATABASE IF EXISTS test_mcol641_aggregate;
CREATE DATABASE test_mcol641_aggregate;
USE test_mcol641_aggregate;
DROP TABLE IF EXISTS cs1;
DROP TABLE IF EXISTS cs2;
DROP TABLE IF EXISTS cs3;
DROP TABLE IF EXISTS cs4;
DROP TABLE IF EXISTS cs5;
CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38,10), d3 DECIMAL(38,38), d4 DECIMAL(38),d5 DECIMAL(38),i1 INT) ENGINE=columnstore;
CREATE TABLE cs2 (d1 DECIMAL(38) UNSIGNED, d2 DECIMAL(38,10) UNSIGNED, d3 DECIMAL(38,38) UNSIGNED, d4 DECIMAL(38),d5 DECIMAL(38),i1 INT) ENGINE=columnstore;
CREATE TABLE cs3 (d1 DECIMAL(19), d2 DECIMAL(19,10), d3 DECIMAL(19,19), d4 DECIMAL(19),d5 DECIMAL(19),i1 INT) ENGINE=columnstore;
CREATE TABLE cs4 (d1 DECIMAL(19) UNSIGNED, d2 DECIMAL(19,10) UNSIGNED, d3 DECIMAL(19,19) UNSIGNED, d4 DECIMAL(19) UNSIGNED,d5 DECIMAL(19),i1 INT) ENGINE=columnstore;
CREATE TABLE cs5(d DECIMAL(25,19)) ENGINE=columnstore;
INSERT INTO cs1 VALUES
(1234,5678,-0.12345678901234567890123456789018,2,1,1),
(1234,-5678,0.1234567890123456789012345,2,1,1),
(12345678901234567890123,1234567890123456789012.9012345678,0.123456789012345678901234567890,2,1,1),
(NULL,NULL,NULL,2,1,1),
(0,0,0,2,1,1),
(1234,5678,0.1234567890123456783456789012345678,3,2,2),
(-123456789123456789,-5678,0.123456789012345673456789012345678,3,2,2),
(12345678901234567890901,1234567890123478901234.9012345678,0.1234567890123456789012345678908,3,2,2),
(NULL,NULL,NULL,3,2,2),
(0,0,0,3,2,2),
(1234,5678,-0.12345678901234567890123456789018,11111111111111111111111111111111,1,1),
(1234,5678,-0.12345678901234567890123456789018,11111111111111111111111111111111,1,1),
(1234,5678,-0.12345678901234567890123456789018,11111111111111111111111111111112,1,1);
INSERT INTO cs2 VALUES
(1234,5678,0.12345678901234567890123456789018,2,1,1),
(1234,5678,0.1234567890123456789012345,2,1,1),
(12345678901234567890123,1234567890123456789012.9012345678,0.123456789012345678901234567890,2,1,1),
(NULL,NULL,NULL,2,1,1),
(0,0,0,2,1,1),
(1234,5678,0.1234567890123456783456789012345678,3,2,2),
(123456789123456789123,5678,0.123456789012345673456789012345678,3,2,2),
(12345678901234567890901,1234567890123478901234.9012345678,0.1234567890123456789012345678908,3,2,2),
(NULL,NULL,NULL,3,2,2),
(0,0,0,3,2,2),
(1234,5678,0.12345678901234567890123456789018,11111111111111111111111111111111,1,1),
(1234,5678,0.12345678901234567890123456789018,11111111111111111111111111111111,1,1),
(1234,5678,0.12345678901234567890123456789018,11111111111111111111111111111112,1,1);
INSERT INTO cs3 VALUES
(1234,5678,-0.12345678901234,2,1,1),
(1234,-5678,0.12345678901234,2,1,1),
(12345678901,12345678.90128,0.1234567890123,2,1,1),
(NULL,NULL,NULL,2,1,1),
(0,0,0,2,1,1),
(1234,5678,0.12345678901234,3,2,2),
(-1234567899,-5678,0.1234567890123,3,2,2),
(12345678901,12345678.9012,0.1234567890123,3,2,2),
(NULL,NULL,NULL,3,2,2),
(0,0,0,3,2,2);
INSERT INTO cs4 VALUES
(1234,5678,0.12345678901234,2,1,1),
(1234,5678,0.12345678901234,2,1,1),
(12345678901,12345678.90128,0.1234567890123,2,1,1),
(NULL,NULL,NULL,2,1,1),
(0,0,0,2,1,1),
(1234,5678,0.12345678901234,3,2,2),
(1234567899,5678,0.1234567890123,3,2,2),
(12345678901,12345678.90123,0.1234567890123,3,2,2),
(NULL,NULL,NULL,3,2,2),
(0,0,0,3,2,2);
SELECT "sum(38)_test1", sum(d1),sum(d2),sum(d3) FROM cs1;
sum(38)_test1 sum(d1) sum(d2) sum(d3)
sum(38)_test1 24691234345680012331639 2469135780246935707281.8024691356 0.12345678901234567290123327780032580000
SELECT "avg(38)_test1", avg(d1),avg(d2),avg(d3) FROM cs1;
avg(38)_test1 avg(d1) avg(d2) avg(d3)
avg(38)_test1 2244657667789092030149.0000 224466889113357791571.07295173960000 0.01122334445566778844556666161821143636
SELECT "count(38)_test1", count(d1),count(d2),count(d3) FROM cs1;
count(38)_test1 count(d1) count(d2) count(d3)
count(38)_test1 11 11 11
SELECT "min(38)_test1", min(d1),min(d2),min(d3) FROM cs1;
min(38)_test1 min(d1) min(d2) min(d3)
min(38)_test1 -123456789123456789 -5678.0000000000 -0.12345678901234567890123456789018000000
SELECT "max(38)_test1", max(d1),max(d2),max(d3) FROM cs1;
max(38)_test1 max(d1) max(d2) max(d3)
max(38)_test1 12345678901234567890901 1234567890123478901234.9012345678 0.12345678901234567890123456789080000000
SELECT "group_concat(38)_test1", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM cs1;
group_concat(38)_test1 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_test1 -123456789123456789,0,0,1234,1234,1234,1234,1234,1234,12345678901234567890123,12345678901234567890901 -5678.0000000000,-5678.0000000000,0.0000000000,0.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,1234567890123456789012.9012345678,1234567890123478901234.9012345678 -0.12345678901234567890123456789018000000,-0.12345678901234567890123456789018000000,-0.12345678901234567890123456789018000000,-0.12345678901234567890123456789018000000,0.00000000000000000000000000000000000000,0.00000000000000000000000000000000000000,0.12345678901234567345678901234567800000,0.12345678901234567834567890123456780000,0.12345678901234567890123450000000000000,0.12345678901234567890123456789000000000,0.12345678901234567890123456789080000000
SELECT "count(distinct 38)_test1", count(distinct d1),count(distinct d2),count(distinct d3) FROM cs1;
count(distinct 38)_test1 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_test1 5 5 7
SELECT "sum(38)_test1", sum(d1),sum(d2),sum(d3) FROM (SELECT d1,d2,d3 FROM cs1)a1;
sum(38)_test1 sum(d1) sum(d2) sum(d3)
sum(38)_test1 24691234345680012331639 2469135780246935707281.8024691356 0.12345678901234567290123327780032580000
SELECT "avg(38)_test1", avg(d1),avg(d2),avg(d3) FROM (SELECT d1,d2,d3 FROM cs1)a1;
avg(38)_test1 avg(d1) avg(d2) avg(d3)
avg(38)_test1 2244657667789092030149.0000 224466889113357791571.07295173960000 0.01122334445566778844556666161821143636
SELECT "count(38)_test1", count(d1),count(d2),count(d3) FROM (SELECT d1,d2,d3 FROM cs1)a1;
count(38)_test1 count(d1) count(d2) count(d3)
count(38)_test1 11 11 11
SELECT "min(38)_test1", min(d1),min(d2),min(d3) FROM (SELECT d1,d2,d3 FROM cs1)a1;
min(38)_test1 min(d1) min(d2) min(d3)
min(38)_test1 -123456789123456789 -5678.0000000000 -0.12345678901234567890123456789018000000
SELECT "max(38)_test1", max(d1),max(d2),max(d3) FROM (SELECT d1,d2,d3 FROM cs1)a1;
max(38)_test1 max(d1) max(d2) max(d3)
max(38)_test1 12345678901234567890901 1234567890123478901234.9012345678 0.12345678901234567890123456789080000000
SELECT "group_concat(38)_test1", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM (SELECT d1,d2,d3 FROM cs1)a1;
group_concat(38)_test1 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_test1 -123456789123456789,0,0,1234,1234,1234,1234,1234,1234,12345678901234567890123,12345678901234567890901 -5678.0000000000,-5678.0000000000,0.0000000000,0.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,1234567890123456789012.9012345678,1234567890123478901234.9012345678 -0.12345678901234567890123456789018000000,-0.12345678901234567890123456789018000000,-0.12345678901234567890123456789018000000,-0.12345678901234567890123456789018000000,0.00000000000000000000000000000000000000,0.00000000000000000000000000000000000000,0.12345678901234567345678901234567800000,0.12345678901234567834567890123456780000,0.12345678901234567890123450000000000000,0.12345678901234567890123456789000000000,0.12345678901234567890123456789080000000
SELECT "count(distinct 38)_test1", count(distinct d1),count(distinct d2),count(distinct d3) FROM (SELECT d1,d2,d3 FROM cs1)a1;
count(distinct 38)_test1 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_test1 5 5 7
SELECT "sum(38)_GB(38)_test1" ,d4,d5,sum(d1),sum(d2),sum(d3) FROM cs1 GROUP BY d4,d5 ORDER BY 2,3;
sum(38)_GB(38)_test1 d4 d5 sum(d1) sum(d2) sum(d3)
sum(38)_GB(38)_test1 2 1 12345678901234567892591 1234567890123456789012.9012345678 0.12345678901234567890123449999982000000
sum(38)_GB(38)_test1 3 2 12345555444445444435346 1234567890123478901234.9012345678 0.37037036703703703070370248147104580000
sum(38)_GB(38)_test1 11111111111111111111111111111111 1 2468 11356.0000000000 -0.24691357802469135780246913578036000000
sum(38)_GB(38)_test1 11111111111111111111111111111112 1 1234 5678.0000000000 -0.12345678901234567890123456789018000000
SELECT "avg(38)_GB(38)_test1" ,d4,d5,avg(d1),avg(d2),avg(d3) FROM cs1 GROUP BY d4,d5 ORDER BY 2,3;
avg(38)_GB(38)_test1 d4 d5 avg(d1) avg(d2) avg(d3)
avg(38)_GB(38)_test1 2 1 3086419725308641973147.7500 308641972530864197253.22530864195000 0.03086419725308641972530862499995500000
avg(38)_GB(38)_test1 3 2 3086388861111361108836.5000 308641972530869725308.72530864195000 0.09259259175925925767592562036776145000
avg(38)_GB(38)_test1 11111111111111111111111111111111 1 1234.0000 5678.00000000000000 -0.12345678901234567890123456789018000000
avg(38)_GB(38)_test1 11111111111111111111111111111112 1 1234.0000 5678.00000000000000 -0.12345678901234567890123456789018000000
SELECT "count(38)_GB(38)_test1" ,d4,d5,count(d1),count(d2),count(d3) FROM cs1 GROUP BY d4,d5 ORDER BY 2,3;
count(38)_GB(38)_test1 d4 d5 count(d1) count(d2) count(d3)
count(38)_GB(38)_test1 2 1 4 4 4
count(38)_GB(38)_test1 3 2 4 4 4
count(38)_GB(38)_test1 11111111111111111111111111111111 1 2 2 2
count(38)_GB(38)_test1 11111111111111111111111111111112 1 1 1 1
SELECT "min(38)_GB(38)_test1" ,d4,d5,min(d1),min(d2),min(d3) FROM cs1 GROUP BY d4,d5 ORDER BY 2,3;
min(38)_GB(38)_test1 d4 d5 min(d1) min(d2) min(d3)
min(38)_GB(38)_test1 2 1 0 -5678.0000000000 -0.12345678901234567890123456789018000000
min(38)_GB(38)_test1 3 2 -123456789123456789 -5678.0000000000 0.00000000000000000000000000000000000000
min(38)_GB(38)_test1 11111111111111111111111111111111 1 1234 5678.0000000000 -0.12345678901234567890123456789018000000
min(38)_GB(38)_test1 11111111111111111111111111111112 1 1234 5678.0000000000 -0.12345678901234567890123456789018000000
SELECT "max(38)_GB(38)_test1" ,d4,d5,max(d1),max(d2),max(d3) FROM cs1 GROUP BY d4,d5 ORDER BY 2,3;
max(38)_GB(38)_test1 d4 d5 max(d1) max(d2) max(d3)
max(38)_GB(38)_test1 2 1 12345678901234567890123 1234567890123456789012.9012345678 0.12345678901234567890123456789000000000
max(38)_GB(38)_test1 3 2 12345678901234567890901 1234567890123478901234.9012345678 0.12345678901234567890123456789080000000
max(38)_GB(38)_test1 11111111111111111111111111111111 1 1234 5678.0000000000 -0.12345678901234567890123456789018000000
max(38)_GB(38)_test1 11111111111111111111111111111112 1 1234 5678.0000000000 -0.12345678901234567890123456789018000000
SELECT "group_concat(38)_GB(38)_test1", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM cs1 GROUP BY d4,d5 ORDER BY 2;
group_concat(38)_GB(38)_test1 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_GB(38)_test1 -123456789123456789,0,1234,12345678901234567890901 -5678.0000000000,0.0000000000,5678.0000000000,1234567890123478901234.9012345678 0.00000000000000000000000000000000000000,0.12345678901234567345678901234567800000,0.12345678901234567834567890123456780000,0.12345678901234567890123456789080000000
group_concat(38)_GB(38)_test1 0,1234,1234,12345678901234567890123 -5678.0000000000,0.0000000000,5678.0000000000,1234567890123456789012.9012345678 -0.12345678901234567890123456789018000000,0.00000000000000000000000000000000000000,0.12345678901234567890123450000000000000,0.12345678901234567890123456789000000000
group_concat(38)_GB(38)_test1 1234 5678.0000000000 -0.12345678901234567890123456789018000000
group_concat(38)_GB(38)_test1 1234,1234 5678.0000000000,5678.0000000000 -0.12345678901234567890123456789018000000,-0.12345678901234567890123456789018000000
SELECT "count(distinct 38)_GB(38)_test1", count(distinct d1),count(distinct d2),count(distinct d3) FROM cs1 GROUP BY d4,d5 ORDER BY 2,3;
count(distinct 38)_GB(38)_test1 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_GB(38)_test1 1 1 1
count(distinct 38)_GB(38)_test1 1 1 1
count(distinct 38)_GB(38)_test1 3 4 4
count(distinct 38)_GB(38)_test1 4 4 4
SELECT "sum(38)_GB(8)_test1" ,i1,sum(d1),sum(d2),sum(d3) FROM cs1 GROUP BY i1 ORDER BY 2;
sum(38)_GB(8)_test1 i1 sum(d1) sum(d2) sum(d3)
sum(38)_GB(8)_test1 1 12345678901234567896293 1234567890123456806046.9012345678 -0.24691357802469135780246920367072000000
sum(38)_GB(8)_test1 2 12345555444445444435346 1234567890123478901234.9012345678 0.37037036703703703070370248147104580000
SELECT "avg(38)_GB(8)_test1" ,i1,avg(d1),avg(d2),avg(d3) FROM cs1 GROUP BY i1 ORDER BY 2;
avg(38)_GB(8)_test1 i1 avg(d1) avg(d2) avg(d3)
avg(38)_GB(8)_test1 1 1763668414462081128041.8571 176366841446208115149.55731922397143 -0.03527336828924162254320988623867428571
avg(38)_GB(8)_test1 2 3086388861111361108836.5000 308641972530869725308.72530864195000 0.09259259175925925767592562036776145000
SELECT "count(38)_GB(8)_test1" ,i1,count(d1),count(d2),count(d3) FROM cs1 GROUP BY i1 ORDER BY 2;
count(38)_GB(8)_test1 i1 count(d1) count(d2) count(d3)
count(38)_GB(8)_test1 1 7 7 7
count(38)_GB(8)_test1 2 4 4 4
SELECT "min(38)_GB(8)_test1" ,i1,min(d1),min(d2),min(d3) FROM cs1 GROUP BY i1 ORDER BY 2;
min(38)_GB(8)_test1 i1 min(d1) min(d2) min(d3)
min(38)_GB(8)_test1 1 0 -5678.0000000000 -0.12345678901234567890123456789018000000
min(38)_GB(8)_test1 2 -123456789123456789 -5678.0000000000 0.00000000000000000000000000000000000000
SELECT "max(38)_GB(8)_test1" ,i1,max(d1),max(d2),max(d3) FROM cs1 GROUP BY i1 ORDER BY 2;
max(38)_GB(8)_test1 i1 max(d1) max(d2) max(d3)
max(38)_GB(8)_test1 1 12345678901234567890123 1234567890123456789012.9012345678 0.12345678901234567890123456789000000000
max(38)_GB(8)_test1 2 12345678901234567890901 1234567890123478901234.9012345678 0.12345678901234567890123456789080000000
SELECT "group_concat(38)_GB(8)_test1", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM cs1 GROUP BY i1 ORDER BY 2;
group_concat(38)_GB(8)_test1 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_GB(8)_test1 -123456789123456789,0,1234,12345678901234567890901 -5678.0000000000,0.0000000000,5678.0000000000,1234567890123478901234.9012345678 0.00000000000000000000000000000000000000,0.12345678901234567345678901234567800000,0.12345678901234567834567890123456780000,0.12345678901234567890123456789080000000
group_concat(38)_GB(8)_test1 0,1234,1234,1234,1234,1234,12345678901234567890123 -5678.0000000000,0.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,1234567890123456789012.9012345678 -0.12345678901234567890123456789018000000,-0.12345678901234567890123456789018000000,-0.12345678901234567890123456789018000000,-0.12345678901234567890123456789018000000,0.00000000000000000000000000000000000000,0.12345678901234567890123450000000000000,0.12345678901234567890123456789000000000
SELECT "count(distinct 38)_GB(8)_test1", count(distinct d1),count(distinct d2),count(distinct d3) FROM cs1 GROUP BY i1 ORDER BY 2;
count(distinct 38)_GB(8)_test1 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_GB(8)_test1 3 4 4
count(distinct 38)_GB(8)_test1 4 4 4
SELECT "sum(38)_test2", sum(d1),sum(d2),sum(d3) FROM cs2;
sum(38)_test2 sum(d1) sum(d2) sum(d3)
sum(38)_test2 24814814591592592577551 2469135780246935729993.8024691356 1.11111110111111110411110982092176580000
SELECT "avg(38)_test2", avg(d1),avg(d2),avg(d3) FROM cs2;
avg(38)_test2 avg(d1) avg(d2) avg(d3)
avg(38)_test2 2255892235599326597959.1818 224466889113357793635.80022446687273 0.10101010010101010037373725644743325455
SELECT "count(38)_test2", count(d1),count(d2),count(d3) FROM cs2;
count(38)_test2 count(d1) count(d2) count(d3)
count(38)_test2 11 11 11
SELECT "min(38)_test2", min(d1),min(d2),min(d3) FROM cs2;
min(38)_test2 min(d1) min(d2) min(d3)
min(38)_test2 0 0.0000000000 0.00000000000000000000000000000000000000
SELECT "max(38)_test2", max(d1),max(d2),max(d3) FROM cs2;
max(38)_test2 max(d1) max(d2) max(d3)
max(38)_test2 12345678901234567890901 1234567890123478901234.9012345678 0.12345678901234567890123456789080000000
SELECT "group_concat(38)_test2", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM cs2;
group_concat(38)_test2 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_test2 0,0,1234,1234,1234,1234,1234,1234,123456789123456789123,12345678901234567890123,12345678901234567890901 0.0000000000,0.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,1234567890123456789012.9012345678,1234567890123478901234.9012345678 0.00000000000000000000000000000000000000,0.00000000000000000000000000000000000000,0.12345678901234567345678901234567800000,0.12345678901234567834567890123456780000,0.12345678901234567890123450000000000000,0.12345678901234567890123456789000000000,0.12345678901234567890123456789018000000,0.12345678901234567890123456789018000000,0.12345678901234567890123456789018000000,0.12345678901234567890123456789018000000,0.12345678901234567890123456789080000000
SELECT "count(distinct 38)_test2", count(distinct d1),count(distinct d2),count(distinct d3) FROM cs2;
count(distinct 38)_test2 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_test2 5 4 7
SELECT "sum(38)_test2", sum(d1),sum(d2),sum(d3) FROM (SELECT d1,d2,d3 FROM cs2)a1;
sum(38)_test2 sum(d1) sum(d2) sum(d3)
sum(38)_test2 24814814591592592577551 2469135780246935729993.8024691356 1.11111110111111110411110982092176580000
SELECT "avg(38)_test2", avg(d1),avg(d2),avg(d3) FROM (SELECT d1,d2,d3 FROM cs2)a1;
avg(38)_test2 avg(d1) avg(d2) avg(d3)
avg(38)_test2 2255892235599326597959.1818 224466889113357793635.80022446687273 0.10101010010101010037373725644743325455
SELECT "count(38)_test2", count(d1),count(d2),count(d3) FROM (SELECT d1,d2,d3 FROM cs2)a1;
count(38)_test2 count(d1) count(d2) count(d3)
count(38)_test2 11 11 11
SELECT "min(38)_test2", min(d1),min(d2),min(d3) FROM (SELECT d1,d2,d3 FROM cs2)a1;
min(38)_test2 min(d1) min(d2) min(d3)
min(38)_test2 0 0.0000000000 0.00000000000000000000000000000000000000
SELECT "max(38)_test2", max(d1),max(d2),max(d3) FROM (SELECT d1,d2,d3 FROM cs2)a1;
max(38)_test2 max(d1) max(d2) max(d3)
max(38)_test2 12345678901234567890901 1234567890123478901234.9012345678 0.12345678901234567890123456789080000000
SELECT "group_concat(38)_test2", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM (SELECT d1,d2,d3 FROM cs2)a1;
group_concat(38)_test2 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_test2 0,0,1234,1234,1234,1234,1234,1234,123456789123456789123,12345678901234567890123,12345678901234567890901 0.0000000000,0.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,1234567890123456789012.9012345678,1234567890123478901234.9012345678 0.00000000000000000000000000000000000000,0.00000000000000000000000000000000000000,0.12345678901234567345678901234567800000,0.12345678901234567834567890123456780000,0.12345678901234567890123450000000000000,0.12345678901234567890123456789000000000,0.12345678901234567890123456789018000000,0.12345678901234567890123456789018000000,0.12345678901234567890123456789018000000,0.12345678901234567890123456789018000000,0.12345678901234567890123456789080000000
SELECT "count(distinct 38)_test2", count(distinct d1),count(distinct d2),count(distinct d3) FROM (SELECT d1,d2,d3 FROM cs2)a1;
count(distinct 38)_test2 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_test2 5 4 7
SELECT "sum(38)_GB(38)_test2" ,d4,d5,sum(d1),sum(d2),sum(d3) FROM cs2 GROUP BY d4,d5 ORDER BY 2,3;
sum(38)_GB(38)_test2 d4 d5 sum(d1) sum(d2) sum(d3)
sum(38)_GB(38)_test2 2 1 12345678901234567892591 1234567890123456800368.9012345678 0.37037036703703703670370363578018000000
sum(38)_GB(38)_test2 3 2 12469135690358024681258 1234567890123478912590.9012345678 0.37037036703703703070370248147104580000
sum(38)_GB(38)_test2 11111111111111111111111111111111 1 2468 11356.0000000000 0.24691357802469135780246913578036000000
sum(38)_GB(38)_test2 11111111111111111111111111111112 1 1234 5678.0000000000 0.12345678901234567890123456789018000000
SELECT "avg(38)_GB(38)_test2" ,d4,d5,avg(d1),avg(d2),avg(d3) FROM cs2 GROUP BY d4,d5 ORDER BY 2,3;
avg(38)_GB(38)_test2 d4 d5 avg(d1) avg(d2) avg(d3)
avg(38)_GB(38)_test2 2 1 3086419725308641973147.7500 308641972530864200092.22530864195000 0.09259259175925925917592590894504500000
avg(38)_GB(38)_test2 3 2 3117283922589506170314.5000 308641972530869728147.72530864195000 0.09259259175925925767592562036776145000
avg(38)_GB(38)_test2 11111111111111111111111111111111 1 1234.0000 5678.00000000000000 0.12345678901234567890123456789018000000
avg(38)_GB(38)_test2 11111111111111111111111111111112 1 1234.0000 5678.00000000000000 0.12345678901234567890123456789018000000
SELECT "count(38)_GB(38)_test2" ,d4,d5,count(d1),count(d2),count(d3) FROM cs2 GROUP BY d4,d5 ORDER BY 2,3;
count(38)_GB(38)_test2 d4 d5 count(d1) count(d2) count(d3)
count(38)_GB(38)_test2 2 1 4 4 4
count(38)_GB(38)_test2 3 2 4 4 4
count(38)_GB(38)_test2 11111111111111111111111111111111 1 2 2 2
count(38)_GB(38)_test2 11111111111111111111111111111112 1 1 1 1
SELECT "min(38)_GB(38)_test2" ,d4,d5,min(d1),min(d2),min(d3) FROM cs2 GROUP BY d4,d5 ORDER BY 2,3;
min(38)_GB(38)_test2 d4 d5 min(d1) min(d2) min(d3)
min(38)_GB(38)_test2 2 1 0 0.0000000000 0.00000000000000000000000000000000000000
min(38)_GB(38)_test2 3 2 0 0.0000000000 0.00000000000000000000000000000000000000
min(38)_GB(38)_test2 11111111111111111111111111111111 1 1234 5678.0000000000 0.12345678901234567890123456789018000000
min(38)_GB(38)_test2 11111111111111111111111111111112 1 1234 5678.0000000000 0.12345678901234567890123456789018000000
SELECT "max(38)_GB(38)_test2" ,d4,d5,max(d1),max(d2),max(d3) FROM cs2 GROUP BY d4,d5 ORDER BY 2,3;
max(38)_GB(38)_test2 d4 d5 max(d1) max(d2) max(d3)
max(38)_GB(38)_test2 2 1 12345678901234567890123 1234567890123456789012.9012345678 0.12345678901234567890123456789018000000
max(38)_GB(38)_test2 3 2 12345678901234567890901 1234567890123478901234.9012345678 0.12345678901234567890123456789080000000
max(38)_GB(38)_test2 11111111111111111111111111111111 1 1234 5678.0000000000 0.12345678901234567890123456789018000000
max(38)_GB(38)_test2 11111111111111111111111111111112 1 1234 5678.0000000000 0.12345678901234567890123456789018000000
SELECT "group_concat(38)_GB(38)_test2", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM cs2 GROUP BY d4,d5 ORDER BY 2,3;
group_concat(38)_GB(38)_test2 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_GB(38)_test2 0,1234,1234,12345678901234567890123 0.0000000000,5678.0000000000,5678.0000000000,1234567890123456789012.9012345678 0.00000000000000000000000000000000000000,0.12345678901234567890123450000000000000,0.12345678901234567890123456789000000000,0.12345678901234567890123456789018000000
group_concat(38)_GB(38)_test2 0,1234,123456789123456789123,12345678901234567890901 0.0000000000,5678.0000000000,5678.0000000000,1234567890123478901234.9012345678 0.00000000000000000000000000000000000000,0.12345678901234567345678901234567800000,0.12345678901234567834567890123456780000,0.12345678901234567890123456789080000000
group_concat(38)_GB(38)_test2 1234 5678.0000000000 0.12345678901234567890123456789018000000
group_concat(38)_GB(38)_test2 1234,1234 5678.0000000000,5678.0000000000 0.12345678901234567890123456789018000000,0.12345678901234567890123456789018000000
SELECT "count(distinct 38)_GB(38)_test2", count(distinct d1),count(distinct d2),count(distinct d3) FROM cs2 GROUP BY d4,d5 ORDER BY 2,3;
count(distinct 38)_GB(38)_test2 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_GB(38)_test2 1 1 1
count(distinct 38)_GB(38)_test2 1 1 1
count(distinct 38)_GB(38)_test2 3 3 4
count(distinct 38)_GB(38)_test2 4 3 4
SELECT "sum(38)_GB(8)_test2" ,i1,sum(d1),sum(d2),sum(d3) FROM cs2 GROUP BY i1 ORDER BY 2,3;
sum(38)_GB(8)_test2 i1 sum(d1) sum(d2) sum(d3)
sum(38)_GB(8)_test2 1 12345678901234567896293 1234567890123456817402.9012345678 0.74074073407407407340740733945072000000
sum(38)_GB(8)_test2 2 12469135690358024681258 1234567890123478912590.9012345678 0.37037036703703703070370248147104580000
SELECT "avg(38)_GB(8)_test2" ,i1,avg(d1),avg(d2),avg(d3) FROM cs2 GROUP BY i1 ORDER BY 2,3;
avg(38)_GB(8)_test2 i1 avg(d1) avg(d2) avg(d3)
avg(38)_GB(8)_test2 1 1763668414462081128041.8571 176366841446208116771.84303350968571 0.10582010486772486762962961992153142857
avg(38)_GB(8)_test2 2 3117283922589506170314.5000 308641972530869728147.72530864195000 0.09259259175925925767592562036776145000
SELECT "count(38)_GB(8)_test2" ,i1,count(d1),count(d2),count(d3) FROM cs2 GROUP BY i1 ORDER BY 2,3;
count(38)_GB(8)_test2 i1 count(d1) count(d2) count(d3)
count(38)_GB(8)_test2 1 7 7 7
count(38)_GB(8)_test2 2 4 4 4
SELECT "min(38)_GB(8)_test2" ,i1,min(d1),min(d2),min(d3) FROM cs2 GROUP BY i1 ORDER BY 2,3;
min(38)_GB(8)_test2 i1 min(d1) min(d2) min(d3)
min(38)_GB(8)_test2 1 0 0.0000000000 0.00000000000000000000000000000000000000
min(38)_GB(8)_test2 2 0 0.0000000000 0.00000000000000000000000000000000000000
SELECT "max(38)_GB(8)_test2" ,i1,max(d1),max(d2),max(d3) FROM cs2 GROUP BY i1 ORDER BY 2,3;
max(38)_GB(8)_test2 i1 max(d1) max(d2) max(d3)
max(38)_GB(8)_test2 1 12345678901234567890123 1234567890123456789012.9012345678 0.12345678901234567890123456789018000000
max(38)_GB(8)_test2 2 12345678901234567890901 1234567890123478901234.9012345678 0.12345678901234567890123456789080000000
SELECT "group_concat(38)_GB(8)_test2", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM cs2 GROUP BY i1 ORDER BY 2,3;
group_concat(38)_GB(8)_test2 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_GB(8)_test2 0,1234,1234,1234,1234,1234,12345678901234567890123 0.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,1234567890123456789012.9012345678 0.00000000000000000000000000000000000000,0.12345678901234567890123450000000000000,0.12345678901234567890123456789000000000,0.12345678901234567890123456789018000000,0.12345678901234567890123456789018000000,0.12345678901234567890123456789018000000,0.12345678901234567890123456789018000000
group_concat(38)_GB(8)_test2 0,1234,123456789123456789123,12345678901234567890901 0.0000000000,5678.0000000000,5678.0000000000,1234567890123478901234.9012345678 0.00000000000000000000000000000000000000,0.12345678901234567345678901234567800000,0.12345678901234567834567890123456780000,0.12345678901234567890123456789080000000
SELECT "count(distinct 38)_GB(8)_test2", count(distinct d1),count(distinct d2),count(distinct d3) FROM cs2 GROUP BY i1 ORDER BY 2,3;
count(distinct 38)_GB(8)_test2 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_GB(8)_test2 3 3 4
count(distinct 38)_GB(8)_test2 4 3 4
SELECT "sum(19)_test3", sum(d1),sum(d2),sum(d3) FROM cs3;
sum(19)_test3 sum(d1) sum(d2) sum(d3)
sum(19)_test3 23456793605 24691357.8024800000 0.4938271560492400000
SELECT "avg(19)_test3", avg(d1),avg(d2),avg(d3) FROM cs3;
avg(19)_test3 avg(d1) avg(d2) avg(d3)
avg(19)_test3 2932099200.6250 3086419.72531000000000 0.06172839450615500000000
SELECT "count(19)_test3", count(d1),count(d2),count(d3) FROM cs3;
count(19)_test3 count(d1) count(d2) count(d3)
count(19)_test3 8 8 8
SELECT "min(19)_test3", min(d1),min(d2),min(d3) FROM cs3;
min(19)_test3 min(d1) min(d2) min(d3)
min(19)_test3 -1234567899 -5678.0000000000 -0.1234567890123400000
SELECT "max(19)_test3", max(d1),max(d2),max(d3) FROM cs3;
max(19)_test3 max(d1) max(d2) max(d3)
max(19)_test3 12345678901 12345678.9012800000 0.1234567890123400000
SELECT "group_concat(38)_test3", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM cs3;
group_concat(38)_test3 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_test3 -1234567899,0,0,1234,1234,1234,12345678901,12345678901 -5678.0000000000,-5678.0000000000,0.0000000000,0.0000000000,5678.0000000000,5678.0000000000,12345678.9012000000,12345678.9012800000 -0.1234567890123400000,0.0000000000000000000,0.0000000000000000000,0.1234567890123000000,0.1234567890123000000,0.1234567890123000000,0.1234567890123400000,0.1234567890123400000
SELECT "count(distinct 38)_test3", count(distinct d1),count(distinct d2),count(distinct d3) FROM cs3;
count(distinct 38)_test3 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_test3 4 5 4
SELECT "sum(19)_test3", sum(d1),sum(d2),sum(d3) FROM (SELECT d1,d2,d3 FROM cs3)a1;
sum(19)_test3 sum(d1) sum(d2) sum(d3)
sum(19)_test3 23456793605 24691357.8024800000 0.4938271560492400000
SELECT "avg(19)_test3", avg(d1),avg(d2),avg(d3) FROM (SELECT d1,d2,d3 FROM cs3)a1;
avg(19)_test3 avg(d1) avg(d2) avg(d3)
avg(19)_test3 2932099200.6250 3086419.72531000000000 0.06172839450615500000000
SELECT "count(19)_test3", count(d1),count(d2),count(d3) FROM (SELECT d1,d2,d3 FROM cs3)a1;
count(19)_test3 count(d1) count(d2) count(d3)
count(19)_test3 8 8 8
SELECT "min(19)_test3", min(d1),min(d2),min(d3) FROM (SELECT d1,d2,d3 FROM cs3)a1;
min(19)_test3 min(d1) min(d2) min(d3)
min(19)_test3 -1234567899 -5678.0000000000 -0.1234567890123400000
SELECT "max(19)_test3", max(d1),max(d2),max(d3) FROM cs3;
max(19)_test3 max(d1) max(d2) max(d3)
max(19)_test3 12345678901 12345678.9012800000 0.1234567890123400000
SELECT "group_concat(38)_test3", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM (SELECT d1,d2,d3 FROM cs3)a1;
group_concat(38)_test3 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_test3 -1234567899,0,0,1234,1234,1234,12345678901,12345678901 -5678.0000000000,-5678.0000000000,0.0000000000,0.0000000000,5678.0000000000,5678.0000000000,12345678.9012000000,12345678.9012800000 -0.1234567890123400000,0.0000000000000000000,0.0000000000000000000,0.1234567890123000000,0.1234567890123000000,0.1234567890123000000,0.1234567890123400000,0.1234567890123400000
SELECT "count(distinct 38)_test3", count(distinct d1),count(distinct d2),count(distinct d3) FROM (SELECT d1,d2,d3 FROM cs3)a1;
count(distinct 38)_test3 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_test3 4 5 4
SELECT "sum(38)_GB(38)_test3" ,d4,d5,sum(d1),sum(d2),sum(d3) FROM cs3 GROUP BY d4,d5 ORDER BY 2,3;
sum(38)_GB(38)_test3 d4 d5 sum(d1) sum(d2) sum(d3)
sum(38)_GB(38)_test3 2 1 12345681369 12345678.9012800000 0.1234567890123000000
sum(38)_GB(38)_test3 3 2 11111112236 12345678.9012000000 0.3703703670369400000
SELECT "avg(38)_GB(38)_test3" ,d4,d5,avg(d1),avg(d2),avg(d3) FROM cs3 GROUP BY d4,d5 ORDER BY 2,3;
avg(38)_GB(38)_test3 d4 d5 avg(d1) avg(d2) avg(d3)
avg(38)_GB(38)_test3 2 1 3086420342.2500 3086419.72532000000000 0.03086419725307500000000
avg(38)_GB(38)_test3 3 2 2777778059.0000 3086419.72530000000000 0.09259259175923500000000
SELECT "count(38)_GB(38)_test3" ,d4,d5,count(d1),count(d2),count(d3) FROM cs3 GROUP BY d4,d5 ORDER BY 2,3;
count(38)_GB(38)_test3 d4 d5 count(d1) count(d2) count(d3)
count(38)_GB(38)_test3 2 1 4 4 4
count(38)_GB(38)_test3 3 2 4 4 4
SELECT "min(38)_GB(38)_test3" ,d4,d5,min(d1),min(d2),min(d3) FROM cs3 GROUP BY d4,d5 ORDER BY 2,3;
min(38)_GB(38)_test3 d4 d5 min(d1) min(d2) min(d3)
min(38)_GB(38)_test3 2 1 0 -5678.0000000000 -0.1234567890123400000
min(38)_GB(38)_test3 3 2 -1234567899 -5678.0000000000 0.0000000000000000000
SELECT "max(38)_GB(38)_test3" ,d4,d5,max(d1),max(d2),max(d3) FROM cs3 GROUP BY d4,d5 ORDER BY 2,3;
max(38)_GB(38)_test3 d4 d5 max(d1) max(d2) max(d3)
max(38)_GB(38)_test3 2 1 12345678901 12345678.9012800000 0.1234567890123400000
max(38)_GB(38)_test3 3 2 12345678901 12345678.9012000000 0.1234567890123400000
SELECT "group_concat(38)_GB(38)_test3", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM cs3 GROUP BY d4,d5 ORDER BY 2,3;
group_concat(38)_GB(38)_test3 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_GB(38)_test3 -1234567899,0,1234,12345678901 -5678.0000000000,0.0000000000,5678.0000000000,12345678.9012000000 0.0000000000000000000,0.1234567890123000000,0.1234567890123000000,0.1234567890123400000
group_concat(38)_GB(38)_test3 0,1234,1234,12345678901 -5678.0000000000,0.0000000000,5678.0000000000,12345678.9012800000 -0.1234567890123400000,0.0000000000000000000,0.1234567890123000000,0.1234567890123400000
SELECT "count(distinct 38)_GB(38)_test3", count(distinct d1),count(distinct d2),count(distinct d3) FROM cs3 GROUP BY d4,d5 ORDER BY 2,3;
count(distinct 38)_GB(38)_test3 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_GB(38)_test3 3 4 4
count(distinct 38)_GB(38)_test3 4 4 3
SELECT "sum(38)_GB(8)_test3" ,i1,sum(d1),sum(d2),sum(d3) FROM cs3 GROUP BY i1 ORDER BY 2,3;
sum(38)_GB(8)_test3 i1 sum(d1) sum(d2) sum(d3)
sum(38)_GB(8)_test3 1 12345681369 12345678.9012800000 0.1234567890123000000
sum(38)_GB(8)_test3 2 11111112236 12345678.9012000000 0.3703703670369400000
SELECT "avg(38)_GB(8)_test3" ,i1,avg(d1),avg(d2),avg(d3) FROM cs3 GROUP BY i1 ORDER BY 2,3;
avg(38)_GB(8)_test3 i1 avg(d1) avg(d2) avg(d3)
avg(38)_GB(8)_test3 1 3086420342.2500 3086419.72532000000000 0.03086419725307500000000
avg(38)_GB(8)_test3 2 2777778059.0000 3086419.72530000000000 0.09259259175923500000000
SELECT "count(38)_GB(8)_test3" ,i1,count(d1),count(d2),count(d3) FROM cs3 GROUP BY i1 ORDER BY 2,3;
count(38)_GB(8)_test3 i1 count(d1) count(d2) count(d3)
count(38)_GB(8)_test3 1 4 4 4
count(38)_GB(8)_test3 2 4 4 4
SELECT "min(38)_GB(8)_test3" ,i1,min(d1),min(d2),min(d3) FROM cs3 GROUP BY i1 ORDER BY 2,3;
min(38)_GB(8)_test3 i1 min(d1) min(d2) min(d3)
min(38)_GB(8)_test3 1 0 -5678.0000000000 -0.1234567890123400000
min(38)_GB(8)_test3 2 -1234567899 -5678.0000000000 0.0000000000000000000
SELECT "max(38)_GB(8)_test3" ,i1,max(d1),max(d2),max(d3) FROM cs3 GROUP BY i1 ORDER BY 2,3;
max(38)_GB(8)_test3 i1 max(d1) max(d2) max(d3)
max(38)_GB(8)_test3 1 12345678901 12345678.9012800000 0.1234567890123400000
max(38)_GB(8)_test3 2 12345678901 12345678.9012000000 0.1234567890123400000
SELECT "group_concat(38)_GB(8)_test3", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM cs3 GROUP BY i1 ORDER BY 2,3;
group_concat(38)_GB(8)_test3 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_GB(8)_test3 -1234567899,0,1234,12345678901 -5678.0000000000,0.0000000000,5678.0000000000,12345678.9012000000 0.0000000000000000000,0.1234567890123000000,0.1234567890123000000,0.1234567890123400000
group_concat(38)_GB(8)_test3 0,1234,1234,12345678901 -5678.0000000000,0.0000000000,5678.0000000000,12345678.9012800000 -0.1234567890123400000,0.0000000000000000000,0.1234567890123000000,0.1234567890123400000
SELECT "count(distinct 38)_GB(8)_test3", count(distinct d1),count(distinct d2),count(distinct d3) FROM cs3 GROUP BY i1 ORDER BY 2,3;
count(distinct 38)_GB(8)_test3 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_GB(8)_test3 3 4 4
count(distinct 38)_GB(8)_test3 4 4 3
SELECT "sum(19)_test4", sum(d1),sum(d2),sum(d3) FROM cs4;
sum(19)_test4 sum(d1) sum(d2) sum(d3)
sum(19)_test4 25925929403 24714069.8025100000 0.7407407340739200000
SELECT "avg(19)_test4", avg(d1),avg(d2),avg(d3) FROM cs4;
avg(19)_test4 avg(d1) avg(d2) avg(d3)
avg(19)_test4 3240741175.3750 3089258.72531375000000 0.09259259175924000000000
SELECT "count(19)_test4", count(d1),count(d2),count(d3) FROM cs4;
count(19)_test4 count(d1) count(d2) count(d3)
count(19)_test4 8 8 8
SELECT "min(19)_test4", min(d1),min(d2),min(d3) FROM cs4;
min(19)_test4 min(d1) min(d2) min(d3)
min(19)_test4 0 0.0000000000 0.0000000000000000000
SELECT "max(19)_test4", max(d1),max(d2),max(d3) FROM cs4;
max(19)_test4 max(d1) max(d2) max(d3)
max(19)_test4 12345678901 12345678.9012800000 0.1234567890123400000
SELECT "group_concat(38)_test4", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM cs4;
group_concat(38)_test4 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_test4 0,0,1234,1234,1234,1234567899,12345678901,12345678901 0.0000000000,0.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,12345678.9012300000,12345678.9012800000 0.0000000000000000000,0.0000000000000000000,0.1234567890123000000,0.1234567890123000000,0.1234567890123000000,0.1234567890123400000,0.1234567890123400000,0.1234567890123400000
SELECT "count(distinct 38)_test4", count(distinct d1),count(distinct d2),count(distinct d3) FROM cs4;
count(distinct 38)_test4 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_test4 4 4 3
SELECT "sum(19)_test4", sum(d1),sum(d2),sum(d3) FROM (SELECT d1,d2,d3 FROM cs4)a1;
sum(19)_test4 sum(d1) sum(d2) sum(d3)
sum(19)_test4 25925929403 24714069.8025100000 0.7407407340739200000
SELECT "avg(19)_test4", avg(d1),avg(d2),avg(d3) FROM (SELECT d1,d2,d3 FROM cs4)a1;
avg(19)_test4 avg(d1) avg(d2) avg(d3)
avg(19)_test4 3240741175.3750 3089258.72531375000000 0.09259259175924000000000
SELECT "count(19)_test4", count(d1),count(d2),count(d3) FROM (SELECT d1,d2,d3 FROM cs4)a1;
count(19)_test4 count(d1) count(d2) count(d3)
count(19)_test4 8 8 8
SELECT "min(19)_test4", min(d1),min(d2),min(d3) FROM (SELECT d1,d2,d3 FROM cs4)a1;
min(19)_test4 min(d1) min(d2) min(d3)
min(19)_test4 0 0.0000000000 0.0000000000000000000
SELECT "max(19)_test4", max(d1),max(d2),max(d3) FROM (SELECT d1,d2,d3 FROM cs4)a1;
max(19)_test4 max(d1) max(d2) max(d3)
max(19)_test4 12345678901 12345678.9012800000 0.1234567890123400000
SELECT "group_concat(38)_test4", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM (SELECT d1,d2,d3 FROM cs4)a1;
group_concat(38)_test4 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_test4 0,0,1234,1234,1234,1234567899,12345678901,12345678901 0.0000000000,0.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,5678.0000000000,12345678.9012300000,12345678.9012800000 0.0000000000000000000,0.0000000000000000000,0.1234567890123000000,0.1234567890123000000,0.1234567890123000000,0.1234567890123400000,0.1234567890123400000,0.1234567890123400000
SELECT "count(distinct 38)_test4", count(distinct d1),count(distinct d2),count(distinct d3) FROM (SELECT d1,d2,d3 FROM cs4)a1;
count(distinct 38)_test4 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_test4 4 4 3
SELECT "sum(38)_GB(38)_test4" ,d4,d5,sum(d1),sum(d2),sum(d3) FROM cs4 GROUP BY d4,d5 ORDER BY 2,3;
sum(38)_GB(38)_test4 d4 d5 sum(d1) sum(d2) sum(d3)
sum(38)_GB(38)_test4 2 1 12345681369 12357034.9012800000 0.3703703670369800000
sum(38)_GB(38)_test4 3 2 13580248034 12357034.9012300000 0.3703703670369400000
SELECT "avg(38)_GB(38)_test4" ,d4,d5,avg(d1),avg(d2),avg(d3) FROM cs4 GROUP BY d4,d5 ORDER BY 2,3;
avg(38)_GB(38)_test4 d4 d5 avg(d1) avg(d2) avg(d3)
avg(38)_GB(38)_test4 2 1 3086420342.2500 3089258.72532000000000 0.09259259175924500000000
avg(38)_GB(38)_test4 3 2 3395062008.5000 3089258.72530750000000 0.09259259175923500000000
SELECT "count(38)_GB(38)_test4" ,d4,d5,count(d1),count(d2),count(d3) FROM cs4 GROUP BY d4,d5 ORDER BY 2,3;
count(38)_GB(38)_test4 d4 d5 count(d1) count(d2) count(d3)
count(38)_GB(38)_test4 2 1 4 4 4
count(38)_GB(38)_test4 3 2 4 4 4
SELECT "min(38)_GB(38)_test4" ,d4,d5,min(d1),min(d2),min(d3) FROM cs4 GROUP BY d4,d5 ORDER BY 2,3;
min(38)_GB(38)_test4 d4 d5 min(d1) min(d2) min(d3)
min(38)_GB(38)_test4 2 1 0 0.0000000000 0.0000000000000000000
min(38)_GB(38)_test4 3 2 0 0.0000000000 0.0000000000000000000
SELECT "max(38)_GB(38)_test4" ,d4,d5,max(d1),max(d2),max(d3) FROM cs4 GROUP BY d4,d5 ORDER BY 2,3;
max(38)_GB(38)_test4 d4 d5 max(d1) max(d2) max(d3)
max(38)_GB(38)_test4 2 1 12345678901 12345678.9012800000 0.1234567890123400000
max(38)_GB(38)_test4 3 2 12345678901 12345678.9012300000 0.1234567890123400000
SELECT "group_concat(38)_GB(38)_test4", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM cs4 GROUP BY d4,d5 ORDER BY 2,3;
group_concat(38)_GB(38)_test4 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_GB(38)_test4 0,1234,1234,12345678901 0.0000000000,5678.0000000000,5678.0000000000,12345678.9012800000 0.0000000000000000000,0.1234567890123000000,0.1234567890123400000,0.1234567890123400000
group_concat(38)_GB(38)_test4 0,1234,1234567899,12345678901 0.0000000000,5678.0000000000,5678.0000000000,12345678.9012300000 0.0000000000000000000,0.1234567890123000000,0.1234567890123000000,0.1234567890123400000
SELECT "count(distinct 38)_GB(38)_test4", count(distinct d1),count(distinct d2),count(distinct d3) FROM cs4 GROUP BY d4,d5 ORDER BY 2,3;
count(distinct 38)_GB(38)_test4 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_GB(38)_test4 3 3 3
count(distinct 38)_GB(38)_test4 4 3 3
SELECT "sum(38)_GB(38)_test4" ,i1,sum(d1),sum(d2),sum(d3) FROM cs4 GROUP BY i1 ORDER BY 2,3;
sum(38)_GB(38)_test4 i1 sum(d1) sum(d2) sum(d3)
sum(38)_GB(38)_test4 1 12345681369 12357034.9012800000 0.3703703670369800000
sum(38)_GB(38)_test4 2 13580248034 12357034.9012300000 0.3703703670369400000
SELECT "avg(38)_GB(38)_test4" ,i1,avg(d1),avg(d2),avg(d3) FROM cs4 GROUP BY i1 ORDER BY 2,3;
avg(38)_GB(38)_test4 i1 avg(d1) avg(d2) avg(d3)
avg(38)_GB(38)_test4 1 3086420342.2500 3089258.72532000000000 0.09259259175924500000000
avg(38)_GB(38)_test4 2 3395062008.5000 3089258.72530750000000 0.09259259175923500000000
SELECT "count(38)_GB(38)_test4" ,i1,count(d1),count(d2),count(d3) FROM cs4 GROUP BY i1 ORDER BY 2,3;
count(38)_GB(38)_test4 i1 count(d1) count(d2) count(d3)
count(38)_GB(38)_test4 1 4 4 4
count(38)_GB(38)_test4 2 4 4 4
SELECT "min(38)_GB(38)_test4" ,i1,min(d1),min(d2),min(d3) FROM cs4 GROUP BY i1 ORDER BY 2,3;
min(38)_GB(38)_test4 i1 min(d1) min(d2) min(d3)
min(38)_GB(38)_test4 1 0 0.0000000000 0.0000000000000000000
min(38)_GB(38)_test4 2 0 0.0000000000 0.0000000000000000000
SELECT "max(38)_GB(38)_test4" ,i1,max(d1),max(d2),max(d3) FROM cs4 GROUP BY i1 ORDER BY 2,3;
max(38)_GB(38)_test4 i1 max(d1) max(d2) max(d3)
max(38)_GB(38)_test4 1 12345678901 12345678.9012800000 0.1234567890123400000
max(38)_GB(38)_test4 2 12345678901 12345678.9012300000 0.1234567890123400000
SELECT "group_concat(38)_GB(8)_test4", group_concat(d1 ORDER BY d1),group_concat(d2 ORDER BY d2),group_concat(d3 ORDER BY d3) FROM cs4 GROUP BY i1 ORDER BY 2,3;
group_concat(38)_GB(8)_test4 group_concat(d1 ORDER BY d1) group_concat(d2 ORDER BY d2) group_concat(d3 ORDER BY d3)
group_concat(38)_GB(8)_test4 0,1234,1234,12345678901 0.0000000000,5678.0000000000,5678.0000000000,12345678.9012800000 0.0000000000000000000,0.1234567890123000000,0.1234567890123400000,0.1234567890123400000
group_concat(38)_GB(8)_test4 0,1234,1234567899,12345678901 0.0000000000,5678.0000000000,5678.0000000000,12345678.9012300000 0.0000000000000000000,0.1234567890123000000,0.1234567890123000000,0.1234567890123400000
SELECT "count(distinct 38)_GB(8)_test4", count(distinct d1),count(distinct d2),count(distinct d3) FROM cs4 GROUP BY i1 ORDER BY 2,3;
count(distinct 38)_GB(8)_test4 count(distinct d1) count(distinct d2) count(distinct d3)
count(distinct 38)_GB(8)_test4 3 3 3
count(distinct 38)_GB(8)_test4 4 3 3
SELECT sum(d1), "sum(38)_test1", sum(d2),sum(d3) FROM cs1;
sum(d1) sum(38)_test1 sum(d2) sum(d3)
24691234345680012331639 sum(38)_test1 2469135780246935707281.8024691356 0.12345678901234567290123327780032580000
INSERT INTO cs5 VALUES (1),(0.1),(0.05),(0.05),(0.05),(0.05),(0.2),(0.154),(0.1),(0.05),(0.05);
SELECT avg(d) FROM cs5;
avg(d)
0.16854545454545454545455
UPDATE cs5 SET d=-d;
SELECT avg(d) FROM cs5;
avg(d)
-0.16854545454545454545455
DROP TABLE cs1;
DROP TABLE cs2;
DROP TABLE cs3;
DROP TABLE cs4;
DROP TABLE cs5;
DROP DATABASE test_mcol641_aggregate;
View Git Blame Copy Permalink