Skip to content

Commit a3fd8f3

Browse files
alambclaude
andcommitted
test: add IN list slt coverage for temporal, Decimal128 and Interval types
Part of apache#23307. Adds sqllogictest coverage for IN lists over Date32, Date64, Time32, Time64, Timestamp (with and without a timezone), Duration, Decimal128 and Interval(MonthDayNano), none of which had any SQL level coverage. Also adds a 17 element Int8/UInt8 list and a 9 element Float16 list so the bitmap specialization stays covered as shorter lists gain their own specializations. Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
1 parent 88365dd commit a3fd8f3

1 file changed

Lines changed: 193 additions & 0 deletions

File tree

‎datafusion/sqllogictest/test_files/in_list.slt‎

Lines changed: 193 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -170,6 +170,22 @@ minus_one false false false false false false false false
170170
one true false true false true false true false
171171
zero false true false true false true false true
172172

173+
# Seventeen item IN list (shorter lists have specialized implementation)
174+
query TBB
175+
SELECT
176+
label,
177+
i8 IN (-128, -120, -100, -80, -60, -40, -20, -10, -5, -3, -2, 2, 3, 5, 20, 40, 11),
178+
u8 IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 13, 14, 16, 20, 0, 11, 255)
179+
FROM in_list_ints
180+
ORDER BY label
181+
----
182+
eleven true true
183+
max false true
184+
min true true
185+
minus_one false false
186+
one false false
187+
zero false true
188+
173189
# Cleanup
174190
statement ok
175191
DROP TABLE in_list_ints;
@@ -329,6 +345,183 @@ Float64 match true true false
329345
Float64 no_match false NULL false
330346
Float64 nulls NULL NULL NULL
331347

348+
# Nine element Float16 IN list (shorter lists have specialized code)
349+
query TB
350+
SELECT
351+
label,
352+
f16 IN (arrow_cast(1.0, 'Float16'), arrow_cast(2.0, 'Float16'), arrow_cast(3.0, 'Float16'),
353+
arrow_cast(4.0, 'Float16'), arrow_cast(5.0, 'Float16'), arrow_cast(6.0, 'Float16'),
354+
arrow_cast(8.0, 'Float16'), arrow_cast(9.0, 'Float16'), arrow_cast(11.0, 'Float16'))
355+
FROM in_list_floats
356+
ORDER BY label
357+
----
358+
match true
359+
no_match false
360+
nulls NULL
361+
332362
# Cleanup
333363
statement ok
334364
DROP TABLE in_list_floats
365+
366+
####
367+
## Temporal IN List Specializations
368+
####
369+
370+
statement ok
371+
CREATE TABLE in_list_temporal AS
372+
SELECT
373+
label,
374+
arrow_cast(arrow_cast(value, 'Int32'), 'Date32') AS d32,
375+
arrow_cast(arrow_cast(value, 'Int64'), 'Date64') AS d64,
376+
arrow_cast(arrow_cast(value, 'Int32'), 'Time32(Second)') AS t32s,
377+
arrow_cast(arrow_cast(value, 'Int64'), 'Time64(Nanosecond)') AS t64ns,
378+
arrow_cast(arrow_cast(value, 'Int64'), 'Timestamp(Nanosecond, None)') AS ts_ns,
379+
arrow_cast(arrow_cast(value, 'Int64'), 'Timestamp(Second, Some("UTC"))') AS ts_s_utc,
380+
arrow_cast(arrow_cast(value, 'Int64'), 'Duration(Second)') AS dur_s
381+
FROM (VALUES
382+
('match', 11),
383+
('no_match', 7),
384+
('nulls', NULL)
385+
) AS t(label, value);
386+
387+
# Basic Temporal IN Lists
388+
query TBBBBBBB
389+
SELECT
390+
label,
391+
d32 IN (arrow_cast(arrow_cast(3, 'Int32'), 'Date32'), arrow_cast(arrow_cast(4, 'Int32'), 'Date32'), arrow_cast(arrow_cast(5, 'Int32'), 'Date32'), arrow_cast(arrow_cast(11, 'Int32'), 'Date32')),
392+
d64 IN (arrow_cast(arrow_cast(3, 'Int64'), 'Date64'), arrow_cast(arrow_cast(4, 'Int64'), 'Date64'), arrow_cast(arrow_cast(5, 'Int64'), 'Date64'), arrow_cast(arrow_cast(11, 'Int64'), 'Date64')),
393+
t32s IN (arrow_cast(arrow_cast(3, 'Int32'), 'Time32(Second)'), arrow_cast(arrow_cast(4, 'Int32'), 'Time32(Second)'), arrow_cast(arrow_cast(5, 'Int32'), 'Time32(Second)'), arrow_cast(arrow_cast(11, 'Int32'), 'Time32(Second)')),
394+
t64ns IN (arrow_cast(arrow_cast(3, 'Int64'), 'Time64(Nanosecond)'), arrow_cast(arrow_cast(4, 'Int64'), 'Time64(Nanosecond)'), arrow_cast(arrow_cast(5, 'Int64'), 'Time64(Nanosecond)'), arrow_cast(arrow_cast(11, 'Int64'), 'Time64(Nanosecond)')),
395+
ts_ns IN (arrow_cast(arrow_cast(3, 'Int64'), 'Timestamp(Nanosecond, None)'), arrow_cast(arrow_cast(4, 'Int64'), 'Timestamp(Nanosecond, None)'), arrow_cast(arrow_cast(5, 'Int64'), 'Timestamp(Nanosecond, None)'), arrow_cast(arrow_cast(11, 'Int64'), 'Timestamp(Nanosecond, None)')),
396+
ts_s_utc IN (arrow_cast(arrow_cast(3, 'Int64'), 'Timestamp(Second, Some("UTC"))'), arrow_cast(arrow_cast(4, 'Int64'), 'Timestamp(Second, Some("UTC"))'), arrow_cast(arrow_cast(5, 'Int64'), 'Timestamp(Second, Some("UTC"))'), arrow_cast(arrow_cast(11, 'Int64'), 'Timestamp(Second, Some("UTC"))')),
397+
dur_s IN (arrow_cast(arrow_cast(3, 'Int64'), 'Duration(Second)'), arrow_cast(arrow_cast(4, 'Int64'), 'Duration(Second)'), arrow_cast(arrow_cast(5, 'Int64'), 'Duration(Second)'), arrow_cast(arrow_cast(11, 'Int64'), 'Duration(Second)'))
398+
FROM in_list_temporal
399+
ORDER BY label
400+
----
401+
match true true true true true true true
402+
no_match false false false false false false false
403+
nulls NULL NULL NULL NULL NULL NULL NULL
404+
405+
# The same lists with NOT IN.
406+
query TBBBBBBB
407+
SELECT
408+
label,
409+
d32 NOT IN (arrow_cast(arrow_cast(3, 'Int32'), 'Date32'), arrow_cast(arrow_cast(4, 'Int32'), 'Date32'), arrow_cast(arrow_cast(5, 'Int32'), 'Date32'), arrow_cast(arrow_cast(11, 'Int32'), 'Date32')),
410+
d64 NOT IN (arrow_cast(arrow_cast(3, 'Int64'), 'Date64'), arrow_cast(arrow_cast(4, 'Int64'), 'Date64'), arrow_cast(arrow_cast(5, 'Int64'), 'Date64'), arrow_cast(arrow_cast(11, 'Int64'), 'Date64')),
411+
t32s NOT IN (arrow_cast(arrow_cast(3, 'Int32'), 'Time32(Second)'), arrow_cast(arrow_cast(4, 'Int32'), 'Time32(Second)'), arrow_cast(arrow_cast(5, 'Int32'), 'Time32(Second)'), arrow_cast(arrow_cast(11, 'Int32'), 'Time32(Second)')),
412+
t64ns NOT IN (arrow_cast(arrow_cast(3, 'Int64'), 'Time64(Nanosecond)'), arrow_cast(arrow_cast(4, 'Int64'), 'Time64(Nanosecond)'), arrow_cast(arrow_cast(5, 'Int64'), 'Time64(Nanosecond)'), arrow_cast(arrow_cast(11, 'Int64'), 'Time64(Nanosecond)')),
413+
ts_ns NOT IN (arrow_cast(arrow_cast(3, 'Int64'), 'Timestamp(Nanosecond, None)'), arrow_cast(arrow_cast(4, 'Int64'), 'Timestamp(Nanosecond, None)'), arrow_cast(arrow_cast(5, 'Int64'), 'Timestamp(Nanosecond, None)'), arrow_cast(arrow_cast(11, 'Int64'), 'Timestamp(Nanosecond, None)')),
414+
ts_s_utc NOT IN (arrow_cast(arrow_cast(3, 'Int64'), 'Timestamp(Second, Some("UTC"))'), arrow_cast(arrow_cast(4, 'Int64'), 'Timestamp(Second, Some("UTC"))'), arrow_cast(arrow_cast(5, 'Int64'), 'Timestamp(Second, Some("UTC"))'), arrow_cast(arrow_cast(11, 'Int64'), 'Timestamp(Second, Some("UTC"))')),
415+
dur_s NOT IN (arrow_cast(arrow_cast(3, 'Int64'), 'Duration(Second)'), arrow_cast(arrow_cast(4, 'Int64'), 'Duration(Second)'), arrow_cast(arrow_cast(5, 'Int64'), 'Duration(Second)'), arrow_cast(arrow_cast(11, 'Int64'), 'Duration(Second)'))
416+
FROM in_list_temporal
417+
ORDER BY label
418+
----
419+
match false false false false false false false
420+
no_match true true true true true true true
421+
nulls NULL NULL NULL NULL NULL NULL NULL
422+
423+
# Null IN list values return true for matches and NULL for non-matches.
424+
query TBBBBBBB
425+
SELECT
426+
label,
427+
d32 IN (NULL, arrow_cast(arrow_cast(3, 'Int32'), 'Date32'), arrow_cast(arrow_cast(4, 'Int32'), 'Date32'), arrow_cast(arrow_cast(11, 'Int32'), 'Date32')),
428+
d64 IN (NULL, arrow_cast(arrow_cast(3, 'Int64'), 'Date64'), arrow_cast(arrow_cast(4, 'Int64'), 'Date64'), arrow_cast(arrow_cast(11, 'Int64'), 'Date64')),
429+
t32s IN (NULL, arrow_cast(arrow_cast(3, 'Int32'), 'Time32(Second)'), arrow_cast(arrow_cast(4, 'Int32'), 'Time32(Second)'), arrow_cast(arrow_cast(11, 'Int32'), 'Time32(Second)')),
430+
t64ns IN (NULL, arrow_cast(arrow_cast(3, 'Int64'), 'Time64(Nanosecond)'), arrow_cast(arrow_cast(4, 'Int64'), 'Time64(Nanosecond)'), arrow_cast(arrow_cast(11, 'Int64'), 'Time64(Nanosecond)')),
431+
ts_ns IN (NULL, arrow_cast(arrow_cast(3, 'Int64'), 'Timestamp(Nanosecond, None)'), arrow_cast(arrow_cast(4, 'Int64'), 'Timestamp(Nanosecond, None)'), arrow_cast(arrow_cast(11, 'Int64'), 'Timestamp(Nanosecond, None)')),
432+
ts_s_utc IN (NULL, arrow_cast(arrow_cast(3, 'Int64'), 'Timestamp(Second, Some("UTC"))'), arrow_cast(arrow_cast(4, 'Int64'), 'Timestamp(Second, Some("UTC"))'), arrow_cast(arrow_cast(11, 'Int64'), 'Timestamp(Second, Some("UTC"))')),
433+
dur_s IN (NULL, arrow_cast(arrow_cast(3, 'Int64'), 'Duration(Second)'), arrow_cast(arrow_cast(4, 'Int64'), 'Duration(Second)'), arrow_cast(arrow_cast(11, 'Int64'), 'Duration(Second)'))
434+
FROM in_list_temporal
435+
ORDER BY label
436+
----
437+
match true true true true true true true
438+
no_match NULL NULL NULL NULL NULL NULL NULL
439+
nulls NULL NULL NULL NULL NULL NULL NULL
440+
441+
# A NULL in the list turns off some specializations
442+
query TBBBBBBB
443+
SELECT
444+
label,
445+
d32 IN (NULL, arrow_cast(arrow_cast(11, 'Int32'), 'Date32')),
446+
d64 IN (NULL, arrow_cast(arrow_cast(11, 'Int64'), 'Date64')),
447+
t32s IN (NULL, arrow_cast(arrow_cast(11, 'Int32'), 'Time32(Second)')),
448+
t64ns IN (NULL, arrow_cast(arrow_cast(11, 'Int64'), 'Time64(Nanosecond)')),
449+
ts_ns IN (NULL, arrow_cast(arrow_cast(11, 'Int64'), 'Timestamp(Nanosecond, None)')),
450+
ts_s_utc IN (NULL, arrow_cast(arrow_cast(11, 'Int64'), 'Timestamp(Second, Some("UTC"))')),
451+
dur_s IN (NULL, arrow_cast(arrow_cast(11, 'Int64'), 'Duration(Second)'))
452+
FROM in_list_temporal
453+
ORDER BY label
454+
----
455+
match true true true true true true true
456+
no_match NULL NULL NULL NULL NULL NULL NULL
457+
nulls NULL NULL NULL NULL NULL NULL NULL
458+
459+
# Cleanup
460+
statement ok
461+
DROP TABLE in_list_temporal
462+
463+
####
464+
## Decimal128 IN List Specializations
465+
####
466+
467+
statement ok
468+
CREATE TABLE in_list_wide AS
469+
SELECT * FROM (VALUES
470+
('match', arrow_cast(11, 'Decimal128(10, 2)'), INTERVAL '11 months'),
471+
('no_match', arrow_cast(7, 'Decimal128(10, 2)'), INTERVAL '7 months'),
472+
('nulls', NULL, NULL)
473+
) AS t(label, d128, imdn);
474+
475+
query TT
476+
SELECT arrow_typeof(d128), arrow_typeof(imdn) FROM in_list_wide LIMIT 1
477+
----
478+
Decimal128(10, 2) Interval(MonthDayNano)
479+
480+
# Four non-null values and five non-null values (test different specializations)
481+
query TBBBB
482+
SELECT
483+
label,
484+
d128 IN (arrow_cast(3, 'Decimal128(10, 2)'), arrow_cast(4, 'Decimal128(10, 2)'), arrow_cast(5, 'Decimal128(10, 2)'), arrow_cast(11, 'Decimal128(10, 2)')),
485+
d128 IN (arrow_cast(3, 'Decimal128(10, 2)'), arrow_cast(4, 'Decimal128(10, 2)'), arrow_cast(5, 'Decimal128(10, 2)'), arrow_cast(11, 'Decimal128(10, 2)'), arrow_cast(13, 'Decimal128(10, 2)')),
486+
imdn IN (INTERVAL '3 months', INTERVAL '4 months', INTERVAL '5 months', INTERVAL '11 months'),
487+
imdn IN (INTERVAL '3 months', INTERVAL '4 months', INTERVAL '5 months', INTERVAL '11 months', INTERVAL '13 months')
488+
FROM in_list_wide
489+
ORDER BY label
490+
----
491+
match true true true true
492+
no_match false false false false
493+
nulls NULL NULL NULL NULL
494+
495+
# The same lists with NOT IN.
496+
query TBBBB
497+
SELECT
498+
label,
499+
d128 NOT IN (arrow_cast(3, 'Decimal128(10, 2)'), arrow_cast(4, 'Decimal128(10, 2)'), arrow_cast(5, 'Decimal128(10, 2)'), arrow_cast(11, 'Decimal128(10, 2)')),
500+
d128 NOT IN (arrow_cast(3, 'Decimal128(10, 2)'), arrow_cast(4, 'Decimal128(10, 2)'), arrow_cast(5, 'Decimal128(10, 2)'), arrow_cast(11, 'Decimal128(10, 2)'), arrow_cast(13, 'Decimal128(10, 2)')),
501+
imdn NOT IN (INTERVAL '3 months', INTERVAL '4 months', INTERVAL '5 months', INTERVAL '11 months'),
502+
imdn NOT IN (INTERVAL '3 months', INTERVAL '4 months', INTERVAL '5 months', INTERVAL '11 months', INTERVAL '13 months')
503+
FROM in_list_wide
504+
ORDER BY label
505+
----
506+
match false false false false
507+
no_match true true true true
508+
nulls NULL NULL NULL NULL
509+
510+
# Null IN list values, including short lists with a single non-null value.
511+
query TBBBB
512+
SELECT
513+
label,
514+
d128 IN (NULL, arrow_cast(3, 'Decimal128(10, 2)'), arrow_cast(4, 'Decimal128(10, 2)'), arrow_cast(11, 'Decimal128(10, 2)')),
515+
d128 IN (NULL, arrow_cast(11, 'Decimal128(10, 2)')),
516+
imdn IN (NULL, INTERVAL '3 months', INTERVAL '4 months', INTERVAL '11 months'),
517+
imdn IN (NULL, INTERVAL '11 months')
518+
FROM in_list_wide
519+
ORDER BY label
520+
----
521+
match true true true true
522+
no_match NULL NULL NULL NULL
523+
nulls NULL NULL NULL NULL
524+
525+
# Cleanup
526+
statement ok
527+
DROP TABLE in_list_wide

0 commit comments

Comments
 (0)