#!/usr/bin/env tclsh set testdir [file dirname $argv0] source $testdir/tester.tcl do_execsql_test group_by { select u.first_name, sum(u.age) from users u group by u.first_name limit 10; } {Aaron|2271 Abigail|890 Adam|1642 Adrian|439 Adriana|83 Adrienne|318 Aimee|33 Alan|551 Albert|369 Alec|247} do_execsql_test group_by_without_aggs { select u.first_name from users u group by u.first_name limit 10; } {Aaron Abigail Adam Adrian Adriana Adrienne Aimee Alan Albert Alec} do_execsql_test group_by_two_joined_columns { select u.first_name, p.name, sum(u.age) from users u join products p on u.id = p.id group by u.first_name, p.name limit 10; } {Aimee|jeans|24 Cindy|cap|37 Daniel|coat|13 Edward|sweatshirt|15 Jamie|hat|94 Jennifer|sweater|33 Matthew|boots|77 Nicholas|shorts|89 Rachel|sneakers|63 Tommy|shirt|18} do_execsql_test group_by_order_by { select u.first_name, p.name, sum(u.age) from users u join products p on u.id = p.id group by u.first_name, p.name order by p.name limit 10; } {Travis|accessories|22 Matthew|boots|77 Cindy|cap|37 Daniel|coat|13 Jamie|hat|94 Aimee|jeans|24 Tommy|shirt|18 Nicholas|shorts|89 Rachel|sneakers|63 Jennifer|sweater|33} do_execsql_test group_by_order_by_aggregate { select u.first_name, p.name, sum(u.age) from users u join products p on u.id = p.id group by u.first_name, p.name order by sum(u.age) limit 10; } {Daniel|coat|13 Edward|sweatshirt|15 Tommy|shirt|18 Travis|accessories|22 Aimee|jeans|24 Jennifer|sweater|33 Cindy|cap|37 Rachel|sneakers|63 Matthew|boots|77 Nicholas|shorts|89} do_execsql_test group_by_multiple_aggregates { select u.first_name, sum(u.age), count(u.age) from users u group by u.first_name order by sum(u.age) limit 10; } {Jaclyn|1|1 Mia|1|1 Kirsten|7|1 Kellie|8|1 Makayla|8|1 Yvette|9|1 Mckenzie|12|1 Grant|14|1 Mackenzie|15|1 Cesar|17|1} do_execsql_test group_by_multiple_aggregates_2 { select u.first_name, sum(u.age), group_concat(u.age) from users u group by u.first_name order by u.first_name limit 10; } {Aaron|2271|52,46,17,69,71,91,34,30,97,81,47,98,45,69,97,18,38,26,98,60,33,97,42,43,43,22,18,75,56,67,83,58,82,28,22,72,5,58,96,32,55 Abigail|890|17,82,62,57,55,5,9,83,93,22,23,57,56,100,74,95 Adam|1642|34,23,10,11,46,40,2,57,51,80,65,24,15,84,59,6,34,100,32,79,57,5,77,34,30,19,54,74,89,98,72,91,90 Adrian|439|37,28,94,76,69,60,34,41 Adriana|83|83 Adrienne|318|79,74,82,33,50 Aimee|33|24,9 Alan|551|18,52,30,62,96,13,85,97,98 Albert|369|99,80,41,7,64,7,26,41,4 Alec|247|55,48,53,91} do_execsql_test group_by_complex_order_by { select u.first_name, group_concat(u.last_name) from users u group by u.first_name order by -1 * length(group_concat(u.last_name)) limit 1; } {Michael|Love,Finley,Hurst,Molina,Williams,Brown,King,Whitehead,Ochoa,Davis,Rhodes,Mcknight,Reyes,Johnston,Smith,Young,Lopez,Roberts,Green,Cole,Lane,Wagner,Allen,Simpson,Schultz,Perry,Mendez,Gibson,Hale,Williams,Bradford,Johnson,Weber,Nunez,Walls,Gonzalez,Park,Blake,Vazquez,Garcia,Mathews,Pacheco,Johnson,Perez,Gibson,Sparks,Chapman,Tate,Dudley,Miller,Alvarado,Ward,Nguyen,Rosales,Flynn,Ball,Jones,Hoffman,Clarke,Rivera,Moore,Hardin,Dillon,Montgomery,Rodgers,Payne,Williams,Mueller,Hernandez,Ware,Yates,Grimes,Gilmore,Johnson,Clark,Rodriguez,Walters,Powell,Colon,Mccoy,Allen,Quinn,Dunn,Wilson,Thompson,Bradford,Hunter,Gilmore,Woods,Bennett,Collier,Ali,Herrera,Lawson,Garner,Perez,Brown,Pena,Allen,Davis,Washington,Jackson,Khan,Martinez,Blackwell,Lee,Parker,Lynn,Johnson,Benton,Leonard,Munoz,Alvarado,Mathews,Salazar,Nelson,Jones,Carpenter,Walter,Young,Coleman,Berry,Clark,Powers,Meyer,Lewis,Barton,Guzman,Schneider,Hernandez,Mclaughlin,Allen,Atkinson,Woods,Rivera,Jones,Gordon,Dennis,Yoder,Hunt,Vance,Nelson,Park,Barnes,Lang,Williams,Cervantes,Tran,Anderson,Todd,Gonzalez,Lowery,Sanders,Mccullough,Haley,Rogers,Perez,Watson,Weaver,Wise,Walter,Summers,Long,Chan,Williams,Mccoy,Duncan,Roy,West,Christensen,Cuevas,Garcia,Williams,Butler,Anderson,Armstrong,Villarreal,Boyer,Johnson,Dyer,Hurst,Wilkins,Mercer,Taylor,Montes,Mccarty,Gill,Rodriguez,Williams,Copeland,Hansen,Palmer,Alexander,White,Taylor,Bowers,Hughes,Gibbs,Myers,Kennedy,Sanchez,Bell,Wilson,Berry,Spears,Patton,Rose,Smith,Bowen,Nicholson,Stewart,Quinn,Powell,Delgado,Mills,Duncan,Phillips,Grant,Hatfield,Russell,Anderson,Reed,Mahoney,Mcguire,Ortega,Logan,Schmitt,Walker} do_execsql_test group_by_complex_order_by_2 { select u.first_name, sum(u.age) from users u group by u.first_name order by -1 * sum(u.age) limit 10; } {Michael|11204 David|8758 Robert|8109 Jennifer|7700 John|7299 Christopher|6397 James|5921 Joseph|5711 Brian|5059 William|5047} do_execsql_test group_by_and_binary_expression_that_depends_on_two_aggregates { select u.first_name, sum(u.age) + count(1) from users u group by u.first_name limit 5; } {Aaron|2312 Abigail|906 Adam|1675 Adrian|447 Adriana|84} do_execsql_test group_by_function_expression { select length(phone_number), count(1) from users group by length(phone_number) order by count(1); } {15|392 22|416 13|762 20|791 10|793 19|816 21|821 17|1184 18|1211 16|1231 12|1583} do_execsql_test group_by_function_expression_ridiculous { select upper(substr(phone_number, 1,3)), count(1) from users group by upper(substr(phone_number, 1,3)) order by -1 * count(1) limit 5; } {001|1677 +1-|1606 (97|36 (20|35 (31|35} do_execsql_test group_by_count_star { select u.first_name, count(*) from users u group by u.first_name limit 1; } {Aaron|41} do_execsql_test having { select u.first_name, round(avg(u.age)) from users u group by u.first_name having avg(u.age) > 97 order by avg(u.age) desc limit 5; } {Nina|100.0 Kurt|99.0 Selena|98.0} do_execsql_test having_with_binary_cond { select u.first_name, sum(u.age) from users u group by u.first_name having sum(u.age) + 1000 = 9109; } {Robert|8109} do_execsql_test having_with_scalar_fn_over_aggregate { select u.first_name, concat(count(1), ' people with this name') from users u group by u.first_name having count(1) > 50 order by count(1) asc limit 5; } {"Angela|51 people with this name Justin|51 people with this name Rachel|52 people with this name Susan|52 people with this name Jeffrey|54 people with this name"} do_execsql_test having_with_multiple_conditions { select u.first_name, count(*), round(avg(u.age)) as avg_age from users u group by u.first_name having count(*) > 40 and avg(u.age) > 40 order by count(*) desc, avg(u.age) desc limit 5; } {Michael|228|49.0 David|165|53.0 Robert|159|51.0 Jennifer|151|51.0 John|145|50.0} # Wanda = 9, Whitney = 11, William = 111 do_execsql_test column_alias_in_group_by_order_by_having { select first_name as fn, count(1) as fn_count from users where fn in ('Wanda', 'Whitney', 'William') group by fn having fn_count > 10 order by fn_count; } {Whitney|11 William|111} do_execsql_test group_by_column_number { select u.first_name, count(1) from users u group by 1 limit 1; } {Aaron|41} # There was a regression where we incorrectly removed SOME order by terms and left others in place, which is invalid and results in wrong rows being returned. do_execsql_test groupby_orderby_removal_regression_test { select id, last_name, count(1) from users GROUP BY 1,2 order by id, last_name desc limit 3; } {1|Foster|1 2|Salazar|1 3|Perry|1} do_execsql_test group_by_no_sorting_required { select age, count(1) from users group by age limit 3; } {1|112 2|113 3|97} # Compile-time constants are moved to the end of the program. # Verify that the jump to AggStep works correctly even when the location of the ',' constant has changed. do_execsql_test group_by_no_sorting_required_and_const_agg_arg { select group_concat(state, ',') from users group by age limit 2; } {CA,PW,ME,AS,LA,OH,AL,UT,WA,MO,WA,SC,AR,CO,OK,ME,FM,AR,CT,MT,TN,FL,MA,ND,LA,NE,KS,IN,RI,NH,IL,FM,WA,MH,RI,SC,AS,IL,VA,MI,ID,ME,WY,TN,IN,IN,UT,WA,AZ,VA,NM,IA,MP,WY,RI,OR,OR,FM,WA,DC,RI,GU,TX,HI,IL,TX,WY,OH,TX,CT,KY,NE,MH,AR,MN,IL,NH,HI,NV,UT,FL,MS,NM,NJ,CA,MS,GA,MT,GA,AL,IN,SC,PA,FL,CT,PA,GA,RI,HI,WV,VT,IA,PR,FM,MA,TX,MS,LA,MD,PA,TX,WY OR,SD,KS,MP,WA,VI,SC,SD,SD,MP,WA,MT,FM,IN,ME,OH,KY,RI,DC,MS,OK,VI,KY,MD,SC,OK,NY,WY,AK,MN,UT,NE,VA,MD,AZ,VI,SC,NV,IN,VA,HI,VI,MS,NE,WY,NY,GU,MT,AL,IA,VA,ND,MN,FM,IA,ID,IL,FL,PR,WA,AS,HI,NH,WI,FL,HI,AL,ID,DC,CT,IL,VT,AZ,VI,AK,PW,NC,SD,NV,WA,MO,MS,WY,VA,FM,MN,NH,MN,MT,TX,MS,FM,OH,GU,IN,WA,IA,PA,ID,MI,LA,GU,ND,AR,ND,WV,DC,NY,CO,CT,FM,CT,ND} if {[info exists ::env(SQLITE_EXEC)] && ($::env(SQLITE_EXEC) eq "scripts/limbo-sqlite3-index-experimental" || $::env(SQLITE_EXEC) eq "sqlite3")} { do_execsql_test_on_specific_db {:memory:} group_by_no_sorting_required_reordered_columns { create table t0 (a INT, b INT, c INT); create index a_b_idx on t0 (a, b); insert into t0 values (1,1,1), (1,1,2), (2,1,3), (2,2,3), (2,2,5); select c, b, a from t0 group by a, b; } {1|1|1 3|1|2 3|2|2} } if {[info exists ::env(SQLITE_EXEC)] && ($::env(SQLITE_EXEC) eq "scripts/limbo-sqlite3-index-experimental" || $::env(SQLITE_EXEC) eq "sqlite3")} { do_execsql_test distinct_agg_functions { select first_name, sum(distinct age), count(distinct age), avg(distinct age) from users group by 1 limit 3; } {Aaron|1769|33|53.6060606060606 Abigail|833|15|55.5333333333333 Adam|1517|30|50.5666666666667} } do_execsql_test_on_specific_db {:memory:} having_or { CREATE TABLE users (first_name TEXT, age INTEGER); INSERT INTO users VALUES ('Michael', 25), ('Michael', 50), ('David', 50), ('Sarah', 65); select first_name, count(*) as cnt, avg(age) as avg_age from users group by first_name having cnt = 2 or avg_age = 65 order by cnt desc } {Michael|2|37.5 Sarah|1|65.0} do_execsql_test complex_result_expression_containing_aggregate { select case when price > 70 then group_concat(name, ',') else '' end names from products group by price order by price; } { sweatshirt jeans hat accessories cap,sneakers} do_execsql_test complex_result_expression_containing_aggregate_and_rowid { select case when rowid >= 5 then group_concat(name, ',') else '' end names from products group by rowid order by rowid; } { sweatshirt shorts jeans sneakers boots coat accessories} do_execsql_test complex_having_expression_containing_aggregate { select group_concat(name, ',') from products group by price having (group_concat(name, ',') || price) like 'ca%'; } {cap,sneakers} do_execsql_test complex_order_by_expression_containing_aggregate { select group_concat(name, ',') from products group by price order by (group_concat(name, ',') || price); } {accessories boots cap,sneakers coat hat jeans shirt shorts sweater sweatshirt}