@@ -170,6 +170,22 @@ minus_one false false false false false false false false
170170one true false true false true false true false
171171zero 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
174190statement ok
175191DROP TABLE in_list_ints;
@@ -329,6 +345,183 @@ Float64 match true true false
329345Float64 no_match false NULL false
330346Float64 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
333363statement ok
334364DROP 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