Query#
MuduDB supports single-table SELECT queries with a WHERE clause for filtering rows. The query engine evaluates predicates and returns a result set to the caller.
3.1 Overview#
A query in MuduDB is executed in the context of the current transaction. All reads are consistent with the transaction’s isolation level. Results are returned as a row set that the client can iterate over.
The current query capability is intentionally scoped to single-table lookups and scans. Joins, subqueries, and GROUP BY-style grouped analytics are not supported in v0.1.
3.2 Select#
Syntax#
SELECT select_term [, select_term ...]
FROM table_name
[WHERE predicate [AND predicate ...]];
Select Terms#
A select_term is one of:
column_name— select a single column.A single-argument aggregate call —
COUNT(*),COUNT(column),SUM(column),AVG(column),MIN(column),MAX(column).
Each select term can carry an optional alias (AS alias). Without GROUP BY, the select list must be all plain columns or all aggregate calls; mixing the two is not supported.
WHERE Predicates#
Each predicate compares a column with a value (a literal or a ? placeholder):
column_name comparison_operator value
The reversed form (value on the left, column on the right) is also accepted; the parser normalises it to column-on-the-left.
Comparison operators:
Operator |
Meaning |
|---|---|
|
Equal |
|
Not equal |
|
Less than |
|
Less than or equal |
|
Greater than |
|
Greater than or equal |
Predicates are combined with the AND logical connective only. OR and NOT are not supported in v0.1.
How a predicate is executed depends on the columns involved:
Equality predicates covering a left prefix of the primary key (e.g.
w = 1orw = 1 AND d = 2for a primary key(w, d, o)) use a primary-key point or prefix lookup; equality predicates on non-prefix key columns only (e.g.d = 2) are rejected.Range comparisons (
>,>=,<,<=) on primary-key columns use a primary-key range scan; however, equality and range predicates on key columns cannot be mixed (e.g.w = 1 AND d > 2is rejected).Predicates on non-key columns are evaluated row by row after the key access and can be freely combined with key predicates.
!=cannot be used on primary-key columns (it is allowed on non-key columns).
Expressions#
An operand in a predicate can be:
A column reference
A numeric or string literal
A placeholder (
?; named parameters:nameresolved by the server adapter are also available, see Data Manipulation)
Examples#
Select columns from a table:
SELECT id, name FROM users;
Select specific columns with a filter:
SELECT id, name FROM users WHERE id = 1;
Select with multiple predicates:
SELECT ol_i_id, ol_amount
FROM order_line
WHERE ol_w_id = 1 AND ol_d_id = 2 AND ol_o_id = 100;
Select with placeholders (prepared statement):
SELECT s_i_id, s_quantity FROM stock WHERE s_w_id = ? AND s_i_id = ?;
Select with a reversed comparison (parser normalises the shape):
SELECT id FROM users WHERE 7 > id;
-- internally normalised to: id < 7
Select with aggregate calls in the term list:
SELECT COUNT(*) AS cnt, SUM(ol_amount) FROM order_line WHERE ol_w_id = 1;
Unsupported Query Features#
The following are not available in v0.1:
JOIN(inner, outer, cross)GROUP BY/HAVINGORDER BYLIMIT/OFFSETDISTINCTThe
*wildcard (SELECT *); list columns explicitlySubqueries (
SELECT ... FROM (SELECT ...))Window functions
Common Table Expressions (
WITH)Set operations (
UNION,INTERSECT,EXCEPT)