October 16, 2009 at 9:16 am
Hi there,
I understand what the error means, but I'm stumped in this case.
I am working with an application (not mine, so I can't change it), that has a database on each client. Up until now, the clients have all been using MSDE 2000. We started getting an error in the application that the vendor has found doesn't exist when the client uses sql 2005 or 2008. So, they recommend moving to that. I don't like it because it makes no sense to me yet. Here are the details and I'm wondering if someone can she light on why this is happening.
I generalized the query here because the details don't matter:
SELECT T1.Val1, T1.Val2, T2.Val1,
(SELECT T3.Val1 FROM Table3 AS T3 INNER JOIN Table4 AS T4 ON T4.SomeID=T3.SomeID
WHERE T4.SomeID=T1.SomeID AND T3.Val2 = 1) AS SomeName
FROM Table1 AS T1
INNER JOIN Table2 AS T2 ON T2.SomeID = T1.SomeID
WHERE T1.Val3 = 12345
I backup the database and restore it on two other databases: MSDE 2000 and Sql Server 2008. I run the query on both. On the first, I get the "Subquery returned more than 1 value" error and on the second, it runs perfectly well with one record as a result.
So, I take out the subquery and run it with a value for T1.Val2, which comes from the outer query. I get that value by removing the whole "SomeName" subquery and field and running the whole outer query. I get the same one-record result that I get when it runs on Sql 2008. I take the T1.Val2 result (an int) and plug it into the sub query in another window against both DBs. Both return only ONE record. So now I know that the subquery is returning only one record on both and that that error really may not mean what it says.
So, I know that this is pretty theoretical, but what could cause this strange difference? I unfortunately can't change the code in the app, so I really need this to help the vendor help us. 😉
Thanks very much!
PSW
October 16, 2009 at 1:46 pm
I wonder if that works even in 2005!!!!
You may want to try something similar to this: It gives you same output; If, I understood your request correctly!!!!
SELECT T1.Val1, T1.Val2, T2.Val1, t3.val1 as SomeName
FROM Table1 AS T1
INNER JOIN Table2 AS T2 ON T1.SomeID = T2.SomeID
, Table3 as T3
, Table4 as T4
where T4.SomeID=T3.SomeID and T4.SomeID=T1.SomeID AND T3.Val2 = 1
and T1.Val3 = 12345
Try it...
-RP
October 18, 2009 at 7:55 am
Basically it means that you probably have a corrupted record in there somewhere that the app creator didn't anticipate. It is probably expecting 1:1 relationships and you have a 1:M in there somewhere. A few notes:
1. In your outer query join clause, you have T1=T1, never referencing T2 at all. That's bad, but I'm assuming that was a mistake you made when you generalized the query, not a mistake of the query itself. If that was not an error on your part and that's how the query really is, tell the application developer that they have a bad query.
2. I don't see any reason for this to be a correlated subquery. While you may not be able to change the code that your app runs, you can re-write and run it in SSMS to see what your bad piece of data is and attempt to fix the data, or at least explain to the app dev why you need their code changed. I use the term 'bad' to refer to unexpected really. Your data may not actually be corrupt.
SELECT T1.Val1, T1.Val2, T1.Val3, T2.Val1, T3.Val1
FROM Table1 AS T1
INNER JOIN Table2 AS T2 ON T1.SomeID = T2.SomeID -- Fixed Join, you have T1=T1
LEFT JOIN Table4 AS T4 ON T4.SomeID = T1.SomeID
LEFT JOIN Table3 AS T3 ON T4.SomeID = T3.SomeID AND T3.Val2 = 1
WHERE T1.Val3 = 12345
October 19, 2009 at 7:35 am
Thanks guys.
Yes, the error in the sql was just my mistake when generalizing the code.
I really do wish I could just change it myself and I'm about ready to just let this go. It's time to move to a newer version of sql anyway. However, I found one other interesting clue here that might shed some light. With a more careful comparison between 2000 and 2008 (and 2005, by the way), the newer engines still have no issue, but the 2000 engine seems to sometimes return records despite the 'subquery returned more than 1 value...' error. I didn't notice this at first because it's unexpected, but I actually get the correct record returned for sometimes and not others, depending on the filter in the where clause. BUT, I always get the error in 2000, regardless of whether a record is returned. And, the record should always be returned. So, there are, in fact, two malfunctions, as far as I can see.
1. Sometimes the record isn't returned as I would expect it to be.
2. The 'subquery returned....' exception always gets thrown, even though the subquery DOES only return ONE record.
The application with this sql has no problems when the record is returned (most of the time) and it must eat the exception. But obviously, when the record isn't returned, the application complains.
If this doesn't ring any bells, then I'm dropping the matter. The code is not in my control anyway.
Thanks to you both or giving it some thought.
P.
October 19, 2009 at 7:51 am
And by the way. I experimented with just rewriting the query so that the subquery is a left join in the outer query and it works just fine every time. So, it does look as though this subquery is just evil.
Best,
P.
October 20, 2009 at 1:34 am
Skeeve , First What i have understood.
The same database when is restored in SQL 2008 and in MSDE 2000. The same query runs fine in SQL 2008 but not in MSDE 2000.
Could it be because any ANSI settings have changed which results in things like NULL being treated differently. Just my thoughts...
"Keep Trying"
October 20, 2009 at 8:44 am
ChiragNS,
Yes, you are right in your characterization of the problem. Imported DB in the two MSDE 2000 and SQL 2008 Express. Same query. Two different results (one being an error and the other the proper result).
Your thinking about ANSI settings is great and something I hadn't thought of. I experimented by enabling ANSI NULL and ANSI padding and such, but same error. I do think you might be on to something, but I don't know what other settings would change the way the engine is processing things internally. I also tried the 'no count' setting. It could also be that I'm not selecting the right combination of settings. I'll keep playing with it. Until then, if you have any other thoughts, please do share.
Thanks again
October 20, 2009 at 8:50 am
And by the way. I experimented with just rewriting the query so that the subquery is a left join in the outer query and it works just fine every time. So, it does look as though this subquery is just evil.
That's good, but it was also expected. Execution plans can possibly explain why it works on some databases and not on others. What you need to do with that left joined query is examine the data and figure out if any rows in the table could violate the subquery.
Even if you are filtering them out with your WHERE, they could violate the subquery first depending on the execution plan, so you need to look at *all* of the data in the table, not just the data from whatever filter you're currently running.
October 20, 2009 at 12:21 pm
Does this return anything?
SELECT T3.Val1
FROMTable3 AS T3
INNERJOIN Table4 AS T4 ON T4.SomeID=T3.SomeID
WHERET3.Val2 = 1
GROUPBY T3.Val1
HAVING COUNT(*) > 1
and does it work if you change your subquery to SELECT MAX(T3.Val1) ?
October 20, 2009 at 12:52 pm
Hi lemsip,
Thanks for helping. I'm not sure what the results tell you, but here they are.
SELECT T3.Val1
FROMTable3 AS T3
INNERJOIN Table4 AS T4 ON T4.SomeID=T3.SomeID
WHERE T3.Val2 = 1
returns 132 records
SELECT T3.Val1
FROMTable3 AS T3
INNER JOIN Table4 AS T4 ON T4.SomeID=T3.SomeID
WHERE T3.Val2 = 1
GROUP BY T3.Val1
returns 11 records
SELECT T3.Val1
FROMTable3 AS T3
INNERJOIN Table4 AS T4 ON T4.SomeID=T3.SomeID
WHERE T3.Val2 = 1
GROUP BY T3.Val1
HAVING COUNT(*) > 1
also returns 11 records
adding max(T3.Val1) does not change the results
October 20, 2009 at 1:03 pm
I may have misread the subquery join.
Can you find if it is ever possible for the subquery to return multiple rows? even though it doesn't for the example?
October 20, 2009 at 1:59 pm
Try running this.
SELECT T1.Val3, T3.Val2, COUNT(T3.Val1)
FROM Table1 AS T1
INNER JOIN Table2 AS T2 ON T1.SomeID = T2.SomeID -- Fixed Join, you have T1=T1
LEFT JOIN Table4 AS T4 ON T4.SomeID = T1.SomeID
LEFT JOIN Table3 AS T3 ON T4.SomeID = T3.SomeID
GROUP BY T1.Val3, T3.Val2
HAVING COUNT(T3.Val1) > 1
Assuming I did that correctly (and it makes sense after you translate it), it should point out problem rows.
October 20, 2009 at 2:34 pm
Similar to the ansi nulls setting suggestion, you should check the concatenation settings if you are joining (or if your subquery is correlated) based on data types other than int.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
October 21, 2009 at 7:18 am
Gardin,
Thanks for your suggestion. The query you suggest returns no rows because, in fact, there really are no counts greater than one for the subquery. I think consensus around here is that because the vendor is suggesting a move to sql 2008, we should do it. I hate to make decisions based on not enough information, but it seems that this mystery isn't destined to be solved.
I did try the other suggestions regarding db options (ANSI settings, Concatenation settings, etc) but I never found the right combination, if there even is one, to fix this.
Thanks everyone for your help. I actually learned a few things from this, so I don't consider this effort a loss! 😀
October 21, 2009 at 2:01 pm
I *think* the problem stems from the fact the statement MAY return more than one record
rewrite it so it can only EVER return one record and it might be fine
select one, two, three, (select top 1 blah from whatever)
from table
I also found some cases in SQL2005 where the optimizer
refuses to optimize subqueries and I had to rewrite with
derived tables and joins instead (fast but hard to manage)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply