2006/8/11, Alexei Novakov <[EMAIL PROTECTED]>:
Hi all,
Neerly a year ago I wrote this message to this list:
<quote>
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
Can't you juse use an ordinary join?
# untested
SELECT T2.ID_MAIN AS ID_1, T1.ID_MAIN AS ID_2, IT1.VAL_STR
FROM TMP_1 T1, TMP_1 T2, TMP_2 T3, TMP_2 IT1
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
AND IT1.ID_1 = T2.ID_MAIN AND IT1.ID_2 = T1.ID_MAIN
Kind regards
robert
--
Have a look: http://www.flickr.com/photos/fussel-foto/
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
