[MDEV-39730] COUNT(expr) OVER() counts a NULL-extended constant from an empty derived table - Jira

XML

Word

Printable

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.11, 11.4, 11.8, 12.3, 11.8.6
Fix Version/s: 10.11, 11.8, 12.3
Component/s: Optimizer
Labels:
- derived
- wrong_result

Description

The derived table `SELECT 'x' AS c2 FROM t3` is empty because `t3` is empty, and `LEFT JOIN ... ON (0=1)` guarantees that the only output row sees `subq0.c2 = NULL`. Therefore `COUNT(subq0.c2) OVER ()` must be `0`. MariaDB instead counts that NULL-extended constant as if it were non-NULL and returns `1`.

How to repeat:

CREATE TABLE t0(c0 INT);
CREATE TABLE t3(c0 INT);

INSERT INTO t0 VALUES (0);

SELECT COUNT(subq0.c2) OVER ()
FROM t0
LEFT JOIN (SELECT 'x' AS c2 FROM t3) AS subq0
ON (0=1); – Expected correct result: 0 – actual Wrong result: 1

Attachments

Issue Links

is duplicated by

MDEV-39864 Window SUM over an empty left-joined derived table returns 0 instead of NULL

Closed

relates to

MDEV-39865 A grouped NULL-extended RIGHT JOIN row survives a false HAVING predicate

Confirmed

MDEV-39866 MIN over an empty Aria-backed derived table leaks the inner COALESCE fallback value'

Confirmed

MDEV-39867 Qualifying rows disappear when a constant derived-table column drives both filtering and window MIN

Confirmed

Activity

People

Assignee:: Sergei Petrunia

Reporter:: yx

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2026-05-23 14:13

Updated:: 2026-06-10 07:06

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.