Hi list,
I ran into weird problem (MaxDB 7.5.0.30 on Windows). My query with correlated
subquery in select
gives me wrong results. Here is the testcase (very simplified).
DDL:
CREATE TABLE TMP_1 (ID_MAIN FIXED(19) NOT NULL PRIMARY KEY)
CREATE TABLE TMP_2 (ID_1 FIXED(19) NOT NULL, ID_2 FIXED(19) NOT NULL, VAL_STR
VARCHAR(64))
CREATE UNIQUE INDEX TMP_2_UN on TMP_2 (ID_1, ID_2)
CREATE INDEX TMP_2_STR ON TMP_2 (VAL_STR)
INSERT INTO TMP_1 (ID_MAIN) VALUES (1)
INSERT INTO TMP_1 (ID_MAIN) VALUES (2)
INSERT INTO TMP_1 (ID_MAIN) VALUES (3)
INSERT INTO TMP_1 (ID_MAIN) VALUES (4)
INSERT INTO TMP_1 (ID_MAIN) VALUES (5)
INSERT INTO TMP_1 (ID_MAIN) VALUES (6)
INSERT INTO TMP_1 (ID_MAIN) VALUES (7)
INSERT INTO TMP_1 (ID_MAIN) VALUES (8)
INSERT INTO TMP_1 (ID_MAIN) VALUES (9)
INSERT INTO TMP_1 (ID_MAIN) VALUES (10)
INSERT INTO TMP_2 (ID_1, ID_2, VAL_STR) VALUES (1, 1, 'aaa-1')
INSERT INTO TMP_2 (ID_1, ID_2, VAL_STR) VALUES (2, 1, 'aaa-2')
INSERT INTO TMP_2 (ID_1, ID_2, VAL_STR) VALUES (3, 1, 'aaa-3')
INSERT INTO TMP_2 (ID_1, ID_2, VAL_STR) VALUES (4, 1, 'aaa-4')
INSERT INTO TMP_2 (ID_1, ID_2, VAL_STR) VALUES (5, 1, 'aaa-5')
INSERT INTO TMP_2 (ID_1, ID_2, VAL_STR) VALUES (6, 1, 'bbb-1')
INSERT INTO TMP_2 (ID_1, ID_2, VAL_STR) VALUES (7, 1, 'bbb-2')
INSERT INTO TMP_2 (ID_1, ID_2, VAL_STR) VALUES (8, 1, 'bbb-3')
INSERT INTO TMP_2 (ID_1, ID_2, VAL_STR) VALUES (9, 1, 'bbb-4')
INSERT INTO TMP_2 (ID_1, ID_2, VAL_STR) VALUES (10, 1, 'bbb-5')
SQL query:
SELECT T2.ID_MAIN AS ID_1, T1.ID_MAIN AS ID_2,
(SELECT IT1.VAL_STR FROM TMP_2 IT1 WHERE IT1.ID_1 = T2.ID_MAIN AND IT1.ID_2 =
T1.ID_MAIN)
FROM TMP_1 T1, TMP_1 T2, TMP_2 T3
WHERE
(
(
T2.ID_MAIN IN (1, 2, 3, 4, 5)
AND T3.VAL_STR LIKE 'aaa-%'
AND T3.ID_2 = 1
)
OR
(
T2.ID_MAIN IN (6, 7, 8, 9, 10)
AND T3.VAL_STR LIKE 'bbb-%'
AND T3.ID_2 = 1
)
)
AND T1.ID_MAIN = 1
AND T2.ID_MAIN = T3.ID_1
Obviously, in the result set I want to get ID_1 in the first column, ID_2 in
the second and
VAL_STR corresponding to this ID_1-ID_2 pair. But instead I get for the third
column in the result
whatever but what I expect. It is interesting that if I simplify the condition
to remove OR-clause
it starts working as expected, like:
SELECT T2.ID_MAIN AS ID_1, T1.ID_MAIN AS ID_2,
(SELECT IT1.VAL_STR FROM TMP_2 IT1 WHERE IT1.ID_1 = T2.ID_MAIN AND IT1.ID_2 =
T1.ID_MAIN)
FROM TMP_1 T1, TMP_1 T2, TMP_2 T3
WHERE
(
T2.ID_MAIN IN (1, 2, 3, 4, 5)
AND T3.VAL_STR LIKE 'aaa-%'
AND T3.ID_2 = 1
)
AND T1.ID_MAIN = 1
AND T2.ID_MAIN = T3.ID_1
It gives me absolutely correct result. I can see two possibilities here: either
I'm missing
something or it's a bug in MaxDB :)
Regards.
Alexei Novakov
PS. I didn't try it on 7.6 version.
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
