Fun with Oracle Views

Is a database view which responds without an error to a “SELECT * …” query bug free?

Sounds simple and obvious, until recently I would not have hesitated to say: Yes, sure.

Turns out, it’s more complicated than that.

Backstory

Recently I ran into exactly this problem. There was a database view that I had to query and show the results. The code worked on my local machine, it worked on the test-instance, but failed on the integration environment.

At first I suspected an issue in the code that queried the database view. It was a relatively lengthy query (about 30 lines when properly formatted) which had multiple calls to the TO_DATE function and even a CASE-expression.

The error that was thrown was this:

ORA-01843: not a valid month

Narrowing it down

I removed more and more expressions from the where-part of the query and in the end it turned out that there was no error as soon as I removed all comparison operations to a certain column.

At this point I still thought that the issue was somewhere in my query.
For the most part I thought so because the following query worked:

SELECT * FROM problematic_view;

However, then I realized that certain queries produced the problem which I had thought came from one of my TO_DATE calls, without actually calling TO_DATE. The query was approximately this:

SELECT * FROM problematic_view WHERE some_date_column < SYSDATE;

This gave me the necessary push to check the view-code again and look for the issue there.

The devil is in the details … and the many results

It didn’t take too long after knowing the column that triggered the issue and looking at the view-code to find it. Let me show it to you with an example:

Lets create a simple schema, just 1 table and 1 view. The table stores a date in a strange way (encoded as a varchar), but we’ve all seen worse, right ;)

Now lets populate it with some data:

And woops, some invalid data slipped in at the end there. Well the view will surely fail right away, right?

SELECT * FROM order_view;
...
Returned 50 rows in 0,029 seconds. (output from our db-client)

What happened here?

Well, our db-client& server were smart enough not to transfer the whole database over the wire at once & the database was smart enough not to read in all the data at once when it didn’t have to.

To my surprise, even this query worked:

SELECT * FROM order_view WHERE order_date < SYSDATE;

but in hindsight it’s obvious since it also only reads as much as it needs.

These queries both show the issue immediately after running them:

SELECT COUNT(1) FROM order_view WHERE order_date < SYSDATE;
SELECT * FROM order_view WHERE order_date > SYSDATE;

And that was it, the problem I ran into was the same, the view converted some varchar to a date, but the source-table had some invalid data somewhere deep down after more rows than a quick SELECT * would touch.

My takeaway from this:

• Always keep an open mind to what the source of a problem may be.
• Keep an eye on your SQL view code.
• Run some COUNT queries with where-clauses on different fields to test views thoroughly.

Have you run into similar pitfalls? Are there other strange cases that we can run into with views, …?
Let me know in the comments!

Here’s the full code as a gist, in case you want to try it out yourself: