@@ -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,237 @@ 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(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