Problems with aliases

• 
  Thanks for these Adam, most of them are things we're now aware of but I've put in a quick summary comment for each point below.
  
  

  amachanic wrote:

  Type:
  

  select *
  FROM [Sales].[SalesOrderHeader] soh
  WHERE soh
  

  
  Type a "." at the end of the "WHERE soh" line.... and get a list of all database objects.

  
  We'll be sorting this out for the final release. At that point you definitely only want the columns on SalesOrderHeader. Also affects things like owner/schema qualification, and qualifying with table/view name.
  

  select *
  FROM [Sales].[SalesOrderHeader] soh
  WHERE
  

  
  Hit space at the end, get a list of columns from SalesOrderHeader. Good! But what if I want to find SalesOrderId? I start typing "Sal" and ... Get a list of tables prefixed with "Sal". Not what I wanted.

  
  That was really annoying. We spotted it rather later in the day than would have been ideal. I managed to fix it however the fix clobbered something else so to be on the safe side we took it out. The problem is that after the first character is typed, rather bizarrely, the parser chokes which completely screws up the AST, and that in turn screws up the candidate list. And if you think that sounds a bit grubby you should see the fix.
  

  Now I decide I want to use an IN clause, so...
  

  select *
  FROM [Sales].[SalesOrderHeader] soh
  WHERE soh.SalesOrderId IN
  (
    SELECT *
    from 
  )
  

  
  Hit space after the "from" and get a list of every database object. Again, not what I expected (I wanted a list of tables).

  This list should always contain tables, views, aliases, table valued functions, and any table valued variables that are in scope, but I think at the moment we probably just bung everything in there to be safe (although we don't actually support variables yet anyway).
  
  ... and after manually finding the table name and entering the column name, the WHERE clause does not work either:
  

  select *
  FROM [Sales].[SalesOrderHeader] soh
  WHERE soh.SalesOrderId IN
  (
    SELECT sod.SalesOrderId
    from [Sales].[SalesOrderDetail] AS sod
    where 
  )
  

  
  Again, list of every database object, not just columns from SalesOrderDetail. By the way, regarding ordering: If I'm in a subquery, I'd like to see the "innermost" columns first, then columns from the outer tables (and if I'm nested, I'd like to to maintain nesting order if possible, always showing the innermost tables' columns, then working out from there).

  
  OK, we might be able to do something about that, although we need to be a little careful about the performance impact of throwing together too many candidate lists on the fly. Unfortunately list insertion and deletion isn't the normal O(1), but is O(log n) where n is the number of items in the list for both insertion and removal. Where a list item might appear later in the candidate list it also needs to be removed from that candidate list. If the latter contains 50,000 objects then you can see where I'm getting a bit concerned (it's still not too bad though: O(4-ish) versus O(1)). We'll see how it plays out.
  
  
  Thanks,
  Bart

  Bart Read September 04, 2006 12:38

  - 0 +
• 
  BTW are you using AdventureWorks 2005 there? Just be handy as we can use these as test cases verbatim if you are.
  Thanks,
  Bart

  Bart Read September 04, 2006 12:38

  - 0 +
• 
  Yes, I'm running all of my tests in SSMS on AW2005 (and other databases, but whenever I find a problem I'm trying to replicate it in AW so you can actually repro).

  amachanic September 04, 2006 13:01

  - 0 +
• 
  Thanks Adam, that's extremely useful. Makes our lives a lot easier at any rate.

  Bart Read September 04, 2006 14:02

  - 0 +

Add comment

Please sign in to leave a comment.