Transact SQL – Warning – “No Join Predicate”

Background

Reviewing SQL Server Stored Procedures and as I started to dig in more into each SQL Statement I saw a warning that was intriguing.

In the rest of this post, we will talk about how I burnt hours last night and an additional full day’s work today; as I tried to get a more intimate understanding.

 

Introduction

Though, I will like to share the original tables and Stored Procedures, I obviously can not; so let us talk about how I generated a test case.

I prepared to open up a Microsoft Connect Item and hoped that if I presently a good test data, the engineers will be better able to reproduce the problem.

Per that course, I created a smaller table structure and incrementally copied over rows from the original table into the new table.  Once I was able to reproduce the problem, I tried simulating the same error with publicly sharable contents.

 

Lab Environment

Entity

Here are the entities:

  • Person
    • We have a Person table; that contains a personID attribute
  • listofAttributes
    • We are keeping available attributes in the listofAttributes table
  • AttributeList
    • Each person’s attribute is kept in the AttributeList table
    • The table is very de-normalized as it contains a row for each attribute; whereas in a normalized entity the attribute will be its own separate column
    • The personID column links back to the person table
    • The attributeName column links back to the listofAtributes table
    • We are keeping the actual attribute value in the attributeValue column
    • The person making the change and the time-stamp are kept in the addedBy and addedOn columns respectively

Data Model

Here is our Data Model:

datamodel

 

DDL – Create Tables


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

if schema_id('njp') is null
begin

    exec('create schema [njp] authorization [dbo]')

end
go

if object_id('[njp].[AttributeList]') is not null
begin
    drop table [njp].[AttributeList]
end
go

if object_id('[njp].[person]') is not null
begin
    drop table [njp].[person]
end
go

create table [njp].[person]
(
    [personID] bigint not null identity(1,1)

     CONSTRAINT [PK_PERSON_ID] PRIMARY KEY CLUSTERED
    (
	    [personID] ASC
    )

)

if object_id('[njp].[listofAttributes]') is not null
begin
    drop table [njp].[listofAttributes]
end
go

create table [njp].[listofAttributes]
(
    [attributeName] nvarchar(50) not null primary key
)
go

CREATE TABLE [njp].[AttributeList]
(
	  [pkAttributeListID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL

    , [personID] bigint not null

	, [attributeName] [nvarchar](50) NULL

	, [attributeValue] [nvarchar](380) NULL

	, [addedBy] [sysname] NOT NULL
        CONSTRAINT constrantDefaultNJPAttributeList
            default (SUSER_SNAME())

	, [addedOn] [datetime] NOT NULL
         CONSTRAINT constrantDefaultNJPAttributeListAddedOn
            default getdate() 

     CONSTRAINT [PK_AttributeList] PRIMARY KEY NONCLUSTERED
    (
	    [pkAttributeListID] ASC
    )

    , CONSTRAINT [FK_AttributeList_PersonID]
        FOREIGN KEY
            (
	            [personID]
            )
        REFERENCES [njp].[person]
            (
	            [personID]
            )

     , CONSTRAINT [FK_AttributeList_AttributeName]
        FOREIGN KEY
            (
	            [attributeName]
            )
        REFERENCES [njp].[listofAttributes]
            (
	            [attributeName]
            )

)

go

create clustered index idx_personID_attributeName
on [njp].[AttributeList]
(
          [personID]
	, [attributeName]
)
GO

create nonclustered index idx_pkAttributeListID
on [njp].[AttributeList]
(
      [pkAttributeListID]
)
GO

 

 

DDL – Populate Data

 


set nocount on;
go

truncate table [njp].AttributeList;
delete from [njp].[listofAttributes];
delete from [njp].[person];

go

/* Add 10 employees */
insert into [njp].[person]
default values;
go 10

insert into [njp].[listofAttributes]
( [attributeName])
values
  ('BirthMonth')
 ,('BirthDay')
 ,('BirthCity')
 ,('favoriteColor')
 ,('FirstCar')
go

declare @iNumberofCars int
declare @listofcars table
(
      [id] int not null identity(0,1)
    , [value] varchar(100)
)

declare @iNumberofColors int

declare @listofColors table
(
      [id] int not null identity(0,1)
    , [value] varchar(100)
)

declare @iNumberofCities int

declare @listofCities table
(
      [id] int not null identity(0,1)
    , [value] varchar(100)
)

insert into  @listofCars
(
    [value]
)
values
    ('Toyota')
   ,('Honda')
   ,('Saab')
   ,('Chevrolet')
   ,('Dodge')
   ,('Nizzan')
   ,('Mazda')
   ,('Tata')
   ,('VW')
   ,('Volvo')
   ,('Peugeot')
   ,('Tesla Motors')

set @iNumberofCars = ( select max(id) from @listofCars )

insert into  @listofCities
(
    [value]
)
values
  ('Chicago')
, ('Mephis')
, ('Miami')
, ('Havana')
, ('Washington D.C.')
, ('Houston')
, ('Dallas')
, ('Denver')
, ('Colorado Springs')
, ('Seattle')
, ('Tacoma Washington')
, ('San Jose')
, ('Sacramento')

set @iNumberofCities = ( select max(id) from @listofCities )

insert into  @listofColors
(
    [value]
)
values
      ('Blue')
    , ('Green')
    , ('Purple')
    , ('Orange')
    , ('Yellow')
    , ('White')
    , ('Black')
    , ('Forrest Green')
    , ('Hazel Green')
    , ('Teal')
    , ('Vanilla')
    , ('Khaki')
    , ('Mocassin')

set @iNumberofColors = ( select max(id) from @listofColors )

insert into njp.AttributeList
(
      [personID]
    , [attributeName]
    , [attributeValue]
)
select
          tblP.[personID]
        , tblLA.[attributeName]
        , case

                when tblLA.attributeName = 'BirthMonth' then cast(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 12 as varchar)

                when tblLA.attributeName = 'BirthDay' then cast(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 31 as varchar)

                when tblLA.attributeName = 'BirthCity' then (
                                                                select min([value])
                                                                from   @listofCities tblL
                                                                where  [id] = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % @iNumberofCities
                                                            )

                when tblLA.attributeName = 'FirstCar' then (
                                                                select min([value])
                                                                from   @listofCars tblL
                                                                where  [id] = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % @iNumberofCars
                                                            )
                when tblLA.attributeName = 'favoriteColor' then
                                                            (
                                                                select min([value])
                                                                from   @listofColors
                                                                --where  [id] = floor(rand() * @iNumberofColors)
                                                                where  [id] = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % @iNumberofColors
                                                            )

        end

from  [njp].[person] tblP

        cross join [njp].[listofAttributes] tblLA

go

Quick Explanation

  • We removed data from the tables
  • We added N persons.  N is the number after the go
  • We added the attributes to the njp.listofAttributes table
  • We created and populated the following table variables :- @listofCars, @listofColors, @listofCities
  • We used ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % @iNumberofEntries to generate random pointers to the aforementioned table variables
  • And, used the cross join operators to tie the person and listofAttributes table

 

 

DML – Query Data

 


declare @personID bigint

declare @attributeName nvarchar(50)
declare @attributeNameAlt nvarchar(50)
declare @attributeNameAlt2 nvarchar(50)

set @attributeName = 'BirthMonth'
set @attributeNameAlt = 'favoriteColor'
set @attributeNameAlt2 = 'BirthCity'

set @personID = 1

select
          tblAL.personID
        , tblAL.attributeName
        , tblAL.attributeValue

        , tblALAlt.attributeName
        , tblALAlt.attributeValue

from [njp].[AttributeList] tblAL

    left outer JOIN [njp].[AttributeList] tblALAlt
            ON tblAL.personID = tblALAlt.personID
            AND tblALAlt.attributeName = @attributeNameAlt

where tblAL.personID = @personID 

and tblAL.attributeName = @attributeName

 

Query Plan

NoJoinPredicate

 

The red warning sign

NoJoinPredicate-NestedLoops-NoJoinPredicate

We want to pay attention to the Warnings.  In this case, our warning, states “No Join Predicate“.

 

 

Trouble Shooting

Review data for duplicates

Let us review our data and see if we have duplicates.

 


select
          personID
        , attributeName
        , count(*) as cnt

from [njp].[AttributeList] tblAL

group by
          personID
        , attributeName

having count(*) > 1

order by
        count(*) desc

 

duplicates

 

 

Solution

There are a couple of way to fix this problem.

 

Create a unique index on your join columns

Let us help SQL Server and indicate that each row is unique based on personID and attributeName.

 


    create unique index idx_unique_personID_attributeName
    on [njp].[AttributeList]
    (
              [personID]
	    , [attributeName]
    )

 

 

or better still, re-use our existing clustered index

 


if exists
(
      select 1
      from   sys.indexes tblSI
      where  tblSI.object_id = object_id('[njp].[AttributeList]')
      and    tblSI.name = 'idx_personID_attributeName'
)
begin

 drop index [njp].[AttributeList].idx_personID_attributeName;

end;

create unique clustered index idx_unique_personID_attributeName
on [njp].[AttributeList]
(
   [personID]
 , [attributeName]
)
GO

Or you can consider changing your primary key


    alter table [njp].[AttributeList]
        drop constraint [PK_AttributeList] 

    alter table [njp].[AttributeList]
        add constraint PK_AttributeList
            primary key
    (
            [personID]
	  , [attributeName]
    );

If you go that way:

  • Please make sure that the columns are not nullable; in our original example the column attributeName was nullable
  • Review whether to make it clustered

 

 Post Change Query Plan

goodPlan-WithUnique

 

Rewrite Query

One can also rewrite the query

 


set nocount on
go

DBCC FREEPROCCACHE
go

declare @personID bigint

declare @attributeName nvarchar(50)
declare @attributeNameAlt nvarchar(50)
declare @attributeNameAlt2 nvarchar(50)

set @attributeName = 'BirthMonth'
set @attributeNameAlt = 'favoriteColor'
set @attributeNameAlt2 = 'BirthCity'

set @personID = 1

select
          tblAL.personID
        , tblAL.attributeName
        , tblAL.attributeValue

        , tblALAlt.attributeName
        , tblALAlt.attributeValue

from [njp].[AttributeList] tblAL

    left outer JOIN [njp].[AttributeList] tblALAlt 

            ON tblAL.personID = tblALAlt.personID
            AND tblALAlt.attributeName = @attributeNameAlt

            AND tblALAlt.[pkAttributeListID] =
                      (

                           select max(tblALAlt_Inner.[pkAttributeListID])
                           from   [njp].[AttributeList] tblALAlt_Inner
                           where tblALAlt.personID = tblALAlt_Inner.personID
                           and   tblALAlt.attributeName = tblALAlt_Inner.attributeName
                       )

where tblAL.personID = @personID 

and tblAL.attributeName = @attributeName

 

In our rewrite, we did the following:

  • We did a correlated join and made sure we compared the results of our sub-query against a unique column; in this case pkAttributeListID

 

Query Plan

correlatedJoin

 

Explanation

  • The query plan is more heavy ( I borrowed that adjective from Robert Palmer [Addicted to Love] )
  • And, awful as a Lazy Spool tags along, as well

 

 

Source Control

GitHub

The Transact SQL is available GitHub @ https://github.com/DanielAdeniji/TransactSQLWarningNoJoinPredicate .

 

 

Microsoft Connect Items

Connect Item – 693321 – missing_join_predicate event: behavior and description – by Ana Mihalj – 10/6/2011

Back in 2011, Ana Mihalj opened up a Connect Item and provided supporting code.

Microsoft’s Eric Hanson closed the ticket and provided this explanation:

Thanks for the suggestion. We’ll change the code to ignore the number of rows and issue the warning if there is no join predicate regardless of cardinality estimate. This will appear in a future release after SQL Server 2012. We’ve also updated the documentation to remove the statement that “This event only occurs if both sides of the join return more than one row.”

Unfortunately because of the haste in which Microsoft’s closes these Connect Items, they rob us of the opportunity to track the bugs and correlate the fixes to specific product releases \ service packs \ hot fixes.

And, not properly credit the community participation that is so supportive of the LIFE of the product.

 

Summary

I think what threw me off is the whole “No Join Predicate” thing.

I know it is American English; everything is shorthand.

But, really will “Query is not full joined” be so taxing.

But, as Khalid Hussein’s “Kite Runner” book shared, “It is their Book and they get to choose how they end (name) it“.

 

Quotes

For a first full week, this week was tumultuous at best.  And, so I start my weekend @ Saturday 4 PM ( on this note ):

The Kite Runner
http://en.wikiquote.org/wiki/The_Kite_Runner

There is only one sin, only one. And that is theft. Every other sin is a variation of theft….When you kill a man, you steal a life. You steal his wife’s right to a husband, rob his children of a father. When you tell a lie, you steal someone’s right to the truth. When you cheat, you steal the right to fairness.

 

References

I have looked at this problem a few times in the past.  Here are some of my old thoughts:

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s