Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Queries where a "is null" clause is combined with two equals clauses fail to produce the expected result. #2947

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2011-12-16 09:31:36 +0100
From: bug.monetdb.org.snap.348
To: SQL devs <>
Version: 11.7.5 (Dec2011) [obsolete]
CC: @njnes

Last updated: 2012-03-16 14:56:51 +0100

Comment 16654

Date: 2011-12-16 09:31:36 +0100
From: bug.monetdb.org.snap.348

User-Agent: Mozilla/5.0 (Ubuntu; X11; Linux x86_64; rv:8.0) Gecko/20100101 Firefox/8.0
Build Identifier:

If a column has an indexed varchar column and a query is issued with "is null" on the indexed column the query does not return the correct result. This only happens if there are further clauses in the query.

For example, these queries work as expected:
select * from c__has_a where owner_table is null and property_table='SIMPLE';
select * from c__has_a where owner_table is null and property_id = 4;
select * from c__has_a where owner_table is null;

But this fails:
select * from c__has_a where owner_table is null and property_table='SIMPLE' and property_id = 4;

Reproducible: Always

Steps to Reproduce:

  1. Enter following into database:
    START TRANSACTION;
    SET SCHEMA "sys";
    CREATE TABLE "sys"."c__has_a" (
    "owner_table" VARCHAR(32672),
    "owner_id" BIGINT,
    "relation_name" VARCHAR(32672),
    "property_table" VARCHAR(32672),
    "property_id" BIGINT,
    "property_class" VARCHAR(32672)
    );
    CREATE INDEX "c__has_a_property_index" ON "sys"."c__has_a" ("property_table", "property_id");
    COPY 1 RECORDS INTO "sys"."c__has_a" FROM stdin USING DELIMITERS '\t','\n','"';
    NULL NULL NULL "SIMPLE" 4 "simpleclass"
    COMMIT;

  2. Then execute this query:
    select * from c__has_a where owner_table is null and property_table='SIMPLE' and property_id = 4;

Actual Results:

No rows returned.

Expected Results:

One row returned.

Dropping the index fixes the problem.

Comment 16660

Date: 2011-12-20 11:32:38 +0100
From: bug.monetdb.org.snap.348

The bug manifests if the size of the indexed columns are reduced down to 8.

Comment 16661

Date: 2011-12-20 11:38:22 +0100
From: bug.monetdb.org.snap.348

Simplifying the table does not help:

CREATE TABLE FOO (
"a" VARCHAR(8),
"b" VARCHAR(8),
"c" BIGINT);
CREATE INDEX FOO_INDEX ON FOO ("b","c");

insert into foo (b,c)values('foo',3);

select * from foo where a is null and b='foo' and c=3;

Comment 17013

Date: 2012-02-27 21:59:44 +0100
From: @njnes

fixed, first select based on hash, then handle the other select expressions

Comment 17014

Date: 2012-02-27 22:10:55 +0100
From: @njnes

Changeset 1a5795e15129 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=1a5795e15129

Changeset description:

fixed bug #2947

properly handle hash based selection before the other
select expressions

Comment 17091

Date: 2012-03-16 14:56:51 +0100
From: @grobian

Released in Dec2011-SP2

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant