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

     1  exec-ddl
     2  CREATE TABLE abc (
     3      a INT NOT NULL,
     4      b TEXT DEFAULT ('foo'),
     5      c FLOAT AS (a::float) STORED
     6  )
     7  ----
     8  
     9  exec-ddl
    10  ALTER TABLE abc INJECT STATISTICS '[
    11    {
    12      "columns": ["a"],
    13      "created_at": "2018-01-01 1:00:00.00000+00:00",
    14      "row_count": 2000,
    15      "distinct_count": 2000
    16    },
    17    {
    18      "columns": ["b"],
    19      "created_at": "2018-01-01 1:30:00.00000+00:00",
    20      "row_count": 2000,
    21      "distinct_count": 10
    22    }
    23  ]'
    24  ----
    25  
    26  exec-ddl
    27  CREATE TABLE xyz (
    28      x TEXT PRIMARY KEY,
    29      y INT8 NOT NULL,
    30      z FLOAT8
    31  )
    32  ----
    33  
    34  # Statistics should be derived from INSERT input columns and transferred to
    35  # RETURNING columns.
    36  build
    37  SELECT *
    38  FROM [INSERT INTO xyz (x, y, z) SELECT b, a, c FROM abc WHERE b='foo' RETURNING *]
    39  WHERE z > 1.0
    40  ----
    41  with &1
    42   ├── columns: x:8(string!null) y:9(int!null) z:10(float!null)
    43   ├── volatile, side-effects, mutations
    44   ├── stats: [rows=69.4736842, distinct(10)=6.66666667, null(10)=0]
    45   ├── fd: ()-->(8)
    46   ├── insert xyz
    47   │    ├── columns: xyz.x:1(string!null) xyz.y:2(int!null) xyz.z:3(float)
    48   │    ├── insert-mapping:
    49   │    │    ├── b:5 => xyz.x:1
    50   │    │    ├── a:4 => xyz.y:2
    51   │    │    └── c:6 => xyz.z:3
    52   │    ├── volatile, side-effects, mutations
    53   │    ├── stats: [rows=200]
    54   │    ├── fd: ()-->(1)
    55   │    └── project
    56   │         ├── columns: a:4(int!null) b:5(string!null) c:6(float)
    57   │         ├── stats: [rows=200]
    58   │         ├── fd: ()-->(5)
    59   │         └── select
    60   │              ├── columns: a:4(int!null) b:5(string!null) c:6(float) rowid:7(int!null)
    61   │              ├── stats: [rows=200, distinct(5)=1, null(5)=0]
    62   │              ├── key: (7)
    63   │              ├── fd: ()-->(5), (7)-->(4,6)
    64   │              ├── scan abc
    65   │              │    ├── columns: a:4(int!null) b:5(string) c:6(float) rowid:7(int!null)
    66   │              │    ├── computed column expressions
    67   │              │    │    └── c:6
    68   │              │    │         └── a:4::FLOAT8 [type=float]
    69   │              │    ├── stats: [rows=2000, distinct(4)=2000, null(4)=0, distinct(5)=10, null(5)=0, distinct(7)=2000, null(7)=0]
    70   │              │    ├── key: (7)
    71   │              │    └── fd: (7)-->(4-6)
    72   │              └── filters
    73   │                   └── b:5 = 'foo' [type=bool, outer=(5), constraints=(/5: [/'foo' - /'foo']; tight), fd=()-->(5)]
    74   └── select
    75        ├── columns: x:8(string!null) y:9(int!null) z:10(float!null)
    76        ├── stats: [rows=69.4736842, distinct(10)=6.66666667, null(10)=0]
    77        ├── fd: ()-->(8)
    78        ├── with-scan &1
    79        │    ├── columns: x:8(string!null) y:9(int!null) z:10(float)
    80        │    ├── mapping:
    81        │    │    ├──  xyz.x:1(string) => x:8(string)
    82        │    │    ├──  xyz.y:2(int) => y:9(int)
    83        │    │    └──  xyz.z:3(float) => z:10(float)
    84        │    ├── stats: [rows=200, distinct(8)=20, null(8)=0, distinct(9)=20, null(9)=0, distinct(10)=20, null(10)=2]
    85        │    └── fd: ()-->(8)
    86        └── filters
    87             └── z:10 > 1.0 [type=bool, outer=(10), constraints=(/10: [/1.0000000000000002 - ]; tight)]
    88  
    89  # Cardinality is zero.
    90  build
    91  INSERT INTO xyz (x, y, z) SELECT b, a, c FROM abc WHERE False RETURNING *
    92  ----
    93  insert xyz
    94   ├── columns: x:1(string!null) y:2(int!null) z:3(float)
    95   ├── insert-mapping:
    96   │    ├── b:5 => x:1
    97   │    ├── a:4 => y:2
    98   │    └── c:6 => z:3
    99   ├── cardinality: [0 - 0]
   100   ├── volatile, side-effects, mutations
   101   ├── stats: [rows=0]
   102   └── project
   103        ├── columns: a:4(int!null) b:5(string) c:6(float)
   104        ├── cardinality: [0 - 0]
   105        ├── stats: [rows=0]
   106        └── select
   107             ├── columns: a:4(int!null) b:5(string) c:6(float) rowid:7(int!null)
   108             ├── cardinality: [0 - 0]
   109             ├── stats: [rows=0]
   110             ├── key: (7)
   111             ├── fd: (7)-->(4-6)
   112             ├── scan abc
   113             │    ├── columns: a:4(int!null) b:5(string) c:6(float) rowid:7(int!null)
   114             │    ├── computed column expressions
   115             │    │    └── c:6
   116             │    │         └── a:4::FLOAT8 [type=float]
   117             │    ├── stats: [rows=2000]
   118             │    ├── key: (7)
   119             │    └── fd: (7)-->(4-6)
   120             └── filters
   121                  └── false [type=bool]