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