2 ms·
The question List all departments along with the number of people there has an answer using a correlated subquery, rather than a join. I have a relevant story
by btilly 13y ago
The question List all departments along with the number of people there has an answer using a correlated subquery, rather than a join.
I have a relevant story about that.
About 9 years ago now, another developer escalated a bug to me. Every time they ran a complicated auto-generated query, they got logged out of Oracle. No way! I tried it. Happened to me. Began trying to narrow it down. Ran out of connections. Got a DBA to unwedge the machine. Began again. Ran out of connections again. Got the same DBA to unwedge the machine. Received a lecture about not opening so many connections, replied that I was tracking down a bug and had no choice. Showed him the bug. He was astounded.
Not long after I finished tracking it down and sent them the fix. Showed it to the DBA who verified that it had been reported already, and was fixed in the next release.
The bug was that any time there was a correlated subquery with no records, Oracle logged you out. My guess is that something, somewhere, followed a null pointer. The obvious solution was to move to a left join. If I remember correctly, the way it was being autogenerated made that hard. My solution was to have a correlated subquery which was a left join on DUAL so that there was always a record.
- krsunny 13y agoRelevant story? You're hired!