Hi Holger,
thanks for help, explanation, and future bug fixing.
Best regards,
Lukasz.
Becker, Holger wrote:
> Lukasz Misztal wrote:
>
>> Hello everyone,
>>
>> i have a simple question:
>> is order of fields in (left) join in MAXDB significant?
>>
>> For example:
>>
>> select * from "Table1"
>> left join "Table2" on "Table1_ID"="Table2_IDTable1"
>>
>> Returns:
>> 10 21 10
>> 11 ? ?
>>
>> but
>>
>> select * from "Table1"
>> left join "Table2" on "Table2_IDTable1"="Table1_ID"
>>
>> Returns:
>> ? 20 ?
>> 10 21 10
>>
>> I get confused.
>> Two diffrent strategies (JOIN VIA KEY RANGE/JOIN VIA KEY COLUMN).
>>
>> I have searched bugs.mysql.com but it gets nothing.
>> Is it bug whether my basic mistake?
>>
>> Lukasz
>>
>> ---------------------
>> MAXDB 7.6.00.37 WinXp prof., test data:
>>
>> CREATE TABLE "Table1"
>> (
>> "Table1_ID" Fixed (12,0),
>> PRIMARY KEY ("Table1_ID")
>> )
>> //
>> CREATE TABLE "Table2"
>> (
>> "Table2_ID" Fixed (12,0),
>> "Table2_IDTable1" Fixed (12,0),
>> PRIMARY KEY ("Table2_ID")
>> )
>> //
>> insert into "Table1" values (10)
>> //
>> insert into "Table1" values (11)
>> //
>> insert into "Table2" values (20,null)
>> //
>> insert into "Table2" values (21,10)
>> //
>> select * from "Table1"
>> left join "Table2" on "Table2_IDTable1"="Table1_ID"
>> //
>> select * from "Table1"
>> left join "Table2" on "Table1_ID"="Table2_IDTable1"
>
> Hi,
>
> Lars explanation is not correct the order of the predicate in the on clause
> of an outer join should have no influence on the result.
> The LEFT and RIGHT refers to the table from which you want to see every row
> regardless if the on clause is qualified or not.
> If you have TAB_A LEFT JOIN TAB_B ON TAB_A.X = TAB_B.Y you will see every row
> from TAB_A regardless if there is a corresponding row in TAB_B.
> And if you have TAB_B LEFT JOIN TAB_A ON TAB_A.X = TAB_B.Y you want to see
> all rows from TAB_B regardless if there is a corresponding row in TAB_A.
>
> So this is definitely a bug within MaxDB and we will fix it with the next
> version.
> For detailed information about error processing see
> http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1149721
>
> As a workaround you could use explicit tablename specification.
>
> In my test this example works:
>
> select * from "Table1" left join "Table2" on
> "Table2"."Table2_IDTable1"="Table1"."Table1_ID"
> select * from "Table1" left join "Table2" on
> "Table1"."Table1_ID"="Table2"."Table2_IDTable1"
>
> Sorry for any inconvenience and thank you for reporting this bug.
>
> Best regards
> Holger
>
> Holger Becker
> MaxDB&liveCache
> SAP AG
> www.sap.com
> Sitz der Gesellschaft/Registered Office: Walldorf, Germany
> Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), Léo
> Apotheker, Werner Brandt, Claus Heinrich, Gerhard Oswald, Peter Zencke
> Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory Board:
> Hasso Plattner
> Registergericht/Commercial Register Mannheim No HRB 350269
>
> Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige
> vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich
> erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, eine
> Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.
> Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail.
> Vielen Dank.
>
> This e-mail may contain trade secrets or privileged, undisclosed, or
> otherwise confidential information. If you have received this e-mail in
> error, you are hereby notified that any review, copying, or distribution of
> it is strictly prohibited. Please inform us immediately and destroy the
> original transmittal. Thank you for your cooperation.
>
>
>
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
