[SA 9.0.162 Pro] - Incorrect join [FIXED]

[SA 9.0.162 Pro] - Incorrect join [FIXED]

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

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.

	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.

	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.

Okay, I'm not pushing you. It's not that rare, but then again, you're right, it isn't frequent either.

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.

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:

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}.

Thank you very much. We're looking into this. Going to try to reproduce your setup.

Confirmed as fixed.