Table of contents
Open Table of contents
Article body
While fixing a bug today, I encountered the following problem:
The problem:
There were four records arranged in two pairs. Within each pair, the data was identical, but all four records still needed to appear on the page.
The four records in the allocation tab are shown above. However, someone had added DISTINCT at the end of the SQL, which filtered out the identical records. I didn’t know who had written the query.
The solution:
Step 1: find the underlying problem. Incorrect data on the page meant I needed to inspect the SQL—and our company’s queries were painful to read. After some digging, I traced the issue to a LEFT JOIN whose ON condition did not use a unique field from the right-hand table, producing a Cartesian product. I hadn’t expected to encounter one so directly. Thanks to the author of this post about a Cartesian product in a left join for sharing the explanation.
Step 2: once I found the Cartesian product, I needed to deduplicate the results. Most solutions I found online deleted duplicate records, but I only needed to filter the query output. I remembered Oracle’s ROWID: using DISTINCT with ROWID would let me distinguish records by their row identity.
Step 3: another problem appeared. I initially added DISTINCT ROWID without giving ROWID an alias, while the original query also used *. Oracle did not allow that form, so I assigned an alias to ROWID. That resolved the query issue.
Step 4: the problems kept coming. To display the results, each field had to be assigned to a VO, but ROWID’s type did not match. I first tried changing the return type to double, without success. After searching, I found that Oracle’s ROWID would not convert into the Java entity field as expected. I was stuck—what now? Eventually I discovered Oracle’s rowidtochar(rowid) function, which converts ROWID to a character value. I applied it wherever ROWID appeared in the SQL. The data could finally be queried and displayed correctly.
Step 5: celebrate. Finally getting it working felt great.
This gave me plenty to think about. Why hadn’t this possibility been considered when the tables were designed? When writing SQL, especially multi-table joins, take care with the ON condition instead of writing it on autopilot. For a LEFT JOIN to table A, is the field in the ON condition unique in A? If not, why is the query structured that way? If duplicates cannot be avoided, deduplicate as needed to prevent rework later.
There’s a long road ahead. Sometimes a stumble is what makes a lesson stick.