github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/aggregate (about)

     1  # LogicTest: !3node-tenant
     2  subtest other
     3  
     4  statement ok
     5  CREATE TABLE kv (
     6    k INT PRIMARY KEY,
     7    v INT,
     8    w INT,
     9    s STRING,
    10    i INTERVAL
    11  )
    12  
    13  # Aggregate functions return NULL if there are no rows.
    14  query IIIIRRRRRRBBTII
    15  SELECT min(1), max(1), count(1), sum_int(1), avg(1), sum(1), stddev(1), stddev_samp(1), var_samp(1), variance(1), bool_and(true), bool_and(false), xor_agg(b'\x01'), bit_and(1), bit_or(1) FROM kv
    16  ----
    17  NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    18  
    19  # Regression test for #29695
    20  query T
    21  SELECT min(NULL)
    22  ----
    23  NULL
    24  
    25  # Aggregate functions return NULL if there are no rows.
    26  query T
    27  SELECT array_agg(1) FROM kv
    28  ----
    29  NULL
    30  
    31  query T
    32  SELECT json_agg(1) FROM kv
    33  ----
    34  NULL
    35  
    36  query T
    37  SELECT jsonb_agg(1) FROM kv
    38  ----
    39  NULL
    40  
    41  query TTTT
    42  SELECT min(i), avg(i), max(i), sum(i) FROM kv
    43  ----
    44  NULL NULL NULL NULL
    45  
    46  query IIIIRRRRBBT
    47  SELECT min(v), max(v), count(v), sum_int(1), avg(v), sum(v), stddev(v), variance(v), bool_and(v = 1), bool_and(v = 1), xor_agg(s::bytes) FROM kv
    48  ----
    49  NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL
    50  
    51  query T
    52  SELECT array_agg(v) FROM kv
    53  ----
    54  NULL
    55  
    56  query T
    57  SELECT json_agg(v) FROM kv
    58  ----
    59  NULL
    60  
    61  query T
    62  SELECT jsonb_agg(v) FROM kv
    63  ----
    64  NULL
    65  
    66  # Aggregate functions triggers aggregation and computation when there is no source.
    67  query IIIIRRRRBBT
    68  SELECT min(1), count(1), max(1), sum_int(1), avg(1)::float, sum(1), stddev_samp(1), variance(1), bool_and(true), bool_or(true), to_hex(xor_agg(b'\x01'))
    69  ----
    70  1 1 1 1 1 1 NULL NULL true true 01
    71  
    72  # Aggregate functions triggers aggregation and computation when there is no source.
    73  query T
    74  SELECT array_agg(1)
    75  ----
    76  {1}
    77  
    78  query T
    79  SELECT json_agg(1)
    80  ----
    81  [1]
    82  
    83  query T
    84  SELECT jsonb_agg(1)
    85  ----
    86  [1]
    87  
    88  # Some aggregate functions are not normalized to NULL when given a NULL
    89  # argument.
    90  query I
    91  SELECT count(NULL)
    92  ----
    93  0
    94  
    95  query T
    96  SELECT json_agg(NULL)
    97  ----
    98  [null]
    99  
   100  query T
   101  SELECT jsonb_agg(NULL)
   102  ----
   103  [null]
   104  
   105  # This should ideally return {NULL}, but this is a pathological case, and
   106  # Postgres has the same behavior, so it's sufficient for now.
   107  statement error ambiguous call
   108  SELECT array_agg(NULL)
   109  
   110  # With an explicit cast, this works as expected.
   111  query T
   112  SELECT array_agg(NULL::TEXT)
   113  ----
   114  {NULL}
   115  
   116  # Regression test for #25724 (problem with typed NULLs and distsql planning).
   117  # The previous query doesn't run under distsql.
   118  query T
   119  SELECT array_agg(NULL::TEXT) FROM (VALUES (1)) AS t(x)
   120  ----
   121  {NULL}
   122  
   123  # Check that COALESCE using aggregate results over an empty table
   124  # work properly.
   125  query I
   126  SELECT COALESCE(max(1), 0) FROM generate_series(1,0)
   127  ----
   128  0
   129  
   130  query I
   131  SELECT count_rows() FROM generate_series(1,100)
   132  ----
   133  100
   134  
   135  # Same, using arithmetic on COUNT.
   136  query I
   137  SELECT 1 + count(*) FROM generate_series(1,0)
   138  ----
   139  1
   140  
   141  # Same, using an empty table.
   142  # The following test *must* occur before the first INSERT to the tables,
   143  # so that it can observe an empty table.
   144  query II
   145  SELECT count(*), COALESCE(max(k), 1) FROM kv
   146  ----
   147  0 1
   148  
   149  # Same, using a subquery. (#12705)
   150  query I
   151  SELECT (SELECT COALESCE(max(1), 0) FROM generate_series(1,0))
   152  ----
   153  0
   154  
   155  statement OK
   156  INSERT INTO kv VALUES
   157  (1, 2, 3, 'a', '1min'),
   158  (3, 4, 5, 'a', '2sec'),
   159  (5, NULL, 5, NULL, NULL),
   160  (6, 2, 3, 'b', '1ms'),
   161  (7, 2, 2, 'b', '4 days'),
   162  (8, 4, 2, 'A', '3 years')
   163  
   164  # Aggregate functions triggers aggregation and computation for every row even when applied to a constant.
   165  # NB: The XOR result is 00 because \x01 is XOR'd an even number of times.
   166  query IIIIRRRRBBT
   167  SELECT min(1), count(1), max(1), sum_int(1), avg(1)::float, sum(1), stddev(1), variance(1)::float, bool_and(true), bool_or(true), to_hex(xor_agg(b'\x01')) FROM kv
   168  ----
   169  1 6 1 6 1 6 0 0 true true 00
   170  
   171  # Aggregate functions triggers aggregation and computation for every row even when applied to a constant.
   172  query T
   173  SELECT array_agg(1) FROM kv
   174  ----
   175  {1,1,1,1,1,1}
   176  
   177  query T
   178  SELECT json_agg(1) FROM kv
   179  ----
   180  [1, 1, 1, 1, 1, 1]
   181  
   182  query T
   183  SELECT jsonb_agg(1) FROM kv
   184  ----
   185  [1, 1, 1, 1, 1, 1]
   186  
   187  # Even with no aggregate functions, grouping occurs in the presence of GROUP BY.
   188  query I rowsort
   189  SELECT 1 FROM kv GROUP BY v
   190  ----
   191  1
   192  1
   193  1
   194  
   195  # Presence of HAVING triggers aggregation, reducing results to one row (even without GROUP BY).
   196  query I rowsort
   197  SELECT 3 FROM kv HAVING TRUE
   198  ----
   199  3
   200  
   201  query error pgcode 42803 column "k" must appear in the GROUP BY clause or be used in an aggregate function
   202  SELECT count(*), k FROM kv
   203  
   204  query error unsupported comparison operator: <string> < <int>
   205  SELECT count(*) FROM kv GROUP BY s < 5
   206  
   207  query II rowsort
   208  SELECT count(*), k FROM kv GROUP BY k
   209  ----
   210  1 1
   211  1 3
   212  1 5
   213  1 6
   214  1 7
   215  1 8
   216  
   217  # GROUP BY specified using column index works.
   218  query II rowsort
   219  SELECT count(*), k FROM kv GROUP BY 2
   220  ----
   221  1 1
   222  1 3
   223  1 5
   224  1 6
   225  1 7
   226  1 8
   227  
   228  query error aggregate functions are not allowed in GROUP BY
   229  SELECT * FROM kv GROUP BY v, count(DISTINCT w)
   230  
   231  query error aggregate functions are not allowed in GROUP BY
   232  SELECT count(DISTINCT w) FROM kv GROUP BY 1
   233  
   234  query error aggregate functions are not allowed in RETURNING
   235  INSERT INTO kv (k, v) VALUES (99, 100) RETURNING sum(v)
   236  
   237  query error column "v" does not exist
   238  SELECT sum(v) FROM kv GROUP BY k LIMIT sum(v)
   239  
   240  query error column "v" does not exist
   241  SELECT sum(v) FROM kv GROUP BY k LIMIT 1 OFFSET sum(v)
   242  
   243  query error aggregate functions are not allowed in VALUES
   244  INSERT INTO kv (k, v) VALUES (99, count(1))
   245  
   246  query error pgcode 42P10 GROUP BY position 5 is not in select list
   247  SELECT count(*), k FROM kv GROUP BY 5
   248  
   249  query error pgcode 42P10 GROUP BY position 0 is not in select list
   250  SELECT count(*), k FROM kv GROUP BY 0
   251  
   252  query error pgcode 42601 non-integer constant in GROUP BY
   253  SELECT 1 GROUP BY 'a'
   254  
   255  # Qualifying a name in the SELECT, the GROUP BY, both or neither should not affect validation.
   256  query IT rowsort
   257  SELECT count(*), kv.s FROM kv GROUP BY s
   258  ----
   259  1 A
   260  1 NULL
   261  2 a
   262  2 b
   263  
   264  query IT rowsort
   265  SELECT count(*), s FROM kv GROUP BY kv.s
   266  ----
   267  1 A
   268  1 NULL
   269  2 a
   270  2 b
   271  
   272  query IT rowsort
   273  SELECT count(*), kv.s FROM kv GROUP BY kv.s
   274  ----
   275  1 A
   276  1 NULL
   277  2 a
   278  2 b
   279  
   280  query IT rowsort
   281  SELECT count(*), s FROM kv GROUP BY s
   282  ----
   283  1 A
   284  1 NULL
   285  2 a
   286  2 b
   287  
   288  # Grouping by more than one column works.
   289  query III rowsort
   290  SELECT v, count(*), w FROM kv GROUP BY v, w
   291  ----
   292  2    1 2
   293  2    2 3
   294  4    1 2
   295  4    1 5
   296  NULL 1 5
   297  
   298  # Grouping by more than one column using column numbers works.
   299  query III rowsort
   300  SELECT v, count(*), w FROM kv GROUP BY 1, 3
   301  ----
   302  2    1 2
   303  2    2 3
   304  4    1 2
   305  4    1 5
   306  NULL 1 5
   307  
   308  # Selecting and grouping on a function expression works.
   309  query IT rowsort
   310  SELECT count(*), upper(s) FROM kv GROUP BY upper(s)
   311  ----
   312  1 NULL
   313  2 B
   314  3 A
   315  
   316  # Selecting and grouping on a constant works.
   317  query I
   318  SELECT count(*) FROM kv GROUP BY 1+2
   319  ----
   320  6
   321  
   322  query I
   323  SELECT count(*) FROM kv GROUP BY length('abc')
   324  ----
   325  6
   326  
   327  # Selecting a function of something which is grouped works.
   328  query IT rowsort
   329  SELECT count(*), upper(s) FROM kv GROUP BY s
   330  ----
   331  1 A
   332  1 NULL
   333  2 A
   334  2 B
   335  
   336  # Selecting a value that is not grouped, even if a function of it it, does not work.
   337  query error column "s" must appear in the GROUP BY clause or be used in an aggregate function
   338  SELECT count(*), s FROM kv GROUP BY upper(s)
   339  
   340  # Selecting and grouping on a more complex expression works.
   341  query II rowsort
   342  SELECT count(*), k+v FROM kv GROUP BY k+v
   343  ----
   344  1 12
   345  1 3
   346  1 7
   347  1 8
   348  1 9
   349  1 NULL
   350  
   351  
   352  # Selecting a more complex expression, made up of things which are each grouped, works.
   353  query II rowsort
   354  SELECT count(*), k+v FROM kv GROUP BY k, v
   355  ----
   356  1 12
   357  1 3
   358  1 7
   359  1 8
   360  1 9
   361  1 NULL
   362  
   363  query II rowsort
   364  SELECT count(*), k+v FROM kv GROUP BY k
   365  ----
   366  1  3
   367  1  7
   368  1  NULL
   369  1  8
   370  1  9
   371  1  12
   372  
   373  query error column "k" must appear in the GROUP BY clause or be used in an aggregate function
   374  SELECT count(*), k+v FROM kv GROUP BY v
   375  
   376  query error column "v" must appear in the GROUP BY clause or be used in an aggregate function
   377  SELECT count(*), v/(k+v) FROM kv GROUP BY k+v
   378  
   379  query error aggregate functions are not allowed in WHERE
   380  SELECT k FROM kv WHERE avg(k) > 1
   381  
   382  query error aggregate function calls cannot be nested
   383  SELECT max(avg(k)) FROM kv
   384  
   385  # Test case from #2761.
   386  query II rowsort
   387  SELECT count(kv.k) AS count_1, kv.v + kv.w AS lx FROM kv GROUP BY kv.v + kv.w
   388  ----
   389  1  4
   390  1  6
   391  1  9
   392  1  NULL
   393  2  5
   394  
   395  query TI rowsort
   396  SELECT s, count(*) FROM kv GROUP BY s HAVING count(*) > 1
   397  ----
   398  a 2
   399  b 2
   400  
   401  query TII rowsort
   402  SELECT upper(s), count(DISTINCT s), count(DISTINCT upper(s)) FROM kv GROUP BY upper(s) HAVING count(DISTINCT s) > 1
   403  ----
   404  A 2 1
   405  
   406  query II rowsort
   407  SELECT max(k), min(v) FROM kv HAVING min(v) > 2
   408  ----
   409  
   410  query II rowsort
   411  SELECT max(k), min(v) FROM kv HAVING max(v) > 2
   412  ----
   413  8 2
   414  
   415  query error pgcode 42803 aggregate function calls cannot be nested
   416  SELECT max(k), min(v) FROM kv HAVING max(min(v)) > 2
   417  
   418  query error argument of HAVING must be type bool, not type int
   419  SELECT max(k), min(v) FROM kv HAVING k
   420  
   421  # Expressions listed in the HAVING clause must conform to same validation as the SELECT clause (grouped or aggregated).
   422  query error column "k" must appear in the GROUP BY clause or be used in an aggregate function
   423  SELECT 3 FROM kv GROUP BY v HAVING k > 5
   424  
   425  # Special case for grouping on primary key.
   426  query I
   427  SELECT 3 FROM kv GROUP BY k HAVING v > 2
   428  ----
   429  3
   430  3
   431  
   432  query error column "k" must appear in the GROUP BY clause or be used in an aggregate function
   433  SELECT k FROM kv HAVING k > 7
   434  
   435  query error at or near ",": syntax error
   436  SELECT count(*, 1) FROM kv
   437  
   438  query I
   439  SELECT count(*)
   440  ----
   441  1
   442  
   443  query I
   444  SELECT count(k) from kv
   445  ----
   446  6
   447  
   448  query I
   449  SELECT count(1)
   450  ----
   451  1
   452  
   453  query I
   454  SELECT count(1) from kv
   455  ----
   456  6
   457  
   458  query error unknown signature: count\(int, int\)
   459  SELECT count(k, v) FROM kv
   460  
   461  query II
   462  SELECT v, count(k) FROM kv GROUP BY v ORDER BY v
   463  ----
   464  NULL 1
   465  2 3
   466  4 2
   467  
   468  query II
   469  SELECT v, count(k) FROM kv GROUP BY v ORDER BY v DESC
   470  ----
   471  4 2
   472  2 3
   473  NULL 1
   474  
   475  query II
   476  SELECT v, count(k) FROM kv GROUP BY v ORDER BY count(k) DESC
   477  ----
   478  2 3
   479  4 2
   480  NULL 1
   481  
   482  query II
   483  SELECT v, count(k) FROM kv GROUP BY v ORDER BY v-count(k)
   484  ----
   485  NULL 1
   486  2 3
   487  4 2
   488  
   489  query II
   490  SELECT v, count(k) FROM kv GROUP BY v ORDER BY 1 DESC
   491  ----
   492  4 2
   493  2 3
   494  NULL 1
   495  
   496  query III colnames
   497  SELECT count(*), count(k), count(kv.v) FROM kv
   498  ----
   499  count  count  count
   500  6      6      5
   501  
   502  query I
   503  SELECT count(kv.*) FROM kv
   504  ----
   505  6
   506  
   507  query III
   508  SELECT count(DISTINCT k), count(DISTINCT v), count(DISTINCT (v)) FROM kv
   509  ----
   510  6 2 2
   511  
   512  query TIII rowsort
   513  SELECT upper(s), count(DISTINCT k), count(DISTINCT v), count(DISTINCT (v)) FROM kv GROUP BY upper(s)
   514  ----
   515  A    3 2 2
   516  B    2 1 1
   517  NULL 1 0 0
   518  
   519  
   520  query I
   521  SELECT count((k, v)) FROM kv
   522  ----
   523  6
   524  
   525  query I
   526  SELECT count(DISTINCT (k, v)) FROM kv
   527  ----
   528  6
   529  
   530  query I
   531  SELECT count(DISTINCT (k, (v))) FROM kv
   532  ----
   533  6
   534  
   535  query I
   536  SELECT count(*) FROM kv a, kv b
   537  ----
   538  36
   539  
   540  query I
   541  SELECT count(DISTINCT a.*) FROM kv a, kv b
   542  ----
   543  6
   544  
   545  query I
   546  SELECT count((k, v)) FROM kv LIMIT 1
   547  ----
   548  6
   549  
   550  query I
   551  SELECT count((k, v)) FROM kv OFFSET 1
   552  ----
   553  
   554  query I
   555  SELECT count(k)+count(kv.v) FROM kv
   556  ----
   557  11
   558  
   559  query II
   560  SELECT count(NULL::int), count((NULL, NULL))
   561  ----
   562  0 1
   563  
   564  query IIII
   565  SELECT min(k), max(k), min(v), max(v) FROM kv
   566  ----
   567  1 8 2 4
   568  
   569  # Even if no input rows match, we expect a row (of nulls).
   570  query IIII
   571  SELECT min(k), max(k), min(v), max(v) FROM kv WHERE k > 8
   572  ----
   573  NULL NULL NULL NULL
   574  
   575  query TT
   576  SELECT array_agg(k), array_agg(s) FROM (SELECT k, s FROM kv ORDER BY k)
   577  ----
   578  {1,3,5,6,7,8}  {a,a,NULL,b,b,A}
   579  
   580  query T
   581  SELECT array_agg(k) || 1 FROM (SELECT k FROM kv ORDER BY k)
   582  ----
   583  {1,3,5,6,7,8,1}
   584  
   585  query T
   586  SELECT array_agg(s) FROM kv WHERE s IS NULL
   587  ----
   588  {NULL}
   589  
   590  query T
   591  SELECT json_agg(s) FROM kv WHERE s IS NULL
   592  ----
   593  [null]
   594  
   595  query T
   596  SELECT jsonb_agg(s) FROM kv WHERE s IS NULL
   597  ----
   598  [null]
   599  
   600  query RRRR
   601  SELECT avg(k), avg(v), sum(k), sum(v) FROM kv
   602  ----
   603  5 2.8 30 14
   604  
   605  query TTTT
   606  SELECT min(i), avg(i), max(i), sum(i) FROM kv
   607  ----
   608  00:00:00.001  7 mons 6 days 19:12:12.4002  3 years  3 years 4 days 00:01:02.001
   609  
   610  query RRRR
   611  SELECT avg(k::decimal), avg(v::decimal), sum(k::decimal), sum(v::decimal) FROM kv
   612  ----
   613  5 2.8 30 14
   614  
   615  query RRRR
   616  SELECT avg(DISTINCT k), avg(DISTINCT v), sum(DISTINCT k), sum(DISTINCT v) FROM kv
   617  ----
   618  5 3 30 6
   619  
   620  query R
   621  SELECT avg(k) * 2.0 + max(v)::DECIMAL FROM kv
   622  ----
   623  14.0
   624  
   625  # Verify things work with distsql when some of the nodes emit no results in the
   626  # local stage.
   627  query R
   628  SELECT avg(k) * 2.0 + max(v)::DECIMAL FROM kv WHERE w*2 = k
   629  ----
   630  14.0
   631  
   632  # Grouping columns can be eliminated, but should still return zero rows (i.e.
   633  # shouldn't use scalar GroupBy).
   634  query I
   635  SELECT max(v) FROM kv GROUP BY k HAVING k=100
   636  ----
   637  
   638  # Same query as above, but using scalar GroupBy (should return default row).
   639  query I
   640  SELECT max(v) FROM kv WHERE k=100
   641  ----
   642  NULL
   643  
   644  statement ok
   645  CREATE TABLE abc (
   646    a VARCHAR PRIMARY KEY,
   647    b FLOAT,
   648    c BOOLEAN,
   649    d DECIMAL
   650  )
   651  
   652  statement ok
   653  INSERT INTO abc VALUES ('one', 1.5, true, 5::decimal), ('two', 2.0, false, 1.1::decimal)
   654  
   655  query TRBR
   656  SELECT min(a), min(b), min(c), min(d) FROM abc
   657  ----
   658  one 1.5 false 1.1
   659  
   660  query TRBR
   661  SELECT max(a), max(b), max(c), max(d) FROM abc
   662  ----
   663  two 2 true 5
   664  
   665  query RRRR
   666  SELECT avg(b), sum(b), avg(d), sum(d) FROM abc
   667  ----
   668  1.75 3.5 3.05 6.1
   669  
   670  # Verify summing of intervals
   671  statement ok
   672  CREATE TABLE intervals (
   673    a INTERVAL PRIMARY KEY
   674  )
   675  
   676  statement ok
   677  INSERT INTO intervals VALUES (INTERVAL '1 year 2 months 3 days 4 seconds'), (INTERVAL '2 year 3 months 4 days 5 seconds'), (INTERVAL '10000ms')
   678  
   679  query T
   680  SELECT sum(a) FROM intervals
   681  ----
   682  3 years 5 mons 7 days 00:00:19
   683  
   684  
   685  query error unknown signature: avg\(varchar\)
   686  SELECT avg(a) FROM abc
   687  
   688  query error unknown signature: avg\(bool\)
   689  SELECT avg(c) FROM abc
   690  
   691  query error unknown signature: avg\(tuple{varchar, bool}\)
   692  SELECT avg((a,c)) FROM abc
   693  
   694  query error unknown signature: sum\(varchar\)
   695  SELECT sum(a) FROM abc
   696  
   697  query error unknown signature: sum\(bool\)
   698  SELECT sum(c) FROM abc
   699  
   700  query error unknown signature: sum\(tuple{varchar, bool}\)
   701  SELECT sum((a,c)) FROM abc
   702  
   703  statement ok
   704  CREATE TABLE xyz (
   705    x INT PRIMARY KEY,
   706    y INT,
   707    z FLOAT,
   708    w INT,
   709    INDEX xy (x, y),
   710    INDEX zyx (z, y, x),
   711    INDEX w (w),
   712    FAMILY (x),
   713    FAMILY (y),
   714    FAMILY (z)
   715  )
   716  
   717  statement ok
   718  INSERT INTO xyz VALUES (1, 2, 3.0, NULL), (4, 5, 6.0, 2), (7, NULL, 8.0, 3)
   719  
   720  query I
   721  SELECT min(x) FROM xyz
   722  ----
   723  1
   724  
   725  query I
   726  SELECT min(y) FROM xyz
   727  ----
   728  2
   729  
   730  query I
   731  SELECT min(w) FROM xyz
   732  ----
   733  2
   734  
   735  query I
   736  SELECT min(x) FROM xyz WHERE x in (0, 4, 7)
   737  ----
   738  4
   739  
   740  query I
   741  SELECT max(x) FROM xyz
   742  ----
   743  7
   744  
   745  query I
   746  SELECT min(y) FROM xyz WHERE x = 1
   747  ----
   748  2
   749  
   750  query I
   751  SELECT max(y) FROM xyz WHERE x = 1
   752  ----
   753  2
   754  
   755  query I
   756  SELECT min(y) FROM xyz WHERE x = 7
   757  ----
   758  NULL
   759  
   760  query I
   761  SELECT max(y) FROM xyz WHERE x = 7
   762  ----
   763  NULL
   764  
   765  query I
   766  SELECT min(x) FROM xyz WHERE (y, z) = (2, 3.0)
   767  ----
   768  1
   769  
   770  query I
   771  SELECT max(x) FROM xyz WHERE (z, y) = (3.0, 2)
   772  ----
   773  1
   774  
   775  # VARIANCE/STDDEV
   776  
   777  query RRR
   778  SELECT var_samp(x), variance(y::decimal), round(var_samp(z), 14) FROM xyz
   779  ----
   780  9 4.5 6.33333333333333
   781  
   782  query R
   783  SELECT variance(x) FROM xyz WHERE x = 10
   784  ----
   785  NULL
   786  
   787  query R
   788  SELECT variance(x) FROM xyz WHERE x = 1
   789  ----
   790  NULL
   791  
   792  query RRR
   793  SELECT stddev_samp(x), stddev(y::decimal), round(stddev_samp(z), 14) FROM xyz
   794  ----
   795  3  2.1213203435596425732  2.51661147842358
   796  
   797  query R
   798  SELECT stddev(x) FROM xyz WHERE x = 1
   799  ----
   800  NULL
   801  
   802  # Ensure subqueries don't trigger aggregation.
   803  query B
   804  SELECT x > (SELECT avg(0)) FROM xyz LIMIT 1
   805  ----
   806  true
   807  
   808  statement ok
   809  DROP TABLE xyz
   810  
   811  # Numerical stability test for VARIANCE/STDDEV.
   812  # See https://www.johndcook.com/blog/2008/09/28/theoretical-explanation-for-numerical-results.
   813  # Avoid using random() since we do not have the deterministic option to specify a pseudo-random seed yet.
   814  # Note under distsql, this is non-deterministic since the running variance/stddev algorithms depend on
   815  # the local sum of squared difference values which depend on how the data is distributed across the distsql nodes.
   816  statement ok
   817  CREATE TABLE mnop (
   818    m INT PRIMARY KEY,
   819    n FLOAT,
   820    o DECIMAL,
   821    p BIGINT
   822  )
   823  
   824  statement ok
   825  INSERT INTO mnop (m, n) SELECT i, (1e9 + i/2e4)::float FROM
   826    generate_series(1, 2e4) AS i(i)
   827  
   828  statement ok
   829  UPDATE mnop SET o = n::decimal, p = (n * 10)::bigint
   830  
   831  query RRR
   832  SELECT round(variance(n), 2), round(variance(n), 2), round(variance(p)) FROM mnop
   833  ----
   834  0.08 0.08 8
   835  
   836  
   837  query RRR
   838  SELECT round(stddev_samp(n), 2), round(stddev(n), 2), round(stddev_samp(p)) FROM mnop
   839  ----
   840  0.29 0.29 3
   841  
   842  query RRR
   843  SELECT avg(1::int)::float, avg(2::float)::float, avg(3::decimal)::float
   844  ----
   845  1 2 3
   846  
   847  query III
   848  SELECT count(2::int), count(3::float), count(4::decimal)
   849  ----
   850  1 1 1
   851  
   852  query RRR
   853  SELECT sum(1::int), sum(2::float), sum(3::decimal)
   854  ----
   855  1 2 3
   856  
   857  query RRR
   858  SELECT variance(1::int), variance(1::float), variance(1::decimal)
   859  ----
   860  NULL NULL NULL
   861  
   862  query RRR
   863  SELECT stddev(1::int), stddev_samp(1::float), stddev(1::decimal)
   864  ----
   865  NULL NULL NULL
   866  
   867  statement ok
   868  CREATE TABLE bits (b INT)
   869  
   870  query II
   871  SELECT bit_and(b), bit_or(b) FROM bits
   872  ----
   873  NULL NULL
   874  
   875  statement ok
   876  INSERT INTO bits VALUES (12), (25)
   877  
   878  query II
   879  SELECT bit_and(b), bit_or(b) FROM bits
   880  ----
   881  8 29
   882  
   883  statement ok
   884  INSERT INTO bits VALUES(105)
   885  
   886  query II
   887  SELECT bit_and(b), bit_or(b) FROM bits
   888  ----
   889  8 125
   890  
   891  statement ok
   892  INSERT INTO bits VALUES(NULL)
   893  
   894  query II
   895  SELECT bit_and(b), bit_or(b) FROM bits
   896  ----
   897  8 125
   898  
   899  statement ok
   900  CREATE TABLE bools (b BOOL)
   901  
   902  query BB
   903  SELECT bool_and(b), bool_or(b) FROM bools
   904  ----
   905  NULL NULL
   906  
   907  statement OK
   908  INSERT INTO bools VALUES (true), (true), (true)
   909  
   910  query BB
   911  SELECT bool_and(b), bool_or(b) FROM bools
   912  ----
   913  true true
   914  
   915  statement OK
   916  INSERT INTO bools VALUES (false), (false)
   917  
   918  query BB
   919  SELECT bool_and(b), bool_or(b) FROM bools
   920  ----
   921  false true
   922  
   923  statement OK
   924  DELETE FROM bools WHERE b
   925  
   926  query BB
   927  SELECT bool_and(b), bool_or(b) FROM bools
   928  ----
   929  false false
   930  
   931  query T
   932  SELECT concat_agg(s) FROM (SELECT s FROM kv ORDER BY k)
   933  ----
   934  aabbA
   935  
   936  query T
   937  SELECT json_agg(s) FROM (SELECT s FROM kv ORDER BY k)
   938  ----
   939  ["a", "a", null, "b", "b", "A"]
   940  
   941  query T
   942  SELECT jsonb_agg(s) FROM (SELECT s FROM kv ORDER BY k)
   943  ----
   944  ["a", "a", null, "b", "b", "A"]
   945  
   946  # Verify that FILTER works.
   947  
   948  statement ok
   949  CREATE TABLE filter_test (
   950    k INT,
   951    v INT,
   952    mark BOOL
   953  )
   954  
   955  statement OK
   956  INSERT INTO filter_test VALUES
   957  (1, 2, false),
   958  (3, 4, true),
   959  (5, NULL, true),
   960  (6, 2, true),
   961  (7, 2, true),
   962  (8, 4, true),
   963  (NULL, 4, true)
   964  
   965  # FILTER should eliminate some results.
   966  query II rowsort
   967  SELECT v, count(*) FILTER (WHERE k > 5) FROM filter_test GROUP BY v
   968  ----
   969  2 2
   970  4 1
   971  NULL 0
   972  
   973  # Test multiple filters
   974  query IBIII rowsort
   975  SELECT v, mark, count(*) FILTER (WHERE k > 5), count(*), max(k) FILTER (WHERE k < 8) FROM filter_test GROUP BY v, mark
   976  ----
   977  2 false 0 1 1
   978  2 true 2 2 7
   979  4 true 1 3 3
   980  NULL true 0 1 5
   981  
   982  query error FILTER specified but abs\(\) is not an aggregate function
   983  SELECT k, abs(k) FILTER (WHERE k=1) FROM kv
   984  
   985  query error at or near "filter": syntax error
   986  SELECT k FILTER (WHERE k=1) FROM kv GROUP BY k
   987  
   988  query error aggregate functions are not allowed in FILTER
   989  SELECT v, count(*) FILTER (WHERE count(*) > 5) FROM filter_test GROUP BY v
   990  
   991  # Tests with * inside GROUP BY.
   992  query I
   993  SELECT 1 FROM kv GROUP BY kv.*
   994  ----
   995  1
   996  1
   997  1
   998  1
   999  1
  1000  1
  1001  
  1002  query R rowsort
  1003  SELECT sum(abc.d) FROM kv JOIN abc ON kv.k >= abc.d GROUP BY kv.*
  1004  ----
  1005  1.1
  1006  6.1
  1007  6.1
  1008  6.1
  1009  6.1
  1010  
  1011  # opt_test is used for tests around the single-row optimization for MIN/MAX.
  1012  statement ok
  1013  CREATE TABLE opt_test (k INT PRIMARY KEY, v INT, INDEX v(v))
  1014  
  1015  statement ok
  1016  INSERT INTO opt_test VALUES (1, NULL), (2, 10), (3, NULL), (4, 5)
  1017  
  1018  # Verify that we correctly add the v IS NOT NULL constraint (which restricts the span).
  1019  # Without the "v IS NOT NULL" constraint, this result would incorrectly be NULL.
  1020  query I
  1021  SELECT min(v) FROM opt_test
  1022  ----
  1023  5
  1024  
  1025  # Cross-check against a query without this optimization.
  1026  query I
  1027  SELECT min(v) FROM opt_test@primary
  1028  ----
  1029  5
  1030  
  1031  # Repeat test when there is an existing filter.
  1032  query I
  1033  SELECT min(v) FROM opt_test WHERE k <> 4
  1034  ----
  1035  10
  1036  
  1037  # Verify that we don't use the optimization if there is a GROUP BY.
  1038  query I rowsort
  1039  SELECT min(v) FROM opt_test GROUP BY k
  1040  ----
  1041  NULL
  1042  NULL
  1043  5
  1044  10
  1045  
  1046  query I rowsort
  1047  SELECT max(v) FROM opt_test GROUP BY k
  1048  ----
  1049  NULL
  1050  NULL
  1051  5
  1052  10
  1053  
  1054  statement ok
  1055  CREATE TABLE xor_bytes (a bytes, b int, c int)
  1056  
  1057  statement ok
  1058  INSERT INTO xor_bytes VALUES
  1059    (b'\x01\x01', 1, 3),
  1060    (b'\x02\x01', 1, 1),
  1061    (b'\x04\x01', 2, -5),
  1062    (b'\x08\x01', 2, -1),
  1063    (b'\x10\x01', 2, 0)
  1064  
  1065  query TI
  1066  SELECT to_hex(xor_agg(a)), xor_agg(c) FROM xor_bytes
  1067  ----
  1068  1f01 6
  1069  
  1070  query TII
  1071  SELECT to_hex(xor_agg(a)), b, xor_agg(c) FROM xor_bytes GROUP BY b ORDER BY b
  1072  ----
  1073  0300  1   2
  1074  1c01  2   4
  1075  
  1076  statement error arguments to xor must all be the same length
  1077  SELECT xor_agg(i) FROM (VALUES (b'\x01'), (b'\x01\x01')) AS a(i)
  1078  
  1079  query BB
  1080  SELECT max(true), min(true)
  1081  ----
  1082  true
  1083  true
  1084  
  1085  # Grouping and rendering tuples.
  1086  statement OK
  1087  CREATE TABLE ab (
  1088    a INT PRIMARY KEY,
  1089    b INT,
  1090    FAMILY (a),
  1091    FAMILY (b)
  1092  )
  1093  
  1094  statement ok
  1095  INSERT INTO ab(a,b) VALUES (1,2), (3,4);
  1096    CREATE TABLE xy(x STRING, y STRING);
  1097    INSERT INTO xy(x, y) VALUES ('a', 'b'), ('c', 'd')
  1098  
  1099  # Grouping and rendering tuples.
  1100  query T rowsort
  1101  SELECT (b, a) FROM ab GROUP BY (b, a)
  1102  ----
  1103  (2,1)
  1104  (4,3)
  1105  
  1106  query TT rowsort
  1107  SELECT min(y), (b, a)
  1108   FROM ab, xy GROUP BY (x, (a, b))
  1109  ----
  1110  b  (2,1)
  1111  d  (2,1)
  1112  b  (4,3)
  1113  d  (4,3)
  1114  
  1115  # Test that ordering on GROUP BY columns is maintained.
  1116  statement ok
  1117  CREATE TABLE group_ord (
  1118    x INT PRIMARY KEY,
  1119    y INT,
  1120    z INT,
  1121    INDEX foo(z)
  1122  )
  1123  
  1124  statement ok
  1125  INSERT INTO group_ord VALUES
  1126  (1, 2, 3),
  1127  (3, 4, 5),
  1128  (5, NULL, 5),
  1129  (6, 2, 3),
  1130  (7, 2, 2),
  1131  (8, 4, 2)
  1132  
  1133  # The ordering is on all the GROUP BY columns, and isn't preserved after the
  1134  # aggregation.
  1135  query II rowsort
  1136  SELECT x, max(y) FROM group_ord GROUP BY x
  1137  ----
  1138  1  2
  1139  3  4
  1140  5  NULL
  1141  6  2
  1142  7  2
  1143  8  4
  1144  
  1145  # The ordering is on all the GROUP BY columns, and is preserved after the
  1146  # aggregation.
  1147  query II
  1148  SELECT x, max(y) FROM group_ord GROUP BY x ORDER BY x
  1149  ----
  1150  1  2
  1151  3  4
  1152  5  NULL
  1153  6  2
  1154  7  2
  1155  8  4
  1156  
  1157  # The ordering is on some of the GROUP BY columns, and isn't preserved after
  1158  # the aggregation.
  1159  query III rowsort
  1160  SELECT z, x, max(y) FROM group_ord GROUP BY x, z
  1161  ----
  1162  5  3  4
  1163  3  6  2
  1164  3  1  2
  1165  5  5  NULL
  1166  2  7  2
  1167  2  8  4
  1168  
  1169  # The ordering is on some of the GROUP BY columns, and is preserved after
  1170  # the aggregation.
  1171  query III
  1172  SELECT z, x, max(y) FROM group_ord GROUP BY x, z ORDER BY x
  1173  ----
  1174  3  1  2
  1175  5  3  4
  1176  5  5  NULL
  1177  3  6  2
  1178  2  7  2
  1179  2  8  4
  1180  
  1181  # If the underlying ordering isn't from the primary index, it needs to be hinted
  1182  # for now.
  1183  query II rowsort
  1184  SELECT z, max(y) FROM group_ord@foo GROUP BY z
  1185  ----
  1186  5  4
  1187  2  4
  1188  3  2
  1189  
  1190  # Test that a merge join is used on two aggregate subqueries with orderings on
  1191  # the GROUP BY columns. Note that an ORDER BY is not necessary on the
  1192  # subqueries.
  1193  query IIII rowsort
  1194  SELECT * FROM (SELECT x, max(y) FROM group_ord GROUP BY x) JOIN (SELECT z, min(y) FROM group_ord@foo GROUP BY z) ON x = z
  1195  ----
  1196  5  NULL  5  4
  1197  3  4     3  2
  1198  
  1199  # Regression test for #23798 until #10495 is fixed.
  1200  statement error function reserved for internal use
  1201  SELECT final_variance(1.2, 1.2, 123) FROM kv
  1202  
  1203  # Regression test for #25533 (crash when propagating filter through GROUP BY).
  1204  query I
  1205  SELECT 1 FROM kv GROUP BY v, w::DECIMAL HAVING w::DECIMAL > 1
  1206  ----
  1207  1
  1208  1
  1209  1
  1210  1
  1211  1
  1212  
  1213  # Regression test for distsql aggregator crash when using hash aggregation.
  1214  query IT rowsort
  1215  SELECT v, array_agg('a') FROM kv GROUP BY v
  1216  ----
  1217  2     {a,a,a}
  1218  4     {a,a}
  1219  NULL  {a}
  1220  
  1221  # Regression test for #26419
  1222  query I
  1223  SELECT 123 FROM kv ORDER BY max(v)
  1224  ----
  1225  123
  1226  
  1227  subtest statistics
  1228  
  1229  statement OK
  1230  CREATE TABLE statistics_agg_test (
  1231    y float,
  1232    x float,
  1233    int_y int,
  1234    int_x int
  1235  )
  1236  
  1237  statement OK
  1238  INSERT INTO statistics_agg_test (y, x, int_y, int_x) VALUES
  1239    (1.0,   10.0,    1,   10),
  1240    (2.0,   25.0,    2,   25),
  1241    (2.0,   25.0,    2,   25),
  1242    (3.0,   40.0,    3,   40),
  1243    (3.0,   40.0,    3,   40),
  1244    (3.0,   40.0,    3,   40),
  1245    (4.0,  100.0,    4,  100),
  1246    (4.0,  100.0,    4,  100),
  1247    (4.0,  100.0,    4,  100),
  1248    (4.0,  100.0,    4,  100),
  1249    (NULL,  NULL, NULL, NULL)
  1250  
  1251  query RRRR
  1252  SELECT corr(y, x)::decimal, corr(int_y, int_x)::decimal, corr(y, int_x)::decimal, corr(int_y, x)::decimal FROM statistics_agg_test
  1253  ----
  1254  0.933007822647968 0.933007822647968 0.933007822647968 0.933007822647968
  1255  
  1256  query R
  1257  SELECT corr(DISTINCT y, x)::decimal FROM statistics_agg_test
  1258  ----
  1259  0.9326733179802503
  1260  
  1261  query R
  1262  SELECT CAST(corr(DISTINCT y, x) FILTER (WHERE x > 3 AND y < 30) AS decimal) FROM statistics_agg_test
  1263  ----
  1264  0.9326733179802503
  1265  
  1266  query error pq: unknown signature: corr\(string, string\)
  1267  SELECT corr(y::string, x::string) FROM statistics_agg_test
  1268  
  1269  statement OK
  1270  INSERT INTO statistics_agg_test (y, x, int_y, int_x) VALUES
  1271    (1.797693134862315708145274237317043567981e+308, 0, 0, 0)
  1272  
  1273  query error float out of range
  1274  SELECT corr(y, x)::decimal, corr(int_y, int_x)::decimal FROM statistics_agg_test
  1275  
  1276  statement OK
  1277  TRUNCATE statistics_agg_test
  1278  
  1279  statement OK
  1280  INSERT INTO statistics_agg_test (y, x, int_y, int_x) VALUES
  1281    (1.0,  10.0, 1, 10),
  1282    (2.0,  20.0, 2, 20)
  1283  
  1284  query RR
  1285  SELECT corr(y, x)::decimal, corr(int_y, int_x)::decimal FROM statistics_agg_test
  1286  ----
  1287  1 1
  1288  
  1289  statement OK
  1290  TRUNCATE statistics_agg_test
  1291  
  1292  statement OK
  1293  INSERT INTO statistics_agg_test (y, x, int_y, int_x) VALUES
  1294    (1.0,  10.0, 1,  10),
  1295    (2.0, -20.0, 2, -20)
  1296  
  1297  query RR
  1298  SELECT corr(y, x)::decimal, corr(int_y, int_x)::decimal FROM statistics_agg_test
  1299  ----
  1300  -1 -1
  1301  
  1302  statement OK
  1303  TRUNCATE statistics_agg_test
  1304  
  1305  statement OK
  1306  INSERT INTO statistics_agg_test (y, x, int_y, int_x) VALUES
  1307    (1.0, -1.0, 1, -1),
  1308    (1.0,  1.0, 1,  1)
  1309  
  1310  query RR
  1311  SELECT corr(y, x)::decimal, corr(int_y, int_x)::decimal FROM statistics_agg_test
  1312  ----
  1313  NULL NULL
  1314  
  1315  subtest string_agg
  1316  
  1317  statement OK
  1318  CREATE TABLE string_agg_test (
  1319    id INT PRIMARY KEY,
  1320    company_id INT,
  1321    employee STRING
  1322  )
  1323  
  1324  query IT colnames
  1325  SELECT company_id, string_agg(employee, ',')
  1326  FROM string_agg_test
  1327  GROUP BY company_id
  1328  ORDER BY company_id;
  1329  ----
  1330  company_id  string_agg
  1331  
  1332  query IT colnames
  1333  SELECT company_id, string_agg(employee::BYTES, b',')
  1334  FROM string_agg_test
  1335  GROUP BY company_id
  1336  ORDER BY company_id;
  1337  ----
  1338  company_id  string_agg
  1339  
  1340  query IT colnames
  1341  SELECT company_id, string_agg(employee, NULL)
  1342  FROM string_agg_test
  1343  GROUP BY company_id
  1344  ORDER BY company_id;
  1345  ----
  1346  company_id  string_agg
  1347  
  1348  query IT colnames
  1349  SELECT company_id, string_agg(employee::BYTES, NULL)
  1350  FROM string_agg_test
  1351  GROUP BY company_id
  1352  ORDER BY company_id;
  1353  ----
  1354  company_id  string_agg
  1355  
  1356  statement OK
  1357  INSERT INTO string_agg_test VALUES
  1358    (1, 1, 'A'),
  1359    (2, 2, 'B'),
  1360    (3, 3, 'C'),
  1361    (4, 4, 'D'),
  1362    (5, 3, 'C'),
  1363    (6, 4, 'D'),
  1364    (7, 4, 'D'),
  1365    (8, 4, 'D'),
  1366    (9, 3, 'C'),
  1367    (10, 2, 'B')
  1368  
  1369  # This is a bit strange but the same behavior as PostgreSQL.
  1370  query IT rowsort
  1371  SELECT company_id, string_agg(employee, employee)
  1372  FROM string_agg_test
  1373  GROUP BY company_id;
  1374  ----
  1375  1           A
  1376  2           BBB
  1377  3           CCCCC
  1378  4           DDDDDDD
  1379  
  1380  query IT colnames
  1381  SELECT company_id, string_agg(employee, ',')
  1382  FROM string_agg_test
  1383  GROUP BY company_id
  1384  ORDER BY company_id;
  1385  ----
  1386  company_id  string_agg
  1387  1           A
  1388  2           B,B
  1389  3           C,C,C
  1390  4           D,D,D,D
  1391  
  1392  query IT colnames
  1393  SELECT company_id, string_agg(DISTINCT employee, ',')
  1394  FROM string_agg_test
  1395  GROUP BY company_id
  1396  ORDER BY company_id;
  1397  ----
  1398  company_id  string_agg
  1399  1           A
  1400  2           B
  1401  3           C
  1402  4           D
  1403  
  1404  query IT colnames
  1405  SELECT company_id, string_agg(employee::BYTES, b',')
  1406  FROM string_agg_test
  1407  GROUP BY company_id
  1408  ORDER BY company_id;
  1409  ----
  1410  company_id  string_agg
  1411  1           A
  1412  2           B,B
  1413  3           C,C,C
  1414  4           D,D,D,D
  1415  
  1416  query IT colnames
  1417  SELECT company_id, string_agg(employee, '')
  1418  FROM string_agg_test
  1419  GROUP BY company_id
  1420  ORDER BY company_id;
  1421  ----
  1422  company_id  string_agg
  1423  1           A
  1424  2           BB
  1425  3           CCC
  1426  4           DDDD
  1427  
  1428  query IT colnames
  1429  SELECT company_id, string_agg(employee::BYTES, b'')
  1430  FROM string_agg_test
  1431  GROUP BY company_id
  1432  ORDER BY company_id;
  1433  ----
  1434  company_id  string_agg
  1435  1           A
  1436  2           BB
  1437  3           CCC
  1438  4           DDDD
  1439  
  1440  query IT colnames
  1441  SELECT company_id, string_agg(employee, NULL)
  1442  FROM string_agg_test
  1443  GROUP BY company_id
  1444  ORDER BY company_id;
  1445  ----
  1446  company_id  string_agg
  1447  1           A
  1448  2           BB
  1449  3           CCC
  1450  4           DDDD
  1451  
  1452  query IT colnames
  1453  SELECT company_id, string_agg(employee::BYTES, NULL)
  1454  FROM string_agg_test
  1455  GROUP BY company_id
  1456  ORDER BY company_id;
  1457  ----
  1458  company_id  string_agg
  1459  1           A
  1460  2           BB
  1461  3           CCC
  1462  4           DDDD
  1463  
  1464  query IT colnames
  1465  SELECT company_id, string_agg(NULL::STRING, ',')
  1466  FROM string_agg_test
  1467  GROUP BY company_id
  1468  ORDER BY company_id;
  1469  ----
  1470  company_id  string_agg
  1471  1           NULL
  1472  2           NULL
  1473  3           NULL
  1474  4           NULL
  1475  
  1476  query IT colnames
  1477  SELECT company_id, string_agg(NULL::BYTES, b',')
  1478  FROM string_agg_test
  1479  GROUP BY company_id
  1480  ORDER BY company_id;
  1481  ----
  1482  company_id  string_agg
  1483  1           NULL
  1484  2           NULL
  1485  3           NULL
  1486  4           NULL
  1487  
  1488  query IT colnames
  1489  SELECT company_id, string_agg(NULL::STRING, NULL)
  1490  FROM string_agg_test
  1491  GROUP BY company_id
  1492  ORDER BY company_id;
  1493  ----
  1494  company_id  string_agg
  1495  1           NULL
  1496  2           NULL
  1497  3           NULL
  1498  4           NULL
  1499  
  1500  query IT colnames
  1501  SELECT company_id, string_agg(NULL::BYTES, NULL)
  1502  FROM string_agg_test
  1503  GROUP BY company_id
  1504  ORDER BY company_id;
  1505  ----
  1506  company_id  string_agg
  1507  1           NULL
  1508  2           NULL
  1509  3           NULL
  1510  4           NULL
  1511  
  1512  query error pq: ambiguous call: string_agg\(unknown, unknown\)
  1513  SELECT company_id, string_agg(NULL, NULL)
  1514  FROM string_agg_test
  1515  GROUP BY company_id
  1516  ORDER BY company_id;
  1517  
  1518  # Now test the window function version of string_agg.
  1519  
  1520  query IT colnames
  1521  SELECT company_id, string_agg(employee, ',')
  1522  OVER (PARTITION BY company_id ORDER BY id)
  1523  FROM string_agg_test
  1524  ORDER BY company_id, id;
  1525  ----
  1526  company_id  string_agg
  1527  1           A
  1528  2           B
  1529  2           B,B
  1530  3           C
  1531  3           C,C
  1532  3           C,C,C
  1533  4           D
  1534  4           D,D
  1535  4           D,D,D
  1536  4           D,D,D,D
  1537  
  1538  query IT colnames
  1539  SELECT company_id, string_agg(employee::BYTES, b',')
  1540  OVER (PARTITION BY company_id ORDER BY id)
  1541  FROM string_agg_test
  1542  ORDER BY company_id, id;
  1543  ----
  1544  company_id  string_agg
  1545  1           A
  1546  2           B
  1547  2           B,B
  1548  3           C
  1549  3           C,C
  1550  3           C,C,C
  1551  4           D
  1552  4           D,D
  1553  4           D,D,D
  1554  4           D,D,D,D
  1555  
  1556  query IT colnames
  1557  SELECT company_id, string_agg(employee, '')
  1558  OVER (PARTITION BY company_id ORDER BY id)
  1559  FROM string_agg_test
  1560  ORDER BY company_id, id;
  1561  ----
  1562  company_id  string_agg
  1563  1           A
  1564  2           B
  1565  2           BB
  1566  3           C
  1567  3           CC
  1568  3           CCC
  1569  4           D
  1570  4           DD
  1571  4           DDD
  1572  4           DDDD
  1573  
  1574  query IT colnames
  1575  SELECT company_id, string_agg(employee::BYTES, b'')
  1576  OVER (PARTITION BY company_id ORDER BY id)
  1577  FROM string_agg_test
  1578  ORDER BY company_id, id;
  1579  ----
  1580  company_id  string_agg
  1581  1           A
  1582  2           B
  1583  2           BB
  1584  3           C
  1585  3           CC
  1586  3           CCC
  1587  4           D
  1588  4           DD
  1589  4           DDD
  1590  4           DDDD
  1591  
  1592  query IT colnames
  1593  SELECT company_id, string_agg(employee, NULL)
  1594  OVER (PARTITION BY company_id ORDER BY id)
  1595  FROM string_agg_test
  1596  ORDER BY company_id, id;
  1597  ----
  1598  company_id  string_agg
  1599  1           A
  1600  2           B
  1601  2           BB
  1602  3           C
  1603  3           CC
  1604  3           CCC
  1605  4           D
  1606  4           DD
  1607  4           DDD
  1608  4           DDDD
  1609  
  1610  query IT colnames
  1611  SELECT company_id, string_agg(employee::BYTES, NULL)
  1612  OVER (PARTITION BY company_id ORDER BY id)
  1613  FROM string_agg_test
  1614  ORDER BY company_id, id;
  1615  ----
  1616  company_id  string_agg
  1617  1           A
  1618  2           B
  1619  2           BB
  1620  3           C
  1621  3           CC
  1622  3           CCC
  1623  4           D
  1624  4           DD
  1625  4           DDD
  1626  4           DDDD
  1627  
  1628  query IT colnames
  1629  SELECT company_id, string_agg(NULL::STRING, employee)
  1630  OVER (PARTITION BY company_id ORDER BY id)
  1631  FROM string_agg_test
  1632  ORDER BY company_id, id;
  1633  ----
  1634  company_id  string_agg
  1635  1           NULL
  1636  2           NULL
  1637  2           NULL
  1638  3           NULL
  1639  3           NULL
  1640  3           NULL
  1641  4           NULL
  1642  4           NULL
  1643  4           NULL
  1644  4           NULL
  1645  
  1646  query IT colnames
  1647  SELECT company_id, string_agg(NULL::BYTES, employee::BYTES)
  1648  OVER (PARTITION BY company_id ORDER BY id)
  1649  FROM string_agg_test
  1650  ORDER BY company_id, id;
  1651  ----
  1652  company_id  string_agg
  1653  1           NULL
  1654  2           NULL
  1655  2           NULL
  1656  3           NULL
  1657  3           NULL
  1658  3           NULL
  1659  4           NULL
  1660  4           NULL
  1661  4           NULL
  1662  4           NULL
  1663  
  1664  query IT colnames
  1665  SELECT company_id, string_agg(NULL::STRING, NULL)
  1666  OVER (PARTITION BY company_id ORDER BY id)
  1667  FROM string_agg_test
  1668  ORDER BY company_id, id;
  1669  ----
  1670  company_id  string_agg
  1671  1           NULL
  1672  2           NULL
  1673  2           NULL
  1674  3           NULL
  1675  3           NULL
  1676  3           NULL
  1677  4           NULL
  1678  4           NULL
  1679  4           NULL
  1680  4           NULL
  1681  
  1682  query IT colnames
  1683  SELECT company_id, string_agg(NULL::BYTES, NULL)
  1684  OVER (PARTITION BY company_id ORDER BY id)
  1685  FROM string_agg_test
  1686  ORDER BY company_id, id;
  1687  ----
  1688  company_id  string_agg
  1689  1           NULL
  1690  2           NULL
  1691  2           NULL
  1692  3           NULL
  1693  3           NULL
  1694  3           NULL
  1695  4           NULL
  1696  4           NULL
  1697  4           NULL
  1698  4           NULL
  1699  
  1700  query IT colnames
  1701  SELECT company_id, string_agg(NULL, NULL::STRING)
  1702  OVER (PARTITION BY company_id ORDER BY id)
  1703  FROM string_agg_test
  1704  ORDER BY company_id, id;
  1705  ----
  1706  company_id  string_agg
  1707  1           NULL
  1708  2           NULL
  1709  2           NULL
  1710  3           NULL
  1711  3           NULL
  1712  3           NULL
  1713  4           NULL
  1714  4           NULL
  1715  4           NULL
  1716  4           NULL
  1717  
  1718  query IT colnames
  1719  SELECT company_id, string_agg(NULL, NULL::BYTES)
  1720  OVER (PARTITION BY company_id ORDER BY id)
  1721  FROM string_agg_test
  1722  ORDER BY company_id, id;
  1723  ----
  1724  company_id  string_agg
  1725  1           NULL
  1726  2           NULL
  1727  2           NULL
  1728  3           NULL
  1729  3           NULL
  1730  3           NULL
  1731  4           NULL
  1732  4           NULL
  1733  4           NULL
  1734  4           NULL
  1735  
  1736  query error pq: ambiguous call: string_agg\(unknown, unknown\)
  1737  SELECT company_id, string_agg(NULL, NULL)
  1738  OVER (PARTITION BY company_id ORDER BY id)
  1739  FROM string_agg_test
  1740  ORDER BY company_id, id;
  1741  
  1742  query IT colnames
  1743  SELECT company_id, string_agg(employee, lower(employee))
  1744  OVER (PARTITION BY company_id)
  1745  FROM string_agg_test
  1746  ORDER BY company_id, id;
  1747  ----
  1748  company_id  string_agg
  1749  1           A
  1750  2           BbB
  1751  2           BbB
  1752  3           CcCcC
  1753  3           CcCcC
  1754  3           CcCcC
  1755  4           DdDdDdD
  1756  4           DdDdDdD
  1757  4           DdDdDdD
  1758  4           DdDdDdD
  1759  
  1760  query IT colnames
  1761  SELECT company_id, string_agg(lower(employee), employee)
  1762  OVER (PARTITION BY company_id)
  1763  FROM string_agg_test
  1764  ORDER BY company_id, id;
  1765  ----
  1766  company_id  string_agg
  1767  1           a
  1768  2           bBb
  1769  2           bBb
  1770  3           cCcCc
  1771  3           cCcCc
  1772  3           cCcCc
  1773  4           dDdDdDd
  1774  4           dDdDdDd
  1775  4           dDdDdDd
  1776  4           dDdDdDd
  1777  
  1778  statement error pq: unknown signature: string_agg\(string, string, string\)
  1779  SELECT company_id, string_agg(employee, employee, employee)
  1780  OVER (PARTITION BY company_id)
  1781  FROM string_agg_test
  1782  ORDER BY company_id, id;
  1783  
  1784  query error pq: unknown signature: string_agg\(string\)
  1785  SELECT company_id, string_agg(employee)
  1786  OVER (PARTITION BY company_id)
  1787  FROM string_agg_test
  1788  ORDER BY company_id, id;
  1789  
  1790  query error pq: unknown signature: string_agg\(string, string, string, string\)
  1791  SELECT company_id, string_agg(employee, 'foo', employee, 'bar')
  1792  OVER (PARTITION BY company_id)
  1793  FROM string_agg_test
  1794  ORDER BY company_id, id;
  1795  
  1796  statement OK
  1797  TRUNCATE string_agg_test
  1798  
  1799  statement OK
  1800  INSERT INTO string_agg_test VALUES
  1801    (1, 1, 'A'),
  1802    (2, 1, 'B'),
  1803    (3, 1, 'C'),
  1804    (4, 1, 'D')
  1805  
  1806  query IT colnames
  1807  SELECT e.company_id, string_agg(e.employee, ', ')
  1808  FROM (
  1809    SELECT employee, company_id
  1810    FROM string_agg_test
  1811    ORDER BY employee
  1812    ) AS e
  1813  GROUP BY e.company_id
  1814  ORDER BY e.company_id;
  1815  ----
  1816  company_id  string_agg
  1817  1           A, B, C, D
  1818  
  1819  query IT colnames
  1820  SELECT e.company_id, string_agg(e.employee, b', ')
  1821  FROM (
  1822    SELECT employee::BYTES, company_id
  1823    FROM string_agg_test
  1824    ORDER BY employee
  1825    ) AS e
  1826  GROUP BY e.company_id
  1827  ORDER BY e.company_id;
  1828  ----
  1829  company_id  string_agg
  1830  1           A, B, C, D
  1831  
  1832  query IT colnames
  1833  SELECT e.company_id, string_agg(e.employee, ', ')
  1834  FROM (
  1835    SELECT employee, company_id
  1836    FROM string_agg_test
  1837    ORDER BY employee DESC
  1838    ) AS e
  1839  GROUP BY e.company_id
  1840  ORDER BY e.company_id;
  1841  ----
  1842  company_id  string_agg
  1843  1           D, C, B, A
  1844  
  1845  query IT colnames
  1846  SELECT e.company_id, string_agg(e.employee, b', ')
  1847  FROM (
  1848    SELECT employee::BYTES, company_id
  1849    FROM string_agg_test
  1850    ORDER BY employee DESC
  1851    ) AS e
  1852  GROUP BY e.company_id
  1853  ORDER BY e.company_id;
  1854  ----
  1855  company_id  string_agg
  1856  1           D, C, B, A
  1857  
  1858  query IT colnames
  1859  SELECT e.company_id, string_agg(e.employee, NULL)
  1860  FROM (
  1861    SELECT employee, company_id
  1862    FROM string_agg_test
  1863    ORDER BY employee DESC
  1864    ) AS e
  1865  GROUP BY e.company_id
  1866  ORDER BY e.company_id;
  1867  ----
  1868  company_id  string_agg
  1869  1           DCBA
  1870  
  1871  query IT colnames
  1872  SELECT e.company_id, string_agg(e.employee, NULL)
  1873  FROM (
  1874    SELECT employee::BYTES, company_id
  1875    FROM string_agg_test
  1876    ORDER BY employee DESC
  1877    ) AS e
  1878  GROUP BY e.company_id
  1879  ORDER BY e.company_id;
  1880  ----
  1881  company_id  string_agg
  1882  1           DCBA
  1883  
  1884  statement OK
  1885  DROP TABLE string_agg_test
  1886  
  1887  # Regression test for #28836.
  1888  
  1889  query T
  1890  SELECT string_agg('foo', CAST ((SELECT NULL) AS BYTES)) OVER ();
  1891  ----
  1892  foo
  1893  
  1894  # Regression test for #30166.
  1895  query T
  1896  SELECT array_agg(generate_series(1, 2))
  1897  ----
  1898  {1,2}
  1899  
  1900  # Regression test for #31882.
  1901  
  1902  statement ok
  1903  CREATE TABLE uvw (u INT, v INT, w INT, INDEX uvw(u, v, w))
  1904  
  1905  statement ok
  1906  INSERT INTO uvw VALUES (1, 2, 3), (1, 2, 3), (3, 2, 1), (3, 2, 3)
  1907  
  1908  query IIT rowsort
  1909  SELECT u, v, array_agg(w) AS s FROM (SELECT * FROM uvw ORDER BY w) GROUP BY u, v
  1910  ----
  1911  3  2  {1,3}
  1912  1  2  {3,3}
  1913  
  1914  # Regression test for #36433: don't panic with count_agg if a post-render produces an error.
  1915  
  1916  query error lpad
  1917  SELECT count(*)::TEXT||lpad('foo', 23984729388383834723984) FROM (VALUES(1));
  1918  
  1919  statement ok
  1920  CREATE TABLE tab (
  1921    col1 INT PRIMARY KEY,
  1922    col2 INT,
  1923    col3 STRING
  1924  )
  1925  
  1926  # Ordered aggregations when there are no rows.
  1927  query I
  1928  SELECT array_agg(col1 ORDER BY col2) FROM TAB
  1929  ----
  1930  NULL
  1931  
  1932  statement ok
  1933  INSERT INTO tab VALUES (-3, 7, 'a'), (-2, 6, 'a'), (-1, 5, 'a'), (0, 7, 'b'), (1, 5, 'b'), (2, 6, 'b')
  1934  
  1935  query T colnames
  1936  SELECT array_agg(col1 ORDER BY col1) FROM tab
  1937  ----
  1938  array_agg
  1939  {-3,-2,-1,0,1,2}
  1940  
  1941  query T colnames
  1942  SELECT array_agg(col1 ORDER BY col2*100+col1) FROM tab
  1943  ----
  1944  array_agg
  1945  {-1,1,-2,2,-3,0}
  1946  
  1947  query T colnames
  1948  SELECT json_agg(col1 ORDER BY col1) FROM tab
  1949  ----
  1950  json_agg
  1951  [-3, -2, -1, 0, 1, 2]
  1952  
  1953  query T colnames
  1954  SELECT jsonb_agg(col1 ORDER BY col1) FROM tab
  1955  ----
  1956  jsonb_agg
  1957  [-3, -2, -1, 0, 1, 2]
  1958  
  1959  query T colnames
  1960  SELECT jsonb_agg(col1 ORDER BY col2, col1) FROM tab
  1961  ----
  1962  jsonb_agg
  1963  [-1, 1, -2, 2, -3, 0]
  1964  
  1965  query T colnames
  1966  SELECT concat_agg(col3 ORDER BY col1) FROM tab
  1967  ----
  1968  concat_agg
  1969  aaabbb
  1970  
  1971  query T colnames
  1972  SELECT concat_agg(col3 ORDER BY col1 DESC) FROM tab
  1973  ----
  1974  concat_agg
  1975  bbbaaa
  1976  
  1977  query T colnames
  1978  SELECT string_agg(col3, ', ' ORDER BY col3) FROM tab
  1979  ----
  1980  string_agg
  1981  a, a, a, b, b, b
  1982  
  1983  query T colnames
  1984  SELECT string_agg(col3, ', ' ORDER BY col3 DESC) FROM tab
  1985  ----
  1986  string_agg
  1987  b, b, b, a, a, a
  1988  
  1989  query TTT colnames
  1990  SELECT array_agg(col1 ORDER BY col1), array_agg(col1 ORDER BY col2, col1), array_agg(col1 ORDER BY col3, col1) FROM tab
  1991  ----
  1992  array_agg         array_agg         array_agg
  1993  {-3,-2,-1,0,1,2}  {-1,1,-2,2,-3,0}  {-3,-2,-1,0,1,2}
  1994  
  1995  query TTT colnames
  1996  SELECT array_agg(col1 ORDER BY col1), array_agg(col1 ORDER BY col2, col1), col3 FROM tab GROUP BY col3 ORDER BY col3
  1997  ----
  1998  array_agg   array_agg   col3
  1999  {-3,-2,-1}  {-1,-2,-3}  a
  2000  {0,1,2}     {1,2,0}     b
  2001  
  2002  query TTII colnames
  2003  SELECT array_agg(col1 ORDER BY col1), array_agg(col1 ORDER BY col2, col1), count(col3), count(*) FROM tab
  2004  ----
  2005  array_agg         array_agg         count  count
  2006  {-3,-2,-1,0,1,2}  {-1,1,-2,2,-3,0}  6      6
  2007  
  2008  query TT colnames
  2009  SELECT array_agg(col1 ORDER BY col1), array_agg(col1 ORDER BY col1) FILTER (WHERE col1 < 0) FROM tab
  2010  ----
  2011  array_agg         array_agg
  2012  {-3,-2,-1,0,1,2}  {-3,-2,-1}
  2013  
  2014  query TT colnames
  2015  SELECT array_agg(col1 ORDER BY col3, col1) FILTER (WHERE col1 < 0), array_agg(col1 ORDER BY col3, col1) FROM tab
  2016  ----
  2017  array_agg   array_agg
  2018  {-3,-2,-1}  {-3,-2,-1,0,1,2}
  2019  
  2020  query IT
  2021  SELECT count(1), concat_agg(col3 ORDER BY col1) from tab
  2022  ----
  2023  6  aaabbb
  2024  
  2025  # Testing pre-projections. Tests when the GroupBy clause has a projection.
  2026  query IIIT colnames
  2027  SELECT
  2028      *
  2029  FROM
  2030      (
  2031          SELECT
  2032              count(1) AS count_1,
  2033              count(lower(col3)) AS count_lower,
  2034              count(upper(col3)) AS count_upper,
  2035              concat_agg(col3 ORDER BY col1) AS concat
  2036          FROM
  2037              tab
  2038          GROUP BY
  2039              upper(col3)
  2040      )
  2041  ORDER BY
  2042      concat
  2043  ----
  2044  count_1  count_lower  count_upper  concat
  2045  3        3            3            aaa
  2046  3        3            3            bbb
  2047  
  2048  # Tests for selecting any columns when grouping by the PK.
  2049  statement ok
  2050  DELETE FROM ab WHERE true;
  2051  INSERT INTO ab VALUES (1,1), (2,1), (3,3), (4, 7)
  2052  
  2053  query I rowsort
  2054  SELECT b FROM ab GROUP BY a
  2055  ----
  2056  1
  2057  1
  2058  3
  2059  7
  2060  
  2061  query II rowsort
  2062  SELECT a+b, count(*) FROM ab JOIN tab ON b=col2 GROUP BY a
  2063  ----
  2064  11 2
  2065  
  2066  query IIII rowsort
  2067  SELECT a, col1, b+col2, count(*) FROM ab JOIN tab ON b=col2 GROUP BY a, col1
  2068  ----
  2069  4  -3  14  1
  2070  4  0   14  1
  2071  
  2072  query IIII rowsort
  2073  SELECT a, b, count(*), count(col2) FROM ab LEFT JOIN tab ON b=col2 GROUP BY a
  2074  ----
  2075  1  1  1  0
  2076  2  1  1  0
  2077  3  3  1  0
  2078  4  7  2  2
  2079  
  2080  query III rowsort
  2081  SELECT a, b, count(*) FROM ab RIGHT JOIN tab ON b=col2 GROUP BY a
  2082  ----
  2083  NULL  NULL  4
  2084  4     7     2
  2085  
  2086  # Additional tests for MIN/MAX aggregates with indexes.
  2087  statement ok
  2088  CREATE TABLE xyz (
  2089    x INT PRIMARY KEY,
  2090    y INT,
  2091    z INT,
  2092    INDEX yz (y, z)
  2093  )
  2094  
  2095  statement ok
  2096  INSERT INTO xyz VALUES (1, 2, 3), (2, 2, 7), (3, 2, 1), (4, 2, NULL), (5, 3, -1)
  2097  
  2098  query I
  2099  SELECT min(z) FROM xyz WHERE y = 2 GROUP BY y
  2100  ----
  2101  1
  2102  
  2103  query I
  2104  SELECT min(z) FROM xyz WHERE y = 2 AND z IS NOT NULL GROUP BY y
  2105  ----
  2106  1
  2107  
  2108  query I
  2109  SELECT min(z) FROM xyz WHERE y = 2 AND z IS NULL GROUP BY y
  2110  ----
  2111  NULL
  2112  
  2113  query I
  2114  SELECT min(z) FROM xyz WHERE y = 100 AND z IS NULL GROUP BY y
  2115  ----
  2116  
  2117  query I
  2118  SELECT max(z) FROM xyz WHERE y = 2 GROUP BY y
  2119  ----
  2120  7
  2121  
  2122  query I
  2123  SELECT max(z) FROM xyz WHERE y = 2 AND z IS NOT NULL GROUP BY y
  2124  ----
  2125  7
  2126  
  2127  query I
  2128  SELECT max(z) FROM xyz WHERE y = 2 AND z IS NULL GROUP BY y
  2129  ----
  2130  NULL
  2131  
  2132  query I
  2133  SELECT max(z) FROM xyz WHERE y = 100 GROUP BY y
  2134  ----
  2135  
  2136  statement ok
  2137  DROP TABLE xyz
  2138  
  2139  # Regression test for #44469 (DistinctOn needs to remap the provided ordering).
  2140  statement ok
  2141  CREATE TABLE t44469_a (a INT, INDEX (a))
  2142  
  2143  statement ok
  2144  CREATE TABLE t44469_b (b INT, INDEX (b))
  2145  
  2146  statement ok
  2147  CREATE TABLE t44469_cd (c INT, d INT, INDEX (c, d));
  2148  
  2149  statement ok
  2150  SELECT DISTINCT ON (b) b
  2151  FROM t44469_a INNER LOOKUP JOIN t44469_b ON a = b INNER LOOKUP JOIN t44469_cd ON c = 1 AND d = a
  2152  ORDER BY b
  2153  
  2154  statement ok
  2155  DROP TABLE IF EXISTS t;
  2156  CREATE TABLE t (x JSONB, y INT);
  2157  INSERT INTO t VALUES
  2158    ('{"foo": "bar"}', 5),
  2159    ('{"foo": "bar"}', 10),
  2160    ('[1, 2]', 5),
  2161    ('[1, 2]', 20),
  2162    ('{"foo": "bar", "bar": "baz"}', 5),
  2163    ('{"foo": "bar", "bar": "baz"}', 30),
  2164    ('{"foo": {"bar" : "baz"}}', 5),
  2165    ('{"foo": {"bar" : "baz"}}', 40)
  2166  
  2167  query TT
  2168  SELECT x, SUM (y) FROM t GROUP BY (x) ORDER BY SUM (y)
  2169  ----
  2170  {"foo": "bar"}                15
  2171  [1, 2]                        25
  2172  {"bar": "baz", "foo": "bar"}  35
  2173  {"foo": {"bar": "baz"}}       45
  2174  
  2175  # Tests for the 'every' aggregate function.
  2176  subtest every
  2177  
  2178  statement ok
  2179  CREATE TABLE t_every (x BOOL)
  2180  
  2181  query B
  2182  SELECT every (x) FROM t_every
  2183  ----
  2184  NULL
  2185  
  2186  statement ok
  2187  INSERT INTO t_every VALUES (true), (true)
  2188  
  2189  query B
  2190  SELECT every (x) FROM t_every
  2191  ----
  2192  true
  2193  
  2194  statement ok
  2195  INSERT INTO t_every VALUES (NULL), (true)
  2196  
  2197  query B
  2198  SELECT every (x) FROM t_every
  2199  ----
  2200  true
  2201  
  2202  statement ok
  2203  INSERT INTO t_every VALUES (false), (NULL)
  2204  
  2205  query B
  2206  SELECT every (x) FROM t_every
  2207  ----
  2208  false
  2209  
  2210  statement ok
  2211  TRUNCATE t_every;
  2212  INSERT INTO t_every VALUES (false)
  2213  
  2214  query B
  2215  SELECT every (x) FROM t_every
  2216  ----
  2217  false
  2218  
  2219  statement ok
  2220  TRUNCATE t_every;
  2221  INSERT INTO t_every VALUES (NULL), (NULL), (NULL)
  2222  
  2223  query B
  2224  SELECT every (x) FROM t_every
  2225  ----
  2226  NULL
  2227  
  2228  # Regression test for #46423: this query should return no rows.
  2229  statement ok
  2230  CREATE TABLE t46423(c0 INT);
  2231  INSERT INTO t46423(c0) VALUES(0)
  2232  
  2233  query T
  2234  SELECT c0 FROM t46423 GROUP BY c0 HAVING NOT (variance(0) IS NULL);
  2235  ----
  2236  
  2237  # Regression test for #45453 - make sure that we don't incorrectly treat the
  2238  # aggregation as scalar.
  2239  statement ok
  2240  CREATE TABLE t45453(c INT)
  2241  
  2242  query I
  2243  SELECT count(*) FROM t45453 GROUP BY 0 + 0
  2244  ----
  2245  
  2246  # Tests for the bit_and and bit_or aggregate functions.
  2247  
  2248  subtest bit_aggregates
  2249  
  2250  statement ok
  2251  DROP TABLE IF EXISTS vals
  2252  
  2253  statement ok
  2254  CREATE TABLE vals (
  2255    v VARBIT,
  2256    b BIT(8)
  2257  )
  2258  
  2259  # Testing that bit aggregate functions return NULL if there are no rows.
  2260  
  2261  query T
  2262  SELECT bit_and(v) FROM vals
  2263  ----
  2264  NULL
  2265  
  2266  query T
  2267  SELECT bit_or(v) FROM vals
  2268  ----
  2269  NULL
  2270  
  2271  # Testing that bit aggregate functions do not trigger aggregation on a constant
  2272  # with a source that has no rows.
  2273  
  2274  query T
  2275  SELECT bit_and('1000'::varbit) FROM vals
  2276  ----
  2277  NULL
  2278  
  2279  query T
  2280  SELECT bit_or('1000'::varbit) FROM vals
  2281  ----
  2282  NULL
  2283  
  2284  # Testing that bit aggregate functions trigger aggregation and computation on a
  2285  # constant with no source.
  2286  
  2287  query TTT
  2288  SELECT bit_and('1'::varbit), bit_and('1000'::bit(4)), bit_and('1010'::varbit)
  2289  ----
  2290  1 1000 1010
  2291  
  2292  query TTT
  2293  SELECT bit_or('1'::varbit), bit_or('1000'::bit(4)), bit_or('1010'::varbit)
  2294  ----
  2295  1 1000 1010
  2296  
  2297  # Testing that bit aggregate functions return null given a null.
  2298  
  2299  query T
  2300  SELECT bit_and(NULL::varbit)
  2301  ----
  2302  NULL
  2303  
  2304  query T
  2305  SELECT bit_or(NULL::varbit)
  2306  ----
  2307  NULL
  2308  
  2309  # Testing successful bitwise aggregation over a sequence of non-nulls.
  2310  
  2311  statement ok
  2312  INSERT INTO vals VALUES
  2313  ('11111110'::varbit, '11111110'::bit(8)),
  2314  ('01111111'::varbit, '01111110'::bit(8)),
  2315  ('10111111'::varbit, '10111110'::bit(8)),
  2316  ('11011111'::varbit, '11011110'::bit(8)),
  2317  ('11101111'::varbit, '11101110'::bit(8))
  2318  
  2319  query TT
  2320  SELECT bit_and(v), bit_and(b) FROM vals
  2321  ----
  2322  00001110 00001110
  2323  
  2324  query TT
  2325  SELECT bit_or(v), bit_or(b) FROM vals
  2326  ----
  2327  11111111 11111110
  2328  
  2329  # Testing bit aggregate functions over a sequence with nulls and non-nulls.
  2330  
  2331  statement ok
  2332  INSERT INTO vals VALUES
  2333  (NULL::varbit, NULL::bit),
  2334  (NULL::varbit, NULL::bit)
  2335  
  2336  query TT
  2337  SELECT bit_and(v), bit_and(b) FROM vals
  2338  ----
  2339  00001110 00001110
  2340  
  2341  query TT
  2342  SELECT bit_or(v), bit_or(b) FROM vals
  2343  ----
  2344  11111111 11111110
  2345  
  2346  # Testing bit aggregate functions over a sequence with all nulls.
  2347  
  2348  statement ok
  2349  DELETE FROM vals
  2350  
  2351  statement ok
  2352  INSERT INTO vals VALUES
  2353  (NULL::varbit),
  2354  (NULL::varbit),
  2355  (NULL::varbit),
  2356  (NULL::varbit)
  2357  
  2358  query T
  2359  SELECT bit_and(v) FROM vals
  2360  ----
  2361  NULL
  2362  
  2363  query T
  2364  SELECT bit_or(v) FROM vals
  2365  ----
  2366  NULL
  2367  
  2368  # Testing that bit aggregate functions return an error when given an uncasted null.
  2369  
  2370  statement error ambiguous call: bit_and\(unknown\), candidates are
  2371  SELECT bit_and(NULL)
  2372  
  2373  statement error ambiguous call: bit_or\(unknown\), candidates are
  2374  SELECT bit_or(NULL)
  2375  
  2376  # Testing that an error is returned when bit aggregate functions are called on bit
  2377  # arrays of different sizes.
  2378  
  2379  statement error cannot AND bit strings of different sizes
  2380  SELECT bit_and(x::varbit) FROM (VALUES ('1'), ('11')) t(x)
  2381  
  2382  statement error cannot AND bit strings of different sizes
  2383  SELECT bit_and(x) FROM (VALUES ('100'::bit(3)), ('101010111'::varbit)) t(x)
  2384  
  2385  statement error cannot AND bit strings of different sizes
  2386  SELECT bit_and(x) FROM (VALUES (''::varbit), ('1'::varbit)) t(x)
  2387  
  2388  statement error cannot OR bit strings of different sizes
  2389  SELECT bit_or(x::varbit) FROM (VALUES ('1'), ('11')) t(x)
  2390  
  2391  statement error cannot OR bit strings of different sizes
  2392  SELECT bit_or(x) FROM (VALUES ('100'::bit(3)), ('101010111'::varbit)) t(x)
  2393  
  2394  statement error cannot OR bit strings of different sizes
  2395  SELECT bit_or(x) FROM (VALUES (''::varbit), ('1'::varbit)) t(x)
  2396  
  2397  # Regression test for #46981 (not propagating an error which occurs when
  2398  # rendering the single output row of countRows aggregate).
  2399  statement ok
  2400  CREATE TABLE t46981_0(c0 INT);
  2401  CREATE VIEW v46981_0(c0) AS SELECT count_rows() FROM t46981_0
  2402  
  2403  statement error parsing regexp: missing argument to repetition operator: `\+`
  2404  SELECT * FROM v46981_0 WHERE '' !~ '+'
  2405  
  2406  # Testing ordered-set aggregations.
  2407  subtest ordered_set_aggregates
  2408  
  2409  statement ok
  2410  DROP TABLE IF EXISTS osagg
  2411  
  2412  statement ok
  2413  CREATE TABLE osagg (
  2414    f FLOAT,
  2415    s STRING,
  2416    i INTERVAL
  2417  )
  2418  
  2419  statement ok
  2420  INSERT INTO osagg VALUES
  2421  (NULL, NULL, NULL),
  2422  (0.00, NULL, '1 months'),
  2423  (0.05, NULL, '1 months'),
  2424  (1.0, 'v1', '1 year 1 months'),
  2425  (3.0, 'v3', '1 year 3 months'),
  2426  (5.0, 'v5', '1 year 5 months'),
  2427  (2.0, 'v2', '1 year 2 months'),
  2428  (4.0, 'v4', '1 year 4 months'),
  2429  (6.0, 'v6', '1 year 6 months')
  2430  
  2431  # Test basic functionality.
  2432  query R
  2433  SELECT
  2434    percentile_disc(0.95) WITHIN GROUP (ORDER BY f)
  2435  FROM osagg
  2436  ----
  2437  6
  2438  
  2439  query RT
  2440  SELECT
  2441    percentile_disc(0.95) WITHIN GROUP (ORDER BY f),
  2442    percentile_disc(0.95) WITHIN GROUP (ORDER BY s)
  2443  FROM osagg
  2444  ----
  2445  6  v6
  2446  
  2447  query RRT
  2448  SELECT
  2449    percentile_cont(0.95) WITHIN GROUP (ORDER BY f),
  2450    percentile_cont(0.95) WITHIN GROUP (ORDER BY f DESC),
  2451    percentile_cont(0.95) WITHIN GROUP (ORDER BY i)
  2452  FROM osagg
  2453  ----
  2454  5.65  0.0175  1 year 5 mons 24 days 18:00:00
  2455  
  2456  # Test with null values.
  2457  query TRR
  2458  SELECT
  2459    percentile_disc(0.00) WITHIN GROUP (ORDER BY s),
  2460    percentile_disc(0.1) WITHIN GROUP (ORDER BY f),
  2461    percentile_disc(0.15) WITHIN GROUP (ORDER BY f)
  2462  FROM osagg
  2463  ----
  2464  v1  0  0.05
  2465  
  2466  query RRTT
  2467  SELECT
  2468    percentile_cont(0.05) WITHIN GROUP (ORDER BY f),
  2469    percentile_cont(0.05) WITHIN GROUP (ORDER BY f DESC),
  2470    percentile_cont(0.05) WITHIN GROUP (ORDER BY i),
  2471    percentile_cont(0.05) WITHIN GROUP (ORDER BY i DESC)
  2472  FROM osagg
  2473  ----
  2474  0.0175  5.65  1 mon  1 year 5 mons 24 days 18:00:00
  2475  
  2476  # Test with different percent values.
  2477  query RRR
  2478  SELECT
  2479    percentile_disc(0.25) WITHIN GROUP (ORDER BY f),
  2480    percentile_disc(0.5) WITHIN GROUP (ORDER BY f),
  2481    percentile_disc(0.75) WITHIN GROUP (ORDER BY f)
  2482  FROM osagg
  2483  ----
  2484  0.05  2  4
  2485  
  2486  query RRR
  2487  SELECT
  2488    percentile_cont(0.25) WITHIN GROUP (ORDER BY f),
  2489    percentile_cont(0.5) WITHIN GROUP (ORDER BY f),
  2490    percentile_cont(0.75) WITHIN GROUP (ORDER BY f)
  2491  FROM osagg
  2492  ----
  2493  0.7625  2.5  4.25
  2494  
  2495  # Test with arrays.
  2496  query T
  2497  SELECT
  2498    percentile_disc(ARRAY[0.25]::float[]) WITHIN GROUP (ORDER BY f)
  2499  FROM osagg
  2500  ----
  2501  {0.05}
  2502  
  2503  query T
  2504  SELECT
  2505    percentile_disc(ARRAY[0.25, 0.5, 0.75]::float[]) WITHIN GROUP (ORDER BY f)
  2506  FROM osagg
  2507  ----
  2508  {0.05,2.0,4.0}
  2509  
  2510  query T
  2511  SELECT
  2512    percentile_cont(ARRAY[0.25, 0.5, 0.75]::float[]) WITHIN GROUP (ORDER BY f)
  2513  FROM osagg
  2514  ----
  2515  {0.7625,2.5,4.25}
  2516  
  2517  query T
  2518  SELECT
  2519    percentile_disc(ARRAY[0.25, 0.5, 0.75]::float[]) WITHIN GROUP (ORDER BY i)
  2520  FROM osagg
  2521  ----
  2522  {"1 mon","1 year 2 mons","1 year 4 mons"}
  2523  
  2524  statement error percentile value 1.250000 is not between 0 and 1
  2525  SELECT
  2526    percentile_disc(ARRAY[1.25]::float[]) WITHIN GROUP (ORDER BY f)
  2527  FROM osagg
  2528  
  2529  statement error percentile value 1.250000 is not between 0 and 1
  2530  SELECT
  2531    percentile_disc(ARRAY[0.25, 0.50, 1.25]::float[]) WITHIN GROUP (ORDER BY f)
  2532  FROM osagg
  2533  
  2534  query T
  2535  SELECT
  2536    percentile_cont(ARRAY[0.25, 0.5, 0.75]::float[]) WITHIN GROUP (ORDER BY i)
  2537  FROM osagg
  2538  ----
  2539  {"10 mons 3 days 22:30:00","1 year 2 mons 20 days 06:00:00","1 year 4 mons 12 days 18:00:00"}
  2540  
  2541  # Test that the view query is not broken by the overriding logic in the optbuilder.
  2542  statement ok
  2543  CREATE VIEW osagg_view (disc, cont) AS
  2544    SELECT percentile_disc(0.50) WITHIN GROUP (ORDER BY f),
  2545           percentile_cont(0.50) WITHIN GROUP (ORDER BY f DESC) FROM osagg
  2546  
  2547  query TT
  2548  SHOW CREATE osagg_view
  2549  ----
  2550  osagg_view  CREATE VIEW osagg_view (disc, cont) AS SELECT percentile_disc(0.50) WITHIN GROUP (ORDER BY f), percentile_cont(0.50) WITHIN GROUP (ORDER BY f DESC) FROM test.public.osagg
  2551  
  2552  # Test malformed ordered-set aggregation.
  2553  statement error ordered-set aggregations must have a WITHIN GROUP clause containing one ORDER BY column
  2554  SELECT percentile_disc(0.50) FROM osagg
  2555  
  2556  statement error ordered-set aggregations must have a WITHIN GROUP clause containing one ORDER BY column
  2557  SELECT percentile_cont(0.50) FROM osagg
  2558  
  2559  # Tests for min/max on collated strings.
  2560  statement ok
  2561  CREATE TABLE t_collate (x STRING COLLATE en_us);
  2562  INSERT INTO t_collate VALUES ('hi' COLLATE en_us), ('hello' COLLATE en_us), ('howdy' COLLATE en_us)
  2563  
  2564  query TT
  2565  SELECT min(x), max(x) FROM t_collate
  2566  ----
  2567  hello howdy
  2568  
  2569  query TT
  2570  SELECT min(NULL::STRING COLLATE en_us), max(NULL::STRING COLLATE en_us)
  2571  ----
  2572  NULL NULL