mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-26 11:48:52 +03:00
46 lines
2.5 KiB
Plaintext
46 lines
2.5 KiB
Plaintext
-- source ../include/have_columnstore.inc
|
|
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mcol641_joins_db;
|
|
--enable_warnings
|
|
|
|
CREATE DATABASE mcol641_joins_db;
|
|
USE mcol641_joins_db;
|
|
|
|
--disable_query_log
|
|
SET default_storage_engine=ColumnStore;
|
|
--enable_query_log
|
|
|
|
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;
|
|
SELECT cs2.de1, cs1.d1, cs1.d2 FROM cs1 INNER JOIN cs2 ON cs1.d3 = cs2.de1 ORDER BY 1;
|
|
|
|
SELECT cs2.de2, cs1.d1, cs1.d3, cs2.de1 FROM cs1 LEFT JOIN cs2 ON cs1.d2 = cs2.de2 ORDER BY 2;
|
|
SELECT cs1.d3, cs1.d1, cs1.d2, cs2.de2 FROM cs1 LEFT JOIN cs2 ON cs1.d3 = cs2.de1 ORDER BY 1;
|
|
|
|
SELECT cs1.d1, cs1.d3, cs2.de1, cs2.de2 FROM cs1 RIGHT JOIN cs2 ON cs1.d2 = cs2.de2 ORDER BY 3;
|
|
SELECT cs2.de1, cs2.de2, cs1.d1, cs1.d2 FROM cs1 RIGHT JOIN cs2 ON cs1.d3 = cs2.de1 ORDER BY 1;
|
|
|
|
SELECT * FROM cs1 WHERE cs1.d2 IN (SELECT cs2.de2 FROM cs2 WHERE cs2.de2 = cs1.d2) ORDER BY 1;
|
|
SELECT * FROM cs2 WHERE cs2.de2 IN (SELECT cs1.d2 FROM cs1 WHERE cs1.d2 = cs2.de2) ORDER BY 1;
|
|
SELECT * FROM cs1 WHERE cs1.d3 IN (SELECT cs2.de1 FROM cs2 WHERE cs2.de1 = cs1.d3) ORDER BY 1;
|
|
SELECT * FROM cs2 WHERE cs2.de1 IN (SELECT cs1.d3 FROM cs1 WHERE cs1.d3 = cs2.de1) ORDER BY 1;
|
|
|
|
SELECT count(*) FROM cs1 where exists (SELECT cs2.de2 FROM cs2 WHERE cs1.d2=cs2.de2);
|
|
SELECT count(*) FROM cs2 where not exists (SELECT cs1.d2 FROM cs1 WHERE cs1.d2=cs2.de2);
|
|
|
|
# Clean UP
|
|
DROP DATABASE mcol641_joins_db;
|