SQL Experts: Basic View Question

PirateJon

Working with a custom SQL app that I didn't write and I'm not a developer.

The app calls a view. The view contains a select statement. The select statement references several valid tables and one missing table. A query against the view runs without errors but doesn't return any data.

I would have expected the query to either fail (with some sort of actionable message) or to succeed minus the rows for the missing tables, not just null. But then I'm no DBA.

Is this normal? What should I expect from in that situation?

vonPoonBurGer

What's the database? Different RDBMS systems will handle this error in different ways. For example, on Oracle if you try to query a view that references a nonexistent table, you get the following:

SQL> select * from invalid_view;
select * from invalid_view
              *
ERROR at line 1:
ORA-04063: view "OWNER.INVALID_VIEW" has errors

MS SQL Server apparently does something similar. MySQL apparently does not, instead you only have the option of checking a view's validity manually using the CHECK TABLE command.

Edit: I tried to find info on what PostgreSQL and SQLite do in this situation, but a quick search came up empty. Note that RDBMSs probably differ in this regard because the SQL standard doesn't specify what you're supposed to do in this situation. Sensible databases designed by sensible people (Oracle, SQL Server, probably Postgre too) do sensible things like return an error message in this situation. Stupid toy databases designed by stupid toy designers (MySQL? I'm guessing MySQL) do stupid toy things in this situation, like returning null when in fact the view should be considered invalid.

Lewisham

AFAIK, the SQL specification is anemic on error handling, it's almost always the case that an error will be handled differently between systems.

Q: Why is the table missing?
Q: Why can't you just update the view to not reference the table?

PirateJon

I'm running MS-SQL 2008 on Win2k8 with a coldfusion front end.

Q: Why is the table missing?

Short - It's all hacked junk.
Long - this is a very specific app to do a few very specific things for a specific community (sorry to be vague - gov't security crap). OTS software to do this function ranges in the $20k range. It's written by one guy who works at a different org an hour away. I'm trying to make his custom system work on a different OS and higher version of SQL backend. Dude said we didn't need those tables, so didn't include them with the zip file he sent me. I've reached out to him, but this isn't his primary job and so he's hard to get a hold of.

Q: Why can't you just update the view to not reference the table?

Thought about it, worried I would make it worse. Think I can do this and not break something else?

Deebaser

PirateJon wrote: »

Q: Why can't you just update the view to not reference the table?

Thought about it, worried I would make it worse. Think I can do this and not break something else?

That's hard to tell without knowing more specific data. Is the missing table adding anything to the WHERE other than an equi-join?

If so the only thing you have to worry about is that the columns in the view are a dependancy for one of your queries.

vonPoonBurGer

MS SQL Server 2008? I'm kind of surprised you didn't get an error. I took a closer look at the MS SQL Server documentation I linked earlier and found this interesting quote:

MS SQL Server 2008 documentation wrote:

If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.

Maybe one of the possible "unexpected results" is a null result set. Right before that though, it does explicitly state that queries against a view that reference a dropped table or view should produce an error message, so I'm kind of surprised you're just getting null. Is it possible that coldfusion is stripping out the error message? Have you tried logging into the DB directly and running the frontend's query manually?

If you're worried about making changes, just make a backup before you change anything. You don't even need a whole-database backup, you should be able to rename the existing view using sp_rename, then create a new version using the standard syntax. If in the end your custom version doesn't work, you can always drop it and rename the original back to its original name.

McCow42

Are you sure your app or some middle layer isn't handling/suppressing the exception?

If the table is missing the view is null and void as the data structure is corrupt and unusable. If "dude" didn't think you need it then you just need to forget about that view and move on.

Option 1: Recode your app to not use the view. You could maintain (hack) backwards compatibility by making a conditional based on checking the existence of the table.
Option 2: Rebuild the view so that it doesn't reference the table.
Option 3: Recreate the table and re-validate the structure of the view.

Option 4: Do one of the above and find a new DBA.