Very Computer

Can anyone tell me whether the SQL is suspect, otherwise, perhaps an
MVP could pick this up and report it to Microsoft.  In the meantime, I
guess I'll have to achieve the same thing with different SQL.

How to reproduce the problem:

1. create a table and its primary key (in any database):

CREATE TABLE [dbo].[test_table] (
   [oh_id] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
   [ct_id] [nvarchar] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
   [mto_id] [nvarchar] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
   [term_id] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
   [oh_orderpos] [smallint] NULL ,
   [oh_duedate] [datetime] NULL ,
   [st_id] [smallint] NULL ,
   [oh_selected] [bit] NULL ,
   [oh_uploaded] [bit] NULL ,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test_table] ADD
   CONSTRAINT [PK_test_table] PRIMARY KEY  NONCLUSTERED
   (
      [oh_id],
      [ct_id],
      [mto_id]
   )  ON [PRIMARY]
GO

2. Insert 5 records:

insert test_table values ('10010524', 'CH', 0, NULL, 2, '2003-01-29',
13, 0, 0)
insert test_table values ('10010524', 'FR', 0, NULL, 3, '2003-01-29',
13, 0, 0)
insert test_table values ('10010524', 'AM', 0, NULL, 1, '2003-01-29',
13, 0, 0)
insert test_table values ('10010524', 'MO', 0, NULL, 4, '2003-01-29',
13, 0, 0)
insert test_table values ('10010524', 'MO', 01, NULL, 5, '2003-01-29',
13, 0, 0)
go

3. Run this SELECT query.  It will return 1 record as expected:

SELECT * FROM test_table
WHERE oh_id IN
         (SELECT TOP 1 O.oh_id
          FROM test_table O
          WHERE O.oh_selected = 0 AND
                  O.oh_uploaded = 0 AND
                  (O.st_id = 13 OR O.st_id = 16) AND
                  (oh_duedate = '29 Jan 2003' OR oh_duedate = '30 Jan
2003')
          ORDER BY O.oh_orderpos ASC)
      AND ct_id IN
         (SELECT TOP 1 O.ct_id
          FROM test_table O
          WHERE O.oh_selected = 0 AND
                  O.oh_uploaded = 0 AND
                  (O.st_id = 13 OR O.st_id = 16) AND
                  (oh_duedate = '29 Jan 2003' OR oh_duedate = '30 Jan
2003')
          ORDER BY O.oh_orderpos ASC)
      AND mto_id IN
         (SELECT TOP 1 O.mto_id
          FROM test_table O
          WHERE O.oh_selected = 0 AND
                  O.oh_uploaded = 0 AND
                  (O.st_id = 13 OR O.st_id = 16) AND
                  (oh_duedate = '29 Jan 2003' OR oh_duedate = '30 Jan
2003')
          ORDER BY O.oh_orderpos ASC)

4. Now run this UPDATE statement.  Since it uses the same WHERE clause
as the SELECT above, it should update 1 record, but it actually
updates 4 records. Transactions are used to preserve the data allowing
me to run the test more than once.

begin transaction
GO

UPDATE test_table
   SET term_id = '001', st_id = 14, oh_selected = -1
WHERE oh_id IN
         (SELECT TOP 1 O.oh_id
          FROM test_table O
          WHERE O.oh_selected = 0 AND
                  O.oh_uploaded = 0 AND
                  (O.st_id = 13 OR O.st_id = 16) AND
                  (oh_duedate = '29 Jan 2003' OR oh_duedate = '30 Jan
2003')
          ORDER BY O.oh_orderpos ASC)
      AND ct_id IN
         (SELECT TOP 1 O.ct_id
          FROM test_table O
          WHERE O.oh_selected = 0 AND
                  O.oh_uploaded = 0 AND
                  (O.st_id = 13 OR O.st_id = 16) AND
                  (oh_duedate = '29 Jan 2003' OR oh_duedate = '30 Jan
2003')
          ORDER BY O.oh_orderpos ASC)
      AND mto_id IN
         (SELECT TOP 1 O.mto_id
          FROM test_table O
          WHERE O.oh_selected = 0 AND
                  O.oh_uploaded = 0 AND
                  (O.st_id = 13 OR O.st_id = 16) AND
                  (oh_duedate = '29 Jan 2003' OR oh_duedate = '30 Jan
2003')
          ORDER BY O.oh_orderpos ASC)

GO

rollback transaction
GO

------- update
UPDATE test_table
   SET term_id = '001', st_id = 14, oh_selected = -1
FROM MY_VIEW M INNER JOIN test_table T ON M.oh_id =T.oh_id
AND M.ct_id=T.ct_id AND M.mto_id=T.mto_id

Quote:> A SELECT statement with a WHERE clause that involves 3 sub-selects
> returns one row.  An UPDATE statement with the same WHERE clause
> updates 4 rows.  It seems to me this is a clear bug in SQL 2000 (SP
> 2).  It used to work fine in SQL 7.

> Can anyone tell me whether the SQL is suspect, otherwise, perhaps an
> MVP could pick this up and report it to Microsoft.  In the meantime, I
> guess I'll have to achieve the same thing with different SQL.

> How to reproduce the problem:

> 1. create a table and its primary key (in any database):

> CREATE TABLE [dbo].[test_table] (
>    [oh_id] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
>    [ct_id] [nvarchar] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
>    [mto_id] [nvarchar] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
>    [term_id] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
>    [oh_orderpos] [smallint] NULL ,
>    [oh_duedate] [datetime] NULL ,
>    [st_id] [smallint] NULL ,
>    [oh_selected] [bit] NULL ,
>    [oh_uploaded] [bit] NULL ,
> ) ON [PRIMARY]
> GO

> ALTER TABLE [dbo].[test_table] ADD
>    CONSTRAINT [PK_test_table] PRIMARY KEY  NONCLUSTERED
>    (
>       [oh_id],
>       [ct_id],
>       [mto_id]
>    )  ON [PRIMARY]
> GO

> 2. Insert 5 records:

> insert test_table values ('10010524', 'CH', 0, NULL, 2, '2003-01-29',
> 13, 0, 0)
> insert test_table values ('10010524', 'FR', 0, NULL, 3, '2003-01-29',
> 13, 0, 0)
> insert test_table values ('10010524', 'AM', 0, NULL, 1, '2003-01-29',
> 13, 0, 0)
> insert test_table values ('10010524', 'MO', 0, NULL, 4, '2003-01-29',
> 13, 0, 0)
> insert test_table values ('10010524', 'MO', 01, NULL, 5, '2003-01-29',
> 13, 0, 0)
> go

> 3. Run this SELECT query.  It will return 1 record as expected:

> SELECT * FROM test_table
> WHERE oh_id IN
>          (SELECT TOP 1 O.oh_id
>           FROM test_table O
>           WHERE O.oh_selected = 0 AND
>                   O.oh_uploaded = 0 AND
>                   (O.st_id = 13 OR O.st_id = 16) AND
>                   (oh_duedate = '29 Jan 2003' OR oh_duedate = '30 Jan
> 2003')
>           ORDER BY O.oh_orderpos ASC)
>       AND ct_id IN
>          (SELECT TOP 1 O.ct_id
>           FROM test_table O
>           WHERE O.oh_selected = 0 AND
>                   O.oh_uploaded = 0 AND
>                   (O.st_id = 13 OR O.st_id = 16) AND
>                   (oh_duedate = '29 Jan 2003' OR oh_duedate = '30 Jan
> 2003')
>           ORDER BY O.oh_orderpos ASC)
>       AND mto_id IN
>          (SELECT TOP 1 O.mto_id
>           FROM test_table O
>           WHERE O.oh_selected = 0 AND
>                   O.oh_uploaded = 0 AND
>                   (O.st_id = 13 OR O.st_id = 16) AND
>                   (oh_duedate = '29 Jan 2003' OR oh_duedate = '30 Jan
> 2003')
>           ORDER BY O.oh_orderpos ASC)

> 4. Now run this UPDATE statement.  Since it uses the same WHERE clause
> as the SELECT above, it should update 1 record, but it actually
> updates 4 records. Transactions are used to preserve the data allowing
> me to run the test more than once.

> begin transaction
> GO

> UPDATE test_table
>    SET term_id = '001', st_id = 14, oh_selected = -1
> WHERE oh_id IN
>          (SELECT TOP 1 O.oh_id
>           FROM test_table O
>           WHERE O.oh_selected = 0 AND
>                   O.oh_uploaded = 0 AND
>                   (O.st_id = 13 OR O.st_id = 16) AND
>                   (oh_duedate = '29 Jan 2003' OR oh_duedate = '30 Jan
> 2003')
>           ORDER BY O.oh_orderpos ASC)
>       AND ct_id IN
>          (SELECT TOP 1 O.ct_id
>           FROM test_table O
>           WHERE O.oh_selected = 0 AND
>                   O.oh_uploaded = 0 AND
>                   (O.st_id = 13 OR O.st_id = 16) AND
>                   (oh_duedate = '29 Jan 2003' OR oh_duedate = '30 Jan
> 2003')
>           ORDER BY O.oh_orderpos ASC)
>       AND mto_id IN
>          (SELECT TOP 1 O.mto_id
>           FROM test_table O
>           WHERE O.oh_selected = 0 AND
>                   O.oh_uploaded = 0 AND
>                   (O.st_id = 13 OR O.st_id = 16) AND
>                   (oh_duedate = '29 Jan 2003' OR oh_duedate = '30 Jan
> 2003')
>           ORDER BY O.oh_orderpos ASC)

> GO

> rollback transaction
> GO

Am I the only one to have hit upon this bug in SQL Server?  It seems a
significant hole and has *really* dented my confidence in this database
engine.  I'm sure I'll recover and code around the bug, but why should I
have to??

Graham

CREATE TABLE Test_Table
(oh_id NVARCHAR(20) NOT NULL,
 ct_id NVARCHAR(2) NOT NULL,
 mto_id NVARCHAR(2) NOT NULL,
 term_id NVARCHAR(3),
 oh_orderpos INTEGER,
 oh_duedate DATETIME,
 st_id INTEGER,
 oh_selected INTEGER NULL,
 oh_uploaded INTEGER NULL,
 PRIMARY KEY (oh_id, ct_id, mto_id));

The obvious questions are why did you have BIT datatypes? What did you
expect to happen when you assigned -1 to a bit?  What do all these NULLs
mean (you have more NULL-able columns in one table than I'd expect to
find in an entire schema), no defaults, why are you key columns delcared
as NVARCHAR(n) but restricted to Latin?  Why use short NVARCHAR(n)
instead of CHAR(n)?  Why did you use the non-relational and highly
proprietary SELECT TOP 1 instead MAX()?  

UPDATE Test_Table
   SET term_id = '001', st_id = 14, oh_selected = -1
WHERE oh_id IN
         (SELECT TOP 1 T1.oh_id
            FROM Test_Table AS T1
           WHERE T1.oh_selected = 0
             AND T1.oh_uploaded = 0
             AND T1.st_id IN (13, 16)
             AND oh_duedate IN ('2003-01-29', '2003-01-30')
          ORDER BY T1.oh_orderpos ASC)
  AND ct_id IN
         (SELECT TOP 1 T1.ct_id
            FROM Test_Table AS T1
           WHERE T1.oh_selected = 0
             AND T1.oh_uploaded = 0
             AND T1.st_id IN (13, 16)
             AND oh_duedate IN ('2003-01-29', '2003-01-30')
          ORDER BY T1.oh_orderpos ASC)
  AND mto_id IN
         (SELECT TOP 1 T1.mto_id
            FROM Test_Table AS T1
          WHERE T1.oh_selected = 0
            AND T1.oh_uploaded = 0
            AND T1.st_id IN (13, 16)
            AND oh_duedate IN ('2003-01-29', '2003-01-30')
          ORDER BY T1.oh_orderpos ASC);

This code is so proprietary that there is no appeal to a Standard for
expected behavior.  And you are generating dynamic SQL on the fly to get
it, so all bets are off.  How do you really know what is being executed?

Here is an atempt at a re-write, but I did not take the time to generate
a good set of test data, with all possible combinations of NULLs and
values.  

SELECT T2.*
  FROM Test_Table AS T2
 WHERE NOT EXISTS
       (SELECT *
           FROM Test_Table AS T1
          WHERE T1.oh_selected = 0
            AND T1.oh_uploaded = 0
            AND T1.st_id IN (13, 16)
            AND T1.oh_duedate
                IN ('2003-01-29', '2003-01-30')
            AND (T1.ct_id > T2.ct_id
                 OR T1.mto_id > T2.mto_id
                 OR T1.oh_id > T2.oh_id))
   AND T2.oh_selected = 0
   AND T2.oh_uploaded = 0
   AND T2.st_id IN (13, 16)
   AND oh_duedate IN ('2003-01-29', '2003-01-30');

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!