358 lines
5.1 KiB
Plaintext
358 lines
5.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);
|
|
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);
|
|
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"}
|
|
# ---------------------------------------------------------------------
|
|
# 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
|
|
SHOW STATUS LIKE 'Created_tmp_tables';
|
|
Variable_name Value
|
|
Created_tmp_tables 12
|
|
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;
|
|
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;
|
|
SET @@internal_tmp_mem_storage_engine = default;
|