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

     1  exec-ddl
     2  CREATE TABLE a (x INT PRIMARY KEY, y INT)
     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": 4000,
    17      "distinct_count": 400
    18    }
    19  ]'
    20  ----
    21  
    22  norm
    23  SELECT * FROM (SELECT * FROM a WITH ORDINALITY) WHERE ordinality > 0 AND ordinality <= 10
    24  ----
    25  select
    26   ├── columns: x:1(int!null) y:2(int) ordinality:3(int!null)
    27   ├── cardinality: [0 - 10]
    28   ├── stats: [rows=10, distinct(3)=10, null(3)=0]
    29   ├── key: (1)
    30   ├── fd: (1)-->(2,3), (3)-->(1,2)
    31   ├── ordinality
    32   │    ├── columns: x:1(int!null) y:2(int) ordinality:3(int!null)
    33   │    ├── stats: [rows=4000, distinct(1)=4000, null(1)=0, distinct(3)=4000, null(3)=0]
    34   │    ├── key: (1)
    35   │    ├── fd: (1)-->(2,3), (3)-->(1,2)
    36   │    └── scan a
    37   │         ├── columns: x:1(int!null) y:2(int)
    38   │         ├── stats: [rows=4000, distinct(1)=4000, null(1)=0]
    39   │         ├── key: (1)
    40   │         └── fd: (1)-->(2)
    41   └── filters
    42        └── (ordinality:3 > 0) AND (ordinality:3 <= 10) [type=bool, outer=(3), constraints=(/3: [/1 - /10]; tight)]
    43  
    44  norm
    45  SELECT * FROM (SELECT * FROM a WITH ORDINALITY) WHERE y > 0 AND y <= 10
    46  ----
    47  select
    48   ├── columns: x:1(int!null) y:2(int!null) ordinality:3(int!null)
    49   ├── stats: [rows=100, distinct(2)=10, null(2)=0]
    50   ├── key: (1)
    51   ├── fd: (1)-->(2,3), (3)-->(1,2)
    52   ├── ordinality
    53   │    ├── columns: x:1(int!null) y:2(int) ordinality:3(int!null)
    54   │    ├── stats: [rows=4000, distinct(1)=4000, null(1)=0, distinct(2)=400, null(2)=0, distinct(3)=4000, null(3)=0]
    55   │    ├── key: (1)
    56   │    ├── fd: (1)-->(2,3), (3)-->(1,2)
    57   │    └── scan a
    58   │         ├── columns: x:1(int!null) y:2(int)
    59   │         ├── stats: [rows=4000, distinct(1)=4000, null(1)=0, distinct(2)=400, null(2)=0]
    60   │         ├── key: (1)
    61   │         └── fd: (1)-->(2)
    62   └── filters
    63        └── (y:2 > 0) AND (y:2 <= 10) [type=bool, outer=(2), constraints=(/2: [/1 - /10]; tight)]
    64  
    65  norm
    66  SELECT 1 x FROM a WITH ORDINALITY
    67  ----
    68  project
    69   ├── columns: x:4(int!null)
    70   ├── stats: [rows=4000]
    71   ├── fd: ()-->(4)
    72   ├── ordinality
    73   │    ├── columns: ordinality:3(int!null)
    74   │    ├── stats: [rows=4000]
    75   │    ├── key: (3)
    76   │    └── scan a
    77   │         └── stats: [rows=4000]
    78   └── projections
    79        └── 1 [as=x:4, type=int]
    80  
    81  norm
    82  SELECT x FROM (SELECT * FROM a WITH ORDINALITY) WHERE ordinality > 0 AND ordinality <= 10
    83  ----
    84  project
    85   ├── columns: x:1(int!null)
    86   ├── cardinality: [0 - 10]
    87   ├── stats: [rows=10]
    88   ├── key: (1)
    89   └── select
    90        ├── columns: x:1(int!null) ordinality:3(int!null)
    91        ├── cardinality: [0 - 10]
    92        ├── stats: [rows=10, distinct(3)=10, null(3)=0]
    93        ├── key: (1)
    94        ├── fd: (1)-->(3), (3)-->(1)
    95        ├── ordinality
    96        │    ├── columns: x:1(int!null) ordinality:3(int!null)
    97        │    ├── stats: [rows=4000, distinct(1)=4000, null(1)=0, distinct(3)=4000, null(3)=0]
    98        │    ├── key: (1)
    99        │    ├── fd: (1)-->(3), (3)-->(1)
   100        │    └── scan a
   101        │         ├── columns: x:1(int!null)
   102        │         ├── stats: [rows=4000, distinct(1)=4000, null(1)=0]
   103        │         └── key: (1)
   104        └── filters
   105             └── (ordinality:3 > 0) AND (ordinality:3 <= 10) [type=bool, outer=(3), constraints=(/3: [/1 - /10]; tight)]
   106  
   107  
   108  norm
   109  SELECT * FROM (SELECT * FROM a WITH ORDINALITY) WHERE ordinality = 2
   110  ----
   111  select
   112   ├── columns: x:1(int!null) y:2(int) ordinality:3(int!null)
   113   ├── cardinality: [0 - 1]
   114   ├── stats: [rows=1, distinct(3)=1, null(3)=0]
   115   ├── key: ()
   116   ├── fd: ()-->(1-3)
   117   ├── ordinality
   118   │    ├── columns: x:1(int!null) y:2(int) ordinality:3(int!null)
   119   │    ├── stats: [rows=4000, distinct(1)=4000, null(1)=0, distinct(3)=4000, null(3)=0]
   120   │    ├── key: (1)
   121   │    ├── fd: (1)-->(2,3), (3)-->(1,2)
   122   │    └── scan a
   123   │         ├── columns: x:1(int!null) y:2(int)
   124   │         ├── stats: [rows=4000, distinct(1)=4000, null(1)=0]
   125   │         ├── key: (1)
   126   │         └── fd: (1)-->(2)
   127   └── filters
   128        └── ordinality:3 = 2 [type=bool, outer=(3), constraints=(/3: [/2 - /2]; tight), fd=()-->(3)]
   129  
   130  build
   131  SELECT DISTINCT ordinality FROM (SELECT * FROM a WITH ORDINALITY)
   132  ----
   133  distinct-on
   134   ├── columns: ordinality:3(int!null)
   135   ├── grouping columns: ordinality:3(int!null)
   136   ├── stats: [rows=4000, distinct(3)=4000, null(3)=0]
   137   ├── key: (3)
   138   └── project
   139        ├── columns: ordinality:3(int!null)
   140        ├── stats: [rows=4000, distinct(3)=4000, null(3)=0]
   141        ├── key: (3)
   142        └── ordinality
   143             ├── columns: x:1(int!null) y:2(int) ordinality:3(int!null)
   144             ├── stats: [rows=4000, distinct(3)=4000, null(3)=0]
   145             ├── key: (1)
   146             ├── fd: (1)-->(2,3), (3)-->(1,2)
   147             └── scan a
   148                  ├── columns: x:1(int!null) y:2(int)
   149                  ├── stats: [rows=4000]
   150                  ├── key: (1)
   151                  └── fd: (1)-->(2)