|
| 1 | +# Licensed to the Apache Software Foundation (ASF) under one |
| 2 | +# or more contributor license agreements. See the NOTICE file |
| 3 | +# distributed with this work for additional information |
| 4 | +# regarding copyright ownership. The ASF licenses this file |
| 5 | +# to you under the Apache License, Version 2.0 (the |
| 6 | +# "License"); you may not use this file except in compliance |
| 7 | +# with the License. You may obtain a copy of the License at |
| 8 | +# |
| 9 | +# http://www.apache.org/licenses/LICENSE-2.0 |
| 10 | +# |
| 11 | +# Unless required by applicable law or agreed to in writing, |
| 12 | +# software distributed under the License is distributed on an |
| 13 | +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| 14 | +# KIND, either express or implied. See the License for the |
| 15 | +# specific language governing permissions and limitations |
| 16 | +# under the License. |
| 17 | + |
| 18 | +########## |
| 19 | +# IN List Tests |
| 20 | +# |
| 21 | +# This file focuses on the IN operator and its various specializations |
| 22 | +# |
| 23 | +# Note that "short" IN LISTS do not go through the InList implementation at all, |
| 24 | +# instead they are rewritten into a series of OR expressions. See: |
| 25 | +# https://github.com/apache/datafusion/blob/ed37b6c9555bc278130dc774ed833b8c0bd29bfa/datafusion/optimizer/src/simplify_expressions/inlist_simplifier.rs#L39-L88 |
| 26 | +########## |
| 27 | + |
| 28 | + |
| 29 | +# Tests for IN LIST integer specializations |
| 30 | + |
| 31 | + |
| 32 | +statement ok |
| 33 | +CREATE TABLE in_list_ints ( |
| 34 | + label VARCHAR, |
| 35 | + i8 TINYINT, |
| 36 | + u8 TINYINT UNSIGNED, |
| 37 | + i16 SMALLINT, |
| 38 | + u16 SMALLINT UNSIGNED, |
| 39 | + i32 INT, |
| 40 | + u32 INT UNSIGNED, |
| 41 | + i64 BIGINT, |
| 42 | + u64 BIGINT UNSIGNED |
| 43 | +) AS VALUES |
| 44 | + ('min', -128, 0, -32768, 0, -2147483648, 0, -9223372036854775808, 0), |
| 45 | + ('minus_one', -1, 1, -1, 1, -1, 1, -1, 1), |
| 46 | + ('zero', 0, 0, 0, 0, 0, 0, 0, 0), |
| 47 | + ('one', 1, 1, 1, 1, 1, 1, 1, 1), |
| 48 | + ('eleven', 11, 11, 11, 11, 11, 11, 11, 11), |
| 49 | + ('max', 127, 255, 32767, 65535, 2147483647, 4294967295, 9223372036854775807, 18446744073709551615); |
| 50 | + |
| 51 | +# Verify that the Arrow types of the columns are as expected. This is important because the IN LIST specializations are based on the column types. |
| 52 | +query TTTTTTTT |
| 53 | +SELECT |
| 54 | + arrow_typeof(i8), |
| 55 | + arrow_typeof(u8), |
| 56 | + arrow_typeof(i16), |
| 57 | + arrow_typeof(u16), |
| 58 | + arrow_typeof(i32), |
| 59 | + arrow_typeof(u32), |
| 60 | + arrow_typeof(i64), |
| 61 | + arrow_typeof(u64) |
| 62 | +FROM in_list_ints |
| 63 | +LIMIT 1 |
| 64 | +---- |
| 65 | +Int8 UInt8 Int16 UInt16 Int32 UInt32 Int64 UInt64 |
| 66 | + |
| 67 | +# Verify the data is as expected. |
| 68 | +query TIIIIIIII |
| 69 | +SELECT label, i8, u8, i16, u16, i32, u32, i64, u64 |
| 70 | +FROM in_list_ints |
| 71 | +ORDER BY label |
| 72 | +---- |
| 73 | +eleven 11 11 11 11 11 11 11 11 |
| 74 | +max 127 255 32767 65535 2147483647 4294967295 9223372036854775807 18446744073709551615 |
| 75 | +min -128 0 -32768 0 -2147483648 0 -9223372036854775808 0 |
| 76 | +minus_one -1 1 -1 1 -1 1 -1 1 |
| 77 | +one 1 1 1 1 1 1 1 1 |
| 78 | +zero 0 0 0 0 0 0 0 0 |
| 79 | + |
| 80 | +# Empty IN lists are rejected by the SQL parser. |
| 81 | +statement error .*Expected: an expression, found: \).* |
| 82 | +SELECT 1 IN (); |
| 83 | + |
| 84 | +# Min for each type |
| 85 | +query TBBBBBBBB |
| 86 | +SELECT |
| 87 | + label, |
| 88 | + i8 IN (1, 2, 3, -128), |
| 89 | + u8 IN (1, 2, 3, 0), |
| 90 | + i16 IN (1, 2, 3, -32768), |
| 91 | + u16 IN (1, 2, 3, 0), |
| 92 | + i32 IN (1, 2, 3, -2147483648), |
| 93 | + u32 IN (1, 2, 3, 0), |
| 94 | + i64 IN (1, 2, 3, -9223372036854775808), |
| 95 | + u64 IN (1, 2, 3, 0) |
| 96 | +FROM in_list_ints |
| 97 | +ORDER BY label |
| 98 | +---- |
| 99 | +eleven false false false false false false false false |
| 100 | +max false false false false false false false false |
| 101 | +min true true true true true true true true |
| 102 | +minus_one false true false true false true false true |
| 103 | +one true true true true true true true true |
| 104 | +zero false true false true false true false true |
| 105 | + |
| 106 | +# Max for each type (use values that cover the entire input range |
| 107 | +# e.g. values that have 1 non zero byte. 2 non zero bytes, etc) |
| 108 | +query TBBBBBBBB |
| 109 | +SELECT |
| 110 | + label, |
| 111 | + i8 IN (-64, -32, 32, 64, 127), |
| 112 | + u8 IN (32, 64, 128, 200, 255), |
| 113 | + i16 IN (3, 258, 4097, 16385, 32767), |
| 114 | + u16 IN (3, 258, 4097, 16385, 65535), |
| 115 | + i32 IN (3, 258, 66051, 16909060, 2147483647), |
| 116 | + u32 IN (3, 258, 66051, 16909060, 4294967295), |
| 117 | + i64 IN (3, 258, 66051, 16909060, 9223372036854775807), |
| 118 | + u64 IN (3, 258, 66051, 16909060, 18446744073709551615) |
| 119 | +FROM in_list_ints |
| 120 | +ORDER BY label |
| 121 | +---- |
| 122 | +eleven false false false false false false false false |
| 123 | +max true true true true true true true true |
| 124 | +min false false false false false false false false |
| 125 | +minus_one false false false false false false false false |
| 126 | +one false false false false false false false false |
| 127 | +zero false false false false false false false false |
| 128 | + |
| 129 | +# Twelve item IN list with no matches (also cover the entire range) |
| 130 | +query TBBBBBBBB |
| 131 | +SELECT |
| 132 | + label, |
| 133 | + i8 IN (-120, -64, -32, -16, -8, -4, -2, 2, 32, 64, 100, 126), |
| 134 | + u8 IN (2, 3, 4, 8, 16, 32, 64, 100, 128, 150, 200, 254), |
| 135 | + i16 IN (-30000, -16384, -1024, -257, 2, 257, 4097, 8192, 16385, 20000, 30000, 32000), |
| 136 | + u16 IN (2, 3, 4, 8, 16, 32, 257, 4097, 16385, 32768, 60000, 65534), |
| 137 | + i32 IN (-2000000000, -1000000000, -16711936, -65536, -1024, 2, 66051, 16909060, 305419896, 1076895760, 2000000000, 2147483646), |
| 138 | + u32 IN (2, 3, 4, 8, 16, 66051, 16909060, 305419896, 1076895760, 2309737967, 4000000000, 4294967294), |
| 139 | + i64 IN (-9000000000000000000, -72057594037927936, -1000000000000000000, -65536, -1024, 2, 72623859790382856, 81985529216486895, 819855292164868960, 1234605616436508552, 9000000000000000000, 9223372036854775806), |
| 140 | + u64 IN (2, 3, 4, 8, 16, 72623859790382856, 81985529216486895, 819855292164868960, 1234605616436508552, 18000000000000000000, 18364758544493064720, 18446744073709551614) |
| 141 | +FROM in_list_ints |
| 142 | +ORDER BY label |
| 143 | +---- |
| 144 | +eleven false false false false false false false false |
| 145 | +max false false false false false false false false |
| 146 | +min false false false false false false false false |
| 147 | +minus_one false false false false false false false false |
| 148 | +one false false false false false false false false |
| 149 | +zero false false false false false false false false |
| 150 | + |
| 151 | +# Twelve item IN list with matches, including 11. |
| 152 | +query TBBBBBBBB |
| 153 | +SELECT |
| 154 | + label, |
| 155 | + i8 IN (-120, -64, -32, -16, -8, -4, -2, -128, 1, 3, 5, 11), |
| 156 | + u8 IN (2, 4, 8, 16, 32, 64, 128, 200, 0, 11, 250, 255), |
| 157 | + i16 IN (-30000, -20000, -10000, -32768, -1024, -256, -128, 1, 2, 3, 5, 11), |
| 158 | + u16 IN (2, 4, 8, 16, 32, 64, 128, 256, 0, 11, 60000, 65535), |
| 159 | + i32 IN (-2000000000, -1000000000, -2147483648, -65536, -1024, -256, -128, 1, 2, 3, 5, 11), |
| 160 | + u32 IN (2, 4, 8, 16, 32, 64, 128, 256, 0, 11, 4000000000, 4294967295), |
| 161 | + i64 IN (-9000000000000000000, -9223372036854775808, -1000000000, -65536, -1024, -256, -128, 1, 2, 3, 5, 11), |
| 162 | + u64 IN (2, 4, 8, 16, 32, 64, 128, 256, 0, 11, 18000000000000000000, 18446744073709551615) |
| 163 | +FROM in_list_ints |
| 164 | +ORDER BY label |
| 165 | +---- |
| 166 | +eleven true true true true true true true true |
| 167 | +max false true false true false true false true |
| 168 | +min true true true true true true true true |
| 169 | +minus_one false false false false false false false false |
| 170 | +one true false true false true false true false |
| 171 | +zero false true false true false true false true |
| 172 | + |
| 173 | +# Cleanup |
| 174 | +statement ok |
| 175 | +DROP TABLE in_list_ints; |
| 176 | + |
| 177 | +#### |
| 178 | +## Integer Null Handling |
| 179 | +#### |
| 180 | + |
| 181 | +# Table with nulls to test null handling for integer IN list specializations |
| 182 | +statement ok |
| 183 | +CREATE TABLE in_list_ints_nullable ( |
| 184 | + label VARCHAR, |
| 185 | + i8 TINYINT, |
| 186 | + u8 TINYINT UNSIGNED, |
| 187 | + i16 SMALLINT, |
| 188 | + u16 SMALLINT UNSIGNED, |
| 189 | + i32 INT, |
| 190 | + u32 INT UNSIGNED, |
| 191 | + i64 BIGINT, |
| 192 | + u64 BIGINT UNSIGNED |
| 193 | +) AS VALUES |
| 194 | + ('match', 11, 11, 11, 11, 11, 11, 11, 11), |
| 195 | + ('no_match', 7, 7, 7, 7, 7, 7, 7, 7), |
| 196 | + ('nulls', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); |
| 197 | + |
| 198 | +# Null input values return NULL when the IN list has no nulls. |
| 199 | +query TBBBBBBBB |
| 200 | +SELECT |
| 201 | + label, |
| 202 | + i8 IN (3, 4, 5, 6, 11), |
| 203 | + u8 IN (3, 4, 5, 6, 11), |
| 204 | + i16 IN (3, 258, 4097, 16385, 11), |
| 205 | + u16 IN (3, 258, 4097, 16385, 11), |
| 206 | + i32 IN (3, 258, 66051, 16909060, 11), |
| 207 | + u32 IN (3, 258, 66051, 16909060, 11), |
| 208 | + i64 IN (3, 258, 66051, 16909060, 11), |
| 209 | + u64 IN (3, 258, 66051, 16909060, 11) |
| 210 | +FROM in_list_ints_nullable |
| 211 | +ORDER BY label |
| 212 | +---- |
| 213 | +match true true true true true true true true |
| 214 | +no_match false false false false false false false false |
| 215 | +nulls NULL NULL NULL NULL NULL NULL NULL NULL |
| 216 | + |
| 217 | +# Null IN list values return true for matches and NULL for non-matches. |
| 218 | +query TBBBBBBBB |
| 219 | +SELECT |
| 220 | + label, |
| 221 | + i8 IN (NULL, 3, 4, 5, 11), |
| 222 | + u8 IN (NULL, 3, 4, 5, 11), |
| 223 | + i16 IN (NULL, 3, 258, 4097, 11), |
| 224 | + u16 IN (NULL, 3, 258, 4097, 11), |
| 225 | + i32 IN (NULL, 3, 258, 66051, 11), |
| 226 | + u32 IN (NULL, 3, 258, 66051, 11), |
| 227 | + i64 IN (NULL, 3, 258, 66051, 11), |
| 228 | + u64 IN (NULL, 3, 258, 66051, 11) |
| 229 | +FROM in_list_ints_nullable |
| 230 | +ORDER BY label |
| 231 | +---- |
| 232 | +match true true true true true true true true |
| 233 | +no_match NULL NULL NULL NULL NULL NULL NULL NULL |
| 234 | +nulls NULL NULL NULL NULL NULL NULL NULL NULL |
| 235 | + |
| 236 | +# Cleanup |
| 237 | +statement ok |
| 238 | +DROP TABLE in_list_ints_nullable |
0 commit comments