SQL 2000 bug repeating data in query column output?

Question

SQL 2000 bug repeating data in query column output?

webrunner

SSC-Dedicated

Points: 31885
More actions
February 20, 2007 at 3:32 pm

#92201

Hello,
I got a report from a user that he runs a query in SQL 2000 wherein three columns repeat data from the first column. So for example, if col1 has a NULL, col2 a 0, and col3 a 4, the query outputs
col1 col2 col3
NULL NULL NULL
instead of
col1 col2 col3
NULL 0 4
The query includes a summary function (MIN), joins 17 tables form 3 database, and has a group by clause with 22 fields. He says that the query works in SQL 2005 without repeating the data.
I will post the SQL if absolutely necessary, but I'd like to avoid that if possible for security reasons. (Or I can change the names of the tables and fields).
What I want to know is if anyone knows offhand whether there is a bug in SQL 2000 involving any of the query components above (summary functions, number of tables/databases in a join, number of fields in group by) that would result in 3 columns repeating the data from the first column.
FYI, I ran select @@version on our server and got the result below:
==============
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
==============
Does the service pack refer to SQL Server or to Windows?
Thanks in advance for any help. If this is not an obvious, known SQL 2000 bug with a patch for it, I can post the query to see if there is a bug in his code. But if the query worked in SQL 2005, then I don't see how it can be a bug in his code.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 1

The service pack notation revers to Windows.... you have SP4 (8.00.2039) on SQL Server.

There is no bug that does this... it's a setting. SQL Server 2005 is setup for CONCATENATE NULL YIELDS NULL being OFF as a default while SQL Server 2000 is setup for it being ON as a default.

My personal belief is that the default setting in SQL Server 2005 is incorrect and violates what I've come to love as proper ANSI NULL settings.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gift Peddie SSC Guru Points: 73570 More actions · Answer 2

(The query includes a summary function (MIN), joins 17 tables form 3 database, and has a group by clause with 22 fields. He says that the query works in SQL 2005 without repeating the data.)

It is not a bug the other nulls come from OUTER JOIN it default to a null, you could use ISNULL and provide the expected value. And you are many service packs short both in OS and SQL Server. I was in a thread here a while back about SQL Server not showing correct results in OUTER JOIN with a View, the user later said service pack four fixed it. Hope this helps.

Kind regards,
Gift Peddie

webrunner SSC-Dedicated Points: 31885 More actions · Answer 3

Thanks for your reply. An earlier person said that the output I got from select @@version does show that our SQL Server is on SP4. (Although perhaps the OS is behind on SPs.)

Can you confirm?

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Gift Peddie SSC Guru Points: 73570 More actions · Answer 4

(Windows NT 5.2 (Build 3790: Service Pack 1)

The OS is in service pack one.

Kind regards,
Gift Peddie

webrunner SSC-Dedicated Points: 31885 More actions · Answer 5

Thanks for your reply. However, the columns in question are not concatenated. They are separately selected columns, but with a certain set of joins, the values of the second and third columns are output with the value of the first column. Should that situation also be handled by the SET CONCAT_NULL_YIELDS_NULL setting?

Also, I tried turning the setting off in SQL 2000 temporarily, as follows, but the columns still came out with the second and third columns having the value of the first column:

SET CONCAT_NULL_YIELDS_NULL OFF;

GO

[query]

SET CONCAT_NULL_YIELDS_NULL ON;

GO

Might this be a slightly different kind of problem, or am I missing something?

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Gift Peddie SSC Guru Points: 73570 More actions · Answer 6

(It is not a bug the other nulls come from OUTER JOIN it default to a null, you could use ISNULL and provide the expected value.)

I did not say anything about concatenation I told you in OUTER JOIN both tables are not equal it default to a mathematical Null, if your query does not address it you get funny result because table positions are fixed. I did not go into details because I assumed you will at least read Microsoft dry BOL(books online) entry about OUTER JOIN. I am not trying to confuse you but why are you joining 17 tables in three databases? I see design problems in both layers of your application.

Kind regards,
Gift Peddie

webrunner SSC-Dedicated Points: 31885 More actions · Answer 7

Thanks for the clarification. I will read more about the OUTER JOIN in BOL. But I see the issue even without an OUTER JOIN, as shown below.

This is a version of the query that shows the problem. I think the issue happens when the MIN() function and GROUP BY are introduced, since I removed the left joins and the problem still happens. The problem doesn't happen without the MIN() and GROUP BY.

SELECT DISTINCT last_name, first_name, a.ccode,a.pcode, a.gpa, a.score_total,

MIN(status_date) as sdate

FROM table1 a INNER JOIN

table2 b ON a.userid = b.userid INNER JOIN

table3 c ON a.userid = c.userid INNER JOIN

table4 d ON b.status = d.id INNER JOIN

table5 e ON b.t_number = e.t_number

WHERE program = 'A'

GROUP BY last_name, first_name, a.ccode,a.pcode_res, a.gpa, a.score_total

I'm not sure I'd write a query with as many joins as the original, but this is a query someone else wrote and is asking us about. I want to find out why the result changes when MIN() is added to the query even if there are no OUTER JOINs used (in SQL 2000 but not in SQL 2005). If there is nothing obviously wrong regarding the syntax above, then I think perhaps there is something wrong or missing in the columns being used for the joins in the original, larger, query.

Also, I tried turning the setting you mentioned off with these statements, and the problem still happened:

SET CONCAT_NULL_YIELDS_NULL OFF;

GO

[query]

SET CONCAT_NULL_YIELDS_NULL ON;

GO

Thanks again for any help.

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Gift Peddie SSC Guru Points: 73570 More actions · Answer 8

Ok now I see what is wrong Steve wrote about it while back in SQL Server all aggregate functions ignore nulls except COUNT(*), so add COUNT(*) to your Min because it will count all the rows. Try the link below for more about Nulls. Hope this helps.

http://www.akadia.com/services/dealing_with_null_values.html

Kind regards,
Gift Peddie

webrunner SSC-Dedicated Points: 31885 More actions · Answer 9

Thanks again for your help on this! I'll read up on the info you've posted.

Also, just to set the record straight, I think it was Jeff Moden who mentioned the SET CONCAT_NULL_YIELDS_NULL setting just before you replied. But I appreciate everyone's comments, since they have introduced me to a few more things I didn't know about before.

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Dave W. SSC Enthusiast Points: 182 More actions · Answer 10

Take out the DISTINCT from the query -- the GROUP BY is collapsing the non-aggregated rows to their distinct values already. Also, I'm assuming that the difference between "a.pcode" in the SELCT and "a.pcode_res" in the GROUP BY is a typo.

As far as the actual problem, could it be a difference in the schema or data used to test between SQL2000 and SQL2005? Is the data exactly the same? Is the schema the same (NULL/NOT NULL, DEFAULTs, etc.)? Are the database, server, and connection settings the same (SET ANSI NULL DEFAULT, etc.)?