November 3, 2008 at 5:39 am
Hi all,
I have encountered an unusual problem with some data I maintain and wondered if anybody here had run into similar problems and knew the best way to solve this.
Basically, the problem is that two extremely simple queries are returning different results based on whether or not a particular column is included in the select list.
So for example:
select col1, col2, col3, password
from table1
where col4 = 607
will return the same rows but displays different values for col1, col2 and col3 than the following:
select col1, col2, col3
from table1
where col4 = 607
I know this doesn't seem possible (to me) but it's almost as though including the password column is "corrupting" how the other data is displayed although it isn't actually changing anything in the database.
Does this make any sense to anyone?
Thanks,
Dan
November 3, 2008 at 5:56 am
Are you saying the data is the same, but mixed? Or is it totally different data?
Have you looked at the execution plan?
If the data is the same but in a different order the optimizer may have choosen an index which caused sorted (or unsorted ) data
Wilfred
The best things in life are the simple things
November 3, 2008 at 6:04 am
Hi Wilfred, thanks for your reply.
No, I'm not talking about the order in which the rows are returned. The actual data being displayed is different so even if only a single row were returned (there are actually 24) the queries would still be displaying different values in certain columns even though it's the same row!
November 3, 2008 at 6:23 am
Speaking from a SQL 2000 perspective (I know which forum this is), I saw this once before. We had two clients at different version levels of the client tool (ODBC drivers in this case) that returned different result sets for the same query. It took us nearly two weeks to figure that one out. Maybe this applies to your situtation?
-- You can't be late until you show up.
November 3, 2008 at 6:52 am
What are the data types? Is this 1 row only with 607 for Col4?
November 3, 2008 at 6:54 am
I thought it would clear things up if I copied the queries as they are (apart from that I've added the "top 3" and order by clause to simplify things) along with the output I'm getting:
select top 3 id, role, broker, brokername, brokerreference
from [User]
where broker = 607
order by Id
idrolebrokerbrokernamebrokerreference
3341607HOF80034
4151607HOF80034
6261607HOF80034
select top 3 id, password, role, broker, brokername, brokerreference
from [User]
where broker = 607
order by Id
idpasswordrolebrokerbrokernamebrokerreference
334(string)1607HOF80034
415(string)(string)1607HOF
626(string)(string)1607HOF
* In the second query above I've replaced the crazy long strings of unpronouncable characters (i.e. "#Ãu‚Û㆟E¹¿@H ó™") with the text "(string)" for ease of reading
It looks as though the password column doesn't fit into a single column and is taking up the next column as well, forcing everything along by one column in the second query. I'm fairly sure this shouldn't happen!
Hopefully this clears things up a bit!
November 3, 2008 at 7:53 am
What happens if you put the password column at the end of the select list instead of in the middle?
I don't think that this will relate, but if you have a view with select *, and then the table definition changes, you can get columns shifted around if you don't recompile the view. Is there any chance of something like this going on?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 3, 2008 at 8:05 am
If I put the password column at the end of the select list then everything appears to be normal. I've already suggested this to the developer who asked me to look at this problem for her but since she's not sure whether or not she needs to include the password column then this is a good solution for the time being.
I don't think this is down to a view which hasn't been recompiled since the query runs directly against the table.
I just wondered if this was anything that anyone else had noticed before and thought some of you might be interested to hear about it!
Dan
November 3, 2008 at 8:17 am
First: Are you displaying you results in Grid mode or Text mode? If Text, then switch to Grid and try again.
Secondly, check to make sure that your [User] table is not a View. If it is then recompile it (and any underlying Views) and try again. As WayneS suggested, this could cause this problem.
Thirdly, are you just executing these two queries in a Query window directly, or are you using a stored procedure for the second one? If the later, then recompile the stored procedure and try again.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 3, 2008 at 8:22 am
Hi rbarryyoung, thanks for your suggestions.
The table is definitely a table rather than a view and I was querying it directly rather than via any stored procedures or other code. The results were displayed in grid view but I copied them and pasted them out into the text box here.
Thanks,
Dan
November 3, 2008 at 8:36 am
What version of SQL Server? And what is the definition of the [password] column?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 3, 2008 at 8:41 am
Hi,
It's SQL Server 2000 Enterprise Edition SP4 and password is a varchar(128).
Thanks!
November 3, 2008 at 9:04 am
OK, that makes more sense then. I have on very rare occasion seen things like this in SQL 2000, but never in SQL 2005 (note, this is a SQL 2005 forum). I have always assumed that these were SQL Server 2000 bugs, but never confirmed it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 3, 2008 at 9:29 am
Yes, I apologise if you feel like I've wasted your time by posting in the wrong forum but I didn't realise this was 2005 only until too late.
Thanks for your help!
November 4, 2008 at 7:57 am
I'm assuming that the password column is an encrypted column?
If so, could you please send the table definitions, so that
I can recreate the problem here?
Just reply with your table defs pasted in.??
danchard (11/3/2008)
I thought it would clear things up if I copied the queries as they are (apart from that I've added the "top 3" and order by clause to simplify things) along with the output I'm getting:select top 3 id, role, broker, brokername, brokerreference
from [User]
where broker = 607
order by Id
idrolebrokerbrokernamebrokerreference
3341607HOF80034
4151607HOF80034
6261607HOF80034
select top 3 id, password, role, broker, brokername, brokerreference
from [User]
where broker = 607
order by Id
idpasswordrolebrokerbrokernamebrokerreference
334(string)1607HOF80034
415(string)(string)1607HOF
626(string)(string)1607HOF
* In the second query above I've replaced the crazy long strings of unpronouncable characters (i.e. "#Ãu‚Û㆟E¹¿@H ó™") with the text "(string)" for ease of reading
It looks as though the password column doesn't fit into a single column and is taking up the next column as well, forcing everything along by one column in the second query. I'm fairly sure this shouldn't happen!
Hopefully this clears things up a bit!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply