Popup anomaly

Using the popup from inner join shows:

where pairs show by the popup are a bit misleading:
"id2 = t.id2_1" should be "t2.id2 = t.id2_1"
and
"id2 = t.id2_1" should be "t2.id2 = t.id2_2"

Selecting a keypair results in the correct form:

Then again, the popup after the ON keyword shows the correct form:

but after selecting it inserts the incorrect form:

where "id2 = t.id2_1" should be "t2.id2 = t.id2_1"

	Quote:
	where pairs show by the popup are a bit misleading: "id2 = t.id2_1" should be "t2.id2 = t.id2_1" and "id2 = t.id2_1" should be "t2.id2 = t.id2_2"

I disagree with the above "should be" clause. The table suggested for this join is not entered into the query text. SQL Assistant doesn't know which alias you are going to pick, if you enter table name manually, and at the time of the join suggestion it doesn't know which alias would be generated if you let it do it automatically. To illustrate this point, consider having tables Table1 with a primary key, and Table2 and Table3 each with a foreign key pointing to table Table1. Both Table2 and Table 3 would be suggested for a join. Yet their names are almost the same. Picking either table would result in the same alias t2. As you can see in my example, at the time the popup is displayed, the table alias and the exact spelling of the complete join condition are unknown yet.

As for the missing alias before the column name in a join, I have logged a new case #SA-11809.

Last edited by SysOp on Tue Oct 12, 2010 8:59 am; edited 1 time in total

SysOp wrote:

	Quote:
	where pairs show by the popup are a bit misleading: "id2 = t.id2_1" should be "t2.id2 = t.id2_1" and "id2 = t.id2_1" should be "t2.id2 = t.id2_2"

I disagree with the above "should be" clause. The table suggested for this join is not entered into the query text. SQL Assistant doesn't know which alias you are going to pick, if you enter table name manually, and at the time of the join suggestion it doesn't know which alias would be generated if you let it do it automatically. To illustrate this point, consider having tables Table1 with a primary key, and Table2 and Table3 each with a foreign key pointing to table Table1. Both Table2 and Table 3 would be suggested for a join. Yet there names are almost the same. Picking either table would result in the same alias t2. As you can see in my example, at the time the popup is displayed, the table alias and the exact spelling of the complete join conditions are unknown yet.

I'm sorry if I was impolite, I did not mean to. I used the expression "should be" because my opinion is it should work the way I described (which may or may not be the way it was intended or the way that is correct, of course).

Your example with Table2 and Table3 is reasonable (SA does not know which alias would be generated) but I would argue with your argument. It can estimate. The first table called Table1 is already in the script and it holds firm the alias t. Since both Table2 and Table3 has affinity to the same alias t (which is already occupied) it will generate t2. There is nothing wrong showing "t2.id1 = t.id1" for both Table2 (with alias t2) and Table3 (with same alias t2). This will not produce any issues because only one of the options can be picked. The user will either pick Table2 or Table3 (if he/she does pick one at all) in one turn and SA will nevertheless generate the same alias t2 whichever is picked. And by the time showing the condition in the popup, all possible results for that condition can be calculated. So why not show it in the popup?

I know that making the correct guess requires pre-generating that join just to show it in the popup and that without entering it into the script. And I know that results in overhead.

I'm not saying it does not work flawlessly the way it does now. My point was that current behavior is showing signs of some inconsistency. It either does not enter what it shows, or it does not show what will be entered. I'm just saying it could look nicer/more readable the way I imagined.

Quote:

Your example with Table2 and Table3 is reasonable (SA does not know which alias would be generated) but I would argue with your argument. It can estimate. The first table called Table1 is already in the script and it holds firm the alias t. Since both Table2 and Table3 has affinity to the same alias t (which is already occupied) it will generate t2. There is nothing wrong showing "t2.id1 = t.id1" for both Table2 (with alias t2) and Table3 (with same alias t2). This will not produce any issues because only one of the options can be picked. The user will either pick Table2 or Table3 (if he/she does pick one at all) in one turn and SA will nevertheless generate the same alias t2 whichever is picked. And by the time showing the condition in the popup, all possible results for that condition can be calculated. So why not show it in the popup?

Thank you. This makes total sense. In the current implementation, the automatic alias is added during the code generation step, which is after the graphical suggestion. I guess SQL Assistant is doing some kind of lookup for already referenced table names and aliases before adding a new one to the code. I guess the same logic can be applied to the join suggestions popup, so that the aliases appear along column names and the code can be previewed in the popup in WYSIWYG mode - what you see, is what you get. I'm going to log a new enhancement request for this feature.

I'd appreciate that, thank you.