Wrong logic in predicates over views defined with an open-interval predicate

[monetdb-team]

Date: 2011-05-04 15:47:31 +0200
From: Milena Ivanova <<M.Ivanova>>
To: SQL devs <>
Version: -- development
CC: @njnes

Last updated: 2011-05-31 13:59:27 +0200

Comment 15785

Date: 2011-05-04 15:47:31 +0200
From: Milena Ivanova <<M.Ivanova>>

The problem appears in a specific case of queries over views. If the view is defined with an open-interval predicate over a column X, a subsequent query over the view containing a predicate over the same column X gives wrong results.
Attached file reproduces the bug.

A view defined with a closed-interval predicate behaves correctly.

Comment 15786

Date: 2011-05-04 15:49:24 +0200
From: Milena Ivanova <<M.Ivanova>>

Created attachment 61
reproduce viw predicate bug

Attached file: bug.sql (application/octet-stream, 401 bytes)
Description: reproduce viw predicate bug

Comment 15799

Date: 2011-05-13 15:40:30 +0200
From: @njnes

this seems unrelated to views, ie also
sql>plan select id,ival from xx where ival >=2 and ival <5 and ival between 2 and 5;
+------------------------------------------------------------------------------+
| rel |
+==============================================================================+
| project ( |
| | select ( |
| | | table(sys.xx) [ xx.id NOT NULL, xx.ival, xx.%TID% NOT NULL, xx.%xx_id_pk |
: ey NOT NULL ] :
| | ) [ convert(2 from tinyint to int) <= xx.ival < convert(2 from tinyint to |
: int) ] :
| ) [ xx.id NOT NULL, xx.ival ] |
+------------------------------------------------------------------------------+

Comment 15803

Date: 2011-05-13 15:54:50 +0200
From: @njnes

Changeset 03602986e015 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=03602986e015

Changeset description:

fixed bug in merging of single (theta expressions) into range expressions.
(fixes bug #2806)