Alopex DB v0.8.8 Tutorial, Part 3: CASE, Set Operations, CTEs, and Window Functions

Part 2 covered SELECT, WHERE, joins, and aggregation. Part 3 adds four SQL features.

The v0.8.8 release verification confirmed that these queries produce the same results through the library, embedded, server, and cluster-aware modes. This part concentrates on query syntax.

• CASE expressions assign values according to conditions.
• Set operations combine two query results.
• WITH clauses give names to queries.
• Window functions calculate rankings and running totals without removing rows.

The examples use the same articles table as Part 2.

1. Environment and setup

Create the table from Part 2.

from alopex import Database

db = Database.new()
db.execute_sql("""CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  views INTEGER,
  rating REAL
)""")

rows = [
    "(1, 'Rustで書くLSMツリー', 'mio', 1200, 3.5)",
    "(2, 'SQLパーサをNimで実装する', 'mio', 800, 4.5)",
    "(3, 'ベクトル検索の基礎', 'ren', 1500, 4.5)",
    "(4, '組み込みDBの選び方', 'ren', 600, 4.0)",
    "(5, 'WALとクラッシュ復旧', 'sora', 300, NULL)",
]
for row in rows:
    db.execute_sql(f"INSERT INTO articles VALUES {row}")

The data has three useful properties. Two rows have rating = 4.5, creating a tie in rankings. The rating for id = 5 is NULL, so aggregates exclude it. The most-viewed and highest-rated articles are not identical, producing different sets for the two filters.

2. Assigning values with CASE

Classify articles by view count.

print(db.execute_sql("""
SELECT id,
       CASE WHEN views >= 1000 THEN '人気' ELSE '通常' END AS band
FROM articles
ORDER BY id
"""))

[{'id': 1, 'band': '人気'},
 {'id': 2, 'band': '通常'},
 {'id': 3, 'band': '人気'},
 {'id': 4, 'band': '通常'},
 {'id': 5, 'band': '通常'}]

The form is CASE WHEN condition THEN value ELSE value END. A matching condition produces the THEN value; otherwise, the expression produces the ELSE value.

Any number of WHEN branches can appear. They are evaluated from top to bottom, and the first match is used.

Omitting ELSE

ELSE is optional.

print(db.execute_sql("""
SELECT id,
       CASE WHEN rating > 4.0 THEN '高評価' END AS top
FROM articles
ORDER BY id
"""))

[{'id': 1, 'top': None},
 {'id': 2, 'top': '高評価'},
 {'id': 3, 'top': '高評価'},
 {'id': 4, 'top': None},
 {'id': 5, 'top': None}]

Rows that match no WHEN branch receive NULL. The rating for id = 5 is NULL, so its comparison is not true and its result is also NULL.

Resolving branch types

Branches with different numeric types are converted to a common numeric type.

print(db.execute_sql("""
SELECT id,
       CASE WHEN views >= 1000 THEN 1 ELSE 0.5 END AS score
FROM articles
ORDER BY id
"""))

[{'id': 1, 'score': 1.0},
 {'id': 2, 'score': 0.5},
 {'id': 3, 'score': 1.0},
 {'id': 4, 'score': 0.5},
 {'id': 5, 'score': 0.5}]

Although THEN 1 is an integer, the result is 1.0. Because ELSE 0.5 is floating point, the entire result column becomes floating point.

An incompatible combination is rejected before execution.

db.execute_sql("SELECT CASE WHEN TRUE THEN 1 ELSE 'text' END")

error[ALOPEX-T001]: type mismatch: expected Integer, found Text

Integer and text values have no common result type.

Evaluating only the matching branch

CASE stops evaluating after the first matching WHEN.

print(db.execute_sql("SELECT CASE WHEN TRUE THEN 7 ELSE 1 / 0 END AS v"))

[{'v': 7}]

The division by zero in ELSE does not raise an error because WHEN TRUE matches first.

3. Referring to SELECT aliases from later clauses

Part 2 introduced aliases such as AS n. ORDER BY and HAVING can refer to these aliases by name.

print(db.execute_sql("""
SELECT author, SUM(views) AS total
FROM articles
GROUP BY author
HAVING total >= 2000
ORDER BY author
"""))

[{'author': 'mio', 'total': 2000},
 {'author': 'ren', 'total': 2100}]

HAVING total >= 2000 has the same meaning as HAVING SUM(views) >= 2000, avoiding repetition of the aggregate expression.

Aliases are visible to ORDER BY and HAVING, but not to WHERE or GROUP BY.

db.execute_sql("SELECT views AS v FROM articles WHERE v > 1000")

error[ALOPEX-C003]: column 'v' not found in table 'articles'

Under the SQL evaluation order, WHERE and GROUP BY run before SELECT, so the alias does not exist when their names are resolved.

An alias wins when names collide.

print(db.execute_sql("SELECT views AS id FROM articles ORDER BY id"))

[{'id': 300}, {'id': 600}, {'id': 800}, {'id': 1200}, {'id': 1500}]

ORDER BY id sorts by views, which was aliased as id, rather than by the primary key. Qualify the original column as articles.id when that is the intended ordering.

4. UNION, INTERSECT, and EXCEPT

Query articles with many views and articles with high ratings separately.

print(db.execute_sql("SELECT id FROM articles WHERE views >= 800 ORDER BY id"))
print(db.execute_sql("SELECT id FROM articles WHERE rating >= 4.0 ORDER BY id"))

[{'id': 1}, {'id': 2}, {'id': 3}]
[{'id': 2}, {'id': 3}, {'id': 4}]

The left result is {1, 2, 3}, and the right is {2, 3, 4}. Combine them with four operators.

left = "SELECT id FROM articles WHERE views >= 800"
right = "SELECT id FROM articles WHERE rating >= 4.0"

for op in ["UNION", "UNION ALL", "INTERSECT", "EXCEPT"]:
    print(op, db.execute_sql(f"{left} {op} {right} ORDER BY id"))

UNION [{'id': 1}, {'id': 2}, {'id': 3}, {'id': 4}]
UNION ALL [{'id': 1}, {'id': 2}, {'id': 2}, {'id': 3}, {'id': 3}, {'id': 4}]
INTERSECT [{'id': 2}, {'id': 3}]
EXCEPT [{'id': 1}]

Reversing the operands changes EXCEPT.

print(db.execute_sql(f"{right} EXCEPT {left} ORDER BY id"))

[{'id': 4}]

The remaining row, id = 4, has a high rating but fewer views.

Chaining three or more operators

INTERSECT binds more tightly than UNION and EXCEPT. Operators at the same precedence are evaluated from left to right, and ALL applies only to the operator where it appears.

print(db.execute_sql("SELECT 1 AS v UNION ALL SELECT 1 UNION SELECT 1"))
print(db.execute_sql("SELECT 1 AS v UNION SELECT 1 UNION ALL SELECT 1"))
print(db.execute_sql("SELECT 1 AS v UNION SELECT 2 INTERSECT SELECT 2"))

[{'v': 1}]
[{'v': 1}, {'v': 1}]
[{'v': 1}, {'v': 2}]

In the first query, the final UNION removes duplicates from the entire result, leaving one row. In the second, UNION first reduces the result to one row and UNION ALL then retains the duplicate, leaving two. In the third, 2 INTERSECT 2 is evaluated first.

NULL and incompatible result shapes

NULL is treated as a value for set operations.

print(db.execute_sql("SELECT rating FROM articles UNION SELECT rating FROM articles"))

[{'rating': 4.5}, {'rating': 4.0}, {'rating': 3.5}, {'rating': None}]

Two rows have rating = 4.5, but UNION reduces them to one. NULL values are also considered duplicates and collapse into one row.

Different column counts or incompatible types are rejected before execution.

db.execute_sql("SELECT id FROM articles UNION SELECT id, title FROM articles")

error[ALOPEX-T008]: set operation column count mismatch: left 1, right 2

db.execute_sql("SELECT id FROM articles UNION SELECT title FROM articles")

error[ALOPEX-T001]: type mismatch: expected Integer, found Text

5. Naming a query with WITH

A WITH clause before a SELECT gives a subquery a name that the main query can reference.

WITH name AS (SELECT ...)
SELECT ... FROM name;

The name AS (...) definition is a common table expression, or CTE.

Name the popular articles and join them to articles by the same author.

print(db.execute_sql("""
WITH popular AS (
  SELECT id, author FROM articles WHERE views >= 800
)
SELECT articles.id AS aid, popular.id AS pid
FROM articles
JOIN popular ON articles.author = popular.author
ORDER BY aid, pid
"""))

[{'aid': 1, 'pid': 1}, {'aid': 1, 'pid': 2},
 {'aid': 2, 'pid': 1}, {'aid': 2, 'pid': 2},
 {'aid': 3, 'pid': 3}, {'aid': 4, 'pid': 3}]

A CTE joins like a table. Because mio has two articles on each side, their combinations produce four rows. A CTE does not remove duplicates automatically; use DISTINCT or GROUP BY when uniqueness is required.

Multiple CTE definitions can be listed, and a later definition can refer to one defined earlier.

Reusing a table name

A CTE hides a base table with the same name for the duration of the statement.

print(db.execute_sql("""
WITH articles AS (SELECT id + 100 AS id FROM articles WHERE id = 1)
SELECT id FROM articles
"""))

[{'id': 101}]

The FROM articles inside the CTE definition refers to the base table. The outer FROM articles refers to the CTE. Use distinct names unless this shadowing is intentional.

Self-referencing CTEs

A CTE can refer to itself when declared with WITH RECURSIVE. Recursive CTEs are used for queries whose hierarchy depth is not known in advance, such as walking an organization to its root manager or expanding a bill of materials.

WITH RECURSIVE is not currently supported. Alopex DB returns ALOPEX-F001 instead of silently ignoring RECURSIVE and executing one level.

Referring to an undefined CTE in FROM returns ALOPEX-C001, the same error used for an invalid table name.

6. Rankings and running totals with window functions

GROUP BY combines rows, so it cannot display an aggregate beside every original row. Window functions calculate over a group while preserving the rows.

print(db.execute_sql("""
SELECT id,
       RANK() OVER (ORDER BY rating) AS rk,
       DENSE_RANK() OVER (ORDER BY rating) AS dk,
       SUM(views) OVER (ORDER BY id) AS running
FROM articles
ORDER BY id
"""))

[{'id': 1, 'rk': 1, 'dk': 1, 'running': 1200},
 {'id': 2, 'rk': 3, 'dk': 3, 'running': 2000},
 {'id': 3, 'rk': 3, 'dk': 3, 'running': 3500},
 {'id': 4, 'rk': 2, 'dk': 2, 'running': 4100},
 {'id': 5, 'rk': 5, 'dk': 4, 'running': 4400}]

All five input rows remain in the result. Unlike GROUP BY, the window calculation does not reduce them.

The two rows with rating = 4.5, id = 2 and id = 3, tie. RANK leaves a gap after a tie and gives the final row rank 5, while DENSE_RANK does not leave gaps and gives it rank 4.

running is the cumulative sum in id order. Supported ranking functions are ROW_NUMBER, RANK, and DENSE_RANK. Supported aggregate window functions are SUM, COUNT, AVG, MIN, and MAX.

The recording combines CASE and ROW_NUMBER() in v0.8.8. It returns the conditional result and rank together while retaining information from every row.

Defining the aggregation scope

The contents of OVER define the aggregation scope.

print(db.execute_sql("SELECT id, SUM(views) OVER () AS grand FROM articles ORDER BY id"))
print(db.execute_sql("SELECT id, SUM(views) OVER (PARTITION BY author) AS by_author FROM articles ORDER BY id"))

[{'id': 1, 'grand': 4400}, {'id': 2, 'grand': 4400}, {'id': 3, 'grand': 4400}, {'id': 4, 'grand': 4400}, {'id': 5, 'grand': 4400}]
[{'id': 1, 'by_author': 2000}, {'id': 2, 'by_author': 2000}, {'id': 3, 'by_author': 2100}, {'id': 4, 'by_author': 2100}, {'id': 5, 'by_author': 300}]

OVER () sums all rows. PARTITION BY author sums each author’s rows. The earlier OVER (ORDER BY id) accumulates from the first row through the current row.

The scope follows two rules.

1. Without ORDER BY inside OVER, the scope is the entire window.
2. With ORDER BY inside OVER, the scope runs from the first row through the current row.

The same SUM(views) therefore changes meaning according to the contents of OVER.

NULL handling

Window aggregates ignore NULL values.

print(db.execute_sql("SELECT id, SUM(rating) OVER (PARTITION BY author) AS r FROM articles ORDER BY id"))

[{'id': 1, 'r': 8.0}, {'id': 2, 'r': 8.0},
 {'id': 3, 'r': 8.5}, {'id': 4, 'r': 8.5},
 {'id': 5, 'r': None}]

sora has one article whose rating is NULL. With no value to sum, the result is NULL rather than zero.

Accessing a previous row

LAG and LEAD, which directly access adjacent rows, are not currently supported. Explicit frames with ROWS BETWEEN or RANGE BETWEEN are also unsupported. The former returns ALOPEX-F001, and the latter returns ALOPEX-P001.

For now, calculate a difference from the previous row with a self-join.

print(db.execute_sql("""
SELECT curr.id, curr.views - prev.views AS diff
FROM articles AS curr
LEFT JOIN articles AS prev ON prev.id = curr.id - 1
ORDER BY curr.id
"""))

[{'id': 1, 'diff': None},
 {'id': 2, 'diff': -400},
 {'id': 3, 'diff': 700},
 {'id': 4, 'diff': -900},
 {'id': 5, 'diff': -300}]

id = 1 has no previous row, so the LEFT JOIN produces NULL as it did in Part 2.

7. Types and values

An unexpected aggregate result can come from its type. The views column is INTEGER, and rating is REAL. The following queries show where those types affect results.

print(db.execute_sql("SELECT SUM(views) AS total FROM articles"))
print(db.execute_sql("SELECT id, views * 1.5 AS scaled FROM articles WHERE id <= 2 ORDER BY id"))
print(db.execute_sql("SELECT pg_typeof(views) AS iv, pg_typeof(rating) AS rv FROM articles WHERE id = 1"))
print(db.execute_sql("SELECT COUNT(rating) AS cnt, COUNT(*) AS all_rows FROM articles"))

[{'total': 4400}]
[{'id': 1, 'scaled': 1800.0}, {'id': 2, 'scaled': 1200.0}]
[{'iv': 'integer', 'rv': 'real'}]
[{'cnt': 4, 'all_rows': 5}]

1. A sum of integers remains an integer. SUM(views) returns 4400, not 4400.0.
2. Arithmetic mixing integers and floating-point values produces floating point. views * 1.5 returns 1800.0.
3. pg_typeof() reports column types. views is integer, while rating is real. REAL is an alias for FLOAT, a four-byte f32. Declare a column as DOUBLE when double precision is required.
4. COUNT(column) excludes NULL. COUNT(rating) is 4, while COUNT(*) is 5. Choose according to whether you need the number of rows or the number of non-NULL values.

8. Window functions and WITH from Rust

Rust passes window functions and WITH clauses directly to execute_sql(). Reuse the show function from Part 2.

use alopex_embedded::{Database, SqlResult};
use std::path::Path;

fn show(db: &Database, sql: &str) -> Result<(), Box<dyn std::error::Error>> {
    if let SqlResult::Query(q) = db.execute_sql(sql)? {
        let names: Vec<&str> = q.columns.iter().map(|c| c.name.as_str()).collect();
        println!("{}", names.join(" | "));
        for row in &q.rows {
            let cells: Vec<String> = row.iter().map(|v| format!("{v:?}")).collect();
            println!("{}", cells.join(" | "));
        }
    }
    Ok(())
}

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let db = Database::open(Path::new("./notes-db"))?;
    db.execute_sql("CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, author TEXT, views INTEGER, rating REAL)")?;
    for sql in [
        "INSERT INTO articles VALUES (1, 'Rustで書くLSMツリー', 'mio', 1200, 3.5)",
        "INSERT INTO articles VALUES (2, 'SQLパーサをNimで実装する', 'mio', 800, 4.5)",
        "INSERT INTO articles VALUES (3, 'ベクトル検索の基礎', 'ren', 1500, 4.5)",
        "INSERT INTO articles VALUES (4, '組み込みDBの選び方', 'ren', 600, 4.0)",
        "INSERT INTO articles VALUES (5, 'WALとクラッシュ復旧', 'sora', 300, NULL)",
    ] {
        db.execute_sql(sql)?;
    }

    show(&db, "SELECT title, rating,
                      RANK() OVER (ORDER BY rating DESC) AS r,
                      DENSE_RANK() OVER (ORDER BY rating DESC) AS dr
               FROM articles")?;
    Ok(())
}

title | rating | r | dr
Text("Rustで書くLSMツリー") | Float(3.5) | BigInt(4) | BigInt(3)
Text("SQLパーサをNimで実装する") | Float(4.5) | BigInt(1) | BigInt(1)
Text("ベクトル検索の基礎") | Float(4.5) | BigInt(1) | BigInt(1)
Text("組み込みDBの選び方") | Float(4.0) | BigInt(3) | BigInt(2)
Text("WALとクラッシュ復旧") | Null | BigInt(5) | BigInt(4)

The rankings match the Python results. Both rows with rating = 4.5 rank first. RANK assigns the next row rank three, while DENSE_RANK assigns rank two. The NULL rating for id = 5 sorts last.

The types from the previous section appear directly in Rust output. rating was declared as REAL, so it returns Float, while rankings return BigInt. Replacing the query with AVG and COUNT shows the aggregate result types.

show(&db, "SELECT AVG(rating) AS avg_rating, COUNT(rating) AS c_rating, COUNT(*) AS c_all
           FROM articles")?;

avg_rating | c_rating | c_all
Double(4.125) | BigInt(4) | BigInt(5)

The rating column is Float (32-bit), while AVG returns Double (64-bit) because the average is calculated at 64-bit precision. COUNT(rating) is four and COUNT(*) is five because NULL is excluded from the former.

Python returns only the numeric value 4.125, so the distinction is not visible there. Rust returns the type with the value, making the Float and Double result types explicit. If a column type is unexpected, inspect data_type in q.columns as well.

9. SQL used in this part

Part 4 covers vector search and DataFrames.

References

• v0.8.8 release verification report