Skip to content

Commit 1670bee

Browse files
PG1204kosiew
authored andcommitted
test: add sqllogictest coverage for DISTINCT / GROUP BY / aggregation on map columns (apache#23406)
## Which issue does this PR close? - Closes apache#15428. ## Rationale for this change `SELECT DISTINCT` on map columns used to fail with `ArrowError(NotYetImplemented)` because arrow-rs's `RowConverter` did not support Map types. That support has since landed upstream (apache/arrow-rs#7879) and is available on DataFusion main via arrow 59.1, so these queries now work. As suggested in apache#15428 (comment) (maintainer comment), this PR adds sqllogictest coverage to lock in the behavior and close the issue. ## What changes are included in this PR? Adds a new test section to `datafusion/sqllogictest/test_files/map.slt` covering: - `SELECT DISTINCT` on a map column, including the exact reproducer from the issue (`DISTINCT ... LIMIT`), collapsing of duplicate maps and duplicate NULLs, and `DISTINCT` over map + scalar columns together - `GROUP BY` on a map column, `GROUP BY` map + scalar keys, and `HAVING` with a map grouping key - Aggregations with map inputs: `COUNT(map)`, `COUNT(DISTINCT map)`, `SUM` grouped by map, and `array_agg` of map values - Edge cases: empty maps under `DISTINCT` (equal to each other, distinct from `NULL`), NULL maps, and unsorted maps with the same entries in different order being treated as distinct values - `UNION` (distinct) on map columns - `DISTINCT` / `GROUP BY` over the existing 209-row `parquet_map.parquet` file for coverage on real Parquet-backed map data Note: a bare `MAP {'key': NULL}` literal infers a `Null` value type and fails type coercion across a VALUES list against `Map(Utf8, Int64)` rows, so the test uses `CAST(NULL AS BIGINT)` for the null map value. That coercion gap may be worth a separate follow-up issue. ## Are these changes tested? Yes, this PR is test-only. All tests pass locally via `cargo test -p datafusion-sqllogictest --test sqllogictests -- map.slt`. ## Are there any user-facing changes? No. Test-only change; no API or behavior changes.
1 parent 0020e1c commit 1670bee

1 file changed

Lines changed: 132 additions & 0 deletions

File tree

  • datafusion/sqllogictest/test_files

‎datafusion/sqllogictest/test_files/map.slt‎

Lines changed: 132 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -916,3 +916,135 @@ SELECT map([column1, column1 * 10], ['x','y']) FROM (VALUES (1), (2), (3)) t;
916916
{1: x, 10: y}
917917
{2: x, 20: y}
918918
{3: x, 30: y}
919+
920+
# tests for DISTINCT / GROUP BY / aggregation on map columns
921+
# https://github.com/apache/datafusion/issues/15428
922+
923+
statement ok
924+
CREATE TABLE map_distinct_table AS VALUES
925+
(MAP {'k1': 1, 'k2': 2}, 'a', 1),
926+
(MAP {'k1': 1, 'k2': 2}, 'a', 2),
927+
(MAP {'k1': 1, 'k2': 2}, 'b', 3),
928+
(MAP {'k1': 3}, 'a', 4),
929+
(MAP {'k1': CAST(NULL AS BIGINT)}, 'b', 5);
930+
931+
statement ok
932+
INSERT INTO map_distinct_table VALUES (NULL, 'a', 6), (NULL, 'a', 7), (NULL, 'b', 8);
933+
934+
# distinct on a map column collapses duplicate maps and duplicate NULLs
935+
query ? rowsort
936+
SELECT DISTINCT column1 FROM map_distinct_table;
937+
----
938+
NULL
939+
{k1: 1, k2: 2}
940+
{k1: 3}
941+
{k1: NULL}
942+
943+
# exact reproducer from #15428: DISTINCT on a map column with LIMIT
944+
query ? rowsort
945+
SELECT DISTINCT column1 FROM map_distinct_table LIMIT 10;
946+
----
947+
NULL
948+
{k1: 1, k2: 2}
949+
{k1: 3}
950+
{k1: NULL}
951+
952+
# distinct over a map column together with a scalar column
953+
query ?T rowsort
954+
SELECT DISTINCT column1, column2 FROM map_distinct_table;
955+
----
956+
NULL a
957+
NULL b
958+
{k1: 1, k2: 2} a
959+
{k1: 1, k2: 2} b
960+
{k1: 3} a
961+
{k1: NULL} b
962+
963+
# group by a map column
964+
query ?II rowsort
965+
SELECT column1, COUNT(*), SUM(column3) FROM map_distinct_table GROUP BY column1;
966+
----
967+
NULL 3 21
968+
{k1: 1, k2: 2} 3 6
969+
{k1: 3} 1 4
970+
{k1: NULL} 1 5
971+
972+
# group by a map column and a scalar column
973+
query ?TI rowsort
974+
SELECT column1, column2, COUNT(*) FROM map_distinct_table GROUP BY column1, column2;
975+
----
976+
NULL a 2
977+
NULL b 1
978+
{k1: 1, k2: 2} a 2
979+
{k1: 1, k2: 2} b 1
980+
{k1: 3} a 1
981+
{k1: NULL} b 1
982+
983+
# empty maps compare equal under DISTINCT and are distinct from NULL
984+
query ? rowsort
985+
SELECT DISTINCT column1 FROM (VALUES (MAP {}), (MAP {}), (NULL)) t(column1);
986+
----
987+
NULL
988+
{}
989+
990+
# HAVING clause with a map grouping key
991+
query ?I rowsort
992+
SELECT column1, COUNT(*) FROM map_distinct_table GROUP BY column1 HAVING COUNT(*) > 1;
993+
----
994+
NULL 3
995+
{k1: 1, k2: 2} 3
996+
997+
# count and count distinct on a map column
998+
query II
999+
SELECT COUNT(column1), COUNT(DISTINCT column1) FROM map_distinct_table;
1000+
----
1001+
5 3
1002+
1003+
# map column as input to an aggregate function
1004+
query T?
1005+
SELECT column2, array_agg(column1 ORDER BY column3) FROM map_distinct_table GROUP BY column2 ORDER BY column2;
1006+
----
1007+
a [{k1: 1, k2: 2}, {k1: 1, k2: 2}, {k1: 3}, NULL, NULL]
1008+
b [{k1: 1, k2: 2}, {k1: NULL}, NULL]
1009+
1010+
# UNION (distinct) on map columns
1011+
query ?
1012+
SELECT MAP {'a': 1} UNION SELECT MAP {'a': 1};
1013+
----
1014+
{a: 1}
1015+
1016+
# unsorted maps are compared by entry order: maps with the same entries in a
1017+
# different order are treated as distinct values
1018+
query ? rowsort
1019+
SELECT DISTINCT column1 FROM (SELECT MAP {'k1': 1, 'k2': 2} AS column1 UNION ALL SELECT MAP {'k2': 2, 'k1': 1});
1020+
----
1021+
{k1: 1, k2: 2}
1022+
{k2: 2, k1: 1}
1023+
1024+
statement ok
1025+
DROP TABLE map_distinct_table;
1026+
1027+
# distinct / group by on map columns read from parquet
1028+
statement ok
1029+
CREATE EXTERNAL TABLE map_data
1030+
STORED AS PARQUET
1031+
LOCATION '../core/tests/data/parquet_map.parquet';
1032+
1033+
query I
1034+
SELECT COUNT(*) FROM (SELECT DISTINCT ints, strings FROM map_data);
1035+
----
1036+
209
1037+
1038+
query TI rowsort
1039+
SELECT strings['method'] AS method, COUNT(*) FROM (SELECT DISTINCT strings FROM map_data) GROUP BY method;
1040+
----
1041+
DELETE 24
1042+
GET 27
1043+
HEAD 33
1044+
OPTION 29
1045+
PATCH 30
1046+
POST 41
1047+
PUT 25
1048+
1049+
statement ok
1050+
DROP TABLE map_data;

0 commit comments

Comments
 (0)