January 17, 2006 at 9:30 pm
PLEASE LOOK AT THE FOLLOWING SAMPLE DATA, THE PROBLEM QUERY (OUTER JOIN) IS MENTIONED AT THE END, WHICH IS GIVING DIFFERENT RESULT ON DIFFERENT SERVERS (I CHECKED BUT COULDN'T FIND ANY OBVIOUS DIFFERENT ON DIFFERENT SERVERS, THEY ALL HAVE SAME VERSION/SP/OS etc. FOR SQL SERVER)
/*
-- IF REQUIRED PLEASE DROP THE SAMPLE TABLES AND VIEW THAT WILL BE CREATED FOR SAMPLE DATA
DROP TABLE test_table1
DROP TABLE test_table2
DROP VIEW test_view1
*/
-- CREATE TWO SAMPLE TABLES
SELECT 100 AS test_id INTO test_table1
SELECT 200 AS test_id INTO test_table2
-- CREATE A VIEW ON ONE OF THE ABOVE SAMPLE TABLE
CREATE VIEW test_view1 AS SELECT test_id, 'T' as some_test_value FROM test_table2
--- DO A SELECT ON FIRST SAMPLE TABLE AND EVERYTHING IS OK HERE, THE DATA SHOULD LOOK LIKE
SELECT * FROM test_table1
test_id
-----------
100
-- DO A SELECT ON VIEW AND EVERYTHING IS OK UNTIL HERE, DATA SHOULD LOOK LIKE FOLLOWING
SELECT * FROM test_view1
test_id some_test_value
----------- ---------------
200 T
-- NOW WHEN I RUN A QUERY LIKE FOLLOWING IT GIVES CORRECT RESULT ON ONE SERVER (IT GIVES NULL IN SECOND COLUMN)
SELECT test_table1.test_id,test_view1.some_test_value
FROM test_table1
LEFT OUTER JOIN test_view1 ON test_table1.test_id = test_view1.test_id
test_id some_test_value
----------- ---------------
100 NULL
-- BUT WHEN I RUN ABOVE QUERY ON OTHER SERVERS (I TRIED ON 3 DIFFERENT SERVERS) EACH TIME IT GIVES "Y" IN THE
--SECOND COLUMN, WHICH I THINK IS INCORRECT , IT SHOULD BE "NULL" FOR SECOND COLUMN
test_id some_test_value
----------- ---------------
100 T
================
ANY IDEA WHY IS IT SHOWING "Y" FOR SECOND COLUMN INSTEAD OF "NULL"
THANKS FOR ALL THE HELP
January 17, 2006 at 9:34 pm
Sorry, while asking question I should have said "T" instead of "Y"
so the correct question, based on the sample data, should be:
WHY IS IT SHOWING "T" FOR SECOND COLUMN INSTEAD OF "NULL"
Thanks much
January 17, 2006 at 9:39 pm
What if you run
SELECT * FROM test_view1
test_id some_test_value
on that second server?
_____________
Code for TallyGenerator
January 17, 2006 at 9:43 pm
On SELECT from view its the same result on all serves (as mentioned above)
SELECT * FROM test_view1
test_id some_test_value
----------- ---------------
200 T
January 17, 2006 at 10:03 pm
while checking the servers again I think the servers that have latest SP4 are giving "T" in the last column while others (that do not have SP4) are giving "NULL"
Although I am not sure if that only is the reason for this mismatch but please confirm if someone knows abou this for sure.
Sorry for saying earlier that I have checked and all servers have same SP.
Thanks
January 17, 2006 at 10:14 pm
for some reason when I am doing a "select @@version" it's giving same last line on both SP3 and SP4 servers
The last line is shown like "Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)"
But first line shows it different 8.00.818(for SP3) and 8.00.2039 (for SP4)
It shows correct SP number when I do properties check in Enterprise Manager
That's why I got confused while menioning about the version in the above example.
But anyway this is a different issue and I won't divert the real issue with this "SELECT @@version" problem.
Thanks
January 17, 2006 at 10:18 pm
100 should not be equal 200 in any version.
try to display all values:
SELECT test_table1.test_id,test_view1.test_id , test_view1.some_test_value
FROM test_table1
LEFT OUTER JOIN test_view1 ON test_table1.test_id = test_view1.test_id
_____________
Code for TallyGenerator
January 17, 2006 at 10:28 pm
Are these real views ? Or are you using sample code to illustrate the general concept ?
On your systems, do any of your views contain SELECT * <- emphasis on the asterix
If yes, on the systems that give the wrong result, have the underlying tables or views been modified without modifying the overlying view ?
One very good reason why not to use "SELECT *" in views. If you have a view containing "SELECT *" and modify the objects the view references and add/remove columns on those objects, you can get strange results like you're experiencing.
January 17, 2006 at 10:30 pm
I totally agree with you Sergie, that 100 should not be equal to 200 in any version.
here are the results for the above query that you asked me to ckeck (they are different on both servers)
Just to differentiate This is result on SP4 server (although SP may not be the actual cause)
test_id test_id some_test_value
----------- ----------- ---------------
100 NULL T
This is on SP3 server
test_id test_id some_test_value
----------- ----------- ---------------
100 NULL NULL
January 17, 2006 at 10:38 pm
PW,
If you will see the view create script it's not using "SELECT *".
The sample data that I mentioned here is simplified form (although the problem is same for both actual and sample date) for the actual tables and views but even this exact same code is giving different results on different servers.
Thanks
January 17, 2006 at 10:44 pm
>>If you will see the view create script it's not using "SELECT *".
Yep, saw that. Just some people post `generalised` code to prevent posting confidential information to the forums, so wasn`t sure if this was the actual code causing the problem.
What happens if you use full 3-part naming on all objects involved:
SELECT t1.test_id, v1.some_test_value
FROM DBName.ObjectOwner.test_table1 As t1
LEFT OUTER JOIN DBName.ObjectOwner.test_view1 As v1
ON t1.test_id = v1.test_id
January 18, 2006 at 6:50 am
Someone who has a latest SP4 insalled on your servers, Could you please run the above mentioned query and confirm what result are you getting.
If you are NOT getting the same incorrect result as I am on a SP4 server ("T" for some_test_value) then I can at least discard this possibility and start looking for something else.
Of course if someone has an asnwer for my problem that will be even better.
Thanks in advance for all the help.
January 18, 2006 at 7:20 am
I get the same incorrect result on SP4. I see it referred to as a known bug in the SQL Server newsgroups at Microsoft:
January 18, 2006 at 8:37 am
Someone who has SP4 installed on your server, Could you please run the above mentioned query on your server to see what result are you getting?
If you are not getting "T" for "some_test_value" (and getting NULL instead) then that will at least discard one possibility and tell us that this is not an issue because of SP4.
Of course if someone has an answer for the issue that is even better.
Thanks for all the help.
January 18, 2006 at 9:22 am
I get the same incorrect result on SP4. I see it referred to as a known bug in the SQL Server newsgroups at Microsoft:
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply