mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-28 06:45:06 +03:00
72 lines
5.8 KiB
Plaintext
72 lines
5.8 KiB
Plaintext
DROP DATABASE IF EXISTS mcol641_joins_db;
|
|
CREATE DATABASE mcol641_joins_db;
|
|
USE mcol641_joins_db;
|
|
CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38,10), d3 DECIMAL(38,38)) ENGINE=columnstore;
|
|
CREATE TABLE cs2 (de1 DECIMAL(38,38), de2 DECIMAL(38,10)) ENGINE=columnstore;
|
|
INSERT INTO cs1 values (99999999999999999999999999999999999998, 9999999999999999999999999999.9999999998, 0.99999999999999999999999999999999999998);
|
|
INSERT INTO cs1 VALUES (12345678901234567890123456789012345678, 1234567890123456789012345678.1234567890, 0.12345678901234567890123456789012345678);
|
|
INSERT INTO cs1 VALUES (-12345678901234567890123456789012345678, -1234567890123456789012345678.1234567890, -0.12345678901234567890123456789012345678);
|
|
INSERT INTO cs1 VALUES (0, 1.1234567891, 0.1234567890123456789012345678912345);
|
|
INSERT INTO cs2 values (0.99999999999999999999999999999999999998, 9999999999999999999999999999.9999999998);
|
|
INSERT INTO cs2 VALUES (0.12345678901234567890123456789012345678, 1234567890123456789012345678.0123456789);
|
|
INSERT INTO cs2 VALUES (-0.12345678901234567890123456789012345678, -1234567890123456789012345678.0123456789);
|
|
INSERT INTO cs2 VALUES (0.12345678901234567890123456789, 1.1234567891);
|
|
SELECT cs1.d1, cs1.d2, cs2.de1 FROM cs1 INNER JOIN cs2 ON cs1.d2 = cs2.de2 ORDER BY 1;
|
|
d1 d2 de1
|
|
0 1.1234567891 0.12345678901234567890123456789000000000
|
|
99999999999999999999999999999999999998 9999999999999999999999999999.9999999998 0.99999999999999999999999999999999999998
|
|
SELECT cs2.de1, cs1.d1, cs1.d2 FROM cs1 INNER JOIN cs2 ON cs1.d3 = cs2.de1 ORDER BY 1;
|
|
de1 d1 d2
|
|
-0.12345678901234567890123456789012345678 -12345678901234567890123456789012345678 -1234567890123456789012345678.1234567890
|
|
0.12345678901234567890123456789012345678 12345678901234567890123456789012345678 1234567890123456789012345678.1234567890
|
|
0.99999999999999999999999999999999999998 99999999999999999999999999999999999998 9999999999999999999999999999.9999999998
|
|
SELECT cs2.de2, cs1.d1, cs1.d3, cs2.de1 FROM cs1 LEFT JOIN cs2 ON cs1.d2 = cs2.de2 ORDER BY 2;
|
|
de2 d1 d3 de1
|
|
NULL -12345678901234567890123456789012345678 -0.12345678901234567890123456789012345678 NULL
|
|
1.1234567891 0 0.12345678901234567890123456789123450000 0.12345678901234567890123456789000000000
|
|
NULL 12345678901234567890123456789012345678 0.12345678901234567890123456789012345678 NULL
|
|
9999999999999999999999999999.9999999998 99999999999999999999999999999999999998 0.99999999999999999999999999999999999998 0.99999999999999999999999999999999999998
|
|
SELECT cs1.d3, cs1.d1, cs1.d2, cs2.de2 FROM cs1 LEFT JOIN cs2 ON cs1.d3 = cs2.de1 ORDER BY 1;
|
|
d3 d1 d2 de2
|
|
-0.12345678901234567890123456789012345678 -12345678901234567890123456789012345678 -1234567890123456789012345678.1234567890 -1234567890123456789012345678.0123456789
|
|
0.12345678901234567890123456789012345678 12345678901234567890123456789012345678 1234567890123456789012345678.1234567890 1234567890123456789012345678.0123456789
|
|
0.12345678901234567890123456789123450000 0 1.1234567891 NULL
|
|
0.99999999999999999999999999999999999998 99999999999999999999999999999999999998 9999999999999999999999999999.9999999998 9999999999999999999999999999.9999999998
|
|
SELECT cs1.d1, cs1.d3, cs2.de1, cs2.de2 FROM cs1 RIGHT JOIN cs2 ON cs1.d2 = cs2.de2 ORDER BY 3;
|
|
d1 d3 de1 de2
|
|
NULL NULL -0.12345678901234567890123456789012345678 -1234567890123456789012345678.0123456789
|
|
0 0.12345678901234567890123456789123450000 0.12345678901234567890123456789000000000 1.1234567891
|
|
NULL NULL 0.12345678901234567890123456789012345678 1234567890123456789012345678.0123456789
|
|
99999999999999999999999999999999999998 0.99999999999999999999999999999999999998 0.99999999999999999999999999999999999998 9999999999999999999999999999.9999999998
|
|
SELECT cs2.de1, cs2.de2, cs1.d1, cs1.d2 FROM cs1 RIGHT JOIN cs2 ON cs1.d3 = cs2.de1 ORDER BY 1;
|
|
de1 de2 d1 d2
|
|
-0.12345678901234567890123456789012345678 -1234567890123456789012345678.0123456789 -12345678901234567890123456789012345678 -1234567890123456789012345678.1234567890
|
|
0.12345678901234567890123456789000000000 1.1234567891 NULL NULL
|
|
0.12345678901234567890123456789012345678 1234567890123456789012345678.0123456789 12345678901234567890123456789012345678 1234567890123456789012345678.1234567890
|
|
0.99999999999999999999999999999999999998 9999999999999999999999999999.9999999998 99999999999999999999999999999999999998 9999999999999999999999999999.9999999998
|
|
SELECT * FROM cs1 WHERE cs1.d2 IN (SELECT cs2.de2 FROM cs2 WHERE cs2.de2 = cs1.d2) ORDER BY 1;
|
|
d1 d2 d3
|
|
0 1.1234567891 0.12345678901234567890123456789123450000
|
|
99999999999999999999999999999999999998 9999999999999999999999999999.9999999998 0.99999999999999999999999999999999999998
|
|
SELECT * FROM cs2 WHERE cs2.de2 IN (SELECT cs1.d2 FROM cs1 WHERE cs1.d2 = cs2.de2) ORDER BY 1;
|
|
de1 de2
|
|
0.12345678901234567890123456789000000000 1.1234567891
|
|
0.99999999999999999999999999999999999998 9999999999999999999999999999.9999999998
|
|
SELECT * FROM cs1 WHERE cs1.d3 IN (SELECT cs2.de1 FROM cs2 WHERE cs2.de1 = cs1.d3) ORDER BY 1;
|
|
d1 d2 d3
|
|
-12345678901234567890123456789012345678 -1234567890123456789012345678.1234567890 -0.12345678901234567890123456789012345678
|
|
12345678901234567890123456789012345678 1234567890123456789012345678.1234567890 0.12345678901234567890123456789012345678
|
|
99999999999999999999999999999999999998 9999999999999999999999999999.9999999998 0.99999999999999999999999999999999999998
|
|
SELECT * FROM cs2 WHERE cs2.de1 IN (SELECT cs1.d3 FROM cs1 WHERE cs1.d3 = cs2.de1) ORDER BY 1;
|
|
de1 de2
|
|
-0.12345678901234567890123456789012345678 -1234567890123456789012345678.0123456789
|
|
0.12345678901234567890123456789012345678 1234567890123456789012345678.0123456789
|
|
0.99999999999999999999999999999999999998 9999999999999999999999999999.9999999998
|
|
SELECT count(*) FROM cs1 where exists (SELECT cs2.de2 FROM cs2 WHERE cs1.d2=cs2.de2);
|
|
count(*)
|
|
2
|
|
SELECT count(*) FROM cs2 where not exists (SELECT cs1.d2 FROM cs1 WHERE cs1.d2=cs2.de2);
|
|
count(*)
|
|
2
|
|
DROP DATABASE mcol641_joins_db;
|