Skip to content

Commit 63f5b55

Browse files
authored
tests: Add IN list coverage for FixedSizeBinary (#24660)
## Which issue does this PR close? - part of #23307 - related to #24102 ## Rationale for this change I want full end to end coverage of `IN` lists and their optimizations as sqllogictests, not just unit tests ## What changes are included in this PR? 1. Add SLT coverage for FixedSizeBinary arrays (to cover the specializations added in #24102) ## Are these changes tested? They are only tests ## Are there any user-facing changes? No, tests only
1 parent 4397826 commit 63f5b55

1 file changed

Lines changed: 188 additions & 0 deletions

File tree

‎datafusion/sqllogictest/test_files/in_list.slt‎

Lines changed: 188 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -579,3 +579,191 @@ nulls NULL NULL
579579
# Cleanup
580580
statement ok
581581
DROP TABLE in_list_interval
582+
583+
####
584+
## FixedSizeBinary IN List Specializations
585+
##
586+
## Widths that are commonly special cased: 1, 2, 4, 8, and 16
587+
## Width that likely uses general path: 3
588+
####
589+
590+
statement ok
591+
CREATE TABLE in_list_fsb AS
592+
SELECT
593+
label,
594+
arrow_cast(b1, 'FixedSizeBinary(1)') AS fsb1,
595+
arrow_cast(b2, 'FixedSizeBinary(2)') AS fsb2,
596+
arrow_cast(b3, 'FixedSizeBinary(3)') AS fsb3,
597+
arrow_cast(b4, 'FixedSizeBinary(4)') AS fsb4,
598+
arrow_cast(b8, 'FixedSizeBinary(8)') AS fsb8,
599+
arrow_cast(b16, 'FixedSizeBinary(16)') AS fsb16
600+
FROM (VALUES
601+
('match', X'0B', X'000B', X'00000B', X'0000000B', X'000000000000000B', X'0000000000000000000000000000000B'),
602+
('no_match', X'07', X'0007', X'000007', X'00000007', X'0000000000000007', X'00000000000000000000000000000007'),
603+
('nulls', NULL, NULL, NULL, NULL, NULL, NULL)
604+
) AS t(label, b1, b2, b3, b4, b8, b16);
605+
606+
query TTTTTT
607+
SELECT
608+
arrow_typeof(fsb1),
609+
arrow_typeof(fsb2),
610+
arrow_typeof(fsb3),
611+
arrow_typeof(fsb4),
612+
arrow_typeof(fsb8),
613+
arrow_typeof(fsb16)
614+
FROM in_list_fsb
615+
LIMIT 1
616+
----
617+
FixedSizeBinary(1) FixedSizeBinary(2) FixedSizeBinary(3) FixedSizeBinary(4) FixedSizeBinary(8) FixedSizeBinary(16)
618+
619+
# Eight element lists, all matching or none matching
620+
query TBBBBBB
621+
SELECT
622+
label,
623+
fsb1 IN (arrow_cast(X'03', 'FixedSizeBinary(1)'), arrow_cast(X'04', 'FixedSizeBinary(1)'), arrow_cast(X'05', 'FixedSizeBinary(1)'), arrow_cast(X'06', 'FixedSizeBinary(1)'),
624+
arrow_cast(X'08', 'FixedSizeBinary(1)'), arrow_cast(X'09', 'FixedSizeBinary(1)'), arrow_cast(X'0A', 'FixedSizeBinary(1)'), arrow_cast(X'0B', 'FixedSizeBinary(1)')),
625+
fsb2 IN (arrow_cast(X'0003', 'FixedSizeBinary(2)'), arrow_cast(X'0004', 'FixedSizeBinary(2)'), arrow_cast(X'0005', 'FixedSizeBinary(2)'), arrow_cast(X'0006', 'FixedSizeBinary(2)'),
626+
arrow_cast(X'0008', 'FixedSizeBinary(2)'), arrow_cast(X'0009', 'FixedSizeBinary(2)'), arrow_cast(X'000A', 'FixedSizeBinary(2)'), arrow_cast(X'000B', 'FixedSizeBinary(2)')),
627+
fsb3 IN (arrow_cast(X'000003', 'FixedSizeBinary(3)'), arrow_cast(X'000004', 'FixedSizeBinary(3)'), arrow_cast(X'000005', 'FixedSizeBinary(3)'), arrow_cast(X'000006', 'FixedSizeBinary(3)'),
628+
arrow_cast(X'000008', 'FixedSizeBinary(3)'), arrow_cast(X'000009', 'FixedSizeBinary(3)'), arrow_cast(X'00000A', 'FixedSizeBinary(3)'), arrow_cast(X'00000B', 'FixedSizeBinary(3)')),
629+
fsb4 IN (arrow_cast(X'00000003', 'FixedSizeBinary(4)'), arrow_cast(X'00000004', 'FixedSizeBinary(4)'), arrow_cast(X'00000005', 'FixedSizeBinary(4)'), arrow_cast(X'00000006', 'FixedSizeBinary(4)'),
630+
arrow_cast(X'00000008', 'FixedSizeBinary(4)'), arrow_cast(X'00000009', 'FixedSizeBinary(4)'), arrow_cast(X'0000000A', 'FixedSizeBinary(4)'), arrow_cast(X'0000000B', 'FixedSizeBinary(4)')),
631+
fsb8 IN (arrow_cast(X'0000000000000003', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000004', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000005', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000006', 'FixedSizeBinary(8)'),
632+
arrow_cast(X'0000000000000008', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000009', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000000A', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000000B', 'FixedSizeBinary(8)')),
633+
fsb16 IN (arrow_cast(X'00000000000000000000000000000003', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000004', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000005', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000006', 'FixedSizeBinary(16)'),
634+
arrow_cast(X'00000000000000000000000000000008', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000009', 'FixedSizeBinary(16)'), arrow_cast(X'0000000000000000000000000000000A', 'FixedSizeBinary(16)'), arrow_cast(X'0000000000000000000000000000000B', 'FixedSizeBinary(16)'))
635+
FROM in_list_fsb
636+
ORDER BY label
637+
----
638+
match true true true true true true
639+
no_match false false false false false false
640+
nulls NULL NULL NULL NULL NULL NULL
641+
642+
# The same lists with NOT IN.
643+
query TBBBBBB
644+
SELECT
645+
label,
646+
fsb1 NOT IN (arrow_cast(X'03', 'FixedSizeBinary(1)'), arrow_cast(X'04', 'FixedSizeBinary(1)'), arrow_cast(X'05', 'FixedSizeBinary(1)'), arrow_cast(X'06', 'FixedSizeBinary(1)'),
647+
arrow_cast(X'08', 'FixedSizeBinary(1)'), arrow_cast(X'09', 'FixedSizeBinary(1)'), arrow_cast(X'0A', 'FixedSizeBinary(1)'), arrow_cast(X'0B', 'FixedSizeBinary(1)')),
648+
fsb2 NOT IN (arrow_cast(X'0003', 'FixedSizeBinary(2)'), arrow_cast(X'0004', 'FixedSizeBinary(2)'), arrow_cast(X'0005', 'FixedSizeBinary(2)'), arrow_cast(X'0006', 'FixedSizeBinary(2)'),
649+
arrow_cast(X'0008', 'FixedSizeBinary(2)'), arrow_cast(X'0009', 'FixedSizeBinary(2)'), arrow_cast(X'000A', 'FixedSizeBinary(2)'), arrow_cast(X'000B', 'FixedSizeBinary(2)')),
650+
fsb3 NOT IN (arrow_cast(X'000003', 'FixedSizeBinary(3)'), arrow_cast(X'000004', 'FixedSizeBinary(3)'), arrow_cast(X'000005', 'FixedSizeBinary(3)'), arrow_cast(X'000006', 'FixedSizeBinary(3)'),
651+
arrow_cast(X'000008', 'FixedSizeBinary(3)'), arrow_cast(X'000009', 'FixedSizeBinary(3)'), arrow_cast(X'00000A', 'FixedSizeBinary(3)'), arrow_cast(X'00000B', 'FixedSizeBinary(3)')),
652+
fsb4 NOT IN (arrow_cast(X'00000003', 'FixedSizeBinary(4)'), arrow_cast(X'00000004', 'FixedSizeBinary(4)'), arrow_cast(X'00000005', 'FixedSizeBinary(4)'), arrow_cast(X'00000006', 'FixedSizeBinary(4)'),
653+
arrow_cast(X'00000008', 'FixedSizeBinary(4)'), arrow_cast(X'00000009', 'FixedSizeBinary(4)'), arrow_cast(X'0000000A', 'FixedSizeBinary(4)'), arrow_cast(X'0000000B', 'FixedSizeBinary(4)')),
654+
fsb8 NOT IN (arrow_cast(X'0000000000000003', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000004', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000005', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000006', 'FixedSizeBinary(8)'),
655+
arrow_cast(X'0000000000000008', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000009', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000000A', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000000B', 'FixedSizeBinary(8)')),
656+
fsb16 NOT IN (arrow_cast(X'00000000000000000000000000000003', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000004', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000005', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000006', 'FixedSizeBinary(16)'),
657+
arrow_cast(X'00000000000000000000000000000008', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000009', 'FixedSizeBinary(16)'), arrow_cast(X'0000000000000000000000000000000A', 'FixedSizeBinary(16)'), arrow_cast(X'0000000000000000000000000000000B', 'FixedSizeBinary(16)'))
658+
FROM in_list_fsb
659+
ORDER BY label
660+
----
661+
match false false false false false false
662+
no_match true true true true true true
663+
nulls NULL NULL NULL NULL NULL NULL
664+
665+
# Longer lists: 17, 9, 33, 17, and 5 elements for widths 1, 2, 4, 8, and 16
666+
query TBBBBB
667+
SELECT
668+
label,
669+
fsb1 IN (arrow_cast(X'0B', 'FixedSizeBinary(1)'), arrow_cast(X'10', 'FixedSizeBinary(1)'), arrow_cast(X'11', 'FixedSizeBinary(1)'), arrow_cast(X'12', 'FixedSizeBinary(1)'),
670+
arrow_cast(X'13', 'FixedSizeBinary(1)'), arrow_cast(X'14', 'FixedSizeBinary(1)'), arrow_cast(X'15', 'FixedSizeBinary(1)'), arrow_cast(X'16', 'FixedSizeBinary(1)'),
671+
arrow_cast(X'17', 'FixedSizeBinary(1)'), arrow_cast(X'18', 'FixedSizeBinary(1)'), arrow_cast(X'19', 'FixedSizeBinary(1)'), arrow_cast(X'1A', 'FixedSizeBinary(1)'),
672+
arrow_cast(X'1B', 'FixedSizeBinary(1)'), arrow_cast(X'1C', 'FixedSizeBinary(1)'), arrow_cast(X'1D', 'FixedSizeBinary(1)'), arrow_cast(X'1E', 'FixedSizeBinary(1)'),
673+
arrow_cast(X'1F', 'FixedSizeBinary(1)')),
674+
fsb2 IN (arrow_cast(X'000B', 'FixedSizeBinary(2)'), arrow_cast(X'0010', 'FixedSizeBinary(2)'), arrow_cast(X'0011', 'FixedSizeBinary(2)'), arrow_cast(X'0012', 'FixedSizeBinary(2)'),
675+
arrow_cast(X'0013', 'FixedSizeBinary(2)'), arrow_cast(X'0014', 'FixedSizeBinary(2)'), arrow_cast(X'0015', 'FixedSizeBinary(2)'), arrow_cast(X'0016', 'FixedSizeBinary(2)'),
676+
arrow_cast(X'0017', 'FixedSizeBinary(2)')),
677+
fsb4 IN (arrow_cast(X'0000000B', 'FixedSizeBinary(4)'), arrow_cast(X'00000010', 'FixedSizeBinary(4)'), arrow_cast(X'00000011', 'FixedSizeBinary(4)'), arrow_cast(X'00000012', 'FixedSizeBinary(4)'),
678+
arrow_cast(X'00000013', 'FixedSizeBinary(4)'), arrow_cast(X'00000014', 'FixedSizeBinary(4)'), arrow_cast(X'00000015', 'FixedSizeBinary(4)'), arrow_cast(X'00000016', 'FixedSizeBinary(4)'),
679+
arrow_cast(X'00000017', 'FixedSizeBinary(4)'), arrow_cast(X'00000018', 'FixedSizeBinary(4)'), arrow_cast(X'00000019', 'FixedSizeBinary(4)'), arrow_cast(X'0000001A', 'FixedSizeBinary(4)'),
680+
arrow_cast(X'0000001B', 'FixedSizeBinary(4)'), arrow_cast(X'0000001C', 'FixedSizeBinary(4)'), arrow_cast(X'0000001D', 'FixedSizeBinary(4)'), arrow_cast(X'0000001E', 'FixedSizeBinary(4)'),
681+
arrow_cast(X'0000001F', 'FixedSizeBinary(4)'), arrow_cast(X'00000020', 'FixedSizeBinary(4)'), arrow_cast(X'00000021', 'FixedSizeBinary(4)'), arrow_cast(X'00000022', 'FixedSizeBinary(4)'),
682+
arrow_cast(X'00000023', 'FixedSizeBinary(4)'), arrow_cast(X'00000024', 'FixedSizeBinary(4)'), arrow_cast(X'00000025', 'FixedSizeBinary(4)'), arrow_cast(X'00000026', 'FixedSizeBinary(4)'),
683+
arrow_cast(X'00000027', 'FixedSizeBinary(4)'), arrow_cast(X'00000028', 'FixedSizeBinary(4)'), arrow_cast(X'00000029', 'FixedSizeBinary(4)'), arrow_cast(X'0000002A', 'FixedSizeBinary(4)'),
684+
arrow_cast(X'0000002B', 'FixedSizeBinary(4)'), arrow_cast(X'0000002C', 'FixedSizeBinary(4)'), arrow_cast(X'0000002D', 'FixedSizeBinary(4)'), arrow_cast(X'0000002E', 'FixedSizeBinary(4)'),
685+
arrow_cast(X'0000002F', 'FixedSizeBinary(4)')),
686+
fsb8 IN (arrow_cast(X'000000000000000B', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000010', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000011', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000012', 'FixedSizeBinary(8)'),
687+
arrow_cast(X'0000000000000013', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000014', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000015', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000016', 'FixedSizeBinary(8)'),
688+
arrow_cast(X'0000000000000017', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000018', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000019', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000001A', 'FixedSizeBinary(8)'),
689+
arrow_cast(X'000000000000001B', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000001C', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000001D', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000001E', 'FixedSizeBinary(8)'),
690+
arrow_cast(X'000000000000001F', 'FixedSizeBinary(8)')),
691+
fsb16 IN (arrow_cast(X'0000000000000000000000000000000B', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000010', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000011', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000012', 'FixedSizeBinary(16)'),
692+
arrow_cast(X'00000000000000000000000000000013', 'FixedSizeBinary(16)'))
693+
FROM in_list_fsb
694+
ORDER BY label
695+
----
696+
match true true true true true
697+
no_match false false false false false
698+
nulls NULL NULL NULL NULL NULL
699+
700+
# The same lists with NOT IN.
701+
query TBBBBB
702+
SELECT
703+
label,
704+
fsb1 NOT IN (arrow_cast(X'0B', 'FixedSizeBinary(1)'), arrow_cast(X'10', 'FixedSizeBinary(1)'), arrow_cast(X'11', 'FixedSizeBinary(1)'), arrow_cast(X'12', 'FixedSizeBinary(1)'),
705+
arrow_cast(X'13', 'FixedSizeBinary(1)'), arrow_cast(X'14', 'FixedSizeBinary(1)'), arrow_cast(X'15', 'FixedSizeBinary(1)'), arrow_cast(X'16', 'FixedSizeBinary(1)'),
706+
arrow_cast(X'17', 'FixedSizeBinary(1)'), arrow_cast(X'18', 'FixedSizeBinary(1)'), arrow_cast(X'19', 'FixedSizeBinary(1)'), arrow_cast(X'1A', 'FixedSizeBinary(1)'),
707+
arrow_cast(X'1B', 'FixedSizeBinary(1)'), arrow_cast(X'1C', 'FixedSizeBinary(1)'), arrow_cast(X'1D', 'FixedSizeBinary(1)'), arrow_cast(X'1E', 'FixedSizeBinary(1)'),
708+
arrow_cast(X'1F', 'FixedSizeBinary(1)')),
709+
fsb2 NOT IN (arrow_cast(X'000B', 'FixedSizeBinary(2)'), arrow_cast(X'0010', 'FixedSizeBinary(2)'), arrow_cast(X'0011', 'FixedSizeBinary(2)'), arrow_cast(X'0012', 'FixedSizeBinary(2)'),
710+
arrow_cast(X'0013', 'FixedSizeBinary(2)'), arrow_cast(X'0014', 'FixedSizeBinary(2)'), arrow_cast(X'0015', 'FixedSizeBinary(2)'), arrow_cast(X'0016', 'FixedSizeBinary(2)'),
711+
arrow_cast(X'0017', 'FixedSizeBinary(2)')),
712+
fsb4 NOT IN (arrow_cast(X'0000000B', 'FixedSizeBinary(4)'), arrow_cast(X'00000010', 'FixedSizeBinary(4)'), arrow_cast(X'00000011', 'FixedSizeBinary(4)'), arrow_cast(X'00000012', 'FixedSizeBinary(4)'),
713+
arrow_cast(X'00000013', 'FixedSizeBinary(4)'), arrow_cast(X'00000014', 'FixedSizeBinary(4)'), arrow_cast(X'00000015', 'FixedSizeBinary(4)'), arrow_cast(X'00000016', 'FixedSizeBinary(4)'),
714+
arrow_cast(X'00000017', 'FixedSizeBinary(4)'), arrow_cast(X'00000018', 'FixedSizeBinary(4)'), arrow_cast(X'00000019', 'FixedSizeBinary(4)'), arrow_cast(X'0000001A', 'FixedSizeBinary(4)'),
715+
arrow_cast(X'0000001B', 'FixedSizeBinary(4)'), arrow_cast(X'0000001C', 'FixedSizeBinary(4)'), arrow_cast(X'0000001D', 'FixedSizeBinary(4)'), arrow_cast(X'0000001E', 'FixedSizeBinary(4)'),
716+
arrow_cast(X'0000001F', 'FixedSizeBinary(4)'), arrow_cast(X'00000020', 'FixedSizeBinary(4)'), arrow_cast(X'00000021', 'FixedSizeBinary(4)'), arrow_cast(X'00000022', 'FixedSizeBinary(4)'),
717+
arrow_cast(X'00000023', 'FixedSizeBinary(4)'), arrow_cast(X'00000024', 'FixedSizeBinary(4)'), arrow_cast(X'00000025', 'FixedSizeBinary(4)'), arrow_cast(X'00000026', 'FixedSizeBinary(4)'),
718+
arrow_cast(X'00000027', 'FixedSizeBinary(4)'), arrow_cast(X'00000028', 'FixedSizeBinary(4)'), arrow_cast(X'00000029', 'FixedSizeBinary(4)'), arrow_cast(X'0000002A', 'FixedSizeBinary(4)'),
719+
arrow_cast(X'0000002B', 'FixedSizeBinary(4)'), arrow_cast(X'0000002C', 'FixedSizeBinary(4)'), arrow_cast(X'0000002D', 'FixedSizeBinary(4)'), arrow_cast(X'0000002E', 'FixedSizeBinary(4)'),
720+
arrow_cast(X'0000002F', 'FixedSizeBinary(4)')),
721+
fsb8 NOT IN (arrow_cast(X'000000000000000B', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000010', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000011', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000012', 'FixedSizeBinary(8)'),
722+
arrow_cast(X'0000000000000013', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000014', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000015', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000016', 'FixedSizeBinary(8)'),
723+
arrow_cast(X'0000000000000017', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000018', 'FixedSizeBinary(8)'), arrow_cast(X'0000000000000019', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000001A', 'FixedSizeBinary(8)'),
724+
arrow_cast(X'000000000000001B', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000001C', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000001D', 'FixedSizeBinary(8)'), arrow_cast(X'000000000000001E', 'FixedSizeBinary(8)'),
725+
arrow_cast(X'000000000000001F', 'FixedSizeBinary(8)')),
726+
fsb16 NOT IN (arrow_cast(X'0000000000000000000000000000000B', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000010', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000011', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000012', 'FixedSizeBinary(16)'),
727+
arrow_cast(X'00000000000000000000000000000013', 'FixedSizeBinary(16)'))
728+
FROM in_list_fsb
729+
ORDER BY label
730+
----
731+
match false false false false false
732+
no_match true true true true true
733+
nulls NULL NULL NULL NULL NULL
734+
735+
# A NULL in the list: matches return true, non-matches return NULL
736+
query TBBB
737+
SELECT
738+
label,
739+
fsb1 IN (NULL, arrow_cast(X'03', 'FixedSizeBinary(1)'), arrow_cast(X'04', 'FixedSizeBinary(1)'), arrow_cast(X'05', 'FixedSizeBinary(1)'),
740+
arrow_cast(X'06', 'FixedSizeBinary(1)'), arrow_cast(X'09', 'FixedSizeBinary(1)'), arrow_cast(X'0A', 'FixedSizeBinary(1)'), arrow_cast(X'0B', 'FixedSizeBinary(1)')),
741+
fsb3 IN (NULL, arrow_cast(X'000003', 'FixedSizeBinary(3)'), arrow_cast(X'000004', 'FixedSizeBinary(3)'), arrow_cast(X'000005', 'FixedSizeBinary(3)'),
742+
arrow_cast(X'000006', 'FixedSizeBinary(3)'), arrow_cast(X'000009', 'FixedSizeBinary(3)'), arrow_cast(X'00000A', 'FixedSizeBinary(3)'), arrow_cast(X'00000B', 'FixedSizeBinary(3)')),
743+
fsb16 IN (NULL, arrow_cast(X'00000000000000000000000000000003', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000004', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000005', 'FixedSizeBinary(16)'),
744+
arrow_cast(X'00000000000000000000000000000006', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000009', 'FixedSizeBinary(16)'), arrow_cast(X'0000000000000000000000000000000A', 'FixedSizeBinary(16)'), arrow_cast(X'0000000000000000000000000000000B', 'FixedSizeBinary(16)'))
745+
FROM in_list_fsb
746+
ORDER BY label
747+
----
748+
match true true true
749+
no_match NULL NULL NULL
750+
nulls NULL NULL NULL
751+
752+
# Dictionary encoded input column
753+
query TBB
754+
SELECT
755+
label,
756+
arrow_cast(fsb16, 'Dictionary(Int32, FixedSizeBinary(16))') IN
757+
(arrow_cast(X'00000000000000000000000000000003', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000004', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000005', 'FixedSizeBinary(16)'), arrow_cast(X'0000000000000000000000000000000B', 'FixedSizeBinary(16)')),
758+
arrow_cast(fsb16, 'Dictionary(Int32, FixedSizeBinary(16))') NOT IN
759+
(arrow_cast(X'00000000000000000000000000000003', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000004', 'FixedSizeBinary(16)'), arrow_cast(X'00000000000000000000000000000005', 'FixedSizeBinary(16)'), arrow_cast(X'0000000000000000000000000000000B', 'FixedSizeBinary(16)'))
760+
FROM in_list_fsb
761+
ORDER BY label
762+
----
763+
match true false
764+
no_match false true
765+
nulls NULL NULL
766+
767+
# Cleanup
768+
statement ok
769+
DROP TABLE in_list_fsb

0 commit comments

Comments
 (0)