turso/testing/agg-functions.test

#!/usr/bin/env tclsh

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_execsql_test select-avg {
  SELECT avg(age) FROM users;
} {50.396}

do_execsql_test select-avg-text {
  SELECT avg(first_name) FROM users;
} {0.0}

do_execsql_test select-sum {
  SELECT sum(age) FROM users;
} {503960}

do_execsql_test select-sum-text {
  SELECT sum(first_name) FROM users;
} {0.0}

do_execsql_test select-total {
  SELECT total(age) FROM users;
} {503960.0}

do_execsql_test select-total-text {
  SELECT total(first_name) FROM users WHERE id < 3;
} {0.0}

do_execsql_test select-limit {
  SELECT typeof(id) FROM users LIMIT 1;
} {integer}

do_execsql_test select-count {
  SELECT count(id) FROM users;
} {10000}

do_execsql_test select-count {
  SELECT count(*) FROM users;
} {10000}

do_execsql_test select-count-constant-true {
  SELECT count(*) FROM users WHERE true;
} {10000}

do_execsql_test select-count-constant-false {
  SELECT count(*) FROM users WHERE false;
} {0}

do_execsql_test select-max {
  SELECT max(age) FROM users;
} {100}

do_execsql_test select-min {
  SELECT min(age) FROM users;
} {1}

do_execsql_test_on_specific_db {:memory:} min-null-regression-test {
  CREATE TABLE t (a);
  INSERT INTO t VALUES ('abc'), (NULL);
  SELECT min(a) FROM t;
} {abc}

do_execsql_test_on_specific_db {:memory:} max-null-regression-test {
  CREATE TABLE t (a);
  INSERT INTO t VALUES ('abc'), (NULL);
  SELECT max(a) FROM t;
} {abc}

do_execsql_test_on_specific_db {:memory:} group-concat-null-values-test {
  CREATE TABLE t (a);
  INSERT INTO t VALUES ('a'), (''), ('b'), (NULL), ('c');
  SELECT group_concat(a) FROM t;
} {a,,b,c}

do_execsql_test select-max-text {
  SELECT max(first_name) FROM users;
} {Zoe}

do_execsql_test select-min-text {
  SELECT min(first_name) FROM users;
} {Aaron}

do_execsql_test select-group-concat {
  SELECT group_concat(name) FROM products;
} {hat,cap,shirt,sweater,sweatshirt,shorts,jeans,sneakers,boots,coat,accessories}

do_execsql_test select-group-concat-with-delimiter {
  SELECT group_concat(name, ';') FROM products;
} {hat;cap;shirt;sweater;sweatshirt;shorts;jeans;sneakers;boots;coat;accessories}

do_execsql_test select-group-concat-with-delimiter-expression {
  SELECT group_concat(name, ';' || '|') FROM products;
} {hat;|cap;|shirt;|sweater;|sweatshirt;|shorts;|jeans;|sneakers;|boots;|coat;|accessories}

do_execsql_test select-group-concat-with-column-delimiter {
  SELECT group_concat(name, id) FROM products;
} {hat2cap3shirt4sweater5sweatshirt6shorts7jeans8sneakers9boots10coat11accessories}

do_execsql_test select-group-concat-with-column-delimiter-group-by {
  SELECT group_concat(name, id) FROM products GROUP BY '1';
} {hat2cap3shirt4sweater5sweatshirt6shorts7jeans8sneakers9boots10coat11accessories}

do_execsql_test select-string-agg-with-delimiter {
  SELECT string_agg(name, ',') FROM products;
} {hat,cap,shirt,sweater,sweatshirt,shorts,jeans,sneakers,boots,coat,accessories}

do_execsql_test select-string-agg-with-delimiter-expression {
  SELECT string_agg(name, ';' || '|') FROM products;
} {hat;|cap;|shirt;|sweater;|sweatshirt;|shorts;|jeans;|sneakers;|boots;|coat;|accessories}

do_execsql_test select-string-agg-with-column-delimiter {
  SELECT string_agg(name, id) FROM products;
} {hat2cap3shirt4sweater5sweatshirt6shorts7jeans8sneakers9boots10coat11accessories}

do_execsql_test select-string-agg-with-column-delimiter-group-by {
  SELECT string_agg(name, id) FROM products GROUP BY '1';
} {hat2cap3shirt4sweater5sweatshirt6shorts7jeans8sneakers9boots10coat11accessories}

do_execsql_test select-agg-unary-negative {
  SELECT -max(age) FROM users;
} {-100}

do_execsql_test select-agg-unary-positive {
  SELECT +max(age) FROM users;
} {100}

do_execsql_test select-agg-binary-unary-negative {
  SELECT min(age) + -max(age) FROM users;
} {-99}

do_execsql_test select-agg-binary-unary-positive {
  SELECT min(age) + +max(age) FROM users;
} {101}

do_execsql_test select-non-agg-cols-should-be-not-null {
  SELECT id, first_name, sum(age) FROM users LIMIT 1;
} {1|Jamie|503960}

do_execsql_test select-with-group-by-and-agg-1 {
  SELECT id, first_name, avg(age) FROM users group by last_name limit 1;
} {274|Debra|66.25}

do_execsql_test select-with-group-by-and-agg-2 {
  select first_name, last_name from users where state = 'AL' group by last_name limit 10;
} {Jay|Acosta
Daniel|Adams
Aaron|Baker
Sharon|Becker
Kim|Berg
Donald|Bishop
Brian|Bradford
Jesus|Bradley
John|Brown
Hunter|Burke}

do_execsql_test select-agg-json-array {
  SELECT json_group_array(name) FROM products;
} {["hat","cap","shirt","sweater","sweatshirt","shorts","jeans","sneakers","boots","coat","accessories"]}

do_execsql_test select-agg-json-array-object {
  SELECT json_group_array(json_object('name', name)) FROM products;
} {[{"name":"hat"},{"name":"cap"},{"name":"shirt"},{"name":"sweater"},{"name":"sweatshirt"},{"name":"shorts"},{"name":"jeans"},{"name":"sneakers"},{"name":"boots"},{"name":"coat"},{"name":"accessories"}]}

do_execsql_test select-distinct-agg-functions {
SELECT sum(distinct age), count(distinct age), avg(distinct age) FROM users;
} {5050|100|50.5}

do_execsql_test select-json-group-object {
  select price,
         json_group_object(cast (id as text), name)
  from products
  group by price
  order by price;
} {1.0|{"9":"boots"}
18.0|{"3":"shirt"}
25.0|{"4":"sweater"}
33.0|{"10":"coat"}
70.0|{"6":"shorts"}
74.0|{"5":"sweatshirt"}
78.0|{"7":"jeans"}
79.0|{"1":"hat"}
81.0|{"11":"accessories"}
82.0|{"2":"cap","8":"sneakers"}}

do_execsql_test select-json-group-object-no-sorting-required {
  select age,
         json_group_object(cast (id as text), first_name)
  from users
  where first_name like 'Am%'
  group by age
  order by age
  limit 5;
} {1|{"6737":"Amy"}
2|{"2297":"Amy","3580":"Amanda"}
3|{"3437":"Amanda"}
5|{"2378":"Amy","3227":"Amy","5605":"Amanda"}
7|{"2454":"Amber"}}

do_execsql_test_error_content select-max-star {
  SELECT max(*) FROM users;
} {"wrong number of arguments to function"}

do_execsql_test_error_content select-max-star-in-expression {
  SELECT CASE WHEN max(*) > 0 THEN 1 ELSE 0 END FROM users;
} {"wrong number of arguments to function"}

do_execsql_test_error select-scalar-func-star {
  SELECT abs(*) FROM users;
} {.*(Invalid aggregate function|wrong number of arguments to function).*}

do_execsql_test_error select-scalar-func-star-in-expression {
  SELECT CASE WHEN abs(*) > 0 THEN 1 ELSE 0 END FROM users;
} {.*(Invalid aggregate function|wrong number of arguments to function).*}

do_execsql_test_error_content select-nested-agg-func {
  SELECT max(abs(sum(age))), sum(age) FROM users;
} {"misuse of aggregate function"}

do_execsql_test_error_content select-nested-agg-func-in-expression {
  SELECT CASE WHEN max(abs(sum(age))) > 0 THEN 1 ELSE 0 END, sum(age) FROM users;
} {"misuse of aggregate function"}

# https://github.com/tursodatabase/turso/issues/3308
do_execsql_test printf-19029102 {
   select printf('%d', 3.9);
} {3}