Results not being restricted by Schema

shanec

If I do the following:

SELECT * FROM POS.

Some of the resulting rows come from other schemas such as my Inventory. schema.

Thoughts?

Thanks

BAlexandrov

Check "Owner or schema filtering" in options

Bart Read

Beat me to it!
Thanks,
Bart

Bart Read

Well it turns out that there is a problem with this after all. I stumbled across it just this afternoon, however once I worked out why it was happening it's easily reproduced in the AdventureWorks 2005 database. Assuming you have filtering by owner/schema turned on enter the following without bringing up the completion list:

SELECT Production.

Now hit CTRL+SPACE to bring up the completion list after the '.'. What you'll see is that only objects in the "Production" schema appear in the candidate list. Now delete all of that and enter the following, again without using the completion list:

SELECT production.

Again hit CTRL+SPACE to bring up the list. Notice that all objects appear in the list, not just those in the "Production" schema.

I can't believe we haven't seen this before, but I think it's probably because we're fairly lazy typists: if you insert "Production" from the completion list then of course it matches the case of the object name and the filtering works.

So, a very easy fix: I just added a '!' to one line of code and by so doing gave us another weeks worth of testing work (groan). Thanks for reporting it, I'm certainly very glad we've managed to track it down, and it'll be fixed in 3.1 which should be out sometime between March 23rd and March 27th.


Cheers,
Bart