SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.162 Pro] - Incorrect join [FIXED]

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.162 Pro] - Incorrect join [FIXED]
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [SA 9.0.162 Pro] - Incorrect join [FIXED] Reply with quote
Having multiple foreign keys in a table pointing to the same different table (just to be clear, table1 having two or more foreign keys pointing to table2) the join conditions in the popup show the conditions merged instead of showing multiple references to the same table with their corresponding conditions.


Last edited by gemisigo on Tue Oct 18, 2016 3:50 pm; edited 1 time in total
Tue Oct 11, 2016 4:38 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
This is a known deficiency and sort of an edge case. In the internal cache, it doesn't save referential constraint names, it saves foreign key and primary key columns only . As a result, when suggesting joins, it cannot differentiate them by constraint.
Tue Oct 11, 2016 8:20 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
SysOp wrote:
This is a known deficiency and sort of an edge case.

Is it going to be addressed? It's not that rare. Even though it isn't hard to fix the code inserted, I'd still call this a moderate inconvenience.
Tue Oct 11, 2016 8:28 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
Quote:
Is it going to be addressed? It's not that rare. Even though it isn't hard to fix the code inserted, I'd still call this a moderate inconvenience.

It should be addressed at some point. Unfortunately I cannot promise a specific date.
Tue Oct 11, 2016 9:32 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Okay, I'm not pushing you. It's not that rare, but then again, you're right, it isn't frequent either.
Tue Oct 11, 2016 10:09 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
I was informed today that for some database types, SQL Server included, we already show multiple foreign keys between two tables as separate join suggestions not merged together. Developers are asking me for more information. They want to know how you connect to the database, database version and connection driver, and if you can provide DDL for reconstructing your tables in a test environment.
Thu Oct 13, 2016 7:47 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Sure. Requesting DDL code for tables still does not work so I had to use SSMS to fetch them.
Here are the create statements:
Code:


/****** Object:  Table [dbo].[kocsiall]    Script Date: 2016.10.13. 14:48:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[kocsiall](
    [kocsiall_id] [int] NOT NULL,
    [foldhely_id] [int] NOT NULL,
    [kocsiall_szam] [char](3) NOT NULL,
    [kocsiall_gps_x] [float] NOT NULL,
    [kocsiall_gps_y] [float] NOT NULL,
    [kocsiall_gps_z] [real] NOT NULL,
    [idegenkod] [char](15) NOT NULL,
    [idegenkod2] [char](15) NOT NULL,
    [aktiv_e] [bit] NOT NULL,
    [datum_tol] [smalldatetime] NOT NULL,
    [datum_ig] [smalldatetime] NULL,
    [utso_datumtol] [datetime] NULL,
 CONSTRAINT [PK_kocsiall2] PRIMARY KEY CLUSTERED
(
    [kocsiall_id] ASC,
    [datum_tol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[szakaszok]    Script Date: 2016.10.13. 14:48:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[szakaszok](
    [id_szakasz] [int] IDENTITY(1,1) NOT NULL,
    [id_kocsiallas_a] [int] NOT NULL,
    [datum_tol_a] [smalldatetime] NOT NULL,
    [id_kocsiallas_b] [int] NOT NULL,
    [datum_tol_b] [smalldatetime] NOT NULL,
    [tavolsag] [decimal](6, 2) NOT NULL,
    [polyline] [nvarchar](max) NOT NULL,
    [id_szakasz_tipus] [int] NOT NULL,
    [ido] [int] NULL,
 CONSTRAINT [Pk_szakaszok] PRIMARY KEY CLUSTERED
(
    [id_szakasz] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[szakaszok]  WITH NOCHECK ADD  CONSTRAINT [fk_szakaszok_kocsiall] FOREIGN KEY([id_kocsiallas_b], [datum_tol_b])
REFERENCES [dbo].[kocsiall] ([kocsiall_id], [datum_tol])
GO
ALTER TABLE [dbo].[szakaszok] CHECK CONSTRAINT [fk_szakaszok_kocsiall]
GO
ALTER TABLE [dbo].[szakaszok]  WITH NOCHECK ADD  CONSTRAINT [fk_szakaszok_kocsiallas_a] FOREIGN KEY([id_kocsiallas_a], [datum_tol_a])
REFERENCES [dbo].[kocsiall] ([kocsiall_id], [datum_tol])
GO
ALTER TABLE [dbo].[szakaszok] CHECK CONSTRAINT [fk_szakaszok_kocsiallas_a]


The database is undergoing some debug process, hence the NOCHECKs for foreign keys.

Here's the popup from one approach, selecting table szakasz first:


Notice, that while the popup only shows the alias k for table kocsiall on k.kocsiall_id = s.id_kocsiallas_a and the rest of the condition lacks alias k completely, the inserted code has those missing k-s in place:
Code:

SELECT * FROM dbo.szakaszok AS s INNER JOIN dbo.kocsiall AS k
                                           ON k.kocsiall_id = s.id_kocsiallas_a AND k.datum_tol = s.datum_tol_a AND k.kocsiall_id = s.id_kocsiallas_b AND k.datum_tol = s.datum_tol_b


And here it is from the other way around, choosing table kocsiall first:

Thu Oct 13, 2016 9:07 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
As for connections, the database version is mostly SQL Server (2012 and 2014, standard and express versions), the editor is the SQL Editor that comes with SA (x64), using ODBC connections with Driver={SQL Server Native Client 11.0}.
Thu Oct 13, 2016 4:58 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
Thank you very much. We're looking into this. Going to try to reproduce your setup.
Thu Oct 13, 2016 6:17 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Confirmed as fixed.
Tue Oct 18, 2016 3:50 pm 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.