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 Email Updates:
Status: No Feedback Impact on me:
None 
Category:MaxDB Severity:S2 (Serious)
Version:7.5.00.34 OS:Microsoft Windows (Windows 2000 Server)
Assigned to: 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".