You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
465 lines
8.6 KiB
465 lines
8.6 KiB
3 months ago
|
SET @@internal_tmp_mem_storage_engine = TempTable;
|
||
|
|
CREATE TABLE t_int (c INT);
|
||
|
|
CREATE TABLE t_char (c CHAR(20));
|
||
|
|
CREATE TABLE t_varchar (c VARCHAR(20));
|
||
|
|
CREATE TABLE t_text (c TEXT);
|
||
|
|
CREATE TABLE t_blob (c BLOB);
|
||
|
|
CREATE TABLE t_json (c JSON);
|
||
|
|
CREATE TABLE t_point (c POINT);
|
||
|
|
CREATE TABLE t_geom (c GEOMETRY);
|
||
|
|
INSERT INTO t_int VALUES
|
||
|
|
(1),
|
||
|
|
(2),
|
||
|
|
(2),
|
||
|
|
(3),
|
||
|
|
(4),
|
||
|
|
(NULL);
|
||
|
|
INSERT INTO t_char VALUES
|
||
|
|
('abcde'),
|
||
|
|
('fghij'),
|
||
|
|
('fghij'),
|
||
|
|
('klmno '),
|
||
|
|
('stxyz'),
|
||
|
|
(''),
|
||
|
|
(NULL);
|
||
|
|
INSERT INTO t_varchar VALUES
|
||
|
|
('abcde'),
|
||
|
|
('fghij'),
|
||
|
|
('fghij'),
|
||
|
|
('klmno '),
|
||
|
|
('stxyz'),
|
||
|
|
(''),
|
||
|
|
(NULL);
|
||
|
|
INSERT INTO t_text VALUES
|
||
|
|
('abcde'),
|
||
|
|
('fghij'),
|
||
|
|
('fghij'),
|
||
|
|
('klmno '),
|
||
|
|
('stxyz'),
|
||
|
|
(''),
|
||
|
|
(NULL);
|
||
|
|
INSERT INTO t_blob VALUES
|
||
|
|
('abcde'),
|
||
|
|
('fghij'),
|
||
|
|
('fghij'),
|
||
|
|
('klmno '),
|
||
|
|
('stxyz'),
|
||
|
|
(''),
|
||
|
|
(NULL);
|
||
|
|
INSERT INTO t_json VALUES
|
||
|
|
('{"k1": "value", "k2": [10, 20]}'),
|
||
|
|
('["hot", "cold"]'),
|
||
|
|
('["hot", "cold"]'),
|
||
|
|
('["a", "b", 1]'),
|
||
|
|
('{"key": "value"}'),
|
||
|
|
(NULL);
|
||
|
|
INSERT INTO t_point VALUES
|
||
|
|
(ST_PointFromText('POINT(10 10)')),
|
||
|
|
(ST_PointFromText('POINT(50 10)')),
|
||
|
|
(ST_PointFromText('POINT(50 10)')),
|
||
|
|
(ST_PointFromText('POINT(-1 -2)')),
|
||
|
|
(ST_PointFromText('POINT(10 50)')),
|
||
|
|
(NULL);
|
||
|
|
INSERT INTO t_geom VALUES
|
||
|
|
(ST_PointFromText('POINT(10 10)')),
|
||
|
|
(ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
|
||
|
|
(ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
|
||
|
|
(ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
|
||
|
|
(ST_LineFromText('LINESTRING(0 0,0 10,10 0)')),
|
||
|
|
(NULL);
|
||
|
|
SHOW STATUS LIKE 'Created_tmp_tables';
|
||
|
|
Variable_name Value
|
||
|
|
Created_tmp_tables 0
|
||
|
|
SHOW STATUS LIKE 'Created_tmp_disk_tables';
|
||
|
|
Variable_name Value
|
||
|
|
Created_tmp_disk_tables 0
|
||
|
|
# ---------------------------------------------------------------------
|
||
|
|
# Scenario 1:
|
||
|
|
#
|
||
|
|
SELECT DISTINCT * FROM
|
||
|
|
t_int AS t1,
|
||
|
|
t_int AS t2;
|
||
|
|
c c
|
||
|
|
1 1
|
||
|
|
1 2
|
||
|
|
1 3
|
||
|
|
1 4
|
||
|
|
1 NULL
|
||
|
|
2 1
|
||
|
|
2 2
|
||
|
|
2 3
|
||
|
|
2 4
|
||
|
|
2 NULL
|
||
|
|
3 1
|
||
|
|
3 2
|
||
|
|
3 3
|
||
|
|
3 4
|
||
|
|
3 NULL
|
||
|
|
4 1
|
||
|
|
4 2
|
||
|
|
4 3
|
||
|
|
4 4
|
||
|
|
4 NULL
|
||
|
|
NULL 1
|
||
|
|
NULL 2
|
||
|
|
NULL 3
|
||
|
|
NULL 4
|
||
|
|
NULL NULL
|
||
|
|
SELECT DISTINCT * FROM
|
||
|
|
t_char AS t1,
|
||
|
|
t_char AS t2;
|
||
|
|
c c
|
||
|
|
|
||
|
|
NULL
|
||
|
|
abcde
|
||
|
|
fghij
|
||
|
|
klmno
|
||
|
|
stxyz
|
||
|
|
NULL
|
||
|
|
NULL NULL
|
||
|
|
NULL abcde
|
||
|
|
NULL fghij
|
||
|
|
NULL klmno
|
||
|
|
NULL stxyz
|
||
|
|
abcde
|
||
|
|
abcde NULL
|
||
|
|
abcde abcde
|
||
|
|
abcde fghij
|
||
|
|
abcde klmno
|
||
|
|
abcde stxyz
|
||
|
|
fghij
|
||
|
|
fghij NULL
|
||
|
|
fghij abcde
|
||
|
|
fghij fghij
|
||
|
|
fghij klmno
|
||
|
|
fghij stxyz
|
||
|
|
klmno
|
||
|
|
klmno NULL
|
||
|
|
klmno abcde
|
||
|
|
klmno fghij
|
||
|
|
klmno klmno
|
||
|
|
klmno stxyz
|
||
|
|
stxyz
|
||
|
|
stxyz NULL
|
||
|
|
stxyz abcde
|
||
|
|
stxyz fghij
|
||
|
|
stxyz klmno
|
||
|
|
stxyz stxyz
|
||
|
|
SELECT DISTINCT * FROM
|
||
|
|
t_varchar AS t1,
|
||
|
|
t_varchar AS t2;
|
||
|
|
c c
|
||
|
|
|
||
|
|
NULL
|
||
|
|
abcde
|
||
|
|
fghij
|
||
|
|
klmno
|
||
|
|
stxyz
|
||
|
|
NULL
|
||
|
|
NULL NULL
|
||
|
|
NULL abcde
|
||
|
|
NULL fghij
|
||
|
|
NULL klmno
|
||
|
|
NULL stxyz
|
||
|
|
abcde
|
||
|
|
abcde NULL
|
||
|
|
abcde abcde
|
||
|
|
abcde fghij
|
||
|
|
abcde klmno
|
||
|
|
abcde stxyz
|
||
|
|
fghij
|
||
|
|
fghij NULL
|
||
|
|
fghij abcde
|
||
|
|
fghij fghij
|
||
|
|
fghij klmno
|
||
|
|
fghij stxyz
|
||
|
|
klmno
|
||
|
|
klmno NULL
|
||
|
|
klmno abcde
|
||
|
|
klmno fghij
|
||
|
|
klmno klmno
|
||
|
|
klmno stxyz
|
||
|
|
stxyz
|
||
|
|
stxyz NULL
|
||
|
|
stxyz abcde
|
||
|
|
stxyz fghij
|
||
|
|
stxyz klmno
|
||
|
|
stxyz stxyz
|
||
|
|
SELECT DISTINCT * FROM
|
||
|
|
t_text AS t1,
|
||
|
|
t_text AS t2;
|
||
|
|
c c
|
||
|
|
|
||
|
|
NULL
|
||
|
|
abcde
|
||
|
|
fghij
|
||
|
|
klmno
|
||
|
|
stxyz
|
||
|
|
NULL
|
||
|
|
NULL NULL
|
||
|
|
NULL abcde
|
||
|
|
NULL fghij
|
||
|
|
NULL klmno
|
||
|
|
NULL stxyz
|
||
|
|
abcde
|
||
|
|
abcde NULL
|
||
|
|
abcde abcde
|
||
|
|
abcde fghij
|
||
|
|
abcde klmno
|
||
|
|
abcde stxyz
|
||
|
|
fghij
|
||
|
|
fghij NULL
|
||
|
|
fghij abcde
|
||
|
|
fghij fghij
|
||
|
|
fghij klmno
|
||
|
|
fghij stxyz
|
||
|
|
klmno
|
||
|
|
klmno NULL
|
||
|
|
klmno abcde
|
||
|
|
klmno fghij
|
||
|
|
klmno klmno
|
||
|
|
klmno stxyz
|
||
|
|
stxyz
|
||
|
|
stxyz NULL
|
||
|
|
stxyz abcde
|
||
|
|
stxyz fghij
|
||
|
|
stxyz klmno
|
||
|
|
stxyz stxyz
|
||
|
|
SELECT DISTINCT * FROM
|
||
|
|
t_blob AS t1,
|
||
|
|
t_blob AS t2;
|
||
|
|
c c
|
||
|
|
|
||
|
|
NULL
|
||
|
|
abcde
|
||
|
|
fghij
|
||
|
|
klmno
|
||
|
|
stxyz
|
||
|
|
NULL
|
||
|
|
NULL NULL
|
||
|
|
NULL abcde
|
||
|
|
NULL fghij
|
||
|
|
NULL klmno
|
||
|
|
NULL stxyz
|
||
|
|
abcde
|
||
|
|
abcde NULL
|
||
|
|
abcde abcde
|
||
|
|
abcde fghij
|
||
|
|
abcde klmno
|
||
|
|
abcde stxyz
|
||
|
|
fghij
|
||
|
|
fghij NULL
|
||
|
|
fghij abcde
|
||
|
|
fghij fghij
|
||
|
|
fghij klmno
|
||
|
|
fghij stxyz
|
||
|
|
klmno
|
||
|
|
klmno NULL
|
||
|
|
klmno abcde
|
||
|
|
klmno fghij
|
||
|
|
klmno klmno
|
||
|
|
klmno stxyz
|
||
|
|
stxyz
|
||
|
|
stxyz NULL
|
||
|
|
stxyz abcde
|
||
|
|
stxyz fghij
|
||
|
|
stxyz klmno
|
||
|
|
stxyz stxyz
|
||
|
|
SELECT DISTINCT * FROM
|
||
|
|
t_json AS t1,
|
||
|
|
t_json AS t2;
|
||
|
|
c c
|
||
|
|
NULL NULL
|
||
|
|
NULL ["a", "b", 1]
|
||
|
|
NULL ["hot", "cold"]
|
||
|
|
NULL {"k1": "value", "k2": [10, 20]}
|
||
|
|
NULL {"key": "value"}
|
||
|
|
["a", "b", 1] NULL
|
||
|
|
["a", "b", 1] ["a", "b", 1]
|
||
|
|
["a", "b", 1] ["hot", "cold"]
|
||
|
|
["a", "b", 1] {"k1": "value", "k2": [10, 20]}
|
||
|
|
["a", "b", 1] {"key": "value"}
|
||
|
|
["hot", "cold"] NULL
|
||
|
|
["hot", "cold"] ["a", "b", 1]
|
||
|
|
["hot", "cold"] ["hot", "cold"]
|
||
|
|
["hot", "cold"] {"k1": "value", "k2": [10, 20]}
|
||
|
|
["hot", "cold"] {"key": "value"}
|
||
|
|
{"k1": "value", "k2": [10, 20]} NULL
|
||
|
|
{"k1": "value", "k2": [10, 20]} ["a", "b", 1]
|
||
|
|
{"k1": "value", "k2": [10, 20]} ["hot", "cold"]
|
||
|
|
{"k1": "value", "k2": [10, 20]} {"k1": "value", "k2": [10, 20]}
|
||
|
|
{"k1": "value", "k2": [10, 20]} {"key": "value"}
|
||
|
|
{"key": "value"} NULL
|
||
|
|
{"key": "value"} ["a", "b", 1]
|
||
|
|
{"key": "value"} ["hot", "cold"]
|
||
|
|
{"key": "value"} {"k1": "value", "k2": [10, 20]}
|
||
|
|
{"key": "value"} {"key": "value"}
|
||
|
|
SELECT DISTINCT ST_AsText(t1.c),ST_AsText(t2.c) FROM
|
||
|
|
t_point AS t1,
|
||
|
|
t_point AS t2;
|
||
|
|
ST_AsText(t1.c) ST_AsText(t2.c)
|
||
|
|
NULL NULL
|
||
|
|
NULL POINT(-1 -2)
|
||
|
|
NULL POINT(10 10)
|
||
|
|
NULL POINT(10 50)
|
||
|
|
NULL POINT(50 10)
|
||
|
|
POINT(-1 -2) NULL
|
||
|
|
POINT(-1 -2) POINT(-1 -2)
|
||
|
|
POINT(-1 -2) POINT(10 10)
|
||
|
|
POINT(-1 -2) POINT(10 50)
|
||
|
|
POINT(-1 -2) POINT(50 10)
|
||
|
|
POINT(10 10) NULL
|
||
|
|
POINT(10 10) POINT(-1 -2)
|
||
|
|
POINT(10 10) POINT(10 10)
|
||
|
|
POINT(10 10) POINT(10 50)
|
||
|
|
POINT(10 10) POINT(50 10)
|
||
|
|
POINT(10 50) NULL
|
||
|
|
POINT(10 50) POINT(-1 -2)
|
||
|
|
POINT(10 50) POINT(10 10)
|
||
|
|
POINT(10 50) POINT(10 50)
|
||
|
|
POINT(10 50) POINT(50 10)
|
||
|
|
POINT(50 10) NULL
|
||
|
|
POINT(50 10) POINT(-1 -2)
|
||
|
|
POINT(50 10) POINT(10 10)
|
||
|
|
POINT(50 10) POINT(10 50)
|
||
|
|
POINT(50 10) POINT(50 10)
|
||
|
|
SELECT DISTINCT ST_AsText(t1.c),ST_AsText(t2.c) FROM
|
||
|
|
t_geom AS t1,
|
||
|
|
t_geom AS t2;
|
||
|
|
ST_AsText(t1.c) ST_AsText(t2.c)
|
||
|
|
LINESTRING(0 0,0 10,10 0) LINESTRING(0 0,0 10,10 0)
|
||
|
|
LINESTRING(0 0,0 10,10 0) MULTIPOINT((0 0),(10 10),(10 20),(20 20))
|
||
|
|
LINESTRING(0 0,0 10,10 0) NULL
|
||
|
|
LINESTRING(0 0,0 10,10 0) POINT(10 10)
|
||
|
|
LINESTRING(0 0,0 10,10 0) POLYGON((10 10,20 10,20 20,10 20,10 10))
|
||
|
|
MULTIPOINT((0 0),(10 10),(10 20),(20 20)) LINESTRING(0 0,0 10,10 0)
|
||
|
|
MULTIPOINT((0 0),(10 10),(10 20),(20 20)) MULTIPOINT((0 0),(10 10),(10 20),(20 20))
|
||
|
|
MULTIPOINT((0 0),(10 10),(10 20),(20 20)) NULL
|
||
|
|
MULTIPOINT((0 0),(10 10),(10 20),(20 20)) POINT(10 10)
|
||
|
|
MULTIPOINT((0 0),(10 10),(10 20),(20 20)) POLYGON((10 10,20 10,20 20,10 20,10 10))
|
||
|
|
NULL LINESTRING(0 0,0 10,10 0)
|
||
|
|
NULL MULTIPOINT((0 0),(10 10),(10 20),(20 20))
|
||
|
|
NULL NULL
|
||
|
|
NULL POINT(10 10)
|
||
|
|
NULL POLYGON((10 10,20 10,20 20,10 20,10 10))
|
||
|
|
POINT(10 10) LINESTRING(0 0,0 10,10 0)
|
||
|
|
POINT(10 10) MULTIPOINT((0 0),(10 10),(10 20),(20 20))
|
||
|
|
POINT(10 10) NULL
|
||
|
|
POINT(10 10) POINT(10 10)
|
||
|
|
POINT(10 10) POLYGON((10 10,20 10,20 20,10 20,10 10))
|
||
|
|
POLYGON((10 10,20 10,20 20,10 20,10 10)) LINESTRING(0 0,0 10,10 0)
|
||
|
|
POLYGON((10 10,20 10,20 20,10 20,10 10)) MULTIPOINT((0 0),(10 10),(10 20),(20 20))
|
||
|
|
POLYGON((10 10,20 10,20 20,10 20,10 10)) NULL
|
||
|
|
POLYGON((10 10,20 10,20 20,10 20,10 10)) POINT(10 10)
|
||
|
|
POLYGON((10 10,20 10,20 20,10 20,10 10)) POLYGON((10 10,20 10,20 20,10 20,10 10))
|
||
|
|
# ---------------------------------------------------------------------
|
||
|
|
# Scenario 2:
|
||
|
|
#
|
||
|
|
SELECT c,COUNT(*) FROM t_int GROUP BY c;
|
||
|
|
c COUNT(*)
|
||
|
|
1 1
|
||
|
|
2 2
|
||
|
|
3 1
|
||
|
|
4 1
|
||
|
|
NULL 1
|
||
|
|
SELECT c,COUNT(*) FROM t_char GROUP BY c;
|
||
|
|
c COUNT(*)
|
||
|
|
1
|
||
|
|
NULL 1
|
||
|
|
abcde 1
|
||
|
|
fghij 2
|
||
|
|
klmno 1
|
||
|
|
stxyz 1
|
||
|
|
SELECT c,COUNT(*) FROM t_varchar GROUP BY c;
|
||
|
|
c COUNT(*)
|
||
|
|
1
|
||
|
|
NULL 1
|
||
|
|
abcde 1
|
||
|
|
fghij 2
|
||
|
|
klmno 1
|
||
|
|
stxyz 1
|
||
|
|
SELECT c,COUNT(*) FROM t_text GROUP BY c;
|
||
|
|
c COUNT(*)
|
||
|
|
1
|
||
|
|
NULL 1
|
||
|
|
abcde 1
|
||
|
|
fghij 2
|
||
|
|
klmno 1
|
||
|
|
stxyz 1
|
||
|
|
SELECT c,COUNT(*) FROM t_blob GROUP BY c;
|
||
|
|
c COUNT(*)
|
||
|
|
1
|
||
|
|
NULL 1
|
||
|
|
abcde 1
|
||
|
|
fghij 2
|
||
|
|
klmno 1
|
||
|
|
stxyz 1
|
||
|
|
SELECT c,COUNT(*) FROM t_json GROUP BY c;
|
||
|
|
c COUNT(*)
|
||
|
|
NULL 1
|
||
|
|
["a", "b", 1] 1
|
||
|
|
["hot", "cold"] 2
|
||
|
|
{"k1": "value", "k2": [10, 20]} 1
|
||
|
|
{"key": "value"} 1
|
||
|
|
SELECT ST_AsText(c),COUNT(*) FROM t_point GROUP BY c;
|
||
|
|
ST_AsText(c) COUNT(*)
|
||
|
|
NULL 1
|
||
|
|
POINT(-1 -2) 1
|
||
|
|
POINT(10 10) 1
|
||
|
|
POINT(10 50) 1
|
||
|
|
POINT(50 10) 2
|
||
|
|
SELECT ST_AsText(c),COUNT(*) FROM t_geom GROUP BY c;
|
||
|
|
ST_AsText(c) COUNT(*)
|
||
|
|
LINESTRING(0 0,0 10,10 0) 1
|
||
|
|
MULTIPOINT((0 0),(10 10),(10 20),(20 20)) 2
|
||
|
|
NULL 1
|
||
|
|
POINT(10 10) 1
|
||
|
|
POLYGON((10 10,20 10,20 20,10 20,10 10)) 1
|
||
|
|
SHOW STATUS LIKE 'Created_tmp_tables';
|
||
|
|
Variable_name Value
|
||
|
|
Created_tmp_tables 16
|
||
|
|
SHOW STATUS LIKE 'Created_tmp_disk_tables';
|
||
|
|
Variable_name Value
|
||
|
|
Created_tmp_disk_tables 0
|
||
|
|
DROP TABLE t_int;
|
||
|
|
DROP TABLE t_char;
|
||
|
|
DROP TABLE t_varchar;
|
||
|
|
DROP TABLE t_text;
|
||
|
|
DROP TABLE t_blob;
|
||
|
|
DROP TABLE t_json;
|
||
|
|
DROP TABLE t_point;
|
||
|
|
DROP TABLE t_geom;
|
||
|
|
CREATE TABLE t_pk (
|
||
|
|
pk INT NOT NULL,
|
||
|
|
PRIMARY KEY (pk)
|
||
|
|
);
|
||
|
|
INSERT INTO t_pk VALUES
|
||
|
|
(1),
|
||
|
|
(2),
|
||
|
|
(3);
|
||
|
|
SELECT COUNT(t_pk.pk) FROM t_pk
|
||
|
|
WHERE 1 IN (SELECT 1 FROM t_pk AS SQ2_alias1
|
||
|
|
WHERE 1 IN (SELECT 1 FROM t_pk AS C_SQ1_alias1)
|
||
|
|
);
|
||
|
|
COUNT(t_pk.pk)
|
||
|
|
3
|
||
|
|
DROP TABLE t_pk;
|
||
|
|
CREATE TABLE t_json(json_col JSON);
|
||
|
|
INSERT INTO t_json VALUES (
|
||
|
|
'[
|
||
|
|
{ "name":"John Johnson", "nickname": {"stringValue": "Johnny"}},
|
||
|
|
{ "name":"John Smith"}
|
||
|
|
]'),
|
||
|
|
('[
|
||
|
|
{ "name":"John Smith"},
|
||
|
|
{ "name":"John Johnson", "nickname": {"stringValue": "Johnny"}}
|
||
|
|
]');
|
||
|
|
SELECT attrs.* FROM t_json, JSON_TABLE(json_col, '$[*]' COLUMNS (nickname JSON PATH '$.nickname')) as attrs;
|
||
|
|
nickname
|
||
|
|
{"stringValue": "Johnny"}
|
||
|
|
NULL
|
||
|
|
NULL
|
||
|
|
{"stringValue": "Johnny"}
|
||
|
|
DROP TABLE t_json;
|
||
|
|
SET @@internal_tmp_mem_storage_engine = default;
|