github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/norm/testdata/rules/bool (about) 1 exec-ddl 2 CREATE TABLE a (k INT PRIMARY KEY, i INT, f FLOAT, s STRING, j JSON) 3 ---- 4 5 exec-ddl 6 CREATE TABLE b (x INT PRIMARY KEY, z INT) 7 ---- 8 9 exec-ddl 10 CREATE TABLE c (a BOOL, b BOOL, c BOOL, d BOOL, e BOOL) 11 ---- 12 13 14 # -------------------------------------------------- 15 # NormalizeNestedAnds 16 # -------------------------------------------------- 17 18 norm expect=NormalizeNestedAnds 19 SELECT a AND (b AND (c AND (d AND e))) FROM c 20 ---- 21 project 22 ├── columns: "?column?":7 23 ├── scan c 24 │ └── columns: a:1 b:2 c:3 d:4 e:5 25 └── projections 26 └── (((a:1 AND b:2) AND c:3) AND d:4) AND e:5 [as="?column?":7, outer=(1-5)] 27 28 norm expect=NormalizeNestedAnds 29 SELECT (a AND b) AND (c AND (d OR e)) FROM c 30 ---- 31 project 32 ├── columns: "?column?":7 33 ├── scan c 34 │ └── columns: a:1 b:2 c:3 d:4 e:5 35 └── projections 36 └── ((a:1 AND b:2) AND c:3) AND (d:4 OR e:5) [as="?column?":7, outer=(1-5)] 37 38 # Already normalized. 39 norm expect-not=NormalizeNestedAnds 40 SELECT a AND b AND c FROM c 41 ---- 42 project 43 ├── columns: "?column?":7 44 ├── scan c 45 │ └── columns: a:1 b:2 c:3 46 └── projections 47 └── (a:1 AND b:2) AND c:3 [as="?column?":7, outer=(1-3)] 48 49 # -------------------------------------------------- 50 # SimplifyTrueAnd + SimplifyAndTrue 51 # -------------------------------------------------- 52 53 norm expect=SimplifyTrueAnd 54 SELECT true AND k=1 AS r FROM a 55 ---- 56 project 57 ├── columns: r:6!null 58 ├── scan a 59 │ ├── columns: k:1!null 60 │ └── key: (1) 61 └── projections 62 └── k:1 = 1 [as=r:6, outer=(1)] 63 64 norm expect=SimplifyAndTrue 65 SELECT k=1 AND true AS r FROM a 66 ---- 67 project 68 ├── columns: r:6!null 69 ├── scan a 70 │ ├── columns: k:1!null 71 │ └── key: (1) 72 └── projections 73 └── k:1 = 1 [as=r:6, outer=(1)] 74 75 norm expect=(SimplifyTrueAnd,SimplifyAndTrue) 76 SELECT true AND k=1 AND true AND i=2 AS r FROM a 77 ---- 78 project 79 ├── columns: r:6 80 ├── scan a 81 │ ├── columns: k:1!null i:2 82 │ ├── key: (1) 83 │ └── fd: (1)-->(2) 84 └── projections 85 └── (k:1 = 1) AND (i:2 = 2) [as=r:6, outer=(1,2)] 86 87 # No conditions left after rule. 88 norm expect=SimplifyTrueAnd 89 SELECT * FROM a WHERE true AND (true AND true) 90 ---- 91 scan a 92 ├── columns: k:1!null i:2 f:3 s:4 j:5 93 ├── key: (1) 94 └── fd: (1)-->(2-5) 95 96 # -------------------------------------------------- 97 # SimplifyFalseAnd + SimplifyAndFalse 98 # -------------------------------------------------- 99 100 norm expect=SimplifyFalseAnd 101 SELECT false AND s='foo' AS r FROM a 102 ---- 103 project 104 ├── columns: r:6!null 105 ├── fd: ()-->(6) 106 ├── scan a 107 └── projections 108 └── false [as=r:6] 109 110 norm expect=SimplifyAndFalse 111 SELECT s='foo' AND false AS r FROM a 112 ---- 113 project 114 ├── columns: r:6!null 115 ├── fd: ()-->(6) 116 ├── scan a 117 └── projections 118 └── false [as=r:6] 119 120 norm expect=(SimplifyAndFalse,SimplifyFalseAnd) 121 SELECT k=1 AND false AND (f=3.5 AND false) AS r FROM a 122 ---- 123 project 124 ├── columns: r:6!null 125 ├── fd: ()-->(6) 126 ├── scan a 127 └── projections 128 └── false [as=r:6] 129 130 # -------------------------------------------------- 131 # SimplifyTrueOr + SimplifyOrTrue 132 # -------------------------------------------------- 133 134 norm expect=SimplifyTrueOr 135 SELECT true OR s='foo' AS r FROM a 136 ---- 137 project 138 ├── columns: r:6!null 139 ├── fd: ()-->(6) 140 ├── scan a 141 └── projections 142 └── true [as=r:6] 143 144 norm expect=SimplifyOrTrue 145 SELECT s='foo' OR true AS r FROM a 146 ---- 147 project 148 ├── columns: r:6!null 149 ├── fd: ()-->(6) 150 ├── scan a 151 └── projections 152 └── true [as=r:6] 153 154 norm expect=(SimplifyTrueOr,SimplifyOrTrue) 155 SELECT k=1 OR true OR (true OR f=3.5) AS r FROM a 156 ---- 157 project 158 ├── columns: r:6!null 159 ├── fd: ()-->(6) 160 ├── scan a 161 └── projections 162 └── true [as=r:6] 163 164 # -------------------------------------------------- 165 # SimplifyFalseOr + SimplifyOrFalse 166 # -------------------------------------------------- 167 168 norm expect=SimplifyFalseOr 169 SELECT false OR k=1 AS r FROM a 170 ---- 171 project 172 ├── columns: r:6!null 173 ├── scan a 174 │ ├── columns: k:1!null 175 │ └── key: (1) 176 └── projections 177 └── k:1 = 1 [as=r:6, outer=(1)] 178 179 norm expect=SimplifyOrFalse 180 SELECT k=1 OR false AS r FROM a 181 ---- 182 project 183 ├── columns: r:6!null 184 ├── scan a 185 │ ├── columns: k:1!null 186 │ └── key: (1) 187 └── projections 188 └── k:1 = 1 [as=r:6, outer=(1)] 189 190 norm expect=(SimplifyFalseOr,SimplifyOrFalse) 191 SELECT (false OR k=1) OR (i=2 OR false) AS r FROM a 192 ---- 193 project 194 ├── columns: r:6 195 ├── scan a 196 │ ├── columns: k:1!null i:2 197 │ ├── key: (1) 198 │ └── fd: (1)-->(2) 199 └── projections 200 └── (k:1 = 1) OR (i:2 = 2) [as=r:6, outer=(1,2)] 201 202 # No conditions left after rule. 203 norm expect=SimplifyFalseOr 204 SELECT * FROM a WHERE false OR false OR false 205 ---- 206 values 207 ├── columns: k:1!null i:2!null f:3!null s:4!null j:5!null 208 ├── cardinality: [0 - 0] 209 ├── key: () 210 └── fd: ()-->(1-5) 211 212 # -------------------------------------------------- 213 # SimplifyAnd + SimplifyOr 214 # -------------------------------------------------- 215 norm expect=(SimplifyOrFalse,SimplifyFalseOr,SimplifyAndTrue) 216 SELECT (k=1 OR false) AND (false OR k=2 OR false) AND true AS r FROM a 217 ---- 218 project 219 ├── columns: r:6!null 220 ├── scan a 221 │ ├── columns: k:1!null 222 │ └── key: (1) 223 └── projections 224 └── (k:1 = 1) AND (k:1 = 2) [as=r:6, outer=(1)] 225 226 # -------------------------------------------------- 227 # SimplifyRange 228 # -------------------------------------------------- 229 230 norm expect=SimplifyRange disable=InlineConstVar 231 SELECT * FROM a WHERE k = 1 AND k = 2-1 232 ---- 233 select 234 ├── columns: k:1!null i:2 f:3 s:4 j:5 235 ├── cardinality: [0 - 1] 236 ├── key: () 237 ├── fd: ()-->(1-5) 238 ├── scan a 239 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 240 │ ├── key: (1) 241 │ └── fd: (1)-->(2-5) 242 └── filters 243 └── k:1 = 1 [outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)] 244 245 # -------------------------------------------------- 246 # FoldNullAndOr 247 # -------------------------------------------------- 248 norm expect=FoldNullAndOr 249 SELECT null and null AS r FROM a 250 ---- 251 project 252 ├── columns: r:6 253 ├── fd: ()-->(6) 254 ├── scan a 255 └── projections 256 └── CAST(NULL AS BOOL) [as=r:6] 257 258 norm expect=FoldNullAndOr 259 SELECT null or null AS r FROM a 260 ---- 261 project 262 ├── columns: r:6 263 ├── fd: ()-->(6) 264 ├── scan a 265 └── projections 266 └── CAST(NULL AS BOOL) [as=r:6] 267 268 norm expect=FoldNullAndOr 269 SELECT null or (null and null and null) or null AS r FROM a 270 ---- 271 project 272 ├── columns: r:6 273 ├── fd: ()-->(6) 274 ├── scan a 275 └── projections 276 └── CAST(NULL AS BOOL) [as=r:6] 277 278 # Don't fold. 279 norm expect-not=FoldNullAndOr 280 SELECT (null or k=1) AS r, (null and k=1) AS s FROM a 281 ---- 282 project 283 ├── columns: r:6 s:7 284 ├── scan a 285 │ ├── columns: k:1!null 286 │ └── key: (1) 287 └── projections 288 ├── NULL OR (k:1 = 1) [as=r:6, outer=(1)] 289 └── NULL AND (k:1 = 1) [as=s:7, outer=(1)] 290 291 # -------------------------------------------------- 292 # FoldNotTrue + FoldNotFalse + FoldNotNull 293 # -------------------------------------------------- 294 295 norm expect=(FoldNotTrue,FoldNotFalse,FoldNotNull) 296 SELECT NOT(1=1), NOT(1=2), NOT(NULL) 297 ---- 298 values 299 ├── columns: "?column?":1!null "?column?":2!null "?column?":3 300 ├── cardinality: [1 - 1] 301 ├── key: () 302 ├── fd: ()-->(1-3) 303 └── (false, true, NULL) 304 305 # -------------------------------------------------- 306 # NegateComparison 307 # -------------------------------------------------- 308 309 # Equality and inequality comparisons. 310 norm expect=NegateComparison 311 SELECT * FROM a WHERE NOT(i=1) AND NOT(f<>i) AND NOT(i>k) AND NOT(i>=f) AND NOT(f<1) AND NOT(i<=1) 312 ---- 313 select 314 ├── columns: k:1!null i:2!null f:3!null s:4 j:5 315 ├── key: (1) 316 ├── fd: (1)-->(2-5), (2)==(3), (3)==(2) 317 ├── scan a 318 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 319 │ ├── key: (1) 320 │ └── fd: (1)-->(2-5) 321 └── filters 322 ├── (i:2 != 1) AND (i:2 > 1) [outer=(2), constraints=(/2: [/2 - ]; tight)] 323 ├── f:3 = i:2 [outer=(2,3), constraints=(/2: (/NULL - ]; /3: (/NULL - ]), fd=(2)==(3), (3)==(2)] 324 ├── i:2 <= k:1 [outer=(1,2), constraints=(/1: (/NULL - ]; /2: (/NULL - ])] 325 ├── i:2 < f:3 [outer=(2,3), constraints=(/2: (/NULL - ]; /3: (/NULL - ])] 326 └── f:3 >= 1.0 [outer=(3), constraints=(/3: [/1.0 - ]; tight)] 327 328 # IN and IS comparisons. 329 norm expect=NegateComparison 330 SELECT * 331 FROM a 332 WHERE NOT(i IN (1,2)) AND NOT(f NOT IN (3,4)) AND NOT(f IS NULL) AND NOT(s IS NOT NULL) 333 ---- 334 select 335 ├── columns: k:1!null i:2 f:3!null s:4 j:5 336 ├── key: (1) 337 ├── fd: ()-->(4), (1)-->(2,3,5) 338 ├── scan a 339 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 340 │ ├── key: (1) 341 │ └── fd: (1)-->(2-5) 342 └── filters 343 ├── (f:3 IN (3.0, 4.0)) AND (f:3 IS NOT NULL) [outer=(3), constraints=(/3: [/3.0 - /3.0] [/4.0 - /4.0]; tight)] 344 ├── i:2 NOT IN (1, 2) [outer=(2)] 345 └── s:4 IS NULL [outer=(4), constraints=(/4: [/NULL - /NULL]; tight), fd=()-->(4)] 346 347 # Like comparisons. 348 norm expect=NegateComparison 349 SELECT * 350 FROM a 351 WHERE NOT(s LIKE 'foo') AND NOT(s NOT LIKE 'foo') AND NOT(s ILIKE 'foo') AND NOT(s NOT ILIKE 'foo') 352 ---- 353 select 354 ├── columns: k:1!null i:2 f:3 s:4!null j:5 355 ├── key: (1) 356 ├── fd: ()-->(4), (1)-->(2,3,5) 357 ├── scan a 358 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 359 │ ├── key: (1) 360 │ └── fd: (1)-->(2-5) 361 └── filters 362 ├── s:4 NOT LIKE 'foo' [outer=(4), constraints=(/4: (/NULL - ])] 363 ├── s:4 LIKE 'foo' [outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)] 364 ├── s:4 NOT ILIKE 'foo' [outer=(4), constraints=(/4: (/NULL - ])] 365 └── s:4 ILIKE 'foo' [outer=(4), constraints=(/4: (/NULL - ])] 366 367 # SimilarTo comparisons. 368 norm expect=NegateComparison 369 SELECT * FROM a WHERE NOT(s SIMILAR TO 'foo') AND NOT(s NOT SIMILAR TO 'foo') 370 ---- 371 select 372 ├── columns: k:1!null i:2 f:3 s:4!null j:5 373 ├── key: (1) 374 ├── fd: ()-->(4), (1)-->(2,3,5) 375 ├── scan a 376 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 377 │ ├── key: (1) 378 │ └── fd: (1)-->(2-5) 379 └── filters 380 ├── s:4 NOT SIMILAR TO 'foo' [outer=(4), constraints=(/4: (/NULL - ])] 381 └── s:4 SIMILAR TO 'foo' [outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)] 382 383 # RegMatch comparisons. 384 norm expect=NegateComparison 385 SELECT * FROM a WHERE NOT(s ~ 'foo') AND NOT(s !~ 'foo') AND NOT(s ~* 'foo') AND NOT (s !~* 'foo') 386 ---- 387 select 388 ├── columns: k:1!null i:2 f:3 s:4!null j:5 389 ├── key: (1) 390 ├── fd: (1)-->(2-5) 391 ├── scan a 392 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 393 │ ├── key: (1) 394 │ └── fd: (1)-->(2-5) 395 └── filters 396 ├── s:4 !~ 'foo' [outer=(4), constraints=(/4: (/NULL - ])] 397 ├── s:4 ~ 'foo' [outer=(4), constraints=(/4: (/NULL - ])] 398 ├── s:4 !~* 'foo' [outer=(4), constraints=(/4: (/NULL - ])] 399 └── s:4 ~* 'foo' [outer=(4), constraints=(/4: (/NULL - ])] 400 401 norm expect-not=NegateComparison 402 SELECT * FROM a WHERE 403 NOT('[1, 2]' @> j) AND NOT(j <@ '[3, 4]') AND 404 NOT(j ? 'foo') AND 405 NOT(j ?| ARRAY['foo']) AND 406 NOT(j ?& ARRAY['foo']) AND 407 NOT(ARRAY[i] && ARRAY[1]) 408 ---- 409 select 410 ├── columns: k:1!null i:2 f:3 s:4 j:5 411 ├── key: (1) 412 ├── fd: (1)-->(2-5) 413 ├── scan a 414 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 415 │ ├── key: (1) 416 │ └── fd: (1)-->(2-5) 417 └── filters 418 ├── NOT ('[1, 2]' @> j:5) [outer=(5)] 419 ├── NOT ('[3, 4]' @> j:5) [outer=(5)] 420 ├── NOT (j:5 ? 'foo') [outer=(5)] 421 ├── NOT (j:5 ?| ARRAY['foo']) [outer=(5)] 422 ├── NOT (j:5 ?& ARRAY['foo']) [outer=(5)] 423 └── NOT (ARRAY[i:2] && ARRAY[1]) [outer=(2)] 424 425 # -------------------------------------------------- 426 # EliminateNot 427 # -------------------------------------------------- 428 norm expect=EliminateNot 429 SELECT * FROM c WHERE NOT(NOT(a)) 430 ---- 431 select 432 ├── columns: a:1!null b:2 c:3 d:4 e:5 433 ├── fd: ()-->(1) 434 ├── scan c 435 │ └── columns: a:1 b:2 c:3 d:4 e:5 436 └── filters 437 └── a:1 [outer=(1), constraints=(/1: [/true - /true]; tight), fd=()-->(1)] 438 439 # -------------------------------------------------- 440 # NegateAnd + NegateComparison 441 # -------------------------------------------------- 442 norm expect=(NegateAnd,NegateComparison) 443 SELECT * FROM a WHERE NOT (k >= i AND i < f) 444 ---- 445 select 446 ├── columns: k:1!null i:2!null f:3 s:4 j:5 447 ├── key: (1) 448 ├── fd: (1)-->(2-5) 449 ├── scan a 450 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 451 │ ├── key: (1) 452 │ └── fd: (1)-->(2-5) 453 └── filters 454 └── (k:1 < i:2) OR (i:2 >= f:3) [outer=(1-3), constraints=(/2: (/NULL - ])] 455 456 norm expect=(NegateAnd,NegateComparison) 457 SELECT * FROM a WHERE NOT (k >= i AND i < f AND (i > 5 AND i < 10 AND f > 1)) 458 ---- 459 select 460 ├── columns: k:1!null i:2 f:3 s:4 j:5 461 ├── key: (1) 462 ├── fd: (1)-->(2-5) 463 ├── scan a 464 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 465 │ ├── key: (1) 466 │ └── fd: (1)-->(2-5) 467 └── filters 468 └── ((((k:1 < i:2) OR (i:2 >= f:3)) OR (i:2 <= 5)) OR (i:2 >= 10)) OR (f:3 <= 1.0) [outer=(1-3)] 469 470 471 # -------------------------------------------------- 472 # NegateOr + NegateComparison 473 # -------------------------------------------------- 474 norm expect=(NegateOr,NegateComparison) 475 SELECT * FROM a WHERE NOT (k >= i OR i < f OR k + i < f) 476 ---- 477 select 478 ├── columns: k:1!null i:2!null f:3!null s:4 j:5 479 ├── key: (1) 480 ├── fd: (1)-->(2-5) 481 ├── scan a 482 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 483 │ ├── key: (1) 484 │ └── fd: (1)-->(2-5) 485 └── filters 486 ├── k:1 < i:2 [outer=(1,2), constraints=(/1: (/NULL - ]; /2: (/NULL - ])] 487 ├── i:2 >= f:3 [outer=(2,3), constraints=(/2: (/NULL - ]; /3: (/NULL - ])] 488 └── f:3 <= (k:1 + i:2) [outer=(1-3), constraints=(/3: (/NULL - ])] 489 490 norm expect=(NegateOr,NegateComparison) 491 SELECT * FROM a WHERE NOT (k >= i OR i < f OR (i > 10 OR i < 5 OR f > 1)) 492 ---- 493 select 494 ├── columns: k:1!null i:2!null f:3!null s:4 j:5 495 ├── key: (1) 496 ├── fd: (1)-->(2-5) 497 ├── scan a 498 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 499 │ ├── key: (1) 500 │ └── fd: (1)-->(2-5) 501 └── filters 502 ├── (i:2 <= 10) AND (i:2 >= 5) [outer=(2), constraints=(/2: [/5 - /10]; tight)] 503 ├── k:1 < i:2 [outer=(1,2), constraints=(/1: (/NULL - ]; /2: (/NULL - ])] 504 ├── i:2 >= f:3 [outer=(2,3), constraints=(/2: (/NULL - ]; /3: (/NULL - ])] 505 └── f:3 <= 1.0 [outer=(3), constraints=(/3: (/NULL - /1.0]; tight)] 506 507 # -------------------------------------------------- 508 # NegateAnd + NegateOr + NegateComparison 509 # -------------------------------------------------- 510 norm expect=(NegateAnd,NegateOr,NegateComparison) 511 SELECT * FROM a WHERE NOT ((k >= i OR i < f) AND (i > 5 OR f > 1)) 512 ---- 513 select 514 ├── columns: k:1!null i:2!null f:3!null s:4 j:5 515 ├── key: (1) 516 ├── fd: (1)-->(2-5) 517 ├── scan a 518 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 519 │ ├── key: (1) 520 │ └── fd: (1)-->(2-5) 521 └── filters 522 └── ((k:1 < i:2) AND (i:2 >= f:3)) OR ((i:2 <= 5) AND (f:3 <= 1.0)) [outer=(1-3), constraints=(/2: (/NULL - ]; /3: (/NULL - ])] 523 524 norm expect=(NegateAnd,NegateOr,NegateComparison) 525 SELECT * FROM a WHERE NOT ((k >= i AND i < f) OR (i > 5 AND f > 1)) 526 ---- 527 select 528 ├── columns: k:1!null i:2!null f:3 s:4 j:5 529 ├── key: (1) 530 ├── fd: (1)-->(2-5) 531 ├── scan a 532 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 533 │ ├── key: (1) 534 │ └── fd: (1)-->(2-5) 535 └── filters 536 ├── (k:1 < i:2) OR (i:2 >= f:3) [outer=(1-3), constraints=(/2: (/NULL - ])] 537 └── (i:2 <= 5) OR (f:3 <= 1.0) [outer=(2,3)] 538 539 # -------------------------------------------------- 540 # ExtractRedundantConjunct 541 # -------------------------------------------------- 542 norm expect=(ExtractRedundantConjunct) 543 SELECT b OR b FROM c 544 ---- 545 project 546 ├── columns: "?column?":7 547 ├── scan c 548 │ └── columns: b:2 549 └── projections 550 └── b:2 [as="?column?":7, outer=(2)] 551 552 norm expect=(ExtractRedundantConjunct) 553 SELECT a OR (a AND b) OR (a AND c) FROM c 554 ---- 555 project 556 ├── columns: "?column?":7 557 ├── scan c 558 │ └── columns: a:1 559 └── projections 560 └── a:1 [as="?column?":7, outer=(1)] 561 562 norm expect=(ExtractRedundantConjunct) 563 SELECT (a AND b) OR a OR (a AND c) FROM c 564 ---- 565 project 566 ├── columns: "?column?":7 567 ├── scan c 568 │ └── columns: a:1 569 └── projections 570 └── a:1 [as="?column?":7, outer=(1)] 571 572 norm expect=(ExtractRedundantConjunct) 573 SELECT (a AND b) OR (b AND a) FROM c 574 ---- 575 project 576 ├── columns: "?column?":7 577 ├── scan c 578 │ └── columns: a:1 b:2 579 └── projections 580 └── b:2 AND a:1 [as="?column?":7, outer=(1,2)] 581 582 norm expect=(ExtractRedundantConjunct) 583 SELECT (a AND b) OR (c AND a) FROM c 584 ---- 585 project 586 ├── columns: "?column?":7 587 ├── scan c 588 │ └── columns: a:1 b:2 c:3 589 └── projections 590 └── a:1 AND (b:2 OR c:3) [as="?column?":7, outer=(1-3)] 591 592 norm expect=(ExtractRedundantConjunct) 593 SELECT * FROM c WHERE (a AND b) OR (a AND b AND c) OR (b AND a) 594 ---- 595 select 596 ├── columns: a:1!null b:2!null c:3 d:4 e:5 597 ├── fd: ()-->(1,2) 598 ├── scan c 599 │ └── columns: a:1 b:2 c:3 d:4 e:5 600 └── filters 601 ├── a:1 [outer=(1), constraints=(/1: [/true - /true]; tight), fd=()-->(1)] 602 └── b:2 [outer=(2), constraints=(/2: [/true - /true]; tight), fd=()-->(2)] 603 604 norm expect=(ExtractRedundantConjunct) 605 SELECT * FROM c WHERE (b AND (a AND c)) OR (d AND (e AND a)) 606 ---- 607 select 608 ├── columns: a:1!null b:2 c:3 d:4 e:5 609 ├── fd: ()-->(1) 610 ├── scan c 611 │ └── columns: a:1 b:2 c:3 d:4 e:5 612 └── filters 613 ├── a:1 [outer=(1), constraints=(/1: [/true - /true]; tight), fd=()-->(1)] 614 └── (b:2 AND c:3) OR (d:4 AND e:5) [outer=(2-5)] 615 616 norm expect=(ExtractRedundantConjunct) 617 SELECT * FROM c WHERE (b AND a) OR (c AND (a AND e) OR (e AND a AND d)) 618 ---- 619 select 620 ├── columns: a:1!null b:2 c:3 d:4 e:5 621 ├── fd: ()-->(1) 622 ├── scan c 623 │ └── columns: a:1 b:2 c:3 d:4 e:5 624 └── filters 625 ├── a:1 [outer=(1), constraints=(/1: [/true - /true]; tight), fd=()-->(1)] 626 └── b:2 OR (e:5 AND (c:3 OR d:4)) [outer=(2-5)] 627 628 norm expect=(ExtractRedundantConjunct) 629 SELECT * FROM a WHERE ((k > 5) AND (i < 2) AND (i > 0)) OR ((k > 5) AND (i < 2) AND (s = 'foo')) 630 ---- 631 select 632 ├── columns: k:1!null i:2!null f:3 s:4 j:5 633 ├── key: (1) 634 ├── fd: (1)-->(2-5) 635 ├── scan a 636 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 637 │ ├── key: (1) 638 │ └── fd: (1)-->(2-5) 639 └── filters 640 ├── i:2 < 2 [outer=(2), constraints=(/2: (/NULL - /1]; tight)] 641 ├── k:1 > 5 [outer=(1), constraints=(/1: [/6 - ]; tight)] 642 └── (i:2 > 0) OR (s:4 = 'foo') [outer=(2,4)] 643 644 norm expect=(ExtractRedundantConjunct) 645 SELECT * FROM a WHERE (k > 5) OR ((k > 5) AND (i < 2) AND (s = 'foo')) 646 ---- 647 select 648 ├── columns: k:1!null i:2 f:3 s:4 j:5 649 ├── key: (1) 650 ├── fd: (1)-->(2-5) 651 ├── scan a 652 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 653 │ ├── key: (1) 654 │ └── fd: (1)-->(2-5) 655 └── filters 656 └── k:1 > 5 [outer=(1), constraints=(/1: [/6 - ]; tight)] 657 658 # Works with nulls too. 659 norm expect=(ExtractRedundantConjunct) 660 SELECT null or (null and k=1) AS r FROM a 661 ---- 662 project 663 ├── columns: r:6 664 ├── fd: ()-->(6) 665 ├── scan a 666 └── projections 667 └── NULL [as=r:6] 668 669 norm expect=(ExtractRedundantConjunct) 670 SELECT (null and k=2) or (null and k=1) AS r FROM a 671 ---- 672 project 673 ├── columns: r:6 674 ├── scan a 675 │ ├── columns: k:1!null 676 │ └── key: (1) 677 └── projections 678 └── NULL AND ((k:1 = 2) OR (k:1 = 1)) [as=r:6, outer=(1)] 679 680 # Check that we don't match non-redundant cases. 681 norm expect-not=(ExtractRedundantConjunct) 682 SELECT a OR b OR b FROM c 683 ---- 684 project 685 ├── columns: "?column?":7 686 ├── scan c 687 │ └── columns: a:1 b:2 688 └── projections 689 └── (a:1 OR b:2) OR b:2 [as="?column?":7, outer=(1,2)] 690 691 norm expect-not=(ExtractRedundantConjunct) 692 SELECT (a AND b) OR (a OR c) FROM c 693 ---- 694 project 695 ├── columns: "?column?":7 696 ├── scan c 697 │ └── columns: a:1 b:2 c:3 698 └── projections 699 └── (a:1 AND b:2) OR (a:1 OR c:3) [as="?column?":7, outer=(1-3)] 700 701 norm expect-not=(ExtractRedundantConjunct) 702 SELECT (a AND b) OR (NOT a AND c) FROM c 703 ---- 704 project 705 ├── columns: "?column?":7 706 ├── scan c 707 │ └── columns: a:1 b:2 c:3 708 └── projections 709 └── (a:1 AND b:2) OR ((NOT a:1) AND c:3) [as="?column?":7, outer=(1-3)]