limbo/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 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-column-delimiter {
  SELECT group_concat(name, id) FROM products;
} {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-column-delimiter {
  SELECT string_agg(name, id) FROM products;
} {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"}]}

if {[info exists ::env(SQLITE_EXEC)] && ($::env(SQLITE_EXEC) eq "scripts/limbo-sqlite3-index-experimental" || $::env(SQLITE_EXEC) eq "sqlite3")} {
    do_execsql_test select-distinct-agg-functions {
    SELECT sum(distinct age), count(distinct age), avg(distinct age) FROM users;
    } {5050|100|50.5}
}