Results not being restricted by Schema

shanec · February 16, 2007 5:18PM

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 · February 19, 2007 7:21AM

Check "Owner or schema filtering" in options

Bart Read · February 20, 2007 5:50AM

Beat me to it!

Thanks,
Bart

Bart Read · March 14, 2007 12:57PM

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

Results not being restricted by Schema

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk