How can we help you today? How can we help you today?

Format SQL - valid but incorrect sql

I found a scenario today where Format SQL will change the SQL statement shown below into valid SQL that runs without error but is wrong. This is a dangerous error because the SQL is changed in a way that results in no UPDATE actually happening.
CREATE TABLE Cars (name VARCHAR(10)
                  ,wheels INT);

CREATE TABLE c (name VARCHAR(10));


UPDATE  r3
SET     r3.wheels = c.wheels
FROM    dbo.Cars r3
CROSS APPLY (SELECT wheels = 4) c;

The UPDATE is changed to
UPDATE  r3
SET     r3.wheels = r3.wheels
FROM    dbo.Cars r3
CROSS APPLY (SELECT wheels = 4) c;
KevinGreiner
0

Comments

5 comments

Sort by
  • Rob C
    Hi Kevin,

    I tried this with the latest version but I couldn't reproduce the behaviour you described. Can you try upgrading to the latest version to see if that fixes things? The URL is here if you need it - ftp://support.red-gate.com/patches/SQLP ... .0.335.exe
    Rob C
    0
  • KevinGreiner
    I produced this code using 6.5.0.335.

    Looking through my original post, I see that I missed two steps. First, execute just the first two statements. Then refresh your suggestions.

    Now you should be able to reformat the UPDATE code shown and see the incorrect result.
    KevinGreiner
    0
  • Rob C
    Hi Kevin,

    I've been able to reproduce the problem and have filed a bug report with reference SP-5246. I don't have a timeframe for a possible fix, but I'll let you know when I hear any information.

    Thanks for letting us know about the bug.
    Rob C
    0
  • Rob C
    Hi Kevin,

    This should now be fixed in the latest beta release.
    Rob C
    0
  • KevinGreiner
    Confirmed that the beta fixes this.
    KevinGreiner
    0

Add comment

Please sign in to leave a comment.