Bug #19365 No results for SQL Statements with WHERE ... IN Condition

• Submitted: 26 Apr 2006 9:37
• Modified: 23 Jan 2007 13:02
• Reporter: Dirk Straube
• Status: No Feedback
• Category: MaxDB
• Severity: S2 (Serious)
• Version: 7.5.00.34
• OS: Windows (Windows 2000 Server)
• CPU Architecture: Any

[26 Apr 2006 9:37] Dirk Straube

Description:
With MaxDB 7.5.00.34 the following SQL Satement did not return a result, while MaxDB 7.5.00.18 works correctly:

SELECT s_eventtaskhistorymaster.indexid, 
s_eventtaskhistorymaster.propvalue,  s_eventtaskhistorydetail.propname, 
  s_eventtaskhistorydetail.propvalue  FROM 
s_eventtaskhistorydetail,s_eventtaskhistorymaster  WHERE 
s_eventtaskhistorymaster.indexid in (select indexid from 
s_eventtaskhistorydetail where propname = 'schedeventid'  AND propvalue 
IN ( '3' ) ) AND s_eventtaskhistorymaster.indexid = 
s_eventtaskhistorydetail.indexid 

Problem is the WHERE ... IN Statement followed by the embedded SQL Query (select indexid from s_eventtaskhistorydetail where propname = 'schedeventid'  AND propvalue IN ( '3' ) ) .

When I replace this one with the values returned by the subquery I get the desired results.

How to repeat:
Table definitions:

CREATE TABLE s_eventtaskhistorymaster
(
   indexid   FIXED(9)     CONSTRAINT pk_eventtaskhistorymaster PRIMARY KEY,
   propvalue VARCHAR(255) NOT NULL
);

CREATE TABLE s_eventtaskhistorydetail
(
   indexid   FIXED(9)      NOT NULL,
   propname  VARCHAR(50)   NOT NULL,
   propvalue VARCHAR(1024) NOT NULL
);

CREATE INDEX IDX_eventtaskhistorydetail ON s_eventtaskhistorydetail(indexid, propname);

[23 Dec 2006 13:02] Valeriy Kravchuk

Thank you for a problem report. Please, try to repeat with a newer version, 7.5.0.38, and inform about the results.

[24 Jan 2007 0:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".