github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/idxconstraint/testdata/misc (about) 1 # Tests with a type that doesn't support Prev. 2 index-constraints vars=(string) index=(@1) 3 @1 > 'a' AND @1 < 'z' 4 ---- 5 [/e'a\x00' - /'z') 6 7 index-constraints vars=(string, int) index=(@1, @2) 8 @1 > 'a' AND @1 < 'z' AND @2 = 5 9 ---- 10 [/e'a\x00'/5 - /'z') 11 Remaining filter: @2 = 5 12 13 index-constraints vars=(string) index=(@1 desc) 14 @1 > 'a' AND @1 < 'z' 15 ---- 16 (/'z' - /e'a\x00'] 17 18 index-constraints vars=(string, int) index=(@1 desc, @2) 19 @1 > 'a' AND @1 < 'z' AND @2 = 5 20 ---- 21 (/'z' - /e'a\x00'/5] 22 Remaining filter: @2 = 5 23 24 # Tests with a type that doesn't support Next or Prev. 25 index-constraints vars=(decimal) index=(@1) 26 @1 > 1.5 27 ---- 28 (/1.5 - ] 29 30 index-constraints vars=(decimal) index=(@1) 31 @1 > 1.5 AND @1 < 2 32 ---- 33 (/1.5 - /2) 34 35 index-constraints vars=(decimal, decimal) index=(@1 not null, @2 not null) 36 @1 <= 1.5 AND @2 < 2.5 37 ---- 38 [ - /1.5/2.5) 39 Remaining filter: @2 < 2.5 40 41 # Tests with a type that supports Next/Prev but we have a maximal/minimal value. 42 index-constraints vars=(bool) index=(@1) 43 @1 > true 44 ---- 45 (/true - ] 46 47 index-constraints vars=(bool) index=(@1) 48 @1 < false 49 ---- 50 (/NULL - /false) 51 52 # Note the difference here between decimal and int: we 53 # can't extend the exclusive start key. 54 index-constraints vars=(decimal, decimal) index=(@1, @2) 55 @1 > 1.5 AND @2 > 2 56 ---- 57 (/1.5 - ] 58 Remaining filter: @2 > 2 59 60 index-constraints vars=(int) index=(@1) 61 @1 IS NULL 62 ---- 63 [/NULL - /NULL] 64 65 index-constraints vars=(int) index=(@1 not null) 66 @1 IS NOT DISTINCT FROM 1 67 ---- 68 [/1 - /1] 69 70 index-constraints vars=(int, int) index=(@1, @2) 71 @2 = @1 72 ---- 73 (/NULL - ] 74 Remaining filter: @2 = @1 75 76 index-constraints vars=(int, int) index=(@1, @2) 77 @2 < @1 78 ---- 79 (/NULL - ] 80 Remaining filter: @2 < @1 81 82 index-constraints vars=(int, int) index=(@1 not null, @2) 83 @1 = @2 84 ---- 85 [ - ] 86 Remaining filter: @1 = @2 87 88 # Tests with top-level OR. 89 # TODO(radu): expression simplification is limited when dealing with ORs; some 90 # of the remaining filters below are not necessary (or could be simplified 91 # further). 92 93 index-constraints vars=(int) index=(@1) 94 @1 = 1 OR @1 = 2 95 ---- 96 [/1 - /2] 97 98 index-constraints vars=(int) index=(@1) 99 @1 IS NULL OR @1 = 1 100 ---- 101 [/NULL - /NULL] 102 [/1 - /1] 103 104 index-constraints vars=(int) index=(@1) 105 (@1 >= 1 AND @1 <= 5) OR (@1 >= 2 AND @1 <= 8) 106 ---- 107 [/1 - /8] 108 109 index-constraints vars=(int) index=(@1) 110 (@1 >= 1 AND @1 <= 3) OR (@1 >= 5 AND @1 <= 8) 111 ---- 112 [/1 - /3] 113 [/5 - /8] 114 115 index-constraints vars=(int, int) index=(@1) 116 (@1 = 1 AND @2 = 5) OR (@1 = 2 and @2 = 6) 117 ---- 118 [/1 - /2] 119 Remaining filter: ((@1 = 1) AND (@2 = 5)) OR ((@1 = 2) AND (@2 = 6)) 120 121 index-constraints vars=(int, int) index=(@2) 122 (@1 = 1 AND @2 = 5) OR (@1 = 2 and @2 = 6) 123 ---- 124 [/5 - /6] 125 Remaining filter: ((@1 = 1) AND (@2 = 5)) OR ((@1 = 2) AND (@2 = 6)) 126 127 index-constraints vars=(int, int) index=(@2) 128 @1 = 1 OR @2 = 2 129 ---- 130 [ - ] 131 Remaining filter: (@1 = 1) OR (@2 = 2) 132 133 index-constraints vars=(int, int, int) index=(@1, @2, @3) 134 @1 = 1 OR (@1, @2, @3) IN ((4, 5, 6), (7, 8, 9)) 135 ---- 136 [/1 - /1] 137 [/4/5/6 - /4/5/6] 138 [/7/8/9 - /7/8/9] 139 140 index-constraints vars=(int, int, int) index=(@1, @2, @3) 141 @1 = 1 OR (@1 = 2 AND (@2, @3) IN ((4, 5), (6, 7))) OR (@1 = 3) 142 ---- 143 [/1 - /1] 144 [/2/4/5 - /2/4/5] 145 [/2/6/7 - /2/6/7] 146 [/3 - /3] 147 148 # Tests with inner OR. 149 150 index-constraints vars=(int, int) index=(@1, @2) 151 @1 = 1 AND (@2 = 2 OR @2 = 3) 152 ---- 153 [/1/2 - /1/3] 154 155 index-constraints vars=(int, int, int) index=(@1, @2, @3) 156 @1 = 1 AND (@2 = 2 OR (@2 = 3 AND @3 = 4)) 157 ---- 158 [/1/2 - /1/2] 159 [/1/3/4 - /1/3/4] 160 161 index-constraints vars=(int, int) index=(@1, @2) 162 @1 >= 1 AND (@2 = 2 OR @2 = 3) 163 ---- 164 [/1/2 - ] 165 Remaining filter: (@2 = 2) OR (@2 = 3) 166 167 index-constraints vars=(int, int, int) index=(@1, @2, @3) 168 @1 = 1 AND (@2 = 2 OR @2 = 3) AND (@3 >= 4) 169 ---- 170 [/1/2/4 - /1/2] 171 [/1/3/4 - /1/3] 172 173 index-constraints vars=(int, int, int) index=(@1, @2, @3) 174 @1 = 1 AND @2 = CASE WHEN @3 = 2 THEN 1 ELSE 2 END 175 ---- 176 (/1/NULL - /1] 177 Remaining filter: @2 = CASE WHEN @3 = 2 THEN 1 ELSE 2 END 178 179 index-constraints vars=(int, int) index=(@1, @2) 180 @1 = 1 AND @2 IS OF (INT) 181 ---- 182 [/1 - /1] 183 184 # This testcase exposed an issue around extending spans. We don't normalize the 185 # expression so we have a hierarchy of ANDs (which requires a more complex path 186 # for calculating spans). As a side-effect of disabling normalization, an 187 # unnecessary filter remains. 188 index-constraints vars=(int, int, int, int) index=(@1, @2, @3, @4) nonormalize 189 @1 = 1 AND @2 = 2 AND @3 = 3 AND @4 IN (4,5,6) 190 ---- 191 [/1/2/3/4 - /1/2/3/6] 192 193 index-constraints vars=(int, int) index=(@1, @2) 194 (@1 = 1) AND (@2 > 5) AND (@2 < 1) 195 ---- 196 197 # Verify that we ignore mixed-type comparisons (they would result in incorrect 198 # encodings, see #4313). We don't have testcases for IN because those error 199 # out early (during type-checking). 200 index-constraints vars=(int) index=(@1) 201 @1 = 1.5 202 ---- 203 (/NULL - ] 204 Remaining filter: @1 = 1.5 205 206 index-constraints vars=(int) index=(@1) 207 @1 > 1.5 208 ---- 209 (/NULL - ] 210 Remaining filter: @1 > 1.5 211 212 index-constraints vars=(int, int) index=(@1, @2) 213 (@1, @2) = (1, 2.5) 214 ---- 215 (/1/NULL - /1] 216 Remaining filter: @2 = 2.5 217 218 index-constraints vars=(int, int) index=(@1, @2) 219 (@1, @2) >= (1, 2.5) 220 ---- 221 [/1 - ] 222 Remaining filter: (@1, @2) >= (1, 2.5) 223 224 # Verify that we ignore spans that become invalid after extension. 225 index-constraints vars=(int, int) index=(@1, @2) 226 @1 >= 1 AND (@1, @2) < (1, 2) AND @2 = 5 227 ---- 228 229 index-constraints vars=(int, int) index=(@1, @2) 230 (@1 >= 1 AND (@1, @2) < (1, 2) OR @1 > 3) AND @2 = 5 231 ---- 232 [/4/5 - ] 233 Remaining filter: @2 = 5 234 235 # Regression test for #3472. 236 index-constraints vars=(int, int) index=(@1, @2) 237 (@1,@2) IN ((1, 2)) AND @1 = 1 238 ---- 239 [/1/2 - /1/2] 240 241 # Function call. 242 index-constraints vars=(string) index=(@1) 243 @1 > 'a' AND length(@1) = 2 244 ---- 245 [/e'a\x00' - ] 246 Remaining filter: length(@1) = 2 247 248 index-constraints vars=(bool) index=(@1) 249 true 250 ---- 251 [ - ]