# Bug in Oracle to ANSI join conversion? **URL:** \ **Category:** Toad for Oracle & DevOps Toolkit\ **Created:** [April 21, 2017, 3:30am UTC](https://forums.toadworld.com/t/bug-in-oracle-to-ansi-join-conversion/39355 "2017-04-21T03:30:56Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![adiemoran](https://avatars.discourse-cdn.com/v4/letter/a/c37758/32.png) [@adiemoran](https://forums.toadworld.com/u/adiemoran)\ **Post date:** [April 21, 2017, 3:30am UTC](https://forums.toadworld.com/t/bug-in-oracle-to-ansi-join-conversion/39355/1 "2017-04-21T03:30:56Z")
Hi everybody, when using Toad to convert a query featuring outer joins written in the old Oracle notation into ANSI notation, I’ve run into a problem: If I write it like this: select AGENTS.AG\_AGCLE, AGENTS.AG\_NOM, S01\_SERVICES.SE\_CODE, S01\_DEPART.DP\_CODE, S01\_DEPART.DP\_NOM, CONGES.CG\_CPANT, S01\_PERSO.PE\_TEMPSW, MEDWORK.MK\_VILLE, MEDWORK.MK\_DOCTORNAME from S01\_PERSO,S01\_SERVICES,S01\_DEPART,AGENTS,CONGES,MEDWORK where SE\_DPCLE=DP\_DPCLE and SE\_SECLE=PE\_SECLE and PE\_AGCLE=AG\_AGCLE and AG\_AGCLE=CG\_AGCLE(+) and AG\_MKCLE=MK\_MKCLE(+) and PE\_PECLE=25; Toad converts it to this, which is correct: SELECT AG\_AGCLE, AG\_NOM, SE\_CODE, DP\_CODE, DP\_NOM, CG\_CPANT, PE\_TEMPSW, MK\_VILLE, MK\_DOCTORNAME FROM S01\_PERSO INNER JOIN AGENTS ON (PE\_AGCLE = AG\_AGCLE) INNER JOIN S01\_SERVICES ON (SE\_SECLE = PE\_SECLE) INNER JOIN S01\_DEPART ON (SE\_DPCLE = DP\_DPCLE) LEFT OUTER JOIN CONGES ON (AG\_AGCLE = CG\_AGCLE) LEFT OUTER JOIN MEDWORK ON (AG\_MKCLE = MK\_MKCLE) WHERE PE\_PECLE = 25; Whereas if I write it like this, which is the same query (I just wrote one of the join conditions the other way round): select AGENTS.AG\_AGCLE, AGENTS.AG\_NOM, S01\_SERVICES.SE\_CODE, S01\_DEPART.DP\_CODE, S01\_DEPART.DP\_NOM, CONGES.CG\_CPANT, S01\_PERSO.PE\_TEMPSW, MEDWORK.MK\_VILLE, MEDWORK.MK\_DOCTORNAME from S01\_PERSO,S01\_SERVICES,S01\_DEPART,AGENTS,CONGES,MEDWORK where SE\_DPCLE=DP\_DPCLE and SE\_SECLE=PE\_SECLE and PE\_AGCLE=AG\_AGCLE and CG\_AGCLE(+)=AG\_AGCLE and AG\_MKCLE=MK\_MKCLE(+) and PE\_PECLE=25; Toad converts it to this, which is wrong! SELECT AG\_AGCLE, AG\_NOM, SE\_CODE, DP\_CODE, DP\_NOM, CG\_CPANT, PE\_TEMPSW, MK\_VILLE, MK\_DOCTORNAME FROM S01\_PERSO INNER JOIN AGENTS ON (PE\_AGCLE = AG\_AGCLE) INNER JOIN S01\_SERVICES ON (SE\_SECLE = PE\_SECLE) INNER JOIN S01\_DEPART ON (SE\_DPCLE = DP\_DPCLE) LEFT OUTER JOIN MEDWORK ON (AG\_MKCLE = MK\_MKCLE) RIGHT OUTER JOIN CONGES ON (CG\_AGCLE = AG\_AGCLE) WHERE PE\_PECLE = 25; I have searched online for this problem and found nothing, Has anybody else come across this? Is this a bug, or am I missing something? If it is a bug, how come a product as mature as Toad can still have bugs this serious? I’m using Toad for Oracle Freeware 12.10.0.30, 64 bit on Windows 7 64 bit. Thanks in advance. Andy ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [April 21, 2017, 7:47am UTC](https://forums.toadworld.com/t/bug-in-oracle-to-ansi-join-conversion/39355/2 "2017-04-21T07:47:22Z")
Is the problem that this: and CG\_AGCLE(+)=AG\_AGCLE and AG\_MKCLE=MK\_MKCLE(+) is being converted to this? LEFT OUTER JOIN MEDWORK ON (AG\_MKCLE = MK\_MKCLE) RIGHT OUTER JOIN CONGES ON (CG\_AGCLE = AG\_AGCLE) At first glance I thought the Left/Rights were backwards, but then I noticed the line order is reversed, so it’s not wrong, it’s just that the lines are in a different order. Is that it or am I missing something? (By the way, it’s a lot easier for us to help you if you give an example with data dictionary tables/viewws or something in one of the standard Oracle sample schemas, so that we can test your examples) ---
**Author:** ![adiemoran](https://avatars.discourse-cdn.com/v4/letter/a/c37758/32.png) [@adiemoran](https://forums.toadworld.com/u/adiemoran)\ **Post date:** [April 21, 2017, 8:10am UTC](https://forums.toadworld.com/t/bug-in-oracle-to-ansi-join-conversion/39355/3 "2017-04-21T08:10:47Z")
Thanks for your answer! My concern is that the order in which you write the two fields in the outer join criteria should not matter (ie CG\_AGCLE(+)=AG\_AGCLE and AG\_AGCLE=CG\_AGCLE(+) should produce exactly the same join), yet Toad gives a different version in ANSI notation according to whether the (+) side is to the left of the equals sign or to the right… Since posting the original question, I’ve looked into it further and it seems the problem only happens when there are several outer joins and some of the (+) signs are on the left side of the join condition. When all (+) signs are on the right of the join condition, it translates the query correctly. Regarding your answer, I don’t understand why in this particular case the order in which you write the join conditions should matter, since the two outer joins are made from two separate tables (MEDWORK AND CONGES) to a third one (AGENTS), so the order should not matter, as long as they both come after AGENTS. (I would agree with you if table A were being outer joined with table B, which in turn was being outer joined with table C, the order would matter in that case) I am new to the forum, so I didn’t know about giving examples with standard schemas, but I wll try next time! 🙂 ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [April 21, 2017, 8:47am UTC](https://forums.toadworld.com/t/bug-in-oracle-to-ansi-join-conversion/39355/4 "2017-04-21T08:47:01Z")
When I said that the order was different, I wasn’t referring to order of the items on each side of the equal sign. I was referring to the order of the lines changing. and CG\_AGCLE(+)=AG\_AGCLE – Line A and AG\_MKCLE=MK\_MKCLE(+) – Line B begin converted to LEFT OUTER JOIN MEDWORK ON (AG\_MKCLE = MK\_MKCLE) – Line B RIGHT OUTER JOIN CONGES ON (CG\_AGCLE = AG\_AGCLE) – Line A So…if you don’t notice that lines A and B come out as B, then A, it looks like we got LEFT OUTER JOIN and RIGHT OUTER JOINs reversed. But really each line is correct - they are just presented in a different order. I’m not sure why that happens, I’m just making an observation. ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [April 24, 2017, 6:35am UTC](https://forums.toadworld.com/t/bug-in-oracle-to-ansi-join-conversion/39355/5 "2017-04-24T06:35:26Z")
One of the other devs here (John Bowman) worked up an example similar to yours with the HR.EMPLOYEES table. We can reproduce the problem and I see that the conversion produces a different result. I guess I have something to learn about the ANSI syntax. 🙂 ---
**Author:** ![jbowman](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/jbowman/32/22_2.png) [@jbowman](https://forums.toadworld.com/u/jbowman)\ **Post date:** [April 24, 2017, 6:54am UTC](https://forums.toadworld.com/t/bug-in-oracle-to-ansi-join-conversion/39355/6 "2017-04-24T06:54:44Z")
Hey Andy, This should be fixed for the next 12.11 Beta and GA versions of Toad. Toad is a pretty massive product and sometimes bugs like this can fall through the cracks. It’s the same with any piece of software. After all, how many operating system updates does the average person go through in order to fix bugs that were missed when released? We rely on, and very much appreciate when our users find things that don’t look right and let us know about them so we can fix them. It helps us give you the best product possible. You mentioned you were on the Freeware version of Toad, so you may not have access to run the beta; however, you should see this fixed when the next GA version is released. Thanks again for letting us know about this! -John ---
**Author:** ![adiemoran](https://avatars.discourse-cdn.com/v4/letter/a/c37758/32.png) [@adiemoran](https://forums.toadworld.com/u/adiemoran)\ **Post date:** [April 24, 2017, 7:15am UTC](https://forums.toadworld.com/t/bug-in-oracle-to-ansi-join-conversion/39355/7 "2017-04-24T07:15:01Z")
Hi John, wow, congratulations to you and your team for solving it so quickly! Thank you very much for looking into it. Toad is truly a great product, has saved me hundreds of hours of work and helped me immensely with my job, so that’s why I was so surprised and upset when I came across this (and wrote the message in the heat of the moment!). Naturally all software products can have hidden bugs (mine has many more than Toad, believe me!), but the mark of a great dev team is how efficiently they can deal with them when they’re found, so you’re definitely up there! I look forward to the new release. In the meanwhile, I’m using a script to flip the join condition when there’s a (+) on the left of the equals sign before running the converter, and all is going well. Best regards, Andy