Hi Robert.
My query is not a problem - I know how to rewrite it to make it work. This is
just a testcase to demonstrate possible DB bug.
Regards.
Alexei
----- Original Message ----
From: Robert Klemme <[EMAIL PROTECTED]>
To: Alexei Novakov <[EMAIL PROTECTED]>
Cc: MaxDB mailing list. <maxdb@lists.mysql.com>
Sent: Friday, August 11, 2006 4:07:53 AM
Subject: Re: Subquery with OR-condition error.
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
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
