Description

A scalar subquery that produces zero rows must evaluate to NULL. A table-less query such as `SELECT 1` produces exactly one row, but `LIMIT 0` or `OFFSET 1` truncates it to zero rows, so `(SELECT 1 LIMIT 1 OFFSET 1)` must be NULL.

MariaDB removes the table-less subquery during optimization and replaces it with its select-list expression, discarding the `LIMIT` / `OFFSET` clause. `SELECT (SELECT 1 LIMIT 1 OFFSET 1)` therefore returns `1`.

The defect is confined to the table-less flattening path. Every other way of producing zero rows in a scalar subquery is handled correctly by MariaDB, including the same `LIMIT`/`OFFSET` clauses on a subquery that has a `FROM` clause. This is what makes the diagnosis concrete: the "this query is guaranteed to return exactly one row" assumption that licenses the flattening is not re-checked against `LIMIT` / `OFFSET`.

Minimal Reproduction

```sql
DROP DATABASE IF EXISTS bugrep_mariadb;
CREATE DATABASE bugrep_mariadb;
USE bugrep_mariadb;

SELECT 'SELECT (SELECT 1 LIMIT 1 OFFSET 1)' AS form, (SELECT 1 LIMIT 1 OFFSET 1) AS r;
SELECT 'SELECT (SELECT 1 LIMIT 0)' AS form, (SELECT 1 LIMIT 0) AS r;
SELECT 'SELECT (SELECT 1 LIMIT 10 OFFSET 5)' AS form, (SELECT 1 LIMIT 10 OFFSET 5) AS r;

– the wrong value propagates into surrounding expressions
SELECT '... IS NULL' AS form, (SELECT 1 LIMIT 1 OFFSET 1) IS NULL AS r;
SELECT '... + 100' AS form, (SELECT 1 LIMIT 1 OFFSET 1) + 100 AS r;

– controls: every other way of producing zero rows is handled correctly
CREATE TABLE one_row (x INT);
INSERT INTO one_row VALUES (1234);
SELECT 'with FROM, OFFSET 1' AS form, (SELECT 1 FROM one_row LIMIT 1 OFFSET 1) AS r;
SELECT 'with FROM, LIMIT 0' AS form, (SELECT x FROM one_row LIMIT 0) AS r;
SELECT 'with FROM, WHERE 1=0' AS form, (SELECT 1 FROM one_row WHERE 1=0) AS r;
SELECT 'aggregate + false HAVING' AS form, (SELECT COUNT FROM one_row HAVING COUNT>100) AS r;
SELECT 'table-less + false HAVING' AS form, (SELECT 1 AS c HAVING 1=0) AS r;
SELECT 'table-less + false WHERE' AS form, (SELECT 1 WHERE 1=0) AS r;
SELECT 'table-less OFFSET, EXISTS' AS form, EXISTS (SELECT 1 LIMIT 1 OFFSET 1) AS r;
SELECT 'table-less OFFSET, derived' AS form,
(SELECT COUNT FROM (SELECT 1 AS x LIMIT 1 OFFSET 1) d) AS r;

EXPLAIN EXTENDED SELECT (SELECT 1 LIMIT 1 OFFSET 1) AS r;
SHOW WARNINGS;
```

Expected Result

The first three queries return `NULL`; `IS NULL` returns `1`; `... + 100` returns `NULL`.

Actual Result

Measured on MariaDB 12.3.2:

```

controls, all correct:

```

The last two controls are worth noting: the same table-less `LIMIT 1 OFFSET 1` subquery is treated as zero rows when it appears under `EXISTS` or as a derived table. Only the scalar-subquery-in-projection form is wrong, which localises the defect precisely.

(`1 IN (SELECT 1 LIMIT 1 OFFSET 1)` cannot be used as a further probe: MariaDB rejects it with `ERROR 1235 ... 'LIMIT & IN/ALL/ANY/SOME subquery'`.)

Cross-Engine Comparison

Same three statements on the same machine:

MariaDB is the only engine of the five that returns a value here.

Plan / Activation Evidence

```
EXPLAIN EXTENDED SELECT (SELECT 1 LIMIT 1 OFFSET 1) AS r;

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

SHOW WARNINGS;
---------------------------------------------------

---------------------------------------------------

---------------------------------------------------
```

This is the whole bug in two lines: the subquery is eliminated (`Select 2 was reduced during optimization`) and the rewritten statement shown by the optimizer is literally `select 1 AS r` — the `LIMIT 1 OFFSET 1` has vanished from the plan.

For contrast, the same subquery with a `FROM` clause is kept as a real subquery and returns NULL:

```

```

Root Cause Analysis

Hypothesis. The optimization that removes a table-less query block ("select ... was reduced during optimization") is licensed by the assumption that such a block returns exactly one row, so a scalar subquery over it can be replaced by its select-list expression. That assumption is invalid when the block carries a row-limiting clause: `LIMIT 0` and any non-zero `OFFSET` can reduce the guaranteed single row to zero rows, at which point the scalar subquery must evaluate to NULL. The reduction appears not to test for the presence of `LIMIT` / `OFFSET` before firing. MariaDB does perform this test for `WHERE 1=0` and for `HAVING 1=0` on the same table-less block (both controls return NULL correctly), so the missing case is specifically the row-limiting clauses.

Upstream Reference

This is the same defect that MySQL fixed:

• *MySQL Bug#37293822* — "Wrong result with OFFSET in table-less subquery", fixed by commit [101f0aed74](https://github.com/mysql/mysql-server/commit/101f0aed74) (commit title verified: `Bug#37293822: Wrong result with OFFSET in table-less subquery`). MySQL 9.7.1 on this machine returns NULL for all three forms.
• A closely related sibling in the same family is *MySQL Bug#37293786* — "Wrong result with QUALIFY in table-less query", commit [57bf7f9708](https://github.com/mysql/mysql-server/commit/57bf7f9708) (title verified) — the same guaranteed-one-row assumption defeated by a different zero-row-truncating clause. MariaDB has no `QUALIFY`, so only the `LIMIT` / `OFFSET` form applies here.

Fixed-status contrast measured on this machine:

Workaround

Give the subquery a `FROM` clause, or express the zero-row condition another way — `(SELECT 1 FROM one_row LIMIT 1 OFFSET 1)`, `(SELECT 1 WHERE 1=0)` and `(SELECT 1 AS c HAVING 1=0)` all return NULL correctly. There is no configuration-level workaround: the reduction fires in the default configuration.

Environment

```
Server: MariaDB 12.3.2-MariaDB (Homebrew), 127.0.0.1:3307
OS: macOS (darwin 25.5.0, arm64)
sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
optimizer_switch: server default, unmodified
```