Re: Order in left join

Hi Holger,

thanks for help, explanation, and future bug fixing.

Best regards,

Lukasz.

Becker, Holger wrote:
> Lukasz Misztal wrote:
>=20
>> 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"=3D"Table2_IDTable1"
>>
>> Returns:
>> 10	21	10=09
>> 11	?	?=09
>>
>> but
>>
>> select * from "Table1"
>> left join "Table2" on "Table2_IDTable1"=3D"Table1_ID"
>>
>> Returns:
>> ?	20	?=09
>> 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"=3D"Table1_ID"
>> //
>> select * from "Table1"
>> left join "Table2" on "Table1_ID"=3D"Table2_IDTable1"
>=20
> Hi,
>=20
> Lars explanation is not correct the order of the predicate in the on cl=
ause of an outer join should have no influence on the result.=20
> 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 =3D TAB_B.Y you will see e=
very 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 =3D TAB_B.Y you want t=
o see all rows from TAB_B regardless if there is a corresponding row in T=
AB_A.
>=20
> So this is definitely a bug within MaxDB and we will fix it with the ne=
xt version.
> For detailed information about error processing see http://www.sapdb.or=
g/webpts?wptsdetail=3Dyes&ErrorType=3D0&ErrorID=3D1149721
>=20
> As a workaround you could use explicit tablename specification.
>=20
> In my test this example works:
>=20
> select * from "Table1" left join "Table2" on "Table2"."Table2_IDTable1"=
=3D"Table1"."Table1_ID"
> select * from "Table1" left join "Table2" on "Table1"."Table1_ID"=3D"Ta=
ble2"."Table2_IDTable1"
>=20
> Sorry for any inconvenience and thank you for reporting this bug.
>=20
> Best regards
> Holger
>=20
> 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=E9o =
Apotheker, Werner Brandt, Claus Heinrich, Gerhard Oswald, Peter Zencke
> Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory Board=
: Hasso Plattner=20
> Registergericht/Commercial Register Mannheim No HRB 350269
>=20
> Diese E-Mail kann Betriebs- oder Gesch=E4ftsgeheimnisse oder sonstige v=
ertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrt=FCmlic=
h erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, eine Vervielf=
=E4ltigung oder Weitergabe der E-Mail ausdr=FCcklich untersagt.
> Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail.=
 Vielen Dank.
>=20
> This e-mail may contain trade secrets or privileged, undisclosed, or ot=
herwise confidential information. If you have received this e-mail in err=
or, you are hereby notified that any review, copying, or distribution of =
it is strictly prohibited. Please inform us immediately and destroy the o=
riginal transmittal. Thank you for your cooperation.=20
>=20
> =20
>=20




-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[email protected]