Cannot reproduce client SQL 2008R2 error: subqueries are not allowed

Question

EDIT: My question is not "how do I work around the failure?", my question is "why is the failure occurring?"

We have a client that reports error "1046 subqueries are not allowed in this context. Only scalar expressions are allowed" for SQL Server 2008 R2. We cannot reproduce in house. Is there a server setting or database configuration option that can cause this?

-- Simplification of tables looks like this

create table foo_type(
    foo_type_id int not null,
    foo_type_name nvarchar(100) not null,
    primary key (foo_type_id)
)

insert into foo_type values (1, 'type one')
insert into foo_type values (2, 'type two')

create table foo(
    foo_id int not null,
    foo_type_id int not null,
    foo_name nvarchar(100) not null,
    primary key (foo_id),
    constraint fk_foo_to_foo_type
    foreign key (foo_type_id) references foo_type
)

insert into foo values (11, 1, 'foo one')

-- and the mysteriously failing insert

insert into foo values (
    (select coalesce(max(foo_id),0) + 1 from foo),
    2,
    'foo two'
)

Answer 1

Are you 100% sure the (select coalesce(max(foo_id),0) + 1 from foo) gives one row? You said it was simplified code so this may not be the case.

This should be an IDENTITY column because 2 processes can insert the same row and you don't require an aggregate over the current data.

Anyway, how to fix it. Change to this (MAX without GROUP BY always gives one row)

insert into foo values (
select
    coalesce(max(foo_id),0) + 1,
    2,
    'foo two'
from
    foo WITH (ROWLOCK, UPDLOCK, HOLDLOCK)

To test on the client, add this immediately after

SELECT @@ROWCOUNT

Note: the hints reduce (not remove) the chances of the same value being inserted.

Answer 2

Do they have the database compatibility mode set correctly? I'd check all the database options and compare them to what you expect from your systems.

Answer 3

Wow, I have never used INSERT with a sub-SELECT in that way. This is what I would have done:

insert into foo 
select 
    coalesce(max(foo_id),0) + 1,
    2,
    'foo two'
from foo

That is, after all, the exact purpose for which that statement was made!

Or, if you absolutely demand to use the VALUES clause, you could try:

DECLARE @MaxId INT
SELECT 
    @MaxId = coalesce(max(foo_id),0) + 1
FROM foo

INSERT INTO foo 
VALUES (
    @MaxId
    2,
    'foo two')

I've never seen the "subqueries are not allowed in t his context" error, but I always use one of those two formats above (primarily the first on).

---

Regarding why this error is happening: SQL Server thinks that the sub-select will return more than one row. Whether it will or not is beside the point. When SQL Server tries to parse the T-SQL for this statement, it is expecting multiple rows.

One more possibility is that you could add "TOP 1" to let SQL Server know that this will return only one row. Personally, though, I would rather have the strange statement fixed than see a hacky workaround put in place. But that's just my opinion.

Answer 4

How about this:

    insert into foo (foo_id,foo_type_id,foo_name)
    VALUES (
    (select coalesce(max(foo_id),0) + 1 from foo),
    2,
    'foo two'
    )

This should also work:

insert into foo
select coalesce(max(foo_id),0) + 1, 2,'foo two' from foo