Skip to content

Commit 076e149

Browse files
alambclaude
authored andcommitted
test: add IN list slt coverage for temporal, Decimal128 and Interval types (apache#23875)
## Which issue does this PR close? - Part of apache#23307. ## Rationale for this change IN lists over temporal, `Decimal128` and `Interval` columns had no SQL level coverage at all, and the specializations being added in apache#23014 route those types onto new code paths. ## What changes are included in this PR? Adds `in_list.slt` coverage for `Date32`, `Date64`, `Time32(Second)`, `Time64(Nanosecond)`, `Timestamp(Nanosecond, None)`, `Timestamp(Second, "UTC")`, `Duration(Second)`, `Decimal128` and `Interval(MonthDayNano)` ## Are these changes tested? This PR is only tests; they pass on `main` and against the head of apache#23014. ## Are there any user-facing changes? No. --------- Co-authored-by: Claude Opus 5 (1M context) <[email protected]>
1 parent c25a6e5 commit 076e149

1 file changed

Lines changed: 247 additions & 0 deletions

File tree

‎datafusion/sqllogictest/test_files/in_list.slt‎

Lines changed: 247 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,237 @@ 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(value, 'Date32') AS d32,
375+
arrow_cast(value, 'Date64') AS d64,
376+
arrow_cast(arrow_cast(value, 'Int32'), 'Time32(Second)') AS t32s,
377+
arrow_cast(value, 'Time64(Nanosecond)') AS t64ns,
378+
arrow_cast(value, 'Timestamp(Nanosecond, None)') AS ts_ns,
379+
arrow_cast(value, 'Timestamp(Second, Some("UTC"))') AS ts_s_utc,
380+
arrow_cast(value, '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(3, 'Date32'), arrow_cast(4, 'Date32'), arrow_cast(5, 'Date32'), arrow_cast(11, 'Date32')),
392+
d64 IN (arrow_cast(3, 'Date64'), arrow_cast(4, 'Date64'), arrow_cast(5, 'Date64'), arrow_cast(11, '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(3, 'Time64(Nanosecond)'), arrow_cast(4, 'Time64(Nanosecond)'), arrow_cast(5, 'Time64(Nanosecond)'), arrow_cast(11, 'Time64(Nanosecond)')),
395+
ts_ns IN (arrow_cast(3, 'Timestamp(Nanosecond, None)'), arrow_cast(4, 'Timestamp(Nanosecond, None)'), arrow_cast(5, 'Timestamp(Nanosecond, None)'), arrow_cast(11, 'Timestamp(Nanosecond, None)')),
396+
ts_s_utc IN (arrow_cast(3, 'Timestamp(Second, Some("UTC"))'), arrow_cast(4, 'Timestamp(Second, Some("UTC"))'), arrow_cast(5, 'Timestamp(Second, Some("UTC"))'), arrow_cast(11, 'Timestamp(Second, Some("UTC"))')),
397+
dur_s IN (arrow_cast(3, 'Duration(Second)'), arrow_cast(4, 'Duration(Second)'), arrow_cast(5, 'Duration(Second)'), arrow_cast(11, '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(3, 'Date32'), arrow_cast(4, 'Date32'), arrow_cast(5, 'Date32'), arrow_cast(11, 'Date32')),
410+
d64 NOT IN (arrow_cast(3, 'Date64'), arrow_cast(4, 'Date64'), arrow_cast(5, 'Date64'), arrow_cast(11, '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(3, 'Time64(Nanosecond)'), arrow_cast(4, 'Time64(Nanosecond)'), arrow_cast(5, 'Time64(Nanosecond)'), arrow_cast(11, 'Time64(Nanosecond)')),
413+
ts_ns NOT IN (arrow_cast(3, 'Timestamp(Nanosecond, None)'), arrow_cast(4, 'Timestamp(Nanosecond, None)'), arrow_cast(5, 'Timestamp(Nanosecond, None)'), arrow_cast(11, 'Timestamp(Nanosecond, None)')),
414+
ts_s_utc NOT IN (arrow_cast(3, 'Timestamp(Second, Some("UTC"))'), arrow_cast(4, 'Timestamp(Second, Some("UTC"))'), arrow_cast(5, 'Timestamp(Second, Some("UTC"))'), arrow_cast(11, 'Timestamp(Second, Some("UTC"))')),
415+
dur_s NOT IN (arrow_cast(3, 'Duration(Second)'), arrow_cast(4, 'Duration(Second)'), arrow_cast(5, 'Duration(Second)'), arrow_cast(11, '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(3, 'Date32'), arrow_cast(4, 'Date32'), arrow_cast(11, 'Date32')),
428+
d64 IN (NULL, arrow_cast(3, 'Date64'), arrow_cast(4, 'Date64'), arrow_cast(11, '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(3, 'Time64(Nanosecond)'), arrow_cast(4, 'Time64(Nanosecond)'), arrow_cast(11, 'Time64(Nanosecond)')),
431+
ts_ns IN (NULL, arrow_cast(3, 'Timestamp(Nanosecond, None)'), arrow_cast(4, 'Timestamp(Nanosecond, None)'), arrow_cast(11, 'Timestamp(Nanosecond, None)')),
432+
ts_s_utc IN (NULL, arrow_cast(3, 'Timestamp(Second, Some("UTC"))'), arrow_cast(4, 'Timestamp(Second, Some("UTC"))'), arrow_cast(11, 'Timestamp(Second, Some("UTC"))')),
433+
dur_s IN (NULL, arrow_cast(3, 'Duration(Second)'), arrow_cast(4, 'Duration(Second)'), arrow_cast(11, '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(11, 'Date32')),
446+
d64 IN (NULL, arrow_cast(11, 'Date64')),
447+
t32s IN (NULL, arrow_cast(arrow_cast(11, 'Int32'), 'Time32(Second)')),
448+
t64ns IN (NULL, arrow_cast(11, 'Time64(Nanosecond)')),
449+
ts_ns IN (NULL, arrow_cast(11, 'Timestamp(Nanosecond, None)')),
450+
ts_s_utc IN (NULL, arrow_cast(11, 'Timestamp(Second, Some("UTC"))')),
451+
dur_s IN (NULL, arrow_cast(11, '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_decimal AS
469+
SELECT * FROM (VALUES
470+
('match', arrow_cast(11, 'Decimal128(10, 2)')),
471+
('no_match', arrow_cast(7, 'Decimal128(10, 2)')),
472+
('nulls', NULL)
473+
) AS t(label, d128);
474+
475+
query T
476+
SELECT arrow_typeof(d128) FROM in_list_decimal LIMIT 1
477+
----
478+
Decimal128(10, 2)
479+
480+
# Four non-null values and five non-null values (test different specializations)
481+
query TBB
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+
FROM in_list_decimal
487+
ORDER BY label
488+
----
489+
match true true
490+
no_match false false
491+
nulls NULL NULL
492+
493+
# The same lists with NOT IN.
494+
query TBB
495+
SELECT
496+
label,
497+
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)')),
498+
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)'))
499+
FROM in_list_decimal
500+
ORDER BY label
501+
----
502+
match false false
503+
no_match true true
504+
nulls NULL NULL
505+
506+
# Null IN list values, including short lists with a single non-null value.
507+
query TBB
508+
SELECT
509+
label,
510+
d128 IN (NULL, arrow_cast(3, 'Decimal128(10, 2)'), arrow_cast(4, 'Decimal128(10, 2)'), arrow_cast(11, 'Decimal128(10, 2)')),
511+
d128 IN (NULL, arrow_cast(11, 'Decimal128(10, 2)'))
512+
FROM in_list_decimal
513+
ORDER BY label
514+
----
515+
match true true
516+
no_match NULL NULL
517+
nulls NULL NULL
518+
519+
# Cleanup
520+
statement ok
521+
DROP TABLE in_list_decimal
522+
523+
####
524+
## Interval IN List Specializations
525+
####
526+
527+
statement ok
528+
CREATE TABLE in_list_interval AS
529+
SELECT * FROM (VALUES
530+
('match', INTERVAL '11 months'),
531+
('no_match', INTERVAL '7 months'),
532+
('nulls', NULL)
533+
) AS t(label, imdn);
534+
535+
query T
536+
SELECT arrow_typeof(imdn) FROM in_list_interval LIMIT 1
537+
----
538+
Interval(MonthDayNano)
539+
540+
# Four non-null values and five non-null values (test different specializations)
541+
query TBB
542+
SELECT
543+
label,
544+
imdn IN (INTERVAL '3 months', INTERVAL '4 months', INTERVAL '5 months', INTERVAL '11 months'),
545+
imdn IN (INTERVAL '3 months', INTERVAL '4 months', INTERVAL '5 months', INTERVAL '11 months', INTERVAL '13 months')
546+
FROM in_list_interval
547+
ORDER BY label
548+
----
549+
match true true
550+
no_match false false
551+
nulls NULL NULL
552+
553+
# The same lists with NOT IN.
554+
query TBB
555+
SELECT
556+
label,
557+
imdn NOT IN (INTERVAL '3 months', INTERVAL '4 months', INTERVAL '5 months', INTERVAL '11 months'),
558+
imdn NOT IN (INTERVAL '3 months', INTERVAL '4 months', INTERVAL '5 months', INTERVAL '11 months', INTERVAL '13 months')
559+
FROM in_list_interval
560+
ORDER BY label
561+
----
562+
match false false
563+
no_match true true
564+
nulls NULL NULL
565+
566+
# Null IN list values, including short lists with a single non-null value.
567+
query TBB
568+
SELECT
569+
label,
570+
imdn IN (NULL, INTERVAL '3 months', INTERVAL '4 months', INTERVAL '11 months'),
571+
imdn IN (NULL, INTERVAL '11 months')
572+
FROM in_list_interval
573+
ORDER BY label
574+
----
575+
match true true
576+
no_match NULL NULL
577+
nulls NULL NULL
578+
579+
# Cleanup
580+
statement ok
581+
DROP TABLE in_list_interval

0 commit comments

Comments
 (0)