github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/logprops/constraints-null (about)

     1  exec-ddl
     2  CREATE TABLE t (a INT, b BOOL, c STRING)
     3  ----
     4  
     5  opt
     6  SELECT * FROM t WHERE a = NULL
     7  ----
     8  values
     9   ├── columns: a:1(int!null) b:2(bool!null) c:3(string!null)
    10   ├── cardinality: [0 - 0]
    11   ├── key: ()
    12   ├── fd: ()-->(1-3)
    13   └── prune: (1-3)
    14  
    15  opt
    16  SELECT * FROM t WHERE a < NULL
    17  ----
    18  values
    19   ├── columns: a:1(int!null) b:2(bool!null) c:3(string!null)
    20   ├── cardinality: [0 - 0]
    21   ├── key: ()
    22   ├── fd: ()-->(1-3)
    23   └── prune: (1-3)
    24  
    25  opt
    26  SELECT * FROM t WHERE a IS NULL
    27  ----
    28  select
    29   ├── columns: a:1(int) b:2(bool) c:3(string)
    30   ├── fd: ()-->(1)
    31   ├── prune: (2,3)
    32   ├── scan t
    33   │    ├── columns: a:1(int) b:2(bool) c:3(string)
    34   │    └── prune: (1-3)
    35   └── filters
    36        └── is [type=bool, outer=(1), constraints=(/1: [/NULL - /NULL]; tight), fd=()-->(1)]
    37             ├── variable: a:1 [type=int]
    38             └── null [type=unknown]
    39  
    40  opt
    41  SELECT * FROM t WHERE a IS NOT NULL
    42  ----
    43  select
    44   ├── columns: a:1(int!null) b:2(bool) c:3(string)
    45   ├── prune: (2,3)
    46   ├── scan t
    47   │    ├── columns: a:1(int) b:2(bool) c:3(string)
    48   │    └── prune: (1-3)
    49   └── filters
    50        └── is-not [type=bool, outer=(1), constraints=(/1: (/NULL - ]; tight)]
    51             ├── variable: a:1 [type=int]
    52             └── null [type=unknown]
    53  
    54  opt
    55  SELECT * FROM t WHERE b IS NULL AND c IS NULL
    56  ----
    57  select
    58   ├── columns: a:1(int) b:2(bool) c:3(string)
    59   ├── fd: ()-->(2,3)
    60   ├── prune: (1)
    61   ├── scan t
    62   │    ├── columns: a:1(int) b:2(bool) c:3(string)
    63   │    └── prune: (1-3)
    64   └── filters
    65        ├── is [type=bool, outer=(2), constraints=(/2: [/NULL - /NULL]; tight), fd=()-->(2)]
    66        │    ├── variable: b:2 [type=bool]
    67        │    └── null [type=unknown]
    68        └── is [type=bool, outer=(3), constraints=(/3: [/NULL - /NULL]; tight), fd=()-->(3)]
    69             ├── variable: c:3 [type=string]
    70             └── null [type=unknown]
    71  
    72  opt
    73  SELECT * FROM t WHERE b IS NOT NULL AND c IS NOT NULL
    74  ----
    75  select
    76   ├── columns: a:1(int) b:2(bool!null) c:3(string!null)
    77   ├── prune: (1)
    78   ├── scan t
    79   │    ├── columns: a:1(int) b:2(bool) c:3(string)
    80   │    └── prune: (1-3)
    81   └── filters
    82        ├── is-not [type=bool, outer=(2), constraints=(/2: (/NULL - ]; tight)]
    83        │    ├── variable: b:2 [type=bool]
    84        │    └── null [type=unknown]
    85        └── is-not [type=bool, outer=(3), constraints=(/3: (/NULL - ]; tight)]
    86             ├── variable: c:3 [type=string]
    87             └── null [type=unknown]
    88  
    89  exec-ddl
    90  CREATE TABLE xy (
    91    x INT,
    92    y INT
    93  )
    94  ----
    95  
    96  # Test that we get a not-NULL constraint on x.
    97  opt
    98  SELECT * FROM xy WHERE x > abs(y)
    99  ----
   100  select
   101   ├── columns: x:1(int!null) y:2(int)
   102   ├── immutable
   103   ├── scan xy
   104   │    ├── columns: x:1(int) y:2(int)
   105   │    └── prune: (1,2)
   106   └── filters
   107        └── gt [type=bool, outer=(1,2), immutable, constraints=(/1: (/NULL - ])]
   108             ├── variable: x:1 [type=int]
   109             └── function: abs [type=int]
   110                  └── variable: y:2 [type=int]
   111  
   112  # Test that we get a not-NULL constraint on x.
   113  opt
   114  SELECT * FROM xy WHERE sin(x::float)::int < x
   115  ----
   116  select
   117   ├── columns: x:1(int!null) y:2(int)
   118   ├── immutable
   119   ├── prune: (2)
   120   ├── scan xy
   121   │    ├── columns: x:1(int) y:2(int)
   122   │    └── prune: (1,2)
   123   └── filters
   124        └── gt [type=bool, outer=(1), immutable, constraints=(/1: (/NULL - ])]
   125             ├── variable: x:1 [type=int]
   126             └── cast: INT8 [type=int]
   127                  └── function: sin [type=float]
   128                       └── cast: FLOAT8 [type=float]
   129                            └── variable: x:1 [type=int]
   130  
   131  # Test that we get a not-NULL constraint on x and y.
   132  opt
   133  SELECT * FROM xy WHERE x > y
   134  ----
   135  select
   136   ├── columns: x:1(int!null) y:2(int!null)
   137   ├── scan xy
   138   │    ├── columns: x:1(int) y:2(int)
   139   │    └── prune: (1,2)
   140   └── filters
   141        └── gt [type=bool, outer=(1,2), constraints=(/1: (/NULL - ]; /2: (/NULL - ])]
   142             ├── variable: x:1 [type=int]
   143             └── variable: y:2 [type=int]
   144  
   145  # Test that we get a not-NULL constraint on x and y.
   146  opt
   147  SELECT * FROM xy WHERE x = y
   148  ----
   149  select
   150   ├── columns: x:1(int!null) y:2(int!null)
   151   ├── fd: (1)==(2), (2)==(1)
   152   ├── scan xy
   153   │    ├── columns: x:1(int) y:2(int)
   154   │    └── prune: (1,2)
   155   └── filters
   156        └── eq [type=bool, outer=(1,2), constraints=(/1: (/NULL - ]; /2: (/NULL - ]), fd=(1)==(2), (2)==(1)]
   157             ├── variable: x:1 [type=int]
   158             └── variable: y:2 [type=int]