Select Query Weirdness

Question

Select Query Weirdness

five_ten_fiftyfold

Ten Centuries

Points: 1164
More actions
July 5, 2019 at 7:48 am

#3660129
Hi,
I've been given a spreadsheet containing a list of identity values that our marketing team need updating. I absentmindedly copied the column and pasted it into an online delimiter so I could paste this into management studio to check how many of the rows I'd be updating.
The reason I say I absentmindedly pasted the info is that I forget to remove the title from the column, when I came to run the query it gave me an, erm, unexpected result set.
This is a (heavily) truncated version of the script I ran.
```
SELECT * FROM MyTable AS MT
WHERE MT.CustID IN (CustID,112946,124446,670699,179255,726549,186510,186438);
```
So, with the CustID in the 'IN' selection the query returned every row in the table. The column is an Integer so I would have expected the query to fail. I can only something is getting messed up by an implicit conversion.
Can anyone explain what's happening here?
This is a high-class Bureau-de-Change.

Viewing 9 posts - 1 through 8 (of 8 total)

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

five_ten_fiftyfold Ten Centuries Points: 1164 More actions · Answer 1

I think I'm being silly here, I'm combining the column name with explicitly specified values, I didn't think that this was valid syntax. Surprisingly, to me anyway, it seems like it is.

This is a high-class Bureau-de-Change.

@Taps SSC Eights! Points: 879 More actions · Answer 2

Don't have an answer to this - but learned something new today. I would have expected it to fail as well but tested it out and it works !

PB_BI SSCoach Points: 17463 More actions · Answer 3

It's because CustID is a column name. Essentially your where clause is doing 1=1.

The following will fail with an "Invalid column name" error:

DROP TABLE IF EXISTS #noddy

CREATE TABLE #noddy
(NoddyIDINT IDENTITY(1,1),
 NoddyStringVARCHAR(20),
 NoddyUniStringNVARCHAR(20),
 NoddyIntINT)

INSERT INTO #noddy (NoddyString, NoddyUniString, NoddyInt)

VALUES ('Five', 'Ten Fiftyfold', 1900),
('From', 'The Flagstones', 1901),
('Hither', 'to', 1902),
('Musette', 'and Drums', 1903)

SELECT
*
FROM 
#noddy
WHERE 
NoddyInt IN (1900, 1903, Hither)

But this will succeed and return all rows:

SELECT
*
FROM 
#noddy
WHERE 
NoddyInt IN (1900, 1903, NoddyInt)

I'm on LinkedIn

Phil Parkin SSC Guru Points: 246959 More actions · Answer 4

You must work in Toyland.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

five_ten_fiftyfold Ten Centuries Points: 1164 More actions · Answer 5

Can speak for anyone else, but this place feels like a circus sometimes.

This is a high-class Bureau-de-Change.

PB_BI SSCoach Points: 17463 More actions · Answer 6

Phil Parkin wrote:

You must work in Toyland.

Where others use variations of "foobar" for things they can't think of a name for, I use variations of "noddy". You're not the first who's commented on it. I have no idea where it came from and I'm aware that it makes no sense linguistically but I find, as with most things, I just don't care =D

I'm on LinkedIn

Phil Parkin SSC Guru Points: 246959 More actions · Answer 7

PB_BI wrote:

Phil Parkin wrote:
You must work in Toyland.
Where others use variations of "foobar" for things they can't think of a name for, I use variations of "noddy". You're not the first who's commented on it. I have no idea where it came from and I'm aware that it makes no sense linguistically but I find, as with most things, I just don't care =D

I'm old enough to know exactly where it came from & wholeheartedly approve. My temp tables are usually called #crap. Must do better.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

Phil Parkin wrote:

PB_BI wrote:
Phil Parkin wrote:
You must work in Toyland.
Where others use variations of "foobar" for things they can't think of a name for, I use variations of "noddy". You're not the first who's commented on it. I have no idea where it came from and I'm aware that it makes no sense linguistically but I find, as with most things, I just don't care =D
I'm old enough to know exactly where it came from & wholeheartedly approve. My temp tables are usually called #crap. Must do better.

My favorite temp table is #MyHead.

--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)