SELECT query with IS NULL returns incorrect results in some cases
Final Release Note
Description
This is an issue that was observed in Octo during the course of investigating YottaDB/DB/YDB#769.
Octo returns 1 row (correct result) for the below firstname IS NULL
and lastname IS NULL
queries.
OCTO> create table tbl (id integer primary key, firstname varchar, lastname varchar);
OCTO> insert into tbl values (1, 'a1', 'b1');
INSERT 0 1
OCTO> insert into tbl values (2, 'a2', '');
INSERT 0 1
OCTO> insert into tbl values (3, '', 'b3');
INSERT 0 1
OCTO> select * from tbl where firstname IS NULL;
ID|FIRSTNAME|LASTNAME
3||b3
(1 row)
OCTO> select * from tbl where lastname IS NULL;
ID|FIRSTNAME|LASTNAME
2|a2|
(1 row)
But once a new row is inserted with empty values of firstname
and lastname
column (i.e. NULL
values in SQL-land), only the lastname IS NULL
query returns 2 rows (correct result). The firstname IS NULL
query continues to return only 1 row (incorrect result). We instead expect 2 rows in that case too.
OCTO> insert into tbl values (4, '', '');
INSERT 0 1
OCTO> select * from tbl where firstname IS NULL;
ID|FIRSTNAME|LASTNAME
3||b3
(1 row)
OCTO> select * from tbl where lastname IS NULL;
ID|FIRSTNAME|LASTNAME
2|a2|
4||
(2 rows)
Draft Release Note
Queries using the IS NULL
operator now return correct results. Previously they could incorrectly return a fewer number of rows.