No intellisense when alias is in front of column + CASE

Provides intelligent code completion for SQL Server editors.

Moderators: Anu Deshpande, Aaron Law, David Priddle

No intellisense when alias is in front of column + CASE

Postby buckley » Mon Jan 13, 2014 4:03 pm

We bumped into an issue where there were no column suggestions and took the time to write an isolated example.

SELECT
ca. -- no intellisense
BalancePreviousBusinessDay = CASE WHEN 1 = 1 THEN '1'
ELSE '2'
END,

FROM
vwCashAccountSpaarrekening ca

Note that if there is a comma before BalancePreviousBusinessDay the column suggestions appear.


If we can be of further assistance let me know
buckley
 
Posts: 26
Joined: Thu Jan 26, 2006 2:45 pm

Postby buckley » Mon Jan 13, 2014 4:05 pm

Note that this happens when you add a column at the top when you didn't have the chance to type the comma yet. At that moment in time its illegal syntax but that's how one does it.
buckley
 
Posts: 26
Joined: Thu Jan 26, 2006 2:45 pm

Postby Aaron Law » Mon Jan 13, 2014 5:23 pm

Hi Buckley,
Thanks again for the repro steps!

I think your guess is right and since it's illegal syntax the Prompt parser is failing half way through the script and doesn't get to resolve "ca" to "vwCashAccountSpaarrekening". I'll look into seeing if I can make it a little bit more robust in resolving aliases.
Aaron Law
 
Posts: 250
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby buckley » Tue Jan 14, 2014 8:34 am

Alright. With this response rate it is motivating for us to file bug reports. We'll take the time to report any issues in detail so you can reproduce and make the product better/awesome.

Off topic. I am interested in how you develop this product. What kind of testing do you perform. Before or After. Do you in this case first write a failing test and then make it pass.
Do you have a large test harness by this time to detect regressions? I'm primarily a inhouse business developer (lots of CRUD/sql/..) and haven't worked on a product like prompt. I think it must be interesting and poses lots of challenges.
buckley
 
Posts: 26
Joined: Thu Jan 26, 2006 2:45 pm

Postby Aaron Law » Tue Jan 14, 2014 11:27 am

Keep em' coming :) Although that said, this one is going to take a little longer to find a fix for than the last one.

It varies from bug to bug if we write the tests before or after. In the bugs you've reported it was quicker to put the scripts into small tests and run them there than it was to start SSMS every time I made a change.

We have a large set of integration tests (~50,000) that run against test databases every time a build on our build server is completed and then another set of slow running ones overnight. I'm currently trying to move more of our tests into small, quick running unit tests that just mock a database where needed - these are really cool with NCrunch since they can be run every keypress making the feedback if a test fails/passes almost immediate.

It is really interesting to work on, especially as the parser needs to handle incomplete syntax and figure out all the things user might enter at each point (without being too slow!)
Aaron Law
 
Posts: 250
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby buckley » Wed Feb 12, 2014 2:57 pm

Any update on this case? I'm a willing tester for all your beta versions you can throw at me :)
buckley
 
Posts: 26
Joined: Thu Jan 26, 2006 2:45 pm

Postby Aaron Law » Thu Feb 13, 2014 10:34 am

Hi Buckley,
This turned out to be a tricky one to solve with how our parser currently works. We've got some work planned over the next few months on rewriting a part of the parser to allow some of the more advanced prompting feature so I was hoping to include this as part of that work.

I'll have another look at it today to see if there's any quicker fixes that could be included in the beta :)
Aaron Law
 
Posts: 250
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby buckley » Thu Feb 13, 2014 2:05 pm

Hi Aaron,

Thanks for the update. I'll leave it up to you. I don't need a quick fix and prefer the best possible solution even though it will take some time. If you need a tester I'm here. I hope you use my (3) cases which will result in 3 failing tests. Once everything is green again we have a test harness for a robust sql parser than can handle statements that are a working in progress/temporarily invalid.
buckley
 
Posts: 26
Joined: Thu Jan 26, 2006 2:45 pm

Postby Aaron Law » Wed Mar 05, 2014 6:48 pm

Hi Buckley,
I've just put in some changes that have made the tests for your 3 issues turn green. This private build should have more robust error recovery if you'd like to give it a go?

Thanks again for all your help!
Aaron Law
 
Posts: 250
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby buckley » Thu Mar 06, 2014 3:57 pm

Better with build 6.3.0.235.
The previous build had trouble with an alias that comes first. Thanks Aaron

To pick up the original report there is still the case of the invalid comma which breaks intellisense (bold below)

SELECT
ca. -- no intellisense
BalancePreviousBusinessDay = CASE WHEN 1 = 1 THEN '1'
ELSE '2'
END, -- comma here gives no intellesense

FROM
vwCashAccountSpaarrekening ca
buckley
 
Posts: 26
Joined: Thu Jan 26, 2006 2:45 pm

Postby Aaron Law » Thu Mar 06, 2014 5:36 pm

Hmmm odd, I get suggestions on 6.3.0.235 even with the comma in there:
At the "no intellisense" comment:
Image
At the "comma here gives no intellisense":
Image

I'm wondering if I'm missing something that's causing it to break for you?
Aaron Law
 
Posts: 250
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby buckley » Thu Mar 06, 2014 11:50 pm

Indeed, after trying it a second time I get intellisense with or without the comma. Very well done!
buckley
 
Posts: 26
Joined: Thu Jan 26, 2006 2:45 pm


Return to SQL Prompt 6

Who is online

Users browsing this forum: No registered users and 0 guests