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;