Category Archives: Bugs

COLLECT DISTINCT in PL/SQL Works in Oracle 12.2

Oren Nakdimon 1 Reply

About a year ago I wrote the post Subtleties – Part 1 (SQL and PL/SQL). I wrote there: Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement. One example is the COLLECT aggregate function with […]

Continue reading

Optimization that Violates Data Integrity

Oren Nakdimon 1 Reply

The Constraint Optimization series: Part 1: Optimization of Check Constraint Creation Part 2: Optimization that Violates Data Integrity Part 3: Optimization of Foreign Key Constraint Creation Part 4: (Lack of) Optimization of Unique Constraint Creation Part 5: Adding a Column with a Default Value and a Constraint In the previous post I showed that Oracle […]

Continue reading

INSERT into a View with a GROUP BY Clause

Oren Nakdimon 1 Reply

When I wrote the previous post, about updatable views, I noticed an interesting issue. The documentation says: If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the […]

Continue reading

Optimizer bug leads to wrong results

Oren Nakdimon 1 Reply

A few days ago a query that I wrote did not return the expected results. After some investigation I came to conclusion that the reason is a bug of the optimizer. Here is a very simplified example (and quite silly functionality-wise, but I guarantee you that the original, more complex, query does make sense). I […]

Continue reading