| # Copyright 2016, Quickstep Research Group, Computer Sciences Department, |
| # University of Wisconsin—Madison. |
| # |
| # Licensed under the Apache License, Version 2.0 (the "License"); |
| # you may not use this file except in compliance with the License. |
| # You may obtain a copy of the License at |
| # |
| # http://www.apache.org/licenses/LICENSE-2.0 |
| # |
| # Unless required by applicable law or agreed to in writing, software |
| # distributed under the License is distributed on an "AS IS" BASIS, |
| # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| # See the License for the specific language governing permissions and |
| # limitations under the License. |
| |
| # Prepare testing relations |
| CREATE TABLE a(w INT, x LONG, y DOUBLE, z VARCHAR(16)); |
| CREATE TABLE b(w INT, x LONG); |
| CREATE TABLE c(x LONG, y DOUBLE); |
| CREATE TABLE d(y DOUBLE, z VARCHAR(16)); |
| |
| INSERT INTO a VALUES(0, 0, 0, 'C0'); |
| INSERT INTO a VALUES(1, 10, 100, 'C1'); |
| INSERT INTO a VALUES(2, 20, 200, 'C2'); |
| INSERT INTO a VALUES(3, 30, 300, 'C3'); |
| INSERT INTO a VALUES(4, 40, 400, 'C4'); |
| INSERT INTO a VALUES(5, 50, 500, 'C5'); |
| INSERT INTO a VALUES(6, 60, 600, 'C6'); |
| INSERT INTO a VALUES(7, 70, 700, 'C7'); |
| INSERT INTO a VALUES(8, 80, 800, 'C8'); |
| INSERT INTO a VALUES(9, 90, 900, 'C9'); |
| INSERT INTO a VALUES(10, 100, 1000, 'C10'); |
| INSERT INTO a VALUES(11, 110, 1100, 'C11'); |
| INSERT INTO a VALUES(12, 120, 1200, 'C12'); |
| INSERT INTO a VALUES(13, 130, 1300, 'C13'); |
| INSERT INTO a VALUES(14, 140, 1400, 'C14'); |
| INSERT INTO a VALUES(15, 150, 1500, 'C15'); |
| INSERT INTO a VALUES(16, 160, 1600, 'C16'); |
| INSERT INTO a VALUES(17, 170, 1700, 'C17'); |
| INSERT INTO a VALUES(18, 180, 1800, 'C18'); |
| INSERT INTO a VALUES(19, 190, 1900, 'C19'); |
| |
| INSERT INTO b |
| SELECT w, x + (w/2)%2 |
| FROM a |
| WHERE w % 2 = 0; |
| |
| INSERT INTO c |
| SELECT x, y + (x/3)%3-1 |
| FROM a |
| WHERE x % 3 = 0; |
| |
| INSERT INTO d |
| SELECT y, z |
| FROM a; |
| -- |
| == |
| |
| |
| # Join queries |
| SELECT a.w, b.x, c.y, d.z |
| FROM a JOIN b ON a.w = b.w |
| JOIN c ON a.x = c.x |
| JOIN d ON a.y = d.y; |
| -- |
| +-----------+--------------------+------------------------+----------------+ |
| |w |x |y |z | |
| +-----------+--------------------+------------------------+----------------+ |
| | 0| 0| -1| C0| |
| | 6| 61| 601| C6| |
| | 12| 120| 1200| C12| |
| | 18| 181| 1799| C18| |
| +-----------+--------------------+------------------------+----------------+ |
| == |
| |
| SELECT a.w, b.x, c.y |
| FROM a JOIN b ON (a.w = b.w OR a.x > b.x) |
| JOIN c ON (a.x = c.x AND a.y > c.y) |
| JOIN d ON (a.y = d.y OR a.z > d.z) |
| GROUP BY a.w, b.x, c.y |
| ORDER BY a.w, b.x, c.y; |
| -- |
| +-----------+--------------------+------------------------+ |
| |w |x |y | |
| +-----------+--------------------+------------------------+ |
| | 0| 0| -1| |
| | 9| 0| 899| |
| | 9| 21| 899| |
| | 9| 40| 899| |
| | 9| 61| 899| |
| | 9| 80| 899| |
| | 18| 0| 1799| |
| | 18| 21| 1799| |
| | 18| 40| 1799| |
| | 18| 61| 1799| |
| | 18| 80| 1799| |
| | 18| 101| 1799| |
| | 18| 120| 1799| |
| | 18| 141| 1799| |
| | 18| 160| 1799| |
| | 18| 181| 1799| |
| +-----------+--------------------+------------------------+ |
| == |
| |
| SELECT a1.w, b1.x, c1.y, d1.z |
| FROM a AS a1 JOIN b AS b1 ON a1.w = b1.w |
| JOIN c AS c1 ON a1.x = c1.x |
| JOIN d AS d1 ON a1.y = d1.y |
| WHERE a1.x = b1.x |
| AND a1.y = c1.y |
| AND a1.z = d1.z; |
| -- |
| +-----------+--------------------+------------------------+----------------+ |
| |w |x |y |z | |
| +-----------+--------------------+------------------------+----------------+ |
| | 12| 120| 1200| C12| |
| +-----------+--------------------+------------------------+----------------+ |
| == |
| |
| SELECT a1.w, b1.x, c1.y, d1.z |
| FROM a AS a1 JOIN b AS b1 ON a1.x <> b1.x |
| JOIN c AS c1 ON a1.y <> c1.y |
| JOIN d AS d1 ON a1.z = d1.z |
| WHERE a1.w = b1.w |
| AND a1.x = c1.x; |
| -- |
| +-----------+--------------------+------------------------+----------------+ |
| |w |x |y |z | |
| +-----------+--------------------+------------------------+----------------+ |
| | 6| 61| 601| C6| |
| | 18| 181| 1799| C18| |
| +-----------+--------------------+------------------------+----------------+ |
| == |
| |
| SELECT a.w AS "a.w", b.x AS "b.x", c.y AS "c.y", d.z AS "d.z" |
| FROM a LEFT JOIN b ON a.w = b.w |
| LEFT JOIN c ON a.x = c.x |
| LEFT JOIN d ON a.y = d.y |
| ORDER BY a.w; |
| -- |
| +-----------+--------------------+------------------------+----------------+ |
| |a.w |b.x |c.y |d.z | |
| +-----------+--------------------+------------------------+----------------+ |
| | 0| 0| -1| C0| |
| | 1| NULL| NULL| C1| |
| | 2| 21| NULL| C2| |
| | 3| NULL| 300| C3| |
| | 4| 40| NULL| C4| |
| | 5| NULL| NULL| C5| |
| | 6| 61| 601| C6| |
| | 7| NULL| NULL| C7| |
| | 8| 80| NULL| C8| |
| | 9| NULL| 899| C9| |
| | 10| 101| NULL| C10| |
| | 11| NULL| NULL| C11| |
| | 12| 120| 1200| C12| |
| | 13| NULL| NULL| C13| |
| | 14| 141| NULL| C14| |
| | 15| NULL| 1501| C15| |
| | 16| 160| NULL| C16| |
| | 17| NULL| NULL| C17| |
| | 18| 181| 1799| C18| |
| | 19| NULL| NULL| C19| |
| +-----------+--------------------+------------------------+----------------+ |
| == |
| |
| SELECT a.w AS "a.w", b.x AS "b.x", c.y AS "c.y", d.z AS "d.z" |
| FROM a LEFT JOIN b ON a.w = b.w |
| LEFT JOIN c ON b.x = c.x |
| LEFT JOIN d ON c.y = d.y |
| ORDER BY a.w; |
| -- |
| +-----------+--------------------+------------------------+----------------+ |
| |a.w |b.x |c.y |d.z | |
| +-----------+--------------------+------------------------+----------------+ |
| | 0| 0| -1| NULL| |
| | 1| NULL| NULL| NULL| |
| | 2| 21| NULL| NULL| |
| | 3| NULL| NULL| NULL| |
| | 4| 40| NULL| NULL| |
| | 5| NULL| NULL| NULL| |
| | 6| 61| NULL| NULL| |
| | 7| NULL| NULL| NULL| |
| | 8| 80| NULL| NULL| |
| | 9| NULL| NULL| NULL| |
| | 10| 101| NULL| NULL| |
| | 11| NULL| NULL| NULL| |
| | 12| 120| 1200| C12| |
| | 13| NULL| NULL| NULL| |
| | 14| 141| NULL| NULL| |
| | 15| NULL| NULL| NULL| |
| | 16| 160| NULL| NULL| |
| | 17| NULL| NULL| NULL| |
| | 18| 181| NULL| NULL| |
| | 19| NULL| NULL| NULL| |
| +-----------+--------------------+------------------------+----------------+ |
| == |
| |
| SELECT b.x AS "b.x", c1.x AS "c1.x", c2.x AS "c2.x" |
| FROM b LEFT JOIN c c1 ON (b.x = c1.x) |
| LEFT JOIN c c2 ON (b.x = c2.x AND c2.x > 10) |
| ORDER BY b.x; |
| -- |
| +--------------------+--------------------+--------------------+ |
| |b.x |c1.x |c2.x | |
| +--------------------+--------------------+--------------------+ |
| | 0| 0| NULL| |
| | 21| NULL| NULL| |
| | 40| NULL| NULL| |
| | 61| NULL| NULL| |
| | 80| NULL| NULL| |
| | 101| NULL| NULL| |
| | 120| 120| 120| |
| | 141| NULL| NULL| |
| | 160| NULL| NULL| |
| | 181| NULL| NULL| |
| +--------------------+--------------------+--------------------+ |
| == |
| |
| SELECT a.w AS "a.w", b.x AS "b.x", c.y AS "c.y", d.z AS "d.z" |
| FROM a RIGHT JOIN b ON a.w = b.w |
| JOIN c ON a.x = c.x |
| LEFT JOIN d ON a.y = d.y |
| WHERE b.x > 10 |
| AND c.y < 1000; |
| -- |
| +-----------+--------------------+------------------------+----------------+ |
| |a.w |b.x |c.y |d.z | |
| +-----------+--------------------+------------------------+----------------+ |
| | 6| 61| 601| C6| |
| +-----------+--------------------+------------------------+----------------+ |
