-
-
Notifications
You must be signed in to change notification settings - Fork 584
Closed
Labels
Description
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:
- empty set - there are no items in the table yet
- 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 |
+----------+------+------+------+------+