Oracle DBMS is broken

So here's something different for a change - i think we just found a bug in the Oracle database management system at work!

Our clients reported an issue for a simple query, that could not be executed:

DELETE
FROM some_table
WHERE some_column = "some_parameter_goes_here"

which failed with the following error message:

Error updating database.  Cause: java.sql.SQLException: No more data to read from socket

That's absolutely puzzling! There are thousands of such simple queries in the app, as well as the other apps that use Oracle, and none of them have failed in such interesting ways! And yet, after trying to reproduce it in SQL Developer, that exact problem manifested itself once more, but what's more odd, it only happened on the clients' environment, which has the Standard edition of the DB, while we have the Enterprise edition.

The exact same DDL for creating the table, even the same data! And yet, they get the error, we do not.

For a bit, our idea was that it could be caused by a rather complex index, which depends on a virtual column with a generated value:

"COL_A" NUMBER GENERATED ALWAYS AS (CASE WHEN "SOME_FLAG"='1' THEN NULL ELSE "FALLBACK_FIELD_A" END) VIRTUAL VISIBLE 
"COL_B" NUMBER GENERATED ALWAYS AS (CASE WHEN "SOME_FLAG"='1' THEN NULL ELSE "FALLBACK_FIELD_B" END) VIRTUAL VISIBLE
...
CONSTRAINT "OUR_COMPLEX_INDEX" UNIQUE ("COL_A", "COL_B")

here you see the full DDL for the current state of the table. The original idea was to have a conditional uniqueness condition - because SQL doesn't support anything like that natively, we had to create a virtual column, which would provide us with information of whether the check is needed or not.

Imagine that you have a system that processes files (not really our case, but makes for a good example) and you want to treat the initial file upload and the versioned file uploads differently. So, what you'd do in such circumstances would be store them in the same table, but with a flag that indicates whether the file is the first version, or an additional version (well, you could also point to the parent file, same idea). And if there are uniqueness conditions for the initial file uploads (for example, each file must be linked to one document, but each document requires only one file of a particular type), then you'd need to have the constraint work for the initial upload but not for the versions.

Alas, this does not work in Oracle. We get a cryptic error that something is wrong with the actual DB, but only on the clients' environment, truly a horror story! What if stuff like that would suddenly start happening in production as well, in any of the other places where similar complexity should be required?

And the fix? Noone has any idea why (since the Oracle alert logs remained empty), but migrating the data to a new table seemed to help. And of course noone bothered to find the actual root cause or file a bug report with Oracle, because "we're short on time". On that note, i'd urge you not to pay for support for products if you're not going to use said support, and if there are very capable open alternatives instead, like PostgreSQL and MySQL/MariaDB.

Of course, SQL simply wasn't meant to be customizable like that and therefore "conditional uniqueness index" is something you won't find mentioned often either.