December 23, 2014 at 7:00 am
Hi Experts !
I have two select statements, in between select statement taking UNION ALL . I need to avoid the error
Warning: The join order has been enforced because a local join hint is used.
December 23, 2014 at 7:04 am
It's not an error, it's a warning and it's there because one of your queries is using a join hint, like INNER MERGE JOIN or INNER HASH JOIN or LEFT OUTER LOOP JOIN.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2014 at 8:50 am
Thanks for correction is there way to by pass this error. Because one of the application showing no data when getting this warning on the prompt?
Warning: The join order has been enforced because a local join hint is used.
SELECT
p0.ITEM_TYPE as ITEM_TYPE
,p0.PROPERTY_STRING as ITEM_NAME
, e.START_DATETIME AS 'START_DATETIME'
, cast(e.VAL1 as numeric(11,2)) AS 'VAL1'
, cast(e.VAL2 as numeric(11,2)) AS 'VAL2'
FROM [TEST1].[dbo].ITEM_EVENT e
LEFT OUTER LOOP JOIN [TEST1].[dbo].ITEM_PROPERTY p0
ON e.ITEM_ID = p0.ITEM_ID
AND p0.START_DATETIME <= e.START_DATETIME
AND p0.END_DATETIME > e.START_DATETIME
AND p0.PROPERTY_TYPE = 'NAME'
UNION ALL
SELECT
p0.ITEM_TYPE as ITEM_TYPE
,p0.PROPERTY_STRING as ITEM_NAME
, e.START_DATETIME AS 'START_DATETIME'
, cast(e.VAL1 as numeric(11,2)) AS 'VAL1'
, cast(e.VAL2 as numeric(11,2)) AS 'VAL2'
FROM [TEST2].[dbo].ITEM_EVENT e
LEFT OUTER LOOP JOIN [TEST2].[dbo].ITEM_PROPERTY p0
ON e.ITEM_ID = p0.ITEM_ID
AND p0.START_DATETIME <= e.START_DATETIME
AND p0.END_DATETIME > e.START_DATETIME
AND p0.PROPERTY_TYPE = 'NAME'
December 23, 2014 at 10:38 am
It is not an error.
It is a warning and if the application is breaking, then the application is at fault. The query will run fine and will return data.
You are getting the warning because you are specifying a join hint in both subqueries. Either remove the join hint or fix the application so that it can handle SQL sending back warnings.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2014 at 12:21 pm
Hi
Thank you
I removed the join hint form the statement by replacing below. The application having no breaking or fault.
1.
FROM [Test1].[dbo].ITEM_EVENT e,[Test1].[dbo].ITEM_PROPERTY p0
WHERE e.ITEM_ID = p0.ITEM_ID
2.
FROM [Test2].[dbo].ITEM_EVENT e,[Test2].[dbo].ITEM_PROPERTY p0
WHERE e.ITEM_ID = p0.ITEM_ID
December 23, 2014 at 2:02 pm
That's changed the logic (changed an outer join to an inner join), and changed it to a 20+ year old form of SQL. You needed to remove the join *hint*, not the join itself. Old (pre SQL 2000) joins should not be used.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 24, 2014 at 7:28 am
farrukhhameed786 (12/23/2014)
HiThank you
I removed the join hint form the statement by replacing below. The application having no breaking or fault.
1.
FROM [Test1].[dbo].ITEM_EVENT e,[Test1].[dbo].ITEM_PROPERTY p0
WHERE e.ITEM_ID = p0.ITEM_ID
2.
FROM [Test2].[dbo].ITEM_EVENT e,[Test2].[dbo].ITEM_PROPERTY p0
WHERE e.ITEM_ID = p0.ITEM_ID
To specify what Gail means by not using a 20 year old version of SQL
Instead of
FROM [Test1].[dbo].ITEM_EVENT e,[Test1].[dbo].ITEM_PROPERTY p0
WHERE e.ITEM_ID = p0.ITEM_ID [/quote]
2.
FROM [Test2].[dbo].ITEM_EVENT e,[Test2].[dbo].ITEM_PROPERTY p0
WHERE e.ITEM_ID = p0.ITEM_ID
You should write it as
FROM Test1.dbo.Item_Event e
INNER JOIN Test1.dbo.Item_Property p0
ON e.Item_ID = p0.Item_ID
In other words, don't just list your tables with commas in the FROM clause and then make the joins part of the WHERE clause.
Explicitly state INNER JOIN or LEFT JOIN as I did in the second example and create the join condition in the ON clause.
This way if you have conditions like Item_ID = 9 you place them in the WHERE clause and it is much clearer.
Do not use statements like LEFT OUTER LOOP JOIN as that will result in the warning. LEFT JOIN is all you need.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 27, 2014 at 9:41 am
Many thank Gila & Stefan Krzywicki.
By using inner join the issue is solved.
Regards
December 27, 2014 at 2:27 pm
farrukhhameed786 (12/27/2014)
By using inner join the issue is solved.
If by 'solved' you mean 'changed the meaning of the query and possibly the results', then sure.
I can't understand though why you've changed the join type from outer to inner when all that needed to be done was the join type hint removing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 11, 2015 at 6:35 am
OPTION (FORCE ORDER) has resolved the issue.
February 11, 2015 at 8:27 am
All you had to do was change one thing in your code twice.
If you'd changed
FROM [TEST1].[dbo].ITEM_EVENT e
LEFT OUTER LOOP JOIN [TEST1].[dbo].ITEM_PROPERTY p0
to
FROM [TEST1].[dbo].ITEM_EVENT e
LEFT JOIN [TEST1].[dbo].ITEM_PROPERTY p0
and
FROM [TEST2].[dbo].ITEM_EVENT e
LEFT OUTER LOOP JOIN [TEST2].[dbo].ITEM_PROPERTY p0
to
FROM [TEST2].[dbo].ITEM_EVENT e
LEFT JOIN [TEST2].[dbo].ITEM_PROPERTY p0
It would be fine without any OPTION or INNER JOIN or other code changes and workarounds.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 16, 2016 at 6:42 am
I was encountering this error also and did a search and found this thread. I think there is also an issue in SQL Server in reporting this error ('warning' to some...). In my case, I had a temp table populated and was going through a series of inserts and updates using data in the temp table. I added a couple more insert/updates and got:
%E 01000 - 8625 - [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The join order has been enforced because a local join hint is used.
After struggling with it for a while, I removed the loop hint from all the queries and then it reported:
%E 37000 - 4104 - [Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "#LAOTRACE.DSID" could not be bound.
... so the SQL actually had an error in it but it was reporting the warning for some unknown reason instead.
after I fixed the missing field issue in my temp table, I could restore all the loop hints back again and then it worked and no longer reported the 'warning'...
so the moral of the story is "it might not be a join problem at all but you might not know until you remove all the join hints..."
September 16, 2016 at 8:03 am
Kevin.J.Small (9/16/2016)
I was encountering this error also and did a search and found this thread. I think there is also an issue in SQL Server in reporting this error ('warning' to some...). In my case, I had a temp table populated and was going through a series of inserts and updates using data in the temp table. I added a couple more insert/updates and got:%E 01000 - 8625 - [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The join order has been enforced because a local join hint is used.
After struggling with it for a while, I removed the loop hint from all the queries and then it reported:
%E 37000 - 4104 - [Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "#LAOTRACE.DSID" could not be bound.
... so the SQL actually had an error in it but it was reporting the warning for some unknown reason instead.
after I fixed the missing field issue in my temp table, I could restore all the loop hints back again and then it worked and no longer reported the 'warning'...
so the moral of the story is "it might not be a join problem at all but you might not know until you remove all the join hints..."
It is a warning, not an error. Your observations seem extremely unlikely - unless you are executing your SQL statements through something other than SSMS.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 16, 2016 at 8:17 am
Your observations seem extremely unlikely - unless you are executing your SQL statements through something other than SSMS.
It is being executed via ODBC. But it is interesting that ODBC can return multiple 'messages' for a single statement and if I print all the messages we see that the warning is the first in the list so it is what I apparently popup, but the error is the next in the list.
%E 01000 - 8625 - [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The join order has been enforced because a local join hint is used.
%E S0022 - 207 - [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'x'.
%E 37000 - 8180 - [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
I typically ignore warnings so that warning is probably happening all the time but being ignored, but when combined with an error, my dialog apparently only shows the first message in the list even though I dump them all to the debug output.
January 7, 2022 at 9:16 pm
For those who find this article and are confused since your query does not use a hint, the warning is also seen every time you query the DMV sys.dm_db_column_store_row_group_physical_stats because it includes a join hint.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply