494 lines
9.1 KiB
Plaintext
494 lines
9.1 KiB
Plaintext
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;
|
|
#
|
|
# Bug #30677984: UNINITIALISED VALUE ERROR WITH VALGRIND IN SQL_TMP_TABLE.CC
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk INTEGER NOT NULL,
|
|
f1 varchar(255)
|
|
);
|
|
INSERT INTO t1 VALUES (5,'N');
|
|
CREATE TABLE t2 (
|
|
pk int,
|
|
f2 varchar(10)
|
|
);
|
|
INSERT INTO t2 VALUES (5,'he');
|
|
INSERT INTO t2 VALUES (5,'l');
|
|
CREATE TABLE t3 (
|
|
f2 varchar(10),
|
|
f3 varchar(255)
|
|
);
|
|
INSERT INTO t3 VALUES ('L','2.0');
|
|
set optimizer_switch='block_nested_loop=off';
|
|
SELECT SUM(t3.f3)
|
|
FROM t1
|
|
LEFT JOIN t2 ON t1.pk = t2.pk
|
|
LEFT JOIN t3 ON t2.f2 = t3.f2
|
|
GROUP BY t1.f1;
|
|
SUM(t3.f3)
|
|
2
|
|
set optimizer_switch=default;
|
|
DROP TABLE t1, t2, t3;
|
|
SET @@internal_tmp_mem_storage_engine = default;
|