Marxist Hacker 42's Journal: Strange SQL Server 2014 behavior

I have a Select Statement that returns 4 rows. When used in a stored procedure as input to an Insert Into, though, it was returning five rows. I commented out SET NOCOUNT ON; which is added by the SQL 2014 template. It then returned 4 rows. To test that was what was really going on, I uncommented out SET NOCOUNT ON; and it is now returning 4 rows properly.

I made no other changes.

Anybody else ever run into anything like this??

Haven't seen that here - anything on SO? Repro?

[mbourgon]

Haven't seen it, but if you make a repro script I can test it out over here.

Re:

[Marxist Hacker 42]

Not sure I can duplicate, due to the other strangeness I'm dealing with (It's a cross-database query and the other database doesn't have a primary key, just sets of fields with unique constraints). It may have also been a Select Distinct that I thought I had applied but didn't; the one row that was duplicating did have two in the parent table.

Re:

[Bill Dog]

I'm guessing (/sure hoping!) it could only be one of the following:
1) Phantom read
2) PEBKAC

A bit confused

[apraetor]

Are you saying that you received 5 rows before commenting SETNOCOUNT out, 4 once commented, and still just 4 now that you've un-commented it?

Re:

[Qzukk]

That's what it sounds like he's saying. I'm wondering if something got cached wrong somewhere, then he touched it and it rebuilt the cache, but I know nothing at all about SQL Server, any version.

I'm curious though, if he was inserting the rows into another table, what was the 5th row? all nulls or a duplicate of a previous row or?

Re:

[Marxist Hacker 42]

Duplicate of one of the four, aside from the identity field that was not part of the insert.

Re:

[Marxist Hacker 42]

Yep. What gave me the clue was copying the exact same code *out* of the stored procedure, where I got 4.