BUG: NULLIF or COALESCE with Multiple Expression Parameter Generates Access Violation

ID: Q242454


The information in this article applies to:
  • Microsoft SQL Server versions 6.5, 6.5 Service Pack 1 and later, 7.0, 7.0 Service Pack 1

BUG #: 18841 (SQLBUG_65)
BUG #: 56518 (SQLBUG_70)

SYMPTOMS

When you execute either of the following two statements, against the PUBS database, they may generate an Access Violation:


SELECT coalesce((SELECT * FROM authors), 0) 
-or-

SELECT nullif((SELECT * FROM authors), 0) 


CAUSE

The problem is caused by the multiple expressions returned by the innermost SELECT statement causing either the COALESCE or NULLIF functions to fail. In fact, when executed on SQL Server 7.0, an error message similar to the following may occur:

Server: Msg 116, Level 16, State 1, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


WORKAROUND

Avoid using the COALESCE and NULLIF functions with multiple expressions as parameters.


STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5 and 7.0.

Additional query words: nullif coalesce T-SQL

Keywords : SSrvErr_Log SSrvISQL SSrvTran_SQL kbbug6.50 kbbug7.00 kbSQLServ650bug kbSQLServ700bug
Version : winnt:6.5,6.5 Service Pack 1 and later,7.0,7.0 Service Pack 1
Platform : winnt
Issue type : kbbug


Last Reviewed: October 14, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.