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

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;

+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+

|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+

SHOW WARNINGS;

+-------+------+------------------------------------------+

| Level | Code | Message                                  |

+-------+------+------------------------------------------+

| Note  | 1249 | Select 2 was reduced during optimization |

| Note  | 1003 | select 1 AS `r`                          |

+-------+------+------------------------------------------+

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:

|    1 | PRIMARY     | NULL    | NULL | ... | No tables used |

|    2 | SUBQUERY    | one_row | ALL  | ... |                |

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
```