RE: Order in left join

Lukasz Misztal wrote:

> Hello everyone,
>=20
> i have a simple question:
> is order of fields in (left) join in MAXDB significant?
>=20
> For example:
>=20
> select * from "Table1"
> left join "Table2" on "Table1_ID"=3D"Table2_IDTable1"
>=20
> Returns:
> 10	21	10=09
> 11	?	?=09
>=20
> but
>=20
> select * from "Table1"
> left join "Table2" on "Table2_IDTable1"=3D"Table1_ID"
>=20
> Returns:
> ?	20	?=09
> 10	21	10
>=20
> I get confused.
> Two diffrent strategies (JOIN VIA KEY RANGE/JOIN VIA KEY COLUMN).
>=20
> I have searched bugs.mysql.com but it gets nothing.
> Is it bug whether my basic mistake?
>=20
> Lukasz
>=20
> ---------------------
> MAXDB 7.6.00.37 WinXp prof., test data:
>=20
> 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"

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.=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 =
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 =3D 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=3Dyes&ErrorType=3D0&ErrorID=3D1149=
721

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"=3D"Table1"."Table1_ID"
select * from "Table1" left join "Table2" on =
"Table1"."Table1_ID"=3D"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=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

Diese E-Mail kann Betriebs- oder Gesch=E4ftsgeheimnisse oder sonstige =
vertrauliche Informationen enthalten. Sollten Sie diese E-Mail =
irrt=FCmlich 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.

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.=20

=20

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