Skip to content

Unexpected NULL return values with false filter or empty set #5683

@PavelSafronov

Description

@PavelSafronov

Doing a select of the form select count(*), 0, 1, 'a', 'b' on a empty set produces unexpected NULL values.
The example below shows two cases:

  1. empty set - there are no items in the table yet
  2. false filter - there are items in the table, but we filter all of them out

SQL:

use db;

create table test(id int);

-- empty set
select count(*), 0, 1, 'a', 'b'
FROM test;

-- false filter
insert into test values(1),(2),(3);
select count(*), 0, 1, 'a', 'b'
FROM test
where false;

MySQL behavior:

+----------+---+---+---+---+
| count(*) | 0 | 1 | a | b |
+----------+---+---+---+---+
|        0 | 0 | 1 | a | b |
+----------+---+---+---+---+
+----------+---+---+---+---+
| count(*) | 0 | 1 | a | b |
+----------+---+---+---+---+
|        0 | 0 | 1 | a | b |
+----------+---+---+---+---+

dolt behavior:

+----------+------+------+------+------+
| count(*) | 0    | 1    | a    | b    |
+----------+------+------+------+------+
| 0        | NULL | NULL | NULL | NULL |
+----------+------+------+------+------+

Query OK, 3 rows affected (0.00 sec)
+----------+------+------+------+------+
| count(*) | 0    | 1    | a    | b    |
+----------+------+------+------+------+
| 0        | NULL | NULL | NULL | NULL |
+----------+------+------+------+------+

Metadata

Metadata

Assignees

No one assigned

    Labels

    analyzerbugSomething isn't workingsqlIssue with SQL

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions