I think I've found 5 bugs in the Oracle database in the last 4 months, including multiple queries that returned incorrect results.
They end up being in weird edge cases - one was... you needed to be casting something as JSON, parsing it, and have a WHERE clause that included a compound predicate. A similar but slightly different query simply threw an error. I made a full reproduction and everything. Got bounced around between a couple of departments until we finally reached an engineer who said, and I lightly paraphrase: "um, that seems weird. I don't see anything in the docs about it."
The solution to that was updating from oracle 19.3 to 19.12. But _that_ broke our INSERT IF NOT EXISTS style queries. We were using this "hint" they have, "ignore_row_on_dupkey_index", which is a comment that goes before your query which actually affects the code execution (WTF?).
Batch queries via JDBC return an array of integers: the length is how many different queries you batched together, and each element represents how many rows were affected by each batch.
Unfortunately, when using this ignore_row_on_dupkey_index, the array sometimes both 1. has the wrong length, and 2. has invalid integers: stuff like -1203214.
So we switched over to using MERGE INTO WHEN NOT MATCHED style syntax, and everything was hunky dory, right? Wrong. We started getting UNIQUE CONSTRAINT VIOLATED errors. As near as we can tell, MERGE INTO WHEN NOT MATCHED can still run into a race condition - if you have two queries that try to insert the same non-existent row, the database will check that the row doesn't exist, execute the queries, and then blow up one of them. But as far as we can tell, only on batch queries. As I'm not paid to figure out WTF is wrong with oracle, just get it to work, I didn't end up doing a full repro of this stuff. But there's a couple of weeks I'm not getting back.
They end up being in weird edge cases - one was... you needed to be casting something as JSON, parsing it, and have a WHERE clause that included a compound predicate. A similar but slightly different query simply threw an error. I made a full reproduction and everything. Got bounced around between a couple of departments until we finally reached an engineer who said, and I lightly paraphrase: "um, that seems weird. I don't see anything in the docs about it."
The solution to that was updating from oracle 19.3 to 19.12. But _that_ broke our INSERT IF NOT EXISTS style queries. We were using this "hint" they have, "ignore_row_on_dupkey_index", which is a comment that goes before your query which actually affects the code execution (WTF?).
Batch queries via JDBC return an array of integers: the length is how many different queries you batched together, and each element represents how many rows were affected by each batch.
Unfortunately, when using this ignore_row_on_dupkey_index, the array sometimes both 1. has the wrong length, and 2. has invalid integers: stuff like -1203214.
So we switched over to using MERGE INTO WHEN NOT MATCHED style syntax, and everything was hunky dory, right? Wrong. We started getting UNIQUE CONSTRAINT VIOLATED errors. As near as we can tell, MERGE INTO WHEN NOT MATCHED can still run into a race condition - if you have two queries that try to insert the same non-existent row, the database will check that the row doesn't exist, execute the queries, and then blow up one of them. But as far as we can tell, only on batch queries. As I'm not paid to figure out WTF is wrong with oracle, just get it to work, I didn't end up doing a full repro of this stuff. But there's a couple of weeks I'm not getting back.