There's an old joke that goes, "Doctor, doctor. It hurts when I do this" as a man swings his arm up and down, to which the doctor sagely responds, "Don't do that." When presented with this little mystery by a co-worker, my response was, "Don't do that." But then I became very concerned, one could even say, obsessed, with why this particular query "hurt." Fair warning, I couldn't find an answer beyond the Microsoft statement that "In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way."

We have two tables, represented for this article by these simple scripts:

CREATE TABLE [IIAFeedTransaction] (
[IIATransactionId] [uniqueidentifier] NOT NULL ,
[TransactionStatusId] [smallint] NOT NULL ,
)
GO

and

CREATE TABLE [RATSIIAFeedTransaction] (
[RATS_FILENAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATE_ENTERED] [datetime] NULL 
)
GO

The data that goes into the tables is also quite simple:

INSERT INTO IIAFeedTransaction
VALUES ('{365A0FD8-5042-4297-A082-8F5B11450AF4}',1)
INSERT INTO IIAFeedTransaction
VALUES ('{16706611-C94D-4FBC-8F4E-9077C3B9E697}',1)
INSERT INTO IIAFeedTransaction
VALUES ('{55DD6703-9693-45E2-A339-987066EA2864}',2)
INSERT INTO IIAFeedTransaction
VALUES ('{F4849137-6454-46E5-9811-A6985A189249}',1)
INSERT INTO IIAFeedTransaction
VALUES ('{9B33A776-408B-4928-AE2A-0FF43995DE12}',1)
INSERT INTO IIAFeedTransaction
VALUES ('{8EEA51CE-D87A-4F76-B9C1-7AD11532D444}',0)
INSERT INTO IIAFeedTransaction
VALUES ('{5D31FD56-7C0B-408B-83A5-864B7BD35ADC}',1)
INSERT INTO IIAFeedTransaction
VALUES ('{A721BD38-9C76-4C3A-A7C5-9CC0227F90F4}',0)
GO
INSERT INTO RATSIIAFeedTransaction
VALUES ('CISF-{260021}-{365A0FD8-5042-4297-A082-8F5B11450AF4}-{7A050246-59F3-4E72-BFD2-40EB183B7D3C}',GETDATE())         
INSERT INTO RATSIIAFeedTransaction
VALUES ('CISF-{260346}-{16706611-C94D-4FBC-8F4E-9077C3B9E697}-{73916523-EC71-4989-BE99-1EDB507D67DF}',GETDATE())         
INSERT INTO RATSIIAFeedTransaction
VALUES ('RA-1-{259590}-{55DD6703-9693-45E2-A339-987066EA2864}-{EF41CFCE-20BA-4D2D-A4C8-121BFC7DAEE3}',GETDATE())         
INSERT INTO RATSIIAFeedTransaction
VALUES ('RA-2-{219960}-{F4849137-6454-46E5-9811-A6985A189249}-{F7267BD6-62CB-4379-8533-C413DD5EA402}',GETDATE())         
GO

My co-worker is a very strong DBA who works in both Oracle and SQL Server, whereas I only work in SQL Server. He has been writing Oracle queries as often as he writes SQL Server queries, so he tends to use the old style join syntax of:

SELECT ... 
FROM Table1 t1, Table2 t2
WHERE t1.Id *= t2.Id

He was working on a query to determine records missing between the two tables outlined above. In order to do that, he wrote this query:

SELECT S.IIATransactionId 
,substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36) AS OracleTransactionId
FROM iiafeedtransaction S,
ratsiiafeedtransaction o
WHERE S.IIATransactionId *= substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36)
AND  o.Rats_filename IS NOT NULL
ORDER BY S.IIATransactionId 

The results that came back were not what he expected, at all:

IIATransactionId                     OracleTransactionId                  
------------------------------------ ------------------------------------ 
9B33A776-408B-4928-AE2A-0FF43995DE12 NULL
8EEA51CE-D87A-4F76-B9C1-7AD11532D444 NULL
5D31FD56-7C0B-408B-83A5-864B7BD35ADC NULL
365A0FD8-5042-4297-A082-8F5B11450AF4 365A0FD8-5042-4297-A082-8F5B11450AF4
16706611-C94D-4FBC-8F4E-9077C3B9E697 16706611-C94D-4FBC-8F4E-9077C3B9E697
55DD6703-9693-45E2-A339-987066EA2864 55DD6703-9693-45E2-A339-987066EA2864
A721BD38-9C76-4C3A-A7C5-9CC0227F90F4 NULL
F4849137-6454-46E5-9811-A6985A189249 F4849137-6454-46E5-9811-A6985A189249

In theory, with the left outer join and the 'IS NOT NULL' in the WHERE clause, he should have only seen four rows come back, not eight. He was confused. So, he tried switching the 'IS NOT NULL' to 'IS NULL' in order to see what comes back that way. The results did not settle his mind at all:

IIATransactionId                     OracleTransactionId                  
------------------------------------ ------------------------------------ 
9B33A776-408B-4928-AE2A-0FF43995DE12 NULL
8EEA51CE-D87A-4F76-B9C1-7AD11532D444 NULL
5D31FD56-7C0B-408B-83A5-864B7BD35ADC NULL
365A0FD8-5042-4297-A082-8F5B11450AF4 NULL
16706611-C94D-4FBC-8F4E-9077C3B9E697 NULL
55DD6703-9693-45E2-A339-987066EA2864 NULL
A721BD38-9C76-4C3A-A7C5-9CC0227F90F4 NULL
F4849137-6454-46E5-9811-A6985A189249 NULL

Now he not only doesn't have four rows instead of eight, but with the second column showing NULL values, but he's completely lost the data that was there and now has nothing but NULL values. At this point he called me over to see what the problem was.

Since I've pretty much exclusively in SQL Server for the last eight years, I long ago learned to write my syntax following the ANSI-92 standards. When I walked through what he had discovered, my first thought was to simply verify the behavior by changing the query syntax. I re-wrote the procedure to this:

SELECT S.IIATransactionId 
,substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36) AS OracleTransactionId
FROM iiafeedtransaction S
LEFT OUTER JOIN ratsiiafeedtransaction o
ON S.IIATransactionId = substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36)
WHERE o.Rats_filename IS  NULL
ORDER BY S.IIATransactionId 

Which, I was a little surprised to see, worked without flaw and returned the appropriate data set:

IIATransactionId                     OracleTransactionId                  
------------------------------------ ------------------------------------ 
9B33A776-408B-4928-AE2A-0FF43995DE12 NULL
8EEA51CE-D87A-4F76-B9C1-7AD11532D444 NULL
5D31FD56-7C0B-408B-83A5-864B7BD35ADC NULL
A721BD38-9C76-4C3A-A7C5-9CC0227F90F4 NULL

Now, it begins to get good. We messed with this for quite a while. Rearranging the WHERE clause on the queries that used the old syntax, putting in parenthesis to try to change the query plan that the engine created. Nothing worked. Then we started looking at the query plan. Here, things were really weird. The first query showed the join between the tables, but, for some reason, just completely ignored the WHERE clause and didn't filter the data in any way. Additionally, it added a transform to set all the values for the second field to NULL.

Running the query that simply changed the WHERE clause from 'NOT IN' to 'IN', the join actually occurs, which corresponds to the fact that the data gets returned.

Neither of which corresponds to the accurate query plan represented by the ANSI syntax that shows both the join and a filter to remove the unwanted records.

Then, I had an epiphany, a false one as it turned out. It's the substring function! So, I added a column to the table of datatype UNIQUEIDENTIFIER and updated all the columns. This allowed me write a much more simplified query:

SELECT S.IIATransactionId 
,o.Fixed
FROM iiafeedtransaction S,
ratsiiafeedtransaction o
WHERE S.IIATransactionId *= o.fixed
AND  o.fixed IS  NULL
ORDER BY S.IIATransactionId 

Which promptly returned exactly the same data.

We went on to experiment with changes to the connection settings, database settings, server settings. Nothing made a difference. We ran the same queries in Oracle and got the correct data sets back. I showed the queries to our in house Microsoft consultant who said, "Old syntax? Don't do that." At that point, having come full circle, I stopped worrying about the problem and decided to write it up as an article to see if anyone else has any insight.