Bug in Oracle to ANSI join conversion?

Toad for Oracle
1

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

2

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)

3

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! :slight_smile:

4

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.

5

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. :slight_smile:

6

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

7

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