[YSQL] JSONB condition using an index will return all rows when there are no hits

[lhotari]

There's a regression in 2.1.1.0-b2 when using JSONB conditions using an index. All rows are returned when there are no hits.

There's a repro here: https://github.com/lhotari/yugabyte-bugs-repro#json-index-bug

git clone https://github.com/lhotari/yugabyte-bugs-repro
cd yugabyte-bugs-repro
./gradlew jsonIndexBug

Also with this SQL:

CREATE TABLE books ( id int PRIMARY KEY, details jsonb );
-- Having an index triggers the json bug in 2.1.1.0-b2. commenting out the following line makes the test pass
CREATE INDEX books_author_first_name ON books ((details->'author'->>'first_name'));
INSERT INTO books (id, details) VALUES (1, '{ "name": "Macbeth", "author": { "first_name": "William", "last_name": "Shakespeare" }, "year": 1623, "editors": ["John", "Elizabeth", "Jeff"] }');
INSERT INTO books (id, details) VALUES (2, '{ "name": "Hamlet", "author": { "first_name": "William", "last_name": "Shakespeare" }, "year": 1603, "editors": ["Lysa", "Mark", "Robert"] }');
INSERT INTO books (id, details) VALUES (3, '{ "name": "Oliver Twist", "author": { "first_name": "Charles", "last_name": "Dickens" }, "year": 1838, "genre": "novel", "editors": ["Mark", "Tony", "Britney"] }');
INSERT INTO books (id, details) VALUES (4, '{ "name": "Great Expectations", "author": { "first_name": "Charles", "last_name": "Dickens" }, "year": 1950, "genre": "novel", "editors": ["Robert", "John", "Melisa"] }');
INSERT INTO books (id, details) VALUES (5, '{ "name": "A Brief History of Time", "author": { "first_name": "Stephen", "last_name": "Hawking" }, "year": 1988, "genre": "science", "editors": ["Melisa", "Mark", "John"] }');
-- this shouldn't return any rows
SELECT id from books where details->'author'->>'first_name'='Hello World';
-- works after dropping the index
DROP index books_author_first_name;
SELECT id from books where details->'author'->>'first_name'='Hello World';

Easy to try out with docker:

# run in one terminal
docker run --name=yb --rm yugabytedb/yugabyte:2.1.1.0-b2 bin/yugabyted start --daemon=false --ui=false --callhome=false
# in another start ysqlsh
docker exec -it yb /home/yugabyte/postgres/bin/ysqlsh
# then paste the sql above

[ddorian]

Confirming that it works in 2.1.0 but breaks in 2.1.1

[lhotari]

interesting detail is that LIKE returns the correct results (no rows)

-- recreate index since it was dropped in the repro sql statements
CREATE INDEX books_author_first_name ON books ((details->'author'->>'first_name'));
-- this shouldn't return any rows and it works as expected
SELECT id from books where details->'author'->>'first_name' LIKE 'Hello World';

[lhotari]

The issue can be reproduced with 2.1.2 as well

[ndeodhar]

Thanks for reporting @lhotari . We've identified the root cause and should have the fix available in next minor release.
About the LIKE: The bug was in the way we handle results when we push down index filter conditions to docDB (yugabyteDB's storage layer). Since we don't do pushdowns yet for LIKE operations, the query filtering is done in the YSQL (postgres) query engine layer and works as expected.

[nocaway]

This has been fixed by commit 9f4448d

Our next release should have it.