github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats/update (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 UPDATE input columns and transferred to
    35  # RETURNING columns.
    36  build
    37  SELECT *
    38  FROM [UPDATE xyz SET y=5 WHERE z=5.5 RETURNING *]
    39  WHERE x > 'foo'
    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=3.33333333, distinct(8)=3.33333333, null(8)=0]
    45   ├── key: (8)
    46   ├── fd: ()-->(9,10)
    47   ├── update xyz
    48   │    ├── columns: xyz.x:1(string!null) xyz.y:2(int!null) xyz.z:3(float!null)
    49   │    ├── fetch columns: xyz.x:4(string) xyz.y:5(int) xyz.z:6(float)
    50   │    ├── update-mapping:
    51   │    │    └── y_new:7 => xyz.y:2
    52   │    ├── volatile, side-effects, mutations
    53   │    ├── stats: [rows=10]
    54   │    ├── key: (1)
    55   │    ├── fd: ()-->(2,3)
    56   │    └── project
    57   │         ├── columns: y_new:7(int!null) xyz.x:4(string!null) xyz.y:5(int!null) xyz.z:6(float!null)
    58   │         ├── stats: [rows=10]
    59   │         ├── key: (4)
    60   │         ├── fd: ()-->(6,7), (4)-->(5)
    61   │         ├── select
    62   │         │    ├── columns: xyz.x:4(string!null) xyz.y:5(int!null) xyz.z:6(float!null)
    63   │         │    ├── stats: [rows=10, distinct(6)=1, null(6)=0]
    64   │         │    ├── key: (4)
    65   │         │    ├── fd: ()-->(6), (4)-->(5)
    66   │         │    ├── scan xyz
    67   │         │    │    ├── columns: xyz.x:4(string!null) xyz.y:5(int!null) xyz.z:6(float)
    68   │         │    │    ├── stats: [rows=1000, distinct(4)=1000, null(4)=0, distinct(5)=100, null(5)=0, distinct(6)=100, null(6)=10]
    69   │         │    │    ├── key: (4)
    70   │         │    │    └── fd: (4)-->(5,6)
    71   │         │    └── filters
    72   │         │         └── xyz.z:6 = 5.5 [type=bool, outer=(6), constraints=(/6: [/5.5 - /5.5]; tight), fd=()-->(6)]
    73   │         └── projections
    74   │              └── 5 [as=y_new:7, type=int]
    75   └── select
    76        ├── columns: x:8(string!null) y:9(int!null) z:10(float!null)
    77        ├── stats: [rows=3.33333333, distinct(8)=3.33333333, null(8)=0]
    78        ├── key: (8)
    79        ├── fd: ()-->(9,10)
    80        ├── with-scan &1
    81        │    ├── columns: x:8(string!null) y:9(int!null) z:10(float!null)
    82        │    ├── mapping:
    83        │    │    ├──  xyz.x:1(string) => x:8(string)
    84        │    │    ├──  xyz.y:2(int) => y:9(int)
    85        │    │    └──  xyz.z:3(float) => z:10(float)
    86        │    ├── stats: [rows=10, distinct(8)=10, null(8)=0, distinct(9)=1, null(9)=0, distinct(10)=1, null(10)=0]
    87        │    ├── key: (8)
    88        │    └── fd: ()-->(9,10)
    89        └── filters
    90             └── x:8 > 'foo' [type=bool, outer=(8), constraints=(/8: [/e'foo\x00' - ]; tight)]
    91  
    92  # Cardinality is zero.
    93  build
    94  UPDATE xyz SET x='foo' WHERE False RETURNING *
    95  ----
    96  update xyz
    97   ├── columns: x:1(string!null) y:2(int!null) z:3(float)
    98   ├── fetch columns: x:4(string) y:5(int) z:6(float)
    99   ├── update-mapping:
   100   │    └── x_new:7 => x:1
   101   ├── cardinality: [0 - 0]
   102   ├── volatile, side-effects, mutations
   103   ├── stats: [rows=0]
   104   ├── fd: ()-->(1)
   105   └── project
   106        ├── columns: x_new:7(string!null) x:4(string!null) y:5(int!null) z:6(float)
   107        ├── cardinality: [0 - 0]
   108        ├── stats: [rows=0]
   109        ├── key: (4)
   110        ├── fd: ()-->(7), (4)-->(5,6)
   111        ├── select
   112        │    ├── columns: x:4(string!null) y:5(int!null) z:6(float)
   113        │    ├── cardinality: [0 - 0]
   114        │    ├── stats: [rows=0]
   115        │    ├── key: (4)
   116        │    ├── fd: (4)-->(5,6)
   117        │    ├── scan xyz
   118        │    │    ├── columns: x:4(string!null) y:5(int!null) z:6(float)
   119        │    │    ├── stats: [rows=1000]
   120        │    │    ├── key: (4)
   121        │    │    └── fd: (4)-->(5,6)
   122        │    └── filters
   123        │         └── false [type=bool]
   124        └── projections
   125             └── 'foo' [as=x_new:7, type=string]