Professional Web Applications Themes

Bug in Coalesce? - Microsoft SQL / MS SQL Server

Hi all, While answering the post for Paolo below I came across the following unexpected behaviour: CREATE TABLE Product(productID INT, quantity INT, [date] datetime) GO INSERT INTO Product values(1 , 2 , '20030721') INSERT INTO Product values(1 , 3 , '20030720') INSERT INTO Product values(1 , 5 , '20030719') INSERT INTO Product values(2 , 4 , '20030721') INSERT INTO Product values(2 , 5 , '20030720') INSERT INTO Product values(2 , 2 , '20030719') INSERT INTO Product values(3 , 1 , '20030719') GO -- This works correctly SELECT DISTINCT p.productID, (SELECT TOP 1 quantity FROM Product pq1 WHERE p.productID = pq1.productID ...

  1. #1

    Default Bug in Coalesce?

    Hi all,

    While answering the post for Paolo below I came across the following
    unexpected behaviour:
    CREATE TABLE Product(productID INT, quantity INT, [date] datetime)
    GO
    INSERT INTO Product values(1 , 2 , '20030721')
    INSERT INTO Product values(1 , 3 , '20030720')
    INSERT INTO Product values(1 , 5 , '20030719')
    INSERT INTO Product values(2 , 4 , '20030721')
    INSERT INTO Product values(2 , 5 , '20030720')
    INSERT INTO Product values(2 , 2 , '20030719')
    INSERT INTO Product values(3 , 1 , '20030719')

    GO

    -- This works correctly
    SELECT DISTINCT p.productID,
    (SELECT TOP 1 quantity FROM Product pq1 WHERE p.productID =
    pq1.productID
    ORDER BY [date] DESC) AS quantity1,
    COALESCE((SELECT TOP 1 quantity FROM Product pq2 WHERE p.productID =
    pq2.productID
    AND date < (SELECT MAX(date) FROM Product pq3 WHERE pq3.productID =
    p.productID)
    ORDER BY [date] DESC) ,0) AS quantity2
    FROM Product p
    GO
    -- Changing the table alias from p to pq2 in the second subquery raises a
    Internal SQL Server error.
    SELECT DISTINCT p.productID,
    (SELECT TOP 1 quantity FROM Product pq1 WHERE p.productID =
    pq1.productID
    ORDER BY [date] DESC) AS quantity1,
    COALESCE((SELECT TOP 1 quantity FROM Product pq2 WHERE p.productID =
    pq2.productID
    AND date < (SELECT MAX(date) FROM Product pq3 WHERE pq3.productID =
    pq2.productID)
    ORDER BY [date] DESC) ,0) AS quantity2
    FROM Product p
    GO
    -- But using ISNULL instead of COALESCE makes it work again:
    SELECT DISTINCT p.productID,
    (SELECT TOP 1 quantity FROM Product pq1 WHERE p.productID =
    pq1.productID
    ORDER BY [date] DESC) AS quantity1,
    ISNULL((SELECT TOP 1 quantity FROM Product pq2 WHERE p.productID =
    pq2.productID
    AND date < (SELECT MAX(date) FROM Product pq3 WHERE pq3.productID =
    pq2.productID)
    ORDER BY [date] DESC) ,0) AS quantity2
    FROM Product p
    GO

    DROP TABLE product

    Can anyone reproduce this behaviour or have an explanation for it? I am on
    SQL 2000 SP3a, Personal Edition.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.




    Jacco Guest

  2. #2

    Default Re: Bug in Coalesce?

    Dejan,

    I didn't look at the Execution plans before, but it seems like I have to
    change my habit of always using COALESCE in favour of using ISNULL. There is
    a marked difference in performance in this case.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Dejan Sarka" <si> wrote in
    message news:ug$H$phx.gbl... 
    the 
    is [/ref]
    pq3.productID [/ref]
    a [/ref]
    pq3.productID [/ref]
    pq3.productID [/ref]
    on 
    >
    >[/ref]


    Jacco Guest

  3. #3

    Default Re: Bug in Coalesce?

    Btw Dejan,

    Would you want to forward this bug to Microsoft? I believe your channels are
    a lot shorter than mine ;-)

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 
    is 
    > the 
    > is [/ref][/ref]
    = [/ref]
    > pq3.productID [/ref][/ref]
    raises [/ref][/ref]
    = [/ref]
    > pq3.productID [/ref]
    > pq3.productID [/ref][/ref]
    am 
    > >
    > >[/ref]
    >
    >[/ref]


    Jacco Guest

  4. #4

    Default Re: Bug in Coalesce?

    This is a known issue. You want to take a look at this kb for suggested
    workaround.

    http://support.microsoft.com/?id=317527

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 





    oj Guest

  5. #5

    Default Re: Bug in Coalesce?

    yeah. i simply pointed out the acknowledgement on this particular issue.

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Delbert Glass" <com> wrote in message
    news:phx.gbl... 


    oj Guest

  6. #6

    Default Re: Bug in Coalesce?

    Perhaps that's a different bug.

    Their repro does not give me an error.
    (bad repro or bug already fix ?)
    Jacco's repro does give me an error;
    furthermore, the error is at State 21 not State 25.

    (See I was not kidding about the vendor
    having difficulting with aggregrates in sub-queries.)

    Bye,
    Delbert Glass

    "oj" <com> wrote in message
    news:phx.gbl... [/ref]
    pq3.productID [/ref]
    a [/ref]
    pq3.productID [/ref]
    pq3.productID [/ref]
    on 
    >
    >[/ref]


    Delbert Guest

  7. #7

    Default Re: Bug in Coalesce?

    Thanks for the comments everyone, and for pointing out that I should search
    MSDN and the newsgroup archive before I make a post next time ;-)

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 





    Jacco Guest

Similar Threads

  1. #39553 [NEW]: Add coalesce() and coalesce_strict() functions
    By greg at mtechsolutions dot ca in forum PHP Bugs
    Replies: 0
    Last Post: November 19th, 09:36 AM
  2. Using COALESCE for subqueries
    By marty3d in forum MySQL
    Replies: 1
    Last Post: June 15th, 09:02 PM
  3. Coalesce() in Informix SE
    By preetinder dhaliwal in forum Informix
    Replies: 2
    Last Post: July 29th, 07:46 PM
  4. COALESCE with NULL
    By N.V.Dev in forum IBM DB2
    Replies: 1
    Last Post: July 10th, 08:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139