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

     1  exec-ddl
     2  CREATE TABLE a (x INT PRIMARY KEY, y INT, s STRING)
     3  ----
     4  
     5  exec-ddl
     6  ALTER TABLE a INJECT STATISTICS '[
     7    {
     8      "columns": ["x"],
     9      "created_at": "2018-01-01 1:00:00.00000+00:00",
    10      "row_count": 5000,
    11      "distinct_count": 5000
    12    },
    13    {
    14      "columns": ["y"],
    15      "created_at": "2018-01-01 1:30:00.00000+00:00",
    16      "row_count": 5000,
    17      "distinct_count": 400
    18    },
    19    {
    20      "columns": ["s"],
    21      "created_at": "2018-01-01 1:30:00.00000+00:00",
    22      "row_count": 5000,
    23      "distinct_count": 10
    24    },
    25    {
    26      "columns": ["y","s"],
    27      "created_at": "2018-01-01 1:30:00.00000+00:00",
    28      "row_count": 5000,
    29      "distinct_count": 1000
    30    }
    31  ]'
    32  ----
    33  
    34  build colstat=4 colstat=5 colstat=6 disable=InlineWith
    35  WITH foo AS (SELECT * FROM a) SELECT * FROM foo
    36  ----
    37  with &1 (foo)
    38   ├── columns: x:4(int!null) y:5(int) s:6(string)
    39   ├── stats: [rows=5000]
    40   ├── key: (4)
    41   ├── fd: (4)-->(5,6)
    42   ├── scan a
    43   │    ├── columns: a.x:1(int!null) a.y:2(int) a.s:3(string)
    44   │    ├── stats: [rows=5000]
    45   │    ├── key: (1)
    46   │    └── fd: (1)-->(2,3)
    47   └── with-scan &1 (foo)
    48        ├── columns: x:4(int!null) y:5(int) s:6(string)
    49        ├── mapping:
    50        │    ├──  a.x:1(int) => x:4(int)
    51        │    ├──  a.y:2(int) => y:5(int)
    52        │    └──  a.s:3(string) => s:6(string)
    53        ├── stats: [rows=5000, distinct(4)=5000, null(4)=0, distinct(5)=500, null(5)=50, distinct(6)=500, null(6)=50]
    54        ├── key: (4)
    55        └── fd: (4)-->(5,6)
    56  
    57  # Regression test for #40296.
    58  opt disable=InlineWith
    59  WITH
    60    t0 AS ((VALUES (0, 0:::OID, NULL, '')) UNION (VALUES (NULL, 0:::OID,'1970-09-08'::DATE, NULL)))
    61  SELECT
    62    NULL
    63  FROM
    64    a, t0
    65  WHERE
    66    EXISTS(
    67      WITH
    68        t1 AS (SELECT NULL)
    69      SELECT
    70        t0.column2, a.y
    71    );
    72  ----
    73  with &1 (t0)
    74   ├── columns: "?column?":27(unknown)
    75   ├── stats: [rows=10000]
    76   ├── fd: ()-->(27)
    77   ├── union
    78   │    ├── columns: column1:10(int) column2:11(oid!null) column3:12(date) column4:13(string)
    79   │    ├── left columns: column1:1(int) column2:2(oid) column3:9(date) column4:4(string)
    80   │    ├── right columns: column1:5(int) column2:6(oid) column3:7(date) column4:8(string)
    81   │    ├── cardinality: [1 - 2]
    82   │    ├── stats: [rows=2, distinct(10-13)=2, null(10-13)=0]
    83   │    ├── key: (10-13)
    84   │    ├── values
    85   │    │    ├── columns: column1:1(int!null) column2:2(oid!null) column4:4(string!null) column3:9(date)
    86   │    │    ├── cardinality: [1 - 1]
    87   │    │    ├── stats: [rows=1, distinct(1,2,4,9)=1, null(1,2,4,9)=0]
    88   │    │    ├── key: ()
    89   │    │    ├── fd: ()-->(1,2,4,9)
    90   │    │    └── (0, 0, '', NULL) [type=tuple{int, oid, string, date}]
    91   │    └── values
    92   │         ├── columns: column1:5(int) column2:6(oid!null) column3:7(date!null) column4:8(string)
    93   │         ├── cardinality: [1 - 1]
    94   │         ├── stats: [rows=1, distinct(5-8)=1, null(5-8)=0]
    95   │         ├── key: ()
    96   │         ├── fd: ()-->(5-8)
    97   │         └── (NULL, 0, '1970-09-08', NULL) [type=tuple{int, oid, date, string}]
    98   └── with &2 (t1)
    99        ├── columns: "?column?":27(unknown)
   100        ├── stats: [rows=10000]
   101        ├── fd: ()-->(27)
   102        ├── values
   103        │    ├── columns: "?column?":21(unknown)
   104        │    ├── cardinality: [1 - 1]
   105        │    ├── stats: [rows=1]
   106        │    ├── key: ()
   107        │    ├── fd: ()-->(21)
   108        │    └── (NULL,) [type=tuple{unknown}]
   109        └── project
   110             ├── columns: "?column?":27(unknown)
   111             ├── stats: [rows=10000]
   112             ├── fd: ()-->(27)
   113             ├── inner-join (cross)
   114             │    ├── columns: true_agg:25(bool!null)
   115             │    ├── stats: [rows=10000]
   116             │    ├── fd: ()-->(25)
   117             │    ├── scan a
   118             │    │    └── stats: [rows=5000]
   119             │    ├── inner-join (cross)
   120             │    │    ├── columns: true_agg:25(bool!null)
   121             │    │    ├── cardinality: [0 - 2]
   122             │    │    ├── stats: [rows=2]
   123             │    │    ├── fd: ()-->(25)
   124             │    │    ├── with-scan &1 (t0)
   125             │    │    │    ├── mapping:
   126             │    │    │    ├── cardinality: [1 - 2]
   127             │    │    │    └── stats: [rows=2]
   128             │    │    ├── select
   129             │    │    │    ├── columns: true_agg:25(bool!null)
   130             │    │    │    ├── cardinality: [0 - 1]
   131             │    │    │    ├── stats: [rows=1, distinct(25)=1, null(25)=0]
   132             │    │    │    ├── key: ()
   133             │    │    │    ├── fd: ()-->(25)
   134             │    │    │    ├── scalar-group-by
   135             │    │    │    │    ├── columns: true_agg:25(bool)
   136             │    │    │    │    ├── cardinality: [1 - 1]
   137             │    │    │    │    ├── stats: [rows=1, distinct(25)=1, null(25)=0]
   138             │    │    │    │    ├── key: ()
   139             │    │    │    │    ├── fd: ()-->(25)
   140             │    │    │    │    ├── values
   141             │    │    │    │    │    ├── columns: true:24(bool!null)
   142             │    │    │    │    │    ├── cardinality: [1 - 1]
   143             │    │    │    │    │    ├── stats: [rows=1]
   144             │    │    │    │    │    ├── key: ()
   145             │    │    │    │    │    ├── fd: ()-->(24)
   146             │    │    │    │    │    └── (true,) [type=tuple{bool}]
   147             │    │    │    │    └── aggregations
   148             │    │    │    │         └── const-agg [as=true_agg:25, type=bool, outer=(24)]
   149             │    │    │    │              └── true:24 [type=bool]
   150             │    │    │    └── filters
   151             │    │    │         └── true_agg:25 IS NOT NULL [type=bool, outer=(25), constraints=(/25: (/NULL - ]; tight)]
   152             │    │    └── filters (true)
   153             │    └── filters (true)
   154             └── projections
   155                  └── NULL [as="?column?":27, type=unknown]