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

     1  # tests adapted from logictest -- aggregate and distinct
     2  
     3  exec-ddl
     4  CREATE TABLE xyz (
     5    x INT PRIMARY KEY,
     6    y INT,
     7    z FLOAT,
     8    INDEX xy (x, y),
     9    INDEX zyx (z, y, x),
    10    FAMILY (x),
    11    FAMILY (y),
    12    FAMILY (z)
    13  )
    14  ----
    15  
    16  build
    17  SELECT y, z FROM xyz
    18  ----
    19  project
    20   ├── columns: y:2 z:3
    21   └── scan xyz
    22        └── columns: x:1!null y:2 z:3
    23  
    24  build
    25  SELECT DISTINCT y, z FROM xyz
    26  ----
    27  distinct-on
    28   ├── columns: y:2 z:3
    29   ├── grouping columns: y:2 z:3
    30   └── project
    31        ├── columns: y:2 z:3
    32        └── scan xyz
    33             └── columns: x:1!null y:2 z:3
    34  
    35  build
    36  SELECT y FROM (SELECT DISTINCT y, z FROM xyz)
    37  ----
    38  project
    39   ├── columns: y:2
    40   └── distinct-on
    41        ├── columns: y:2 z:3
    42        ├── grouping columns: y:2 z:3
    43        └── project
    44             ├── columns: y:2 z:3
    45             └── scan xyz
    46                  └── columns: x:1!null y:2 z:3
    47  
    48  build
    49  SELECT DISTINCT y, z FROM xyz ORDER BY z
    50  ----
    51  distinct-on
    52   ├── columns: y:2 z:3
    53   ├── grouping columns: y:2 z:3
    54   ├── ordering: +3
    55   └── sort
    56        ├── columns: y:2 z:3
    57        ├── ordering: +3
    58        └── project
    59             ├── columns: y:2 z:3
    60             └── scan xyz
    61                  └── columns: x:1!null y:2 z:3
    62  
    63  build
    64  SELECT DISTINCT y, z FROM xyz ORDER BY y
    65  ----
    66  distinct-on
    67   ├── columns: y:2 z:3
    68   ├── grouping columns: y:2 z:3
    69   ├── ordering: +2
    70   └── sort
    71        ├── columns: y:2 z:3
    72        ├── ordering: +2
    73        └── project
    74             ├── columns: y:2 z:3
    75             └── scan xyz
    76                  └── columns: x:1!null y:2 z:3
    77  
    78  build
    79  SELECT DISTINCT y, z FROM xyz ORDER BY y, z
    80  ----
    81  distinct-on
    82   ├── columns: y:2 z:3
    83   ├── grouping columns: y:2 z:3
    84   ├── ordering: +2,+3
    85   └── sort
    86        ├── columns: y:2 z:3
    87        ├── ordering: +2,+3
    88        └── project
    89             ├── columns: y:2 z:3
    90             └── scan xyz
    91                  └── columns: x:1!null y:2 z:3
    92  
    93  build
    94  SELECT DISTINCT y + x AS r FROM xyz ORDER by (y + x)
    95  ----
    96  distinct-on
    97   ├── columns: r:4
    98   ├── grouping columns: r:4
    99   ├── ordering: +4
   100   └── sort
   101        ├── columns: r:4
   102        ├── ordering: +4
   103        └── project
   104             ├── columns: r:4
   105             ├── scan xyz
   106             │    └── columns: x:1!null y:2 z:3
   107             └── projections
   108                  └── y:2 + x:1 [as=r:4]
   109  
   110  build
   111  SELECT DISTINCT y + x AS r FROM xyz ORDER BY y + x
   112  ----
   113  distinct-on
   114   ├── columns: r:4
   115   ├── grouping columns: r:4
   116   ├── ordering: +4
   117   └── sort
   118        ├── columns: r:4
   119        ├── ordering: +4
   120        └── project
   121             ├── columns: r:4
   122             ├── scan xyz
   123             │    └── columns: x:1!null y:2 z:3
   124             └── projections
   125                  └── y:2 + x:1 [as=r:4]
   126  
   127  build
   128  SELECT DISTINCT y + z FROM xyz ORDER BY y + z
   129  ----
   130  error (22023): unsupported binary operator: <int> + <float>
   131  
   132  # This query causes an error in Postgres, and the optimizer has followed
   133  # that lead. However, it is supported by the heuristic planner in CockroachDB
   134  # with the semantics:
   135  #   SELECT y AS w FROM t GROUP BY y ORDER BY min(z);
   136  build
   137  SELECT DISTINCT y AS w FROM xyz ORDER by z
   138  ----
   139  error (42P10): for SELECT DISTINCT, ORDER BY expressions must appear in select list
   140  
   141  build
   142  SELECT DISTINCT y AS w FROM xyz ORDER by y
   143  ----
   144  sort
   145   ├── columns: w:2
   146   ├── ordering: +2
   147   └── distinct-on
   148        ├── columns: y:2
   149        ├── grouping columns: y:2
   150        └── project
   151             ├── columns: y:2
   152             └── scan xyz
   153                  └── columns: x:1!null y:2 z:3
   154  
   155  build
   156  SELECT DISTINCT (y,z) AS r FROM xyz
   157  ----
   158  distinct-on
   159   ├── columns: r:4
   160   ├── grouping columns: r:4
   161   └── project
   162        ├── columns: r:4
   163        ├── scan xyz
   164        │    └── columns: x:1!null y:2 z:3
   165        └── projections
   166             └── (y:2, z:3) [as=r:4]
   167  
   168  build
   169  SELECT count(*) FROM (SELECT DISTINCT y FROM xyz)
   170  ----
   171  scalar-group-by
   172   ├── columns: count:4!null
   173   ├── project
   174   │    └── distinct-on
   175   │         ├── columns: y:2
   176   │         ├── grouping columns: y:2
   177   │         └── project
   178   │              ├── columns: y:2
   179   │              └── scan xyz
   180   │                   └── columns: x:1!null y:2 z:3
   181   └── aggregations
   182        └── count-rows [as=count_rows:4]
   183  
   184  build
   185  SELECT DISTINCT x FROM xyz WHERE x > 0
   186  ----
   187  distinct-on
   188   ├── columns: x:1!null
   189   ├── grouping columns: x:1!null
   190   └── project
   191        ├── columns: x:1!null
   192        └── select
   193             ├── columns: x:1!null y:2 z:3
   194             ├── scan xyz
   195             │    └── columns: x:1!null y:2 z:3
   196             └── filters
   197                  └── x:1 > 0
   198  
   199  build
   200  SELECT DISTINCT z FROM xyz WHERE x > 0
   201  ----
   202  distinct-on
   203   ├── columns: z:3
   204   ├── grouping columns: z:3
   205   └── project
   206        ├── columns: z:3
   207        └── select
   208             ├── columns: x:1!null y:2 z:3
   209             ├── scan xyz
   210             │    └── columns: x:1!null y:2 z:3
   211             └── filters
   212                  └── x:1 > 0
   213  
   214  build
   215  SELECT DISTINCT max(x) FROM xyz GROUP BY x
   216  ----
   217  distinct-on
   218   ├── columns: max:4!null
   219   ├── grouping columns: max:4!null
   220   └── project
   221        ├── columns: max:4!null
   222        └── group-by
   223             ├── columns: x:1!null max:4!null
   224             ├── grouping columns: x:1!null
   225             ├── project
   226             │    ├── columns: x:1!null
   227             │    └── scan xyz
   228             │         └── columns: x:1!null y:2 z:3
   229             └── aggregations
   230                  └── max [as=max:4]
   231                       └── x:1
   232  
   233  build
   234  SELECT DISTINCT x+y AS r FROM xyz
   235  ----
   236  distinct-on
   237   ├── columns: r:4
   238   ├── grouping columns: r:4
   239   └── project
   240        ├── columns: r:4
   241        ├── scan xyz
   242        │    └── columns: x:1!null y:2 z:3
   243        └── projections
   244             └── x:1 + y:2 [as=r:4]
   245  
   246  build
   247  SELECT DISTINCT 3 r FROM xyz
   248  ----
   249  distinct-on
   250   ├── columns: r:4!null
   251   ├── grouping columns: r:4!null
   252   └── project
   253        ├── columns: r:4!null
   254        ├── scan xyz
   255        │    └── columns: x:1!null y:2 z:3
   256        └── projections
   257             └── 3 [as=r:4]
   258  
   259  build
   260  SELECT DISTINCT 3 r
   261  ----
   262  distinct-on
   263   ├── columns: r:1!null
   264   ├── grouping columns: r:1!null
   265   └── project
   266        ├── columns: r:1!null
   267        ├── values
   268        │    └── ()
   269        └── projections
   270             └── 3 [as=r:1]
   271  
   272  build
   273  SELECT DISTINCT max(z), x+y AS r, 3 AS s FROM xyz GROUP BY x, y HAVING y > 4
   274  ----
   275  distinct-on
   276   ├── columns: max:4 r:5!null s:6!null
   277   ├── grouping columns: max:4 r:5!null s:6!null
   278   └── project
   279        ├── columns: r:5!null s:6!null max:4
   280        ├── select
   281        │    ├── columns: x:1!null y:2!null max:4
   282        │    ├── group-by
   283        │    │    ├── columns: x:1!null y:2 max:4
   284        │    │    ├── grouping columns: x:1!null y:2
   285        │    │    ├── scan xyz
   286        │    │    │    └── columns: x:1!null y:2 z:3
   287        │    │    └── aggregations
   288        │    │         └── max [as=max:4]
   289        │    │              └── z:3
   290        │    └── filters
   291        │         └── y:2 > 4
   292        └── projections
   293             ├── x:1 + y:2 [as=r:5]
   294             └── 3 [as=s:6]
   295  
   296  exec-ddl
   297  CREATE TABLE abcd (
   298    a INT,
   299    b INT,
   300    c INT,
   301    d INT NOT NULL,
   302    PRIMARY KEY (a, b, c),
   303    UNIQUE INDEX (d, b)
   304  )
   305  ----
   306  
   307  build
   308  SELECT DISTINCT 1 AS x, d, b FROM abcd ORDER BY d, b
   309  ----
   310  distinct-on
   311   ├── columns: x:5!null d:4!null b:2!null
   312   ├── grouping columns: b:2!null d:4!null x:5!null
   313   ├── ordering: +4,+2
   314   └── sort
   315        ├── columns: b:2!null d:4!null x:5!null
   316        ├── ordering: +4,+2 opt(5)
   317        └── project
   318             ├── columns: x:5!null b:2!null d:4!null
   319             ├── scan abcd
   320             │    └── columns: a:1!null b:2!null c:3!null d:4!null
   321             └── projections
   322                  └── 1 [as=x:5]