Skip to content

Commit 85de136

Browse files
authored
Add IN list sqllogictest test (and integer type coverage) (apache#23305)
## Which issue does this PR close? - Part of apache#19241 - part of apache#23307 - Follow on to apache#23299 ## Rationale for this change We (mostly @geoffreyclaude ) are in the process of optimizing IN lists with specialized implementations for various different data types. @kosiew suggested in apache#23299 (comment) that we add SQL level coverage for the IN list optimizations to both cover the dispatch logic as well as ensure everything works end to end. > One small follow-up idea: it could be useful to add a public-path regression through InListExpr::try_new_from_array, or a SQL-level test, for Int8 and Int16 signed values. ## What changes are included in this PR? 1. Add `in_list.slt` file to specifically target the IN lists 2. Only add test coverage for integer types note I think we should have significiantly more SLT coverage (list on apache#23307) but to keep the review small / understandable I plan to do it with several PRs ## Are these changes tested? Only tests ## Are there any user-facing changes? No
1 parent bc320bb commit 85de136

1 file changed

Lines changed: 238 additions & 0 deletions

File tree

Lines changed: 238 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,238 @@
1+
# Licensed to the Apache Software Foundation (ASF) under one
2+
# or more contributor license agreements. See the NOTICE file
3+
# distributed with this work for additional information
4+
# regarding copyright ownership. The ASF licenses this file
5+
# to you under the Apache License, Version 2.0 (the
6+
# "License"); you may not use this file except in compliance
7+
# with the License. You may obtain a copy of the License at
8+
#
9+
# http://www.apache.org/licenses/LICENSE-2.0
10+
#
11+
# Unless required by applicable law or agreed to in writing,
12+
# software distributed under the License is distributed on an
13+
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14+
# KIND, either express or implied. See the License for the
15+
# specific language governing permissions and limitations
16+
# under the License.
17+
18+
##########
19+
# IN List Tests
20+
#
21+
# This file focuses on the IN operator and its various specializations
22+
#
23+
# Note that "short" IN LISTS do not go through the InList implementation at all,
24+
# instead they are rewritten into a series of OR expressions. See:
25+
# https://github.com/apache/datafusion/blob/ed37b6c9555bc278130dc774ed833b8c0bd29bfa/datafusion/optimizer/src/simplify_expressions/inlist_simplifier.rs#L39-L88
26+
##########
27+
28+
29+
# Tests for IN LIST integer specializations
30+
31+
32+
statement ok
33+
CREATE TABLE in_list_ints (
34+
label VARCHAR,
35+
i8 TINYINT,
36+
u8 TINYINT UNSIGNED,
37+
i16 SMALLINT,
38+
u16 SMALLINT UNSIGNED,
39+
i32 INT,
40+
u32 INT UNSIGNED,
41+
i64 BIGINT,
42+
u64 BIGINT UNSIGNED
43+
) AS VALUES
44+
('min', -128, 0, -32768, 0, -2147483648, 0, -9223372036854775808, 0),
45+
('minus_one', -1, 1, -1, 1, -1, 1, -1, 1),
46+
('zero', 0, 0, 0, 0, 0, 0, 0, 0),
47+
('one', 1, 1, 1, 1, 1, 1, 1, 1),
48+
('eleven', 11, 11, 11, 11, 11, 11, 11, 11),
49+
('max', 127, 255, 32767, 65535, 2147483647, 4294967295, 9223372036854775807, 18446744073709551615);
50+
51+
# Verify that the Arrow types of the columns are as expected. This is important because the IN LIST specializations are based on the column types.
52+
query TTTTTTTT
53+
SELECT
54+
arrow_typeof(i8),
55+
arrow_typeof(u8),
56+
arrow_typeof(i16),
57+
arrow_typeof(u16),
58+
arrow_typeof(i32),
59+
arrow_typeof(u32),
60+
arrow_typeof(i64),
61+
arrow_typeof(u64)
62+
FROM in_list_ints
63+
LIMIT 1
64+
----
65+
Int8 UInt8 Int16 UInt16 Int32 UInt32 Int64 UInt64
66+
67+
# Verify the data is as expected.
68+
query TIIIIIIII
69+
SELECT label, i8, u8, i16, u16, i32, u32, i64, u64
70+
FROM in_list_ints
71+
ORDER BY label
72+
----
73+
eleven 11 11 11 11 11 11 11 11
74+
max 127 255 32767 65535 2147483647 4294967295 9223372036854775807 18446744073709551615
75+
min -128 0 -32768 0 -2147483648 0 -9223372036854775808 0
76+
minus_one -1 1 -1 1 -1 1 -1 1
77+
one 1 1 1 1 1 1 1 1
78+
zero 0 0 0 0 0 0 0 0
79+
80+
# Empty IN lists are rejected by the SQL parser.
81+
statement error .*Expected: an expression, found: \).*
82+
SELECT 1 IN ();
83+
84+
# Min for each type
85+
query TBBBBBBBB
86+
SELECT
87+
label,
88+
i8 IN (1, 2, 3, -128),
89+
u8 IN (1, 2, 3, 0),
90+
i16 IN (1, 2, 3, -32768),
91+
u16 IN (1, 2, 3, 0),
92+
i32 IN (1, 2, 3, -2147483648),
93+
u32 IN (1, 2, 3, 0),
94+
i64 IN (1, 2, 3, -9223372036854775808),
95+
u64 IN (1, 2, 3, 0)
96+
FROM in_list_ints
97+
ORDER BY label
98+
----
99+
eleven false false false false false false false false
100+
max false false false false false false false false
101+
min true true true true true true true true
102+
minus_one false true false true false true false true
103+
one true true true true true true true true
104+
zero false true false true false true false true
105+
106+
# Max for each type (use values that cover the entire input range
107+
# e.g. values that have 1 non zero byte. 2 non zero bytes, etc)
108+
query TBBBBBBBB
109+
SELECT
110+
label,
111+
i8 IN (-64, -32, 32, 64, 127),
112+
u8 IN (32, 64, 128, 200, 255),
113+
i16 IN (3, 258, 4097, 16385, 32767),
114+
u16 IN (3, 258, 4097, 16385, 65535),
115+
i32 IN (3, 258, 66051, 16909060, 2147483647),
116+
u32 IN (3, 258, 66051, 16909060, 4294967295),
117+
i64 IN (3, 258, 66051, 16909060, 9223372036854775807),
118+
u64 IN (3, 258, 66051, 16909060, 18446744073709551615)
119+
FROM in_list_ints
120+
ORDER BY label
121+
----
122+
eleven false false false false false false false false
123+
max true true true true true true true true
124+
min false false false false false false false false
125+
minus_one false false false false false false false false
126+
one false false false false false false false false
127+
zero false false false false false false false false
128+
129+
# Twelve item IN list with no matches (also cover the entire range)
130+
query TBBBBBBBB
131+
SELECT
132+
label,
133+
i8 IN (-120, -64, -32, -16, -8, -4, -2, 2, 32, 64, 100, 126),
134+
u8 IN (2, 3, 4, 8, 16, 32, 64, 100, 128, 150, 200, 254),
135+
i16 IN (-30000, -16384, -1024, -257, 2, 257, 4097, 8192, 16385, 20000, 30000, 32000),
136+
u16 IN (2, 3, 4, 8, 16, 32, 257, 4097, 16385, 32768, 60000, 65534),
137+
i32 IN (-2000000000, -1000000000, -16711936, -65536, -1024, 2, 66051, 16909060, 305419896, 1076895760, 2000000000, 2147483646),
138+
u32 IN (2, 3, 4, 8, 16, 66051, 16909060, 305419896, 1076895760, 2309737967, 4000000000, 4294967294),
139+
i64 IN (-9000000000000000000, -72057594037927936, -1000000000000000000, -65536, -1024, 2, 72623859790382856, 81985529216486895, 819855292164868960, 1234605616436508552, 9000000000000000000, 9223372036854775806),
140+
u64 IN (2, 3, 4, 8, 16, 72623859790382856, 81985529216486895, 819855292164868960, 1234605616436508552, 18000000000000000000, 18364758544493064720, 18446744073709551614)
141+
FROM in_list_ints
142+
ORDER BY label
143+
----
144+
eleven false false false false false false false false
145+
max false false false false false false false false
146+
min false false false false false false false false
147+
minus_one false false false false false false false false
148+
one false false false false false false false false
149+
zero false false false false false false false false
150+
151+
# Twelve item IN list with matches, including 11.
152+
query TBBBBBBBB
153+
SELECT
154+
label,
155+
i8 IN (-120, -64, -32, -16, -8, -4, -2, -128, 1, 3, 5, 11),
156+
u8 IN (2, 4, 8, 16, 32, 64, 128, 200, 0, 11, 250, 255),
157+
i16 IN (-30000, -20000, -10000, -32768, -1024, -256, -128, 1, 2, 3, 5, 11),
158+
u16 IN (2, 4, 8, 16, 32, 64, 128, 256, 0, 11, 60000, 65535),
159+
i32 IN (-2000000000, -1000000000, -2147483648, -65536, -1024, -256, -128, 1, 2, 3, 5, 11),
160+
u32 IN (2, 4, 8, 16, 32, 64, 128, 256, 0, 11, 4000000000, 4294967295),
161+
i64 IN (-9000000000000000000, -9223372036854775808, -1000000000, -65536, -1024, -256, -128, 1, 2, 3, 5, 11),
162+
u64 IN (2, 4, 8, 16, 32, 64, 128, 256, 0, 11, 18000000000000000000, 18446744073709551615)
163+
FROM in_list_ints
164+
ORDER BY label
165+
----
166+
eleven true true true true true true true true
167+
max false true false true false true false true
168+
min true true true true true true true true
169+
minus_one false false false false false false false false
170+
one true false true false true false true false
171+
zero false true false true false true false true
172+
173+
# Cleanup
174+
statement ok
175+
DROP TABLE in_list_ints;
176+
177+
####
178+
## Integer Null Handling
179+
####
180+
181+
# Table with nulls to test null handling for integer IN list specializations
182+
statement ok
183+
CREATE TABLE in_list_ints_nullable (
184+
label VARCHAR,
185+
i8 TINYINT,
186+
u8 TINYINT UNSIGNED,
187+
i16 SMALLINT,
188+
u16 SMALLINT UNSIGNED,
189+
i32 INT,
190+
u32 INT UNSIGNED,
191+
i64 BIGINT,
192+
u64 BIGINT UNSIGNED
193+
) AS VALUES
194+
('match', 11, 11, 11, 11, 11, 11, 11, 11),
195+
('no_match', 7, 7, 7, 7, 7, 7, 7, 7),
196+
('nulls', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
197+
198+
# Null input values return NULL when the IN list has no nulls.
199+
query TBBBBBBBB
200+
SELECT
201+
label,
202+
i8 IN (3, 4, 5, 6, 11),
203+
u8 IN (3, 4, 5, 6, 11),
204+
i16 IN (3, 258, 4097, 16385, 11),
205+
u16 IN (3, 258, 4097, 16385, 11),
206+
i32 IN (3, 258, 66051, 16909060, 11),
207+
u32 IN (3, 258, 66051, 16909060, 11),
208+
i64 IN (3, 258, 66051, 16909060, 11),
209+
u64 IN (3, 258, 66051, 16909060, 11)
210+
FROM in_list_ints_nullable
211+
ORDER BY label
212+
----
213+
match true true true true true true true true
214+
no_match false false false false false false false false
215+
nulls NULL NULL NULL NULL NULL NULL NULL NULL
216+
217+
# Null IN list values return true for matches and NULL for non-matches.
218+
query TBBBBBBBB
219+
SELECT
220+
label,
221+
i8 IN (NULL, 3, 4, 5, 11),
222+
u8 IN (NULL, 3, 4, 5, 11),
223+
i16 IN (NULL, 3, 258, 4097, 11),
224+
u16 IN (NULL, 3, 258, 4097, 11),
225+
i32 IN (NULL, 3, 258, 66051, 11),
226+
u32 IN (NULL, 3, 258, 66051, 11),
227+
i64 IN (NULL, 3, 258, 66051, 11),
228+
u64 IN (NULL, 3, 258, 66051, 11)
229+
FROM in_list_ints_nullable
230+
ORDER BY label
231+
----
232+
match true true true true true true true true
233+
no_match NULL NULL NULL NULL NULL NULL NULL NULL
234+
nulls NULL NULL NULL NULL NULL NULL NULL NULL
235+
236+
# Cleanup
237+
statement ok
238+
DROP TABLE in_list_ints_nullable

0 commit comments

Comments
 (0)