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