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

     1  exec-ddl
     2  CREATE TABLE a (k INT PRIMARY KEY, i INT, f FLOAT, s STRING, j JSON, arr int[])
     3  ----
     4  
     5  # --------------------------------------------------
     6  # EliminateAggDistinct
     7  # --------------------------------------------------
     8  
     9  norm expect=EliminateAggDistinct
    10  SELECT min(DISTINCT i), max(DISTINCT i), bool_and(DISTINCT i>f), bool_or(DISTINCT i>f), corr(DISTINCT k, i) FROM a
    11  ----
    12  scalar-group-by
    13   ├── columns: min:7 max:8 bool_and:10 bool_or:11 corr:12
    14   ├── cardinality: [1 - 1]
    15   ├── key: ()
    16   ├── fd: ()-->(7,8,10-12)
    17   ├── project
    18   │    ├── columns: column9:9 k:1!null i:2
    19   │    ├── key: (1)
    20   │    ├── fd: (1)-->(2,9)
    21   │    ├── scan a
    22   │    │    ├── columns: k:1!null i:2 f:3
    23   │    │    ├── key: (1)
    24   │    │    └── fd: (1)-->(2,3)
    25   │    └── projections
    26   │         └── i:2 > f:3 [as=column9:9, outer=(2,3)]
    27   └── aggregations
    28        ├── min [as=min:7, outer=(2)]
    29        │    └── i:2
    30        ├── max [as=max:8, outer=(2)]
    31        │    └── i:2
    32        ├── bool-and [as=bool_and:10, outer=(9)]
    33        │    └── column9:9
    34        ├── bool-or [as=bool_or:11, outer=(9)]
    35        │    └── column9:9
    36        └── corr [as=corr:12, outer=(1,2)]
    37             ├── k:1
    38             └── i:2
    39  
    40  # The rule should still work when FILTER is present.
    41  norm expect=EliminateAggDistinct
    42  SELECT
    43      min(DISTINCT i) FILTER (WHERE i > 5),
    44      max(DISTINCT i) FILTER (WHERE i > 5),
    45      bool_and(DISTINCT i>f) FILTER (WHERE f > 0.0),
    46      bool_or(DISTINCT i>f) FILTER (WHERE f > 1.0),
    47      corr(DISTINCT k, i) FILTER(WHERE k > 5 AND i > 5)
    48  FROM a
    49  ----
    50  scalar-group-by
    51   ├── columns: min:8 max:9 bool_and:12 bool_or:14 corr:16
    52   ├── cardinality: [1 - 1]
    53   ├── key: ()
    54   ├── fd: ()-->(8,9,12,14,16)
    55   ├── project
    56   │    ├── columns: column7:7 column10:10 column11:11 column13:13 column15:15 k:1!null i:2
    57   │    ├── key: (1)
    58   │    ├── fd: (1)-->(2,7,10,11,13), (2)-->(7), (1,2)-->(15)
    59   │    ├── scan a
    60   │    │    ├── columns: k:1!null i:2 f:3
    61   │    │    ├── key: (1)
    62   │    │    └── fd: (1)-->(2,3)
    63   │    └── projections
    64   │         ├── i:2 > 5 [as=column7:7, outer=(2)]
    65   │         ├── i:2 > f:3 [as=column10:10, outer=(2,3)]
    66   │         ├── f:3 > 0.0 [as=column11:11, outer=(3)]
    67   │         ├── f:3 > 1.0 [as=column13:13, outer=(3)]
    68   │         └── (k:1 > 5) AND (i:2 > 5) [as=column15:15, outer=(1,2)]
    69   └── aggregations
    70        ├── agg-filter [as=min:8, outer=(2,7)]
    71        │    ├── min
    72        │    │    └── i:2
    73        │    └── column7:7
    74        ├── agg-filter [as=max:9, outer=(2,7)]
    75        │    ├── max
    76        │    │    └── i:2
    77        │    └── column7:7
    78        ├── agg-filter [as=bool_and:12, outer=(10,11)]
    79        │    ├── bool-and
    80        │    │    └── column10:10
    81        │    └── column11:11
    82        ├── agg-filter [as=bool_or:14, outer=(10,13)]
    83        │    ├── bool-or
    84        │    │    └── column10:10
    85        │    └── column13:13
    86        └── agg-filter [as=corr:16, outer=(1,2,15)]
    87             ├── corr
    88             │    ├── k:1
    89             │    └── i:2
    90             └── column15:15
    91  
    92  # The rule should not apply to these aggregations.
    93  norm expect-not=EliminateAggDistinct
    94  SELECT
    95      count(DISTINCT i),
    96      sum(DISTINCT i) FILTER (WHERE i > 5),
    97      sum_int(DISTINCT i),
    98      avg(DISTINCT i),
    99      stddev(DISTINCT f),
   100      variance(DISTINCT f),
   101      xor_agg(DISTINCT s::BYTES),
   102      array_agg(DISTINCT i),
   103      json_agg(DISTINCT j)
   104  FROM a
   105  ----
   106  scalar-group-by
   107   ├── columns: count:7!null sum:9 sum_int:10 avg:11 stddev:12 variance:13 xor_agg:15 array_agg:16 json_agg:17
   108   ├── cardinality: [1 - 1]
   109   ├── key: ()
   110   ├── fd: ()-->(7,9-13,15-17)
   111   ├── project
   112   │    ├── columns: column8:8 column14:14 i:2 f:3 j:5
   113   │    ├── fd: (2)-->(8)
   114   │    ├── scan a
   115   │    │    └── columns: i:2 f:3 s:4 j:5
   116   │    └── projections
   117   │         ├── i:2 > 5 [as=column8:8, outer=(2)]
   118   │         └── s:4::BYTES [as=column14:14, outer=(4)]
   119   └── aggregations
   120        ├── agg-distinct [as=count:7, outer=(2)]
   121        │    └── count
   122        │         └── i:2
   123        ├── agg-filter [as=sum:9, outer=(2,8)]
   124        │    ├── agg-distinct
   125        │    │    └── sum
   126        │    │         └── i:2
   127        │    └── column8:8
   128        ├── agg-distinct [as=sum_int:10, outer=(2)]
   129        │    └── sum-int
   130        │         └── i:2
   131        ├── agg-distinct [as=avg:11, outer=(2)]
   132        │    └── avg
   133        │         └── i:2
   134        ├── agg-distinct [as=stddev:12, outer=(3)]
   135        │    └── std-dev
   136        │         └── f:3
   137        ├── agg-distinct [as=variance:13, outer=(3)]
   138        │    └── variance
   139        │         └── f:3
   140        ├── agg-distinct [as=xor_agg:15, outer=(14)]
   141        │    └── xor-agg
   142        │         └── column14:14
   143        ├── agg-distinct [as=array_agg:16, outer=(2)]
   144        │    └── array-agg
   145        │         └── i:2
   146        └── agg-distinct [as=json_agg:17, outer=(5)]
   147             └── json-agg
   148                  └── j:5