github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/sem/tree/normalize_test.go (about) 1 // Copyright 2015 The Cockroach Authors. 2 // 3 // Use of this software is governed by the Business Source License 4 // included in the file licenses/BSL.txt. 5 // 6 // As of the Change Date specified in that file, in accordance with 7 // the Business Source License, use of this software will be governed 8 // by the Apache License, Version 2.0, included in the file 9 // licenses/APL.txt. 10 11 package tree_test 12 13 import ( 14 "context" 15 "testing" 16 17 "github.com/cockroachdb/cockroach/pkg/settings/cluster" 18 "github.com/cockroachdb/cockroach/pkg/sql/parser" 19 _ "github.com/cockroachdb/cockroach/pkg/sql/sem/builtins" 20 "github.com/cockroachdb/cockroach/pkg/sql/sem/tree" 21 "github.com/cockroachdb/cockroach/pkg/sql/types" 22 "github.com/cockroachdb/cockroach/pkg/util/leaktest" 23 ) 24 25 func TestContainsVars(t *testing.T) { 26 defer leaktest.AfterTest(t)() 27 testData := []struct { 28 expr string 29 expected bool 30 }{ 31 {`123`, false}, 32 {`123+123`, false}, 33 {`$1`, true}, 34 {`123+$1`, true}, 35 {`@1`, true}, 36 {`123+@1`, true}, 37 } 38 39 for _, d := range testData { 40 t.Run(d.expr, func(t *testing.T) { 41 expr, err := parser.ParseExpr(d.expr) 42 if err != nil { 43 t.Fatalf("%s: %v", d.expr, err) 44 } 45 res := tree.ContainsVars(expr) 46 if res != d.expected { 47 t.Fatalf("%s: expected %v, got %v", d.expr, d.expected, res) 48 } 49 }) 50 } 51 } 52 53 func TestNormalizeExpr(t *testing.T) { 54 defer leaktest.AfterTest(t)() 55 defer tree.MockNameTypes(map[string]*types.T{ 56 "a": types.Int, 57 "b": types.Int, 58 "c": types.Int, 59 "d": types.Bool, 60 "s": types.String, 61 "j": types.Jsonb, 62 "jv": types.Jsonb, 63 })() 64 testData := []struct { 65 expr string 66 expected string 67 }{ 68 {`(a)`, `a`}, 69 {`((((a))))`, `a`}, 70 // These expression previously always mapped INT2/INT4 to INT8, but after 71 // unifying the type system, they now produce better results. Leaving the 72 // tests here to make sure they don't regress. See 73 // https://github.com/cockroachdb/cockroach/issues/32639 74 {`CAST(NULL AS INT2)`, `CAST(NULL AS INT2)`}, 75 {`CAST(NULL AS INT4)`, `CAST(NULL AS INT4)`}, 76 {`CAST(NULL AS INT8)`, `CAST(NULL AS INT8)`}, 77 {`+a`, `a`}, 78 {`-(-a)`, `a`}, 79 {`-+-a`, `a`}, 80 {`-(a-b)`, `b - a`}, 81 {`-0`, `0`}, 82 {`-NULL`, `NULL`}, 83 {`-1`, `-1`}, 84 {`a+0`, `a`}, 85 {`0+a`, `a`}, 86 {`a+(2-2)`, `a`}, 87 {`a-0`, `a`}, 88 {`a*1`, `a`}, 89 {`1*a`, `a`}, 90 {`a+NULL`, `NULL`}, 91 {`a/1`, `CAST(a AS DECIMAL)`}, 92 {`0/a`, `0 / a`}, 93 {`0/1`, `0`}, 94 {`12 BETWEEN 24 AND 36`, `false`}, 95 {`12 BETWEEN 10 AND 20`, `true`}, 96 {`10 BETWEEN a AND 20`, `a <= 10`}, 97 {`(1 + 2) BETWEEN b AND c`, `(b <= 3) AND (c >= 3)`}, 98 {`a BETWEEN b AND c`, `(a >= b) AND (a <= c)`}, 99 {`a BETWEEN SYMMETRIC b AND c`, `((a >= b) AND (a <= c)) OR ((a >= c) AND (a <= b))`}, 100 {`a NOT BETWEEN b AND c`, `(a < b) OR (a > c)`}, 101 {`a NOT BETWEEN SYMMETRIC b AND c`, `((a < b) OR (a > c)) AND ((a < c) OR (a > b))`}, 102 {`a BETWEEN NULL AND c`, `NULL AND (a <= c)`}, 103 {`a BETWEEN SYMMETRIC NULL AND c`, `(NULL AND (a <= c)) OR ((a >= c) AND NULL)`}, 104 {`a BETWEEN b AND NULL`, `(a >= b) AND NULL`}, 105 {`a BETWEEN SYMMETRIC b AND NULL`, `((a >= b) AND NULL) OR (NULL AND (a <= b))`}, 106 {`a BETWEEN NULL AND NULL`, `NULL`}, 107 {`NULL BETWEEN 1 AND 2`, `NULL`}, 108 {`1+1`, `2`}, 109 {`(1+1,2+2,3+3)`, `(2, 4, 6)`}, 110 {`a+(1+1)`, `a + 2`}, 111 {`1+1+a`, `2 + a`}, 112 {`1+NULL`, `NULL`}, 113 {`1+(1+NULL)`, `NULL`}, 114 {`a=1+1`, `a = 2`}, 115 {`a=1+(2*3)-4`, `a = 3`}, 116 {`true OR d`, `true`}, 117 {`false OR d`, `d`}, 118 {`NULL OR d`, `NULL OR d`}, 119 {`d OR true`, `true`}, 120 {`d OR false`, `d`}, 121 {`d OR NULL`, `d OR NULL`}, 122 {`true AND d`, `d`}, 123 {`false AND d`, `false`}, 124 {`NULL AND d`, `NULL AND d`}, 125 {`d AND true`, `d`}, 126 {`d AND false`, `false`}, 127 {`d AND NULL`, `d AND NULL`}, 128 {`1 IN (1, 2, 3)`, `true`}, 129 {`1 IN (3, 2, 1)`, `true`}, 130 {`a IN (3, 2, 1)`, `a IN (1, 2, 3)`}, 131 {`1 IN (1, 2, a)`, `1 IN (1, 2, a)`}, 132 {`NULL IN ()`, `false`}, 133 {`NULL NOT IN ()`, `true`}, 134 {`NULL IN (1, 2, 3)`, `NULL`}, 135 {`a IN (NULL)`, `NULL`}, 136 {`a IN (NULL, NULL)`, `NULL`}, 137 {`1 IN (1, NULL)`, `true`}, 138 {`1 IN (2, NULL)`, `CAST(NULL AS BOOL)`}, 139 {`1 = ANY ARRAY[3, 2, 1]`, `true`}, 140 {`1 < SOME ARRAY[3, 2, 1]`, `true`}, 141 {`1 > SOME (ARRAY[3, 2, 1])`, `false`}, 142 {`1 > SOME (NULL)`, `NULL`}, 143 {`1 > SOME (((NULL)))`, `NULL`}, 144 {`NULL > SOME ARRAY[3, 2, 1]`, `NULL`}, 145 {`NULL > ALL ARRAY[3, 2, 1]`, `NULL`}, 146 {`4 > ALL ARRAY[3, 2, 1]`, `true`}, 147 {`a > ALL ARRAY[3, 2, 1]`, `a > ALL ARRAY[3,2,1]`}, 148 {`3 > ALL ARRAY[3, 2, a]`, `3 > ALL ARRAY[3, 2, a]`}, 149 {`3 > ANY (ARRAY[3, 2, a])`, `3 > ANY ARRAY[3, 2, a]`}, 150 {`3 > SOME (((ARRAY[3, 2, a])))`, `3 > SOME ARRAY[3, 2, a]`}, 151 {`NULL LIKE 'a'`, `NULL`}, 152 {`NULL NOT LIKE 'a'`, `NULL`}, 153 {`NULL ILIKE 'a'`, `NULL`}, 154 {`NULL NOT ILIKE 'a'`, `NULL`}, 155 {`NULL SIMILAR TO 'a'`, `NULL`}, 156 {`NULL NOT SIMILAR TO 'a'`, `NULL`}, 157 {`NULL ~ 'a'`, `NULL`}, 158 {`NULL !~ 'a'`, `NULL`}, 159 {`NULL ~* 'a'`, `NULL`}, 160 {`NULL !~* 'a'`, `NULL`}, 161 {`a<1`, `a < 1`}, 162 {`1>a`, `a < 1`}, 163 {`a<NULL`, `NULL`}, 164 {`(a+1)=2`, `a = 1`}, 165 {`(a-1)>=2`, `a >= 3`}, 166 {`(1+a)<=2`, `a <= 1`}, 167 {`(1-a)>2`, `a < -1`}, 168 {`2<(a+1)`, `a > 1`}, 169 {`2>(a-1)`, `a < 3`}, 170 {`2<(1+a)`, `a > 1`}, 171 {`2>(1-a)`, `a > -1`}, 172 {`(a+(1+1))=2`, `a = 0`}, 173 {`((a+1)+1)=2`, `a = 0`}, 174 {`a+1+1=2`, `a = 0`}, 175 {`1+1>=(b+c)`, `(b + c) <= 2`}, 176 {`b+c<=1+1`, `(b + c) <= 2`}, 177 {`a/2=1`, `a = 2`}, 178 {`1=a/2`, `a = 2`}, 179 {`s=lower('FOO')`, `s = 'foo'`}, 180 {`lower(s)='foo'`, `lower(s) = 'foo'`}, 181 {`random()`, `random()`}, 182 {`gen_random_uuid()`, `gen_random_uuid()`}, 183 {`current_date()`, `current_date()`}, 184 {`clock_timestamp()`, `clock_timestamp()`}, 185 {`now()`, `now()`}, 186 {`current_timestamp()`, `current_timestamp()`}, 187 {`current_timestamp(5)`, `current_timestamp(5)`}, 188 {`transaction_timestamp()`, `transaction_timestamp()`}, 189 {`statement_timestamp()`, `statement_timestamp()`}, 190 {`cluster_logical_timestamp()`, `cluster_logical_timestamp()`}, 191 {`clock_timestamp()`, `clock_timestamp()`}, 192 {`crdb_internal.force_error('a', 'b')`, `crdb_internal.force_error('a', 'b')`}, 193 {`crdb_internal.force_panic('a')`, `crdb_internal.force_panic('a')`}, 194 {`crdb_internal.force_log_fatal('a')`, `crdb_internal.force_log_fatal('a')`}, 195 {`crdb_internal.force_retry('1 day'::interval)`, `crdb_internal.force_retry('1 day')`}, 196 {`crdb_internal.no_constant_folding(123)`, `crdb_internal.no_constant_folding(123)`}, 197 {`crdb_internal.set_vmodule('a')`, `crdb_internal.set_vmodule('a')`}, 198 {`uuid_v4()`, `uuid_v4()`}, 199 {`experimental_uuid_v4()`, `experimental_uuid_v4()`}, 200 {`a=count('FOO') OVER ()`, `a = count('FOO') OVER ()`}, 201 {`9223372036854775808`, `9223372036854775808`}, 202 {`-9223372036854775808`, `-9223372036854775808`}, 203 {`(1, 2, 3) = (1, 2, 3)`, `true`}, 204 {`(1, 2, 3) IN ((1, 2, 3), (4, 5, 6))`, `true`}, 205 {`(1, 'one')`, `(1, 'one')`}, 206 {`ANNOTATE_TYPE(1, float)`, `1.0`}, 207 {`1:::float`, `1.0`}, 208 {`IF((true AND a < 0), (0 + a)::decimal, 2 / (1 - 1))`, `IF(a < 0, a::DECIMAL, 2 / 0)`}, 209 {`IF((true OR a < 0), (0 + a)::decimal, 2 / (1 - 1))`, `a::DECIMAL`}, 210 {`COALESCE(NULL, (NULL < 3), a = 2 - 1, d)`, `COALESCE(a = 1, d)`}, 211 {`COALESCE(NULL, a)`, `a`}, 212 {`NOT NULL`, `CAST(NULL AS BOOL)`}, 213 {`NOT d`, `NOT d`}, 214 {`NOT NOT d`, `d`}, 215 {`NOT NOT NOT d`, `NOT d`}, 216 {`NOT NOT NOT NOT d`, `d`}, 217 {`NULL IS NULL`, `true`}, 218 {`NULL IS NOT NULL`, `false`}, 219 {`1 IS NULL`, `false`}, 220 {`1 IS NOT NULL`, `true`}, 221 {`d IS NULL`, `d IS NULL`}, 222 {`d IS NOT NULL`, `d IS NOT NULL`}, 223 {`NULL IS TRUE`, `false`}, 224 {`NULL IS NOT TRUE`, `true`}, 225 {`false IS TRUE`, `false`}, 226 {`false IS NOT TRUE`, `true`}, 227 {`NULL IS FALSE`, `false`}, 228 {`NULL IS NOT FALSE`, `true`}, 229 {`false IS FALSE`, `true`}, 230 {`false IS NOT FALSE`, `false`}, 231 {`NULL IS DISTINCT FROM NULL`, `false`}, 232 {`1 IS NOT DISTINCT FROM NULL`, `false`}, 233 {`1 IS DISTINCT FROM NULL`, `true`}, 234 {`d IS NOT DISTINCT FROM NULL`, `d IS NOT DISTINCT FROM NULL`}, 235 {`d IS DISTINCT FROM NULL`, `d IS DISTINCT FROM NULL`}, 236 {`NULL IS NOT DISTINCT FROM TRUE`, `false`}, 237 {`NULL IS DISTINCT FROM TRUE`, `true`}, 238 {`false IS NOT DISTINCT FROM TRUE`, `false`}, 239 {`false IS DISTINCT FROM TRUE`, `true`}, 240 {`NULL IS NOT DISTINCT FROM FALSE`, `false`}, 241 {`NULL IS DISTINCT FROM FALSE`, `true`}, 242 {`false IS NOT DISTINCT FROM FALSE`, `true`}, 243 {`false IS DISTINCT FROM FALSE`, `false`}, 244 {`NULL IS NOT DISTINCT FROM 1`, `false`}, 245 {`NULL IS DISTINCT FROM 1`, `true`}, 246 {`NULL IS NOT DISTINCT FROM d`, `d IS NOT DISTINCT FROM NULL`}, 247 {`NULL IS DISTINCT FROM d`, `d IS DISTINCT FROM NULL`}, 248 // #15454: ensure that operators are pretty-printed correctly after normalization. 249 {`(random() + 1.0)::INT8`, `(random() + 1.0)::INT8`}, 250 {`('a' || left('b', random()::INT8)) COLLATE en`, `('a' || left('b', random()::INT8)) COLLATE en`}, 251 {`NULL COLLATE en`, `CAST(NULL AS STRING) COLLATE en`}, 252 {`(1.0 + random()) IS OF (INT8)`, `(1.0 + random()) IS OF (INT8)`}, 253 // #14687: ensure that negative divisors flip the inequality when rotating. 254 {`1 < a / -2`, `a < -2`}, 255 {`1 <= a / -2`, `a <= -2`}, 256 {`1 > a / -2`, `a > -2`}, 257 {`1 >= a / -2`, `a >= -2`}, 258 {`1 = a / -2`, `a = -2`}, 259 {`1 < a / 2`, `a > 2`}, 260 {`1 <= a / 2`, `a >= 2`}, 261 {`1 > a / 2`, `a < 2`}, 262 {`1 >= a / 2`, `a <= 2`}, 263 {`1 = a / 2`, `a = 2`}, 264 {`a - 1 < 9223372036854775807`, `(a - 1) < 9223372036854775807`}, 265 {`a - 1 < 9223372036854775806`, `a < 9223372036854775807`}, 266 {`-1 + a < 9223372036854775807`, `(-1 + a) < 9223372036854775807`}, 267 {`-1 + a < 9223372036854775806`, `a < 9223372036854775807`}, 268 {`j->'s' = '"jv"'::JSONB`, `j @> '{"s": "jv"}'`}, 269 {`'"jv"'::JSONB = j->'s'`, `j @> '{"s": "jv"}'`}, 270 {`j->'s' = jv`, `(j->'s') = jv`}, 271 {`j->s = jv`, `(j->s) = jv`}, 272 {`j->2 = '"jv"'::JSONB`, `(j->2) = '"jv"'`}, 273 // We want to check that constant-folded tuples preserve their 274 // labels. 275 {`(ROW (1) AS a)`, `((1,) AS a)`}, // DTuple 276 {`(ROW (a) AS a)`, `((a,) AS a)`}, // Tuple 277 } 278 279 ctx := context.Background() 280 semaCtx := tree.MakeSemaContext() 281 for _, d := range testData { 282 t.Run(d.expr, func(t *testing.T) { 283 expr, err := parser.ParseExpr(d.expr) 284 if err != nil { 285 t.Fatalf("%s: %v", d.expr, err) 286 } 287 typedExpr, err := expr.TypeCheck(ctx, &semaCtx, types.Any) 288 if err != nil { 289 t.Fatalf("%s: %v", d.expr, err) 290 } 291 rOrig := typedExpr.String() 292 ctx := tree.NewTestingEvalContext(cluster.MakeTestingClusterSettings()) 293 defer ctx.Mon.Stop(context.Background()) 294 r, err := ctx.NormalizeExpr(typedExpr) 295 if err != nil { 296 t.Fatalf("%s: %v", d.expr, err) 297 } 298 if s := r.String(); d.expected != s { 299 t.Errorf("%s: expected %s, but found %s", d.expr, d.expected, s) 300 } 301 // Normalizing again should be a no-op. 302 r2, err := ctx.NormalizeExpr(r) 303 if err != nil { 304 t.Fatalf("%s: %v", d.expr, err) 305 } 306 if s := r2.String(); d.expected != s { 307 t.Errorf("%s: expected %s, but found %s", d.expr, d.expected, s) 308 } 309 // The original expression should be unchanged. 310 if rStr := typedExpr.String(); rOrig != rStr { 311 t.Fatalf("Original expression `%s` changed to `%s`", rOrig, rStr) 312 } 313 }) 314 } 315 }