What are the challenges you face when working across database platforms? Take the survey
Options

Results not being restricted by Schema

shanecshanec Posts: 2
edited March 14, 2007 12:57PM in SQL Prompt Previous Versions
If I do the following:

SELECT * FROM POS.

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

Thoughts?

Thanks :)

Comments

  • Options
    Check "Owner or schema filtering" in options
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    Beat me to it! :)
    Thanks,
    Bart
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    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
    Bart Read
    Principal Consultant
    bartread.com Ltd
Sign In or Register to comment.