Options

Scripts with CAST are treated like they should be CONVERT

heck4heck4 Posts: 5 New member
in SQL Compare
As an example, I have this script:
<div>CREATE TABLE [dbo].[tblCastConvertTest]<br>(<br>&nbsp;&nbsp;&nbsp;&nbsp; [Id]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL IDENTITY(1,1)<br>&nbsp;&nbsp;&nbsp; ,[IsIncluded]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BIT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL DEFAULT 1<br>&nbsp;&nbsp;&nbsp; ,[IsExcluded]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AS&nbsp; CASE<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN [IsIncluded]=0 THEN CAST(1 AS BIT)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE CAST(0 AS BIT)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END<br>&nbsp;&nbsp;&nbsp; ,CONSTRAINT [PK_tblCastConvertTest] PRIMARY KEY CLUSTERED ([Id])<br>)<br>GO<br></div>
After I execute it on the database and run a comparison, its showing a false positive difference:


Any ideas on why this is happening?
Tagged:
· Share on Twitter

Answers

  • Options
    Mark RMark R Posts: 49 Silver 1
    Hi - what are you comparing to get that diff? Scripts folder to database?
    Software developer
    Redgate Software
    · Share on Twitter
  • Options
    Tianjiao_LiTianjiao_Li Posts: 684 Rose Gold 5
    @heck4

    Following @Mark R 's investigation, we've reproduced  the issue and logged as it as SC-10390 in our internal bug tracking system. Thanks for reporting it! Please keep an eye on the release note.

    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
    · Share on Twitter
  • Options
    heck4heck4 Posts: 5 New member
    Yes you are correct Mark. Scripts folder to database. We treat our source control as the system of record.
    · Share on Twitter
  • Options
    heck4heck4 Posts: 5 New member
    A few other things that may help:

    • SQL Compare 10 does not exhibit this bug, and treats the two different syntaxes as interchangeable.
    • I've read that CAST is implemented internally within SQL as an alias to CONVERT.
    • Using SSMS 17.7, when I use the above script to create the table, and then re-script it out using SSMS it uses CONVERT, getting rid of my original CAST syntax.
    Thanks. Feel free to close this ticket.
    · Share on Twitter
  • Options
    Tianjiao_LiTianjiao_Li Posts: 684 Rose Gold 5
    Thanks! I've put a note in the bug report.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
    · Share on Twitter
  • Options
    Tianjiao_LiTianjiao_Li Posts: 684 Rose Gold 5
    @heck4

    The dev team has looked into this issue. To be fair, it's not an error in SQL Compare. The differences are real, and we're reporting them as such. As you said, it turns out that SQL Server is basically rewriting the CAST operation to a CONVERT operation under the covers.

    You should be able to workaround it by using SQL Server's preferred CONVERT syntax instead of     CAST.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
    · Share on Twitter
Sign In or Register to comment.