FIX: Use of the RIGHT Function Within an ANSI Join Fails (193757)

The information in this article applies to:

Microsoft SQL Server 6.5 Service Pack 4 and later
Microsoft SQL Server 6.5 Service Pack 3 and later

This article was previously published under Q193757

BUG #: 17401 (SQLBUG_65)

SYMPTOMS

Using the RIGHT function within an ANSI join clause fails with a syntax error on servers with SQL Server 6.5 Service Pack 3 or Service Pack 4 installed.

WORKAROUND

To work around this problem, do either of the following:

Recode the query to use traditional (that is, non-ANSI) join syntax.

-or-
Consider whether the SUBSTRING function could be used. This would only be possible if the data value within the column was guaranteed to be of fixed length. For example, the au_id column in the authors table in the pubs sample database always contains a value of 11 bytes. So in this case, SUBSTRING (au_id,8,4) would always return the same values as RIGHT(au_id, 4).

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5 Service Pack 3 and Service Pack 4. This problem has been corrected in the latest U.S. Service Pack 5a for SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION

The following is an example of a query that demonstrates this problem:

   SELECT a.au_lname, t.title_id
   FROM authors a JOIN titleauthor t
   ON right(t.au_id,11) = right(a.au_id,11)

The following error is generated:

Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'right'.

By recoding the query to use traditional join syntax, the problem does not occur:

   SELECT a.au_lname, t.title_id
   FROM authors a, titleauthor t
   where right(t.au_id,11) = right(a.au_id,11)

Modification Type:	Major	Last Reviewed:	2/24/2004
Keywords:	kbBug kbfix kbSQLServ650sp5fix KB193757