|
15 | 15 | -- specific language governing permissions and limitations |
16 | 16 | -- under the License. |
17 | 17 |
|
| 18 | +-- Run with and without Parquet dictionary encoding, which changes how string and binary columns |
| 19 | +-- are read. |
| 20 | +-- ConfigMatrix: parquet.enable.dictionary=false,true |
| 21 | + |
18 | 22 | statement |
19 | 23 | CREATE TABLE test_min_max(i int, d double, s string, grp string) USING parquet |
20 | 24 |
|
21 | 25 | statement |
22 | 26 | INSERT INTO test_min_max VALUES (1, 1.5, 'b', 'x'), (3, 3.5, 'a', 'x'), (2, 2.5, 'c', 'y'), (NULL, NULL, NULL, 'y'), (-1, -1.5, 'z', 'x') |
23 | 27 |
|
24 | | --- min/max over StringType is not yet supported natively, so this falls back to Spark. |
25 | | --- (Spark plans this global aggregate as SortAggregateExec because the string min/max buffer |
26 | | --- is not hash-friendly; that operator is now supported, but the string aggregate itself is not.) |
27 | | -query expect_fallback(Unsupported data type: StringType) |
| 28 | +-- Spark plans this as SortAggregateExec, because a string min/max buffer is not hash-friendly. |
| 29 | +query |
28 | 30 | SELECT min(i), max(i), min(d), max(d), min(s), max(s) FROM test_min_max |
29 | 31 |
|
30 | 32 | query |
31 | 33 | SELECT grp, min(i), max(i) FROM test_min_max GROUP BY grp ORDER BY grp |
| 34 | + |
| 35 | +-- Spark compares strings and binary as unsigned bytes, and a value sorts before any longer value |
| 36 | +-- it is a prefix of. The groups cover prefixes, case, the empty value, characters whose UTF-8 |
| 37 | +-- order differs from their UTF-16 order ('😀' sorts after 'ア' by bytes but before it by UTF-16 |
| 38 | +-- code units), bytes that a signed comparison would order the other way (X'80' after X'7F'), |
| 39 | +-- and a group of only NULLs. |
| 40 | +statement |
| 41 | +CREATE TABLE test_min_max_bytes(grp string, s string, b binary) USING parquet |
| 42 | + |
| 43 | +statement |
| 44 | +INSERT INTO test_min_max_bytes VALUES |
| 45 | + ('prefix', 'ab', X'0102'), ('prefix', 'a', X'01'), ('prefix', NULL, NULL), |
| 46 | + ('case', 'a', X'61'), ('case', 'B', X'42'), ('case', 'A', X'41'), |
| 47 | + ('empty', ' ', X'20'), ('empty', '', X''), ('empty', NULL, NULL), |
| 48 | + ('utf8', 'ア', X'7F'), ('utf8', '😀', X'80'), ('utf8', 'é', X'FF'), ('utf8', 'z', X'00'), |
| 49 | + ('nulls', NULL, NULL), ('nulls', NULL, NULL) |
| 50 | + |
| 51 | +query |
| 52 | +SELECT grp, min(s), max(s), min(b), max(b) FROM test_min_max_bytes GROUP BY grp ORDER BY grp |
| 53 | + |
| 54 | +query |
| 55 | +SELECT min(s), max(s), min(b), max(b) FROM test_min_max_bytes |
| 56 | + |
| 57 | +query |
| 58 | +SELECT min(s), max(s), min(b), max(b) FROM test_min_max_bytes WHERE grp = 'missing' |
| 59 | + |
| 60 | +-- collect_set makes Spark plan ObjectHashAggregateExec instead, with min/max in the same operator. |
| 61 | +query |
| 62 | +SELECT grp, min(s), max(b), sort_array(collect_set(s)) |
| 63 | +FROM test_min_max_bytes GROUP BY grp ORDER BY grp |
| 64 | + |
| 65 | +-- count(DISTINCT) makes Spark plan four sort aggregates, so the string and binary buffers also go |
| 66 | +-- through a PartialMerge. |
| 67 | +query |
| 68 | +SELECT grp, count(DISTINCT s), min(s), max(b) |
| 69 | +FROM test_min_max_bytes GROUP BY grp ORDER BY grp |
| 70 | + |
| 71 | +-- A VALUES input keeps the partial aggregate on Spark, while the final one merges Spark's string |
| 72 | +-- and binary buffers natively. The plan is not fully native, so only the answer is checked. |
| 73 | +query spark_answer_only |
| 74 | +SELECT k, min(s), max(s), min(b), max(b) |
| 75 | +FROM VALUES (1, 'ab', X'0102'), (1, 'a', X'01'), (2, NULL, NULL), (2, '😀', X'80'), (2, 'ア', X'7F') |
| 76 | + AS t(k, s, b) |
| 77 | +GROUP BY k ORDER BY k |
0 commit comments