DeciDB Documentation

Syntax Overview

DeciDB adds a DECIDE clause to standard SQL. You declare decision variables, define constraints with SUCH THAT, and optionally set an objective with MAXIMIZE or MINIMIZE. DeciDB solves the optimization problem and returns every candidate row with the optimal variable values filled in.

Complete Syntax

SELECT select_list
FROM table_expression
[WHERE filter_conditions]
DECIDE [Table.]variable [IS BOOLEAN | IS INTEGER | IS REAL] [, ...]
SUCH THAT
    constraint [AND constraint ...]   -- constraints may use WHEN, PER
[MAXIMIZE | MINIMIZE] objective        -- SUM / AVG / MIN / MAX / POWER / bilinear

Execution Order

DeciDB processes your query in this order:

1. 1
   FROM + WHERE — Standard SQL filtering produces candidate rows
2. 2
   DECIDE — Creates one decision variable per candidate row
3. 3
   SUCH THAT + Objective — Constraints and objective are sent to the solver
4. 4
   SELECT — Returns all candidate rows with their optimal variable values

Decision variables are available in SUCH THAT, MAXIMIZE/MINIMIZE, and SELECT.

The DECIDE Clause

Use DECIDE to declare one or more decision variables. Each candidate row gets its own instance of each variable. The solver assigns optimal values to these variables.

DECIDE variable_name [IS type] [, variable_name2 [IS type] ...]

Variable Types

Specify the variable type with IS BOOLEAN, IS INTEGER, or IS REAL. If you omit the type, the default is INTEGER.

DECIDE x IS BOOLEAN

DECIDE quantity IS INTEGER

DECIDE amount IS REAL

Multiple Variables

Declare multiple variables separated by commas. Each row gets its own instance of every variable.

-- Two variables per row: include or not, and how many
DECIDE x IS BOOLEAN, quantity IS INTEGER

Table-Scoped Variables

By default, decision variables are row-scoped — one variable per result row. Prefix the variable with a table alias to make it table-scoped: one variable per unique entity in the source table, shared across every result row originating from that entity. This reduces the solver variable count from the size of the join result to the number of distinct entities.

-- One decision per nurse, even though each nurse appears in many shift rows
SELECT n.name, s.shift_date, keepN
FROM nurses n
JOIN shifts s ON n.id = s.nurse_id
DECIDE n.keepN IS BOOLEAN
SUCH THAT SUM(keepN * s.hours) <= 100
MAXIMIZE SUM(keepN * n.skill_score);

The qualifier (n.) must match a table or alias in the FROM clause. SUM still sums over result rows, so an entity that appears in k joined rows contributes its variable k times.

Using Variables in SELECT

Reference decision variables in SELECT to see their optimal values in the output. You can also use them in expressions.

SELECT id, price, x, (x * price) AS cost
FROM Products
DECIDE x IS BOOLEAN
SUCH THAT SUM(x * price) <= 1000
MAXIMIZE SUM(x * profit);

This returns every product row. The x column shows 1 for selected products and 0 for skipped ones. The cost column shows the price for selected items and 0 otherwise.

The SUCH THAT Clause

Use SUCH THAT to define constraints that the solution must satisfy. Constraints are separated by AND. All constraints must be linear in decision variables.

Row-Level Constraints

A constraint without SUM() applies independently to each row. Use this to bound individual variable values.

SUCH THAT
    x >= 0
    AND x <= 5

Each row's x is independently constrained between 0 and 5.

Aggregate Constraints

Wrap an expression in SUM() to constrain the total across all rows. This is how you express global limits like total weight or budget.

SUCH THAT
    x IS BOOLEAN
    AND SUM(x * weight) <= 100
    AND SUM(x * volume) <= 50
    AND SUM(x) >= 5

The total weight of selected items must be at most 100, total volume at most 50, and at least 5 items must be selected.

Comparison Operators

The strict operators (<, >, <>) require the left-hand side to be integer-valued — every term must involve only IS INTEGER / IS BOOLEAN variables with integer coefficients. Constraints involving IS REAL variables must use <= or >= instead.

BETWEEN and IN

Use BETWEEN to constrain a variable to a range, or IN to restrict it to specific discrete values. IN on a decision variable lowers internally to indicator variables and a cardinality constraint. IN on an aggregate (e.g. SUM(x) IN (...)) is not supported.

-- x must be between 1 and 10 (inclusive)
SUCH THAT x BETWEEN 1 AND 10

-- x must be one of these values
SUCH THAT x IN (1, 2, 3)

Conditional Constraints (WHEN)

Append WHEN condition to make a constraint apply only to rows (or groups) for which the condition holds. WHEN works as a postfix on both per-row and aggregate constraints, and the condition may reference table columns or constants (but not decision variables).

-- Per-row: x must be at most 1 only for premium items
SUCH THAT x <= 1 WHEN tier = 'premium'

-- Aggregate: sum only over rows where category = 'A'
SUCH THAT SUM(x * weight) <= 50 WHEN category = 'A'

Grouped Constraints (PER)

Append PER column (or PER (col1, col2, ...)) to an aggregate constraint to emit one constraint per distinct value (or combination) of those columns. Empty groups (filtered out by WHEN) are skipped.

-- One assignment per worker
SUCH THAT SUM(x) <= 1 PER worker_id

-- At most 2 items per (sector, region) pair
SUCH THAT SUM(x) <= 2 PER (sector, region)

MIN and MAX in Constraints

MIN(expr) and MAX(expr) over a linear expression are supported. The "easy" directions — bounding the max above or the min below — just apply the bound to every row. The "hard" directions and equalities use auxiliary binary indicators behind the scenes.

-- Easy: no row exceeds capacity
SUCH THAT MAX(x * weight) <= 50

-- Hard: at least one row must reach the target (linearized internally)
SUCH THAT MAX(x * value) >= 100

AVG and ABS

AVG(expr) is supported in constraints (rewritten to a SUM with the RHS scaled by row count). ABS(linear_expr) is linearized with an auxiliary non-negative variable; the "hard" direction (ABS(expr) >= K) uses a Big-M sign indicator.

-- Average cost across selected items at most 25
SUCH THAT AVG(x * cost) <= 25

-- Deviation from target within tolerance
SUCH THAT ABS(x - target) <= 5

Scalar Subqueries

A subquery on the right-hand side of a constraint can compute a bound from another table. Both uncorrelated and correlated subqueries are accepted: correlated subqueries are decorrelated into joins so they produce a per-row value. Subqueries cannot reference DECIDE variables.

-- Uncorrelated: a single scalar bound shared by all rows
SUCH THAT
    SUM(x) <= (SELECT COUNT(*) FROM Drivers)

-- Correlated: per-row bound pulled from another table
SUCH THAT
    x <= (SELECT budget FROM Depts WHERE Depts.id = Items.dept_id)

For aggregate constraints, the subquery RHS must evaluate to the same scalar for all rows in the aggregate.

Expression Shapes

Terms in constraints (and objectives) can be linear, quadratic, or bilinear. Total degree must be at most 2 — products of three or more decision variables are rejected.

x * 5
x * price
SUM(x * price + y * cost)

POWER(x - target, 2)
(x - target) ** 2
x * y

Solver coverage. Both Gurobi and HiGHS support convex quadratic objectives (MINIMIZE SUM(POWER(expr, 2))) and bilinear products where one factor is IS BOOLEAN (McCormick linearization). Non-convex quadratic objectives (MAXIMIZE SUM(POWER(expr, 2))), general bilinear products of non-Boolean variables, and quadratic constraints are Gurobi only.

SIN(x)              -- non-polynomial functions
x * x * x           -- total degree > 2
POWER(x, 3)         -- only exponent 2 is supported

Quadratic Constraints (QCQP)

Constraints whose left-hand side contains a POWER(linear_expr, 2), expr ** 2, or (expr)*(expr) term — or a bilinear product of non-Boolean variables — are supported with Gurobi via GRBaddqconstr. HiGHS rejects these with a clear error.

-- Per-row: distance from target within a radius
SUCH THAT POWER(x - target, 2) <= 9

-- Aggregate: total variance budget
SUCH THAT SUM(POWER(x - target, 2)) <= 1000

Full Example

SELECT id, value, weight, x
FROM Items
DECIDE x IS BOOLEAN
SUCH THAT
    SUM(x * weight) <= 100
    AND SUM(x * volume) <= 50
    AND SUM(x) >= 5
    AND SUM(x) <= (SELECT capacity FROM Config WHERE name = 'max_items')
MAXIMIZE SUM(x * value);

Select at least 5 items without exceeding weight, volume, or capacity limits. Maximize total value.

The Objective Clause

Use MAXIMIZE or MINIMIZE followed by an aggregate expression to tell the solver what to optimize. SUM, AVG, MIN, and MAX are all supported, along with quadratic (POWER) and bilinear (x * y) shapes.

-- Maximize total value of selected items
MAXIMIZE SUM(x * value)

-- Minimize total cost
MINIMIZE SUM(x * cost)

-- Complex coefficients work too
MAXIMIZE SUM(x * (revenue - cost) * discount_factor)

-- Multiple variables in one objective
MAXIMIZE SUM(x * profit_a + y * profit_b)

Requirements

• • The objective must use a supported aggregate (SUM, AVG, MIN, MAX) or an additive combination of them. COUNT over decision variables is rejected at bind time.
• • Must involve at least one decision variable.
• • Each term has total degree at most 2 in decision variables (linear, quadratic via POWER(expr, 2), or bilinear via x * y).
• • Constant terms are ignored (they don't affect which solution is optimal).

Quadratic Objectives (QP)

SUM(POWER(linear_expr, 2)) turns the objective into a quadratic program. MINIMIZE with a positive coefficient is convex and works with both Gurobi and HiGHS; MAXIMIZE with a positive coefficient is non-convex and requires Gurobi (which handles it via NonConvex=2). Three equivalent syntactic forms are accepted:

-- Least-squares-style: pull every x toward a target
MINIMIZE SUM(POWER(x - target, 2))
MINIMIZE SUM((x - target) ** 2)
MINIMIZE SUM((x - target) * (x - target))

-- Negation gives concave QP (also OK on both solvers under MAXIMIZE)
MAXIMIZE SUM(-POWER(x - target, 2))

Bilinear Objectives

Products of two different decision variables (x * y) are allowed in objectives. When one factor is IS BOOLEAN, DeciDB applies an exact McCormick linearization that works with both solvers (requires a finite upper bound on the other factor). General bilinear products between non-Boolean variables are Gurobi only.

-- Boolean * Integer: maximize selected-revenue, where x is binary
MAXIMIZE SUM(x * quantity * unit_price)

PER on Objectives

The objective can carry a PER grouping by nesting an inner aggregate inside an outer one. The inner aggregate runs per group; the outer aggregates the per-group values into a single scalar to optimize.

-- Minimize the worst region's loss (maximin over regions)
MAXIMIZE MIN(SUM(x * profit)) PER region

Mixed Quadratic + Linear

Linear terms and a single quadratic group can sit in the same objective. The two forms below are equivalent:

MINIMIZE SUM(POWER(x - target, 2) + c * x)
MINIMIZE SUM(POWER(x - target, 2)) + SUM(c * x)

COUNT Workaround

To maximize the number of selected items, use SUM(x) with a boolean variable instead of COUNT.

-- Want to select as many items as possible?
DECIDE x IS BOOLEAN
SUCH THAT SUM(x * weight) <= 100
MAXIMIZE SUM(x);

Since x is 0 or 1, SUM(x) counts how many rows are selected.

Feasibility Queries (No Objective)

You can omit the objective entirely. In that case, the solver finds any feasible assignment that satisfies all constraints.

SELECT id, x
FROM Items
DECIDE x IS BOOLEAN
SUCH THAT
    SUM(x * weight) <= 50
    AND SUM(x) = 3;

Returns any combination of exactly 3 items whose total weight is at most 50.

Solver Selection

DeciDB ships with two solvers and picks one at runtime. Gurobi is the primary backend: when a Gurobi installation is detected on your system, it is used automatically. HiGHS is the bundled open-source fallback and is used when Gurobi is unavailable. Gurobi is significantly faster in practice and supports a broader feature set; HiGHS is convenient because nothing extra is needed to run a query, but it is not recommended for production workloads.

Solver Outcomes

After processing, the solver reports one of three outcomes:

Best Practices

SELECT * FROM Products
DECIDE x IS BOOLEAN
SUCH THAT SUM(x * weight) <= 50
MAXIMIZE SUM(x * value);

SELECT * FROM Products
WHERE category = 'electronics'
  AND in_stock = true
DECIDE x IS BOOLEAN
SUCH THAT SUM(x * weight) <= 50
MAXIMIZE SUM(x * value);

SUCH THAT
    x IS BOOLEAN
    AND SUM(x * weight) <= 50  -- try increasing this
    AND SUM(x) >= 10           -- or lowering this

SELECT o.id, p.name, x
FROM Orders o
JOIN Products p ON o.product_id = p.id
DECIDE x IS BOOLEAN
SUCH THAT SUM(x * p.weight) <= 100
MAXIMIZE SUM(x * p.profit);