SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Add column table without alias adds new cross join table

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Add column table without alias adds new cross join table
Author Message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Add column table without alias adds new cross join table Reply with quote
If we have a Table referenced without alias such as "Contacts" in this example:
Code:

SELECT
   Clients.clientName
FROM dbo.Clients
   JOIN dbo.Contacts ON Contacts.clientIncId = Clients.clientIncId
WHERE Clients.city = 'Auckland'


Using the SQL Assistant column-picker in the SELECT section to pick a column causes SQL assistant to re-add the table in a new CROSS join (with comma) with a new alias.

For example picking "contactName" in this image:


will cause SQL Assistant to re-add the table in a comma-join as in this image:


Here is the code block after SQL Assistant has damaged it:
Code:
SELECT
   Clients.clientName,
   Cnt.contactName
FROM dbo.Clients WITH(NOLOCK)
   JOIN dbo.Contacts WITH(NOLOCK) ON Contacts.clientIncId = Clients.clientIncId, Contacts AS Cnt
WHERE Clients.city = 'Auckland'


As you can see it has put in the new join ", Contacts AS Cnt" so that it can create the table alias.

The comma means it is a join with no ON-Condition so it is a CROSS join, effectively multiply the dataset by the number of rows in the table. This is a massive bug!

Use of a comma join is evil because it makes the bug very hard to see.

Please stop Sql Assistant from autonomously joining tables to our code and do not force the use of aliases. If we use table aliases then it is great to support them but it should also do nothing when we choose not to use them or are editing legacy code.
Tue Mar 02, 2021 3:23 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
I believe this is a result of inconsistent naming. In the FROM clause you refer to the table as "dbo.Contacts" but in the SELECT part you refer to it as "Contacts" which makes it appear as a different reference causing that table name getting added to the FROM list because from the SQL Intellisense point of view it's missing in the list. It doesn't add a a ROSS JOIN, it just adds that *new* name for you, and it expects that you add something later to the WHERE clause to define the join condition for that added table. If you were to use aliases or consistent names, then it wouldn't add the table name to the FROM clause. The result would be like

Code:
SELECT
   Clients.clientName,
   Contacts.contactName
FROM dbo.Clients WITH(NOLOCK) AS Clients
   JOIN dbo.Contacts WITH(NOLOCK) AS Contacts ON Contacts.clientIncId = Clients.clientIncId
WHERE Clients.city = 'Auckland'


or

Code:
SELECT
   dbo.Clients.clientName,
   dbo.Contacts.contactName
FROM dbo.Clients WITH(NOLOCK)
   JOIN dbo.Contacts WITH(NOLOCK) ON dbo.Contacts.clientIncId = dbo.Clients.clientIncId
WHERE Clients.city = 'Auckland'


Just in case WITH(NOLOCK) shouldn't be likely used in queries like above, it may lead to returning uncommitted dirty results from another incomplete transaction (it's like SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED). Bren Ozar explans it here https://www.brentozar.com/archive/2018/10/using-nolock-heres-how-youll-get-the-wrong-query-results/
Tue Mar 02, 2021 5:10 am View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
@SysOp
Thanks for the suggestion but it is not true that this is caused by the inconsistent schema name, "dbo".

I recorded a couple of screen-grab GIFs to show that this problem still happens when we consistently use the schema names, with or without them.

Here you can see it adding the new join when we use "dbo." consistently:


And here is the same problem when we do NOT use "dbo."


Also please note that use of the comma as join operator is VERY BAD. Please use the keyword JOIN.

We have the setting "DB Options -> SQL Assistance (profile "SQL Server") -> Aliases -> Auto Add Aliases = Yes (with AS keyword)".
This appears to be a bug with how the application implements this setting.
Tue Mar 02, 2021 7:52 pm View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
@SysOp
I found the error is caused when we use a joining hint, "WITH(NOLOCK)"

See this animated GIF:



The problem only occurs when we use the joining hint. That should not be a problem though. SQL Assistant should be able to parse the code correctly to understand what is the table name or alias and what is the joining hint.

Note that there should be no discussion about the rights or wrongs of using a joining hint. If present, SQL Assistant should understand the hint and be able to exclude it from its interpretation of what is the table name.
Tue Mar 02, 2021 8:04 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Agree. The hint following table name shouldn't effect the behavior. In the scenario pictured, new table name doesn't need to be added to the FROM clause. I have asked the support team to investigate that issue.

P.S. Just in case "WITH(NOLOCK)" is totally unrelated to joins, it's a legacy transaction isolation hint telling the server it's ok to read "dirty" uncommitted data, it's a synonym to WITH (READUNCOMMITTED). https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15
Wed Mar 03, 2021 3:40 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
The issue with extra table name added to FROM is confirmed as a defect, it's unintentional. The parser kind of chokes on the hints following table names. We are planning on fixing this issue in the next release. We also going to fix the issue with the hints added after table variables, code is going to be added to evaluate table type before adding text specified in the Table Hint option.
Wed Mar 03, 2021 11:56 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.