1#include "catch.hpp"
2#include "duckdb/common/fstream_util.hpp"
3#include "duckdb/common/gzip_stream.hpp"
4#include "test_helpers.hpp"
5
6#include <fstream>
7#include <sstream>
8
9using namespace duckdb;
10using namespace std;
11
12TEST_CASE("Run Sakila test queries", "[sakila][.]") {
13 unique_ptr<QueryResult> result;
14 DuckDB db(nullptr);
15 Connection con(db);
16
17 string token;
18
19 // read schema
20 fstream schema_file;
21 FstreamUtil::OpenFile("test/sakila/duckdb-sakila-schema.sql", schema_file);
22
23 // woo hacks, use getline to separate queries
24 while (getline(schema_file, token, ';')) {
25 REQUIRE_NO_FAIL(con.Query(token));
26 }
27
28 // read data
29 GzipStream data_file("test/sakila/duckdb-sakila-insert-data.sql.gz");
30 while (getline(data_file, token, ';')) {
31 REQUIRE_NO_FAIL(con.Query(token));
32 }
33 // only enable verification here because of 40k or so inserts above
34 con.EnableQueryVerification();
35
36 result = con.Query("SELECT first_name,last_name FROM actor ORDER BY actor_id LIMIT 10");
37 REQUIRE_NO_FAIL(*result);
38 REQUIRE(CHECK_COLUMN(
39 result, 0, {"PENELOPE", "NICK", "ED", "JENNIFER", "JOHNNY", "BETTE", "GRACE", "MATTHEW", "JOE", "CHRISTIAN"}));
40 REQUIRE(CHECK_COLUMN(result, 1,
41 {"GUINESS", "WAHLBERG", "CHASE", "DAVIS", "LOLLOBRIGIDA", "NICHOLSON", "MOSTEL", "JOHANSSON",
42 "SWANK", "GABLE"}));
43
44 result = con.Query("SELECT actor_id, first_name, last_name FROM actor WHERE first_name='JOE' ORDER BY actor_id");
45 REQUIRE_NO_FAIL(*result);
46 REQUIRE(CHECK_COLUMN(result, 0, {9}));
47 REQUIRE(CHECK_COLUMN(result, 1, {"JOE"}));
48 REQUIRE(CHECK_COLUMN(result, 2, {"SWANK"}));
49
50 result =
51 con.Query("SELECT actor_id, first_name, last_name FROM actor WHERE last_name LIKE '%GEN%' ORDER BY actor_id");
52 REQUIRE_NO_FAIL(*result);
53 REQUIRE(CHECK_COLUMN(result, 0, {14, 41, 107, 166}));
54 REQUIRE(CHECK_COLUMN(result, 1, {"VIVIEN", "JODIE", "GINA", "NICK"}));
55 REQUIRE(CHECK_COLUMN(result, 2, {"BERGEN", "DEGENERES", "DEGENERES", "DEGENERES"}));
56
57 result = con.Query("SELECT country_id, country FROM country WHERE country IN('Afghanistan', 'Bangladesh', 'China') "
58 "ORDER BY country_id");
59 REQUIRE_NO_FAIL(*result);
60 REQUIRE(CHECK_COLUMN(result, 0, {1, 12, 23}));
61 REQUIRE(CHECK_COLUMN(result, 1, {"Afghanistan", "Bangladesh", "China"}));
62
63 result = con.Query("SELECT last_name, COUNT(*) as lnc FROM actor GROUP BY last_name HAVING COUNT(*) >=4 ORDER BY "
64 "lnc desc, last_name");
65 REQUIRE_NO_FAIL(*result);
66 REQUIRE(CHECK_COLUMN(result, 0, {"KILMER", "NOLTE", "TEMPLE"}));
67 REQUIRE(CHECK_COLUMN(result, 1, {5, 4, 4}));
68
69 result = con.Query("SELECT first_name, last_name, address FROM staff INNER JOIN address ON staff.address_id = "
70 "address.address_id order by first_name, last_name");
71 REQUIRE_NO_FAIL(*result);
72 REQUIRE(CHECK_COLUMN(result, 0, {"Jon", "Mike"}));
73 REQUIRE(CHECK_COLUMN(result, 1, {"Stephens", "Hillyer"}));
74 REQUIRE(CHECK_COLUMN(result, 2, {"1411 Lillydale Drive", "23 Workhaven Lane"}));
75
76 result =
77 con.Query("SELECT first_name, last_name, SUM(amount) as total FROM staff INNER JOIN payment ON staff.staff_id "
78 "= payment.staff_id AND payment_date LIKE '2005-08%' GROUP BY first_name, last_name ORDER BY total");
79 REQUIRE_NO_FAIL(*result);
80 REQUIRE(CHECK_COLUMN(result, 0, {"Mike", "Jon"}));
81 REQUIRE(CHECK_COLUMN(result, 1, {"Hillyer", "Stephens"}));
82 REQUIRE(CHECK_COLUMN(result, 2, {11853.65, 12218.48}));
83
84 result = con.Query("SELECT title, COUNT(actor_id) as actor_count FROM film_actor INNER JOIN film ON "
85 "film_actor.film_id = film.film_id GROUP BY title order by actor_count desc, title limit 10");
86 REQUIRE_NO_FAIL(*result);
87 REQUIRE(CHECK_COLUMN(result, 0,
88 {"LAMBS CINCINATTI", "BOONDOCK BALLROOM", "CHITTY LOCK", "CRAZY HOME", "DRACULA CRYSTAL",
89 "MUMMY CREATURES", "RANDOM GO", "ARABIA DOGMA", "HELLFIGHTERS SIERRA", "LESSON CLEOPATRA"}));
90 REQUIRE(CHECK_COLUMN(result, 1, {15, 13, 13, 13, 13, 13, 13, 12, 12, 12}));
91
92 result = con.Query("SELECT title, COUNT(title) as copies_available FROM film INNER JOIN inventory ON film.film_id "
93 "= inventory.film_id WHERE title = 'HUNCHBACK IMPOSSIBLE' GROUP BY title");
94 REQUIRE_NO_FAIL(*result);
95 REQUIRE(CHECK_COLUMN(result, 0, {"HUNCHBACK IMPOSSIBLE"}));
96 REQUIRE(CHECK_COLUMN(result, 1, {6}));
97
98 result = con.Query("SELECT first_name, last_name, SUM(amount) as total_paid FROM payment INNER JOIN customer ON "
99 "payment.customer_id = customer.customer_id GROUP BY first_name, last_name ORDER BY total_paid "
100 "desc, first_name limit 10");
101 REQUIRE_NO_FAIL(*result);
102 REQUIRE(CHECK_COLUMN(result, 0,
103 {"KARL", "ELEANOR", "CLARA", "MARION", "RHONDA", "TOMMY", "WESLEY", "TIM", "MARCIA", "ANA"}));
104 REQUIRE(CHECK_COLUMN(result, 1,
105 {"SEAL", "HUNT", "SHAW", "SNYDER", "KENNEDY", "COLLAZO", "BULL", "CARY", "DEAN", "BRADLEY"}));
106 REQUIRE(CHECK_COLUMN(result, 2, {221.55, 216.54, 195.58, 194.61, 194.61, 186.62, 177.6, 175.61, 175.58, 174.66}));
107
108 result = con.Query(
109 "SELECT title FROM film WHERE title LIKE 'K%' OR title LIKE 'Q%' AND title IN ( SELECT title FROM film WHERE "
110 "language_id IN ( SELECT language_id FROM language WHERE name ='English' ) ) ORDER BY title");
111 REQUIRE_NO_FAIL(*result);
112 REQUIRE(CHECK_COLUMN(result, 0,
113 {"KANE EXORCIST", "KARATE MOON", "KENTUCKIAN GIANT", "KICK SAVANNAH", "KILL BROTHERHOOD",
114 "KILLER INNOCENT", "KING EVOLUTION", "KISS GLORY", "KISSING DOLLS", "KNOCK WARLOCK",
115 "KRAMER CHOCOLATE", "KWAI HOMEWARD", "QUEEN LUKE", "QUEST MUSSOLINI", "QUILLS BULL"}));
116
117 result = con.Query("SELECT first_name, last_name FROM actor WHERE actor_id IN ( SELECT actor_id FROM film_actor "
118 "WHERE film_id IN ( SELECT film_id FROM film WHERE title = 'ALONE TRIP' ) ) ORDER BY actor_id");
119 REQUIRE_NO_FAIL(*result);
120 REQUIRE(CHECK_COLUMN(result, 0, {"ED", "KARL", "UMA", "WOODY", "SPENCER", "CHRIS", "LAURENCE", "RENEE"}));
121 REQUIRE(CHECK_COLUMN(result, 1, {"CHASE", "BERRY", "WOOD", "JOLIE", "DEPP", "DEPP", "BULLOCK", "BALL"}));
122
123 result = con.Query(
124 "SELECT first_name, last_name, email FROM customer JOIN address ON (customer.address_id = address.address_id) "
125 "JOIN city ON (city.city_id = address.city_id) JOIN country ON (country.country_id = city.country_id) WHERE "
126 "country.country= 'Canada' ORDER BY first_name, last_name");
127 REQUIRE_NO_FAIL(*result);
128 REQUIRE(CHECK_COLUMN(result, 0, {"CURTIS", "DARRELL", "DERRICK", "LORETTA", "TROY"}));
129 REQUIRE(CHECK_COLUMN(result, 1, {"IRBY", "POWER", "BOURQUE", "CARPENTER", "QUIGLEY"}));
130 REQUIRE(CHECK_COLUMN(result, 2,
131 {"CURTIS.IRBY@sakilacustomer.org", "DARRELL.POWER@sakilacustomer.org",
132 "DERRICK.BOURQUE@sakilacustomer.org", "LORETTA.CARPENTER@sakilacustomer.org",
133 "TROY.QUIGLEY@sakilacustomer.org"}));
134
135 result = con.Query("SELECT title FROM film WHERE film_id IN ( SELECT film_id FROM film_category WHERE category_id "
136 "IN ( SELECT category_id FROM category WHERE name='Family' ) ) ORDER BY film_id LIMIT 10");
137 REQUIRE_NO_FAIL(*result);
138 REQUIRE(
139 CHECK_COLUMN(result, 0,
140 {"AFRICAN EGG", "APACHE DIVINE", "ATLANTIS CAUSE", "BAKED CLEOPATRA", "BANG KWAI",
141 "BEDAZZLED MARRIED", "BILKO ANONYMOUS", "BLANKET BEVERLY", "BLOOD ARGONAUTS", "BLUES INSTINCT"}));
142
143 result = con.Query("SELECT title, COUNT(rental_id) as rental_count FROM rental JOIN inventory ON "
144 "(rental.inventory_id = inventory.inventory_id) JOIN film ON (inventory.film_id = film.film_id) "
145 "GROUP BY film.title ORDER BY COUNT(rental_id) DESC, title limit 10");
146 REQUIRE_NO_FAIL(*result);
147 REQUIRE(
148 CHECK_COLUMN(result, 0,
149 {"BUCKET BROTHERHOOD", "ROCKETEER MOTHER", "FORWARD TEMPLE", "GRIT CLOCKWORK", "JUGGLER HARDLY",
150 "RIDGEMONT SUBMARINE", "SCALAWAG DUCK", "APACHE DIVINE", "GOODFELLAS SALUTE", "HOBBIT ALIEN"}));
151 REQUIRE(CHECK_COLUMN(result, 1, {34, 33, 32, 32, 32, 32, 32, 31, 31, 31}));
152
153 result = con.Query("SELECT store.store_id, SUM(amount) as sum_amount FROM store INNER JOIN staff ON store.store_id "
154 "= staff.store_id INNER JOIN payment ON payment.staff_id = staff.staff_id GROUP BY "
155 "store.store_id order by sum_amount");
156 REQUIRE_NO_FAIL(*result);
157 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
158 REQUIRE(CHECK_COLUMN(result, 1, {33489.47, 33927.04}));
159
160 result = con.Query("SELECT store_id, city, country FROM store INNER JOIN address ON store.address_id = "
161 "address.address_id INNER JOIN city ON city.city_id = address.city_id INNER JOIN country ON "
162 "country.country_id = city.country_id order by store_id");
163 REQUIRE_NO_FAIL(*result);
164 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
165 REQUIRE(CHECK_COLUMN(result, 1, {"Lethbridge", "Woodridge"}));
166 REQUIRE(CHECK_COLUMN(result, 2, {"Canada", "Australia"}));
167
168 result = con.Query("SELECT name, SUM(amount) FROM category INNER JOIN film_category ON category.category_id = "
169 "film_category.category_id INNER JOIN inventory ON film_category.film_id = inventory.film_id "
170 "INNER JOIN rental ON inventory.inventory_id = rental.inventory_id INNER JOIN payment ON "
171 "rental.rental_id = payment.rental_id GROUP BY name ORDER BY SUM(amount) DESC LIMIT 5");
172 REQUIRE_NO_FAIL(*result);
173 REQUIRE(CHECK_COLUMN(result, 0, {"Sports", "Sci-Fi", "Animation", "Drama", "Comedy"}));
174 REQUIRE(CHECK_COLUMN(result, 1, {5314.21, 4756.98, 4656.3, 4587.39, 4383.58}));
175
176 // these four queries are equivalent, see https://www.jooq.org/benchmark
177
178 result = con.Query("SELECT first_name, last_name, count(*) FROM actor a JOIN film_actor fa USING (actor_id) WHERE "
179 "last_name LIKE 'A%' GROUP BY fa.actor_id, a.first_name, a.last_name ORDER BY count(*) DESC");
180 REQUIRE_NO_FAIL(*result);
181 REQUIRE(CHECK_COLUMN(result, 0, {"KIRSTEN", "CHRISTIAN", "ANGELINA", "KIM", "CUBA", "DEBBIE", "MERYL"}));
182 REQUIRE(CHECK_COLUMN(result, 1, {"AKROYD", "AKROYD", "ASTAIRE", "ALLEN", "ALLEN", "AKROYD", "ALLEN"}));
183 REQUIRE(CHECK_COLUMN(result, 2, {34, 32, 31, 28, 25, 24, 22}));
184
185 result = con.Query(
186 "SELECT first_name, last_name, count(*) FROM ( SELECT * FROM actor WHERE last_name LIKE 'A%' ) a JOIN "
187 "film_actor USING (actor_id) GROUP BY a.actor_id, a.first_name, a.last_name ORDER BY count(*) DESC");
188 REQUIRE_NO_FAIL(*result);
189 REQUIRE(CHECK_COLUMN(result, 0, {"KIRSTEN", "CHRISTIAN", "ANGELINA", "KIM", "CUBA", "DEBBIE", "MERYL"}));
190 REQUIRE(CHECK_COLUMN(result, 1, {"AKROYD", "AKROYD", "ASTAIRE", "ALLEN", "ALLEN", "AKROYD", "ALLEN"}));
191 REQUIRE(CHECK_COLUMN(result, 2, {34, 32, 31, 28, 25, 24, 22}));
192
193 result =
194 con.Query("SELECT * FROM ( SELECT first_name, last_name, ( SELECT count(*) FROM film_actor fa WHERE a.actor_id "
195 "= fa.actor_id ) AS c FROM actor a WHERE last_name LIKE 'A%' ) a WHERE c > 0 ORDER BY c DESC");
196 REQUIRE_NO_FAIL(*result);
197 REQUIRE(CHECK_COLUMN(result, 0, {"KIRSTEN", "CHRISTIAN", "ANGELINA", "KIM", "CUBA", "DEBBIE", "MERYL"}));
198 REQUIRE(CHECK_COLUMN(result, 1, {"AKROYD", "AKROYD", "ASTAIRE", "ALLEN", "ALLEN", "AKROYD", "ALLEN"}));
199 REQUIRE(CHECK_COLUMN(result, 2, {34, 32, 31, 28, 25, 24, 22}));
200
201 result = con.Query("SELECT first_name, last_name, c FROM actor JOIN ( SELECT actor_id, count(*) c FROM film_actor "
202 "GROUP BY actor_id ) fa USING (actor_id) WHERE last_name LIKE 'A%' ORDER BY c DESC");
203 REQUIRE_NO_FAIL(*result);
204 REQUIRE(CHECK_COLUMN(result, 0, {"KIRSTEN", "CHRISTIAN", "ANGELINA", "KIM", "CUBA", "DEBBIE", "MERYL"}));
205 REQUIRE(CHECK_COLUMN(result, 1, {"AKROYD", "AKROYD", "ASTAIRE", "ALLEN", "ALLEN", "AKROYD", "ALLEN"}));
206 REQUIRE(CHECK_COLUMN(result, 2, {34, 32, 31, 28, 25, 24, 22}));
207}
208