Treat empty strings as NULL results in comparison errors

wtaylor

Hi,

I recently enabled the option "Treat empty strings as NULL". The results showed what seemed to be false differences.

For example, when comparing a column which contains a Y or an N value, many rows which have a Y on both sides of the comparison were highlighted as being different. When I double-click on an example of this, the pop-up Viewer window indicates that they are a match (there is no crossed-out red equal sign). Neither value contains trailing whitespace. The problem is pervasive and occurs on other column types where the values can independently be verified as identical.

This example was conducted in a SQL Server 2012 database. Both of the compared tables are in the same database instance. The column is defined as "Varchar(2), null" on both sides.

When I disable "Treat empty strings as NULL" the comparison behaves as expected.

Any help is appreciated!

Brian Donahue

I created a sample database and cannot reproduce the issue, so I'm at a loss to explain what you are seeing.
Table in both databases:

CREATE TABLE [dbo].[Table_1](
	[ID] [int] NOT NULL,
	[data] [varchar](2) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Data:

[use database1]
INSERT INTO table_1 (id,data) VALUES(1, 'Y ')
INSERT INTO table_1 (id,data) VALUES(2, 'Y')
INSERT INTO table_1 (id,data) VALUES(3, 'Y')
INSERT INTO table_1 (id,data) VALUES(4, 'Y ')
USE [database2]
INSERT INTO table_1 (id,data) VALUES(1, 'Y ')
INSERT INTO table_1 (id,data) VALUES(2, 'Y')
INSERT INTO table_1 (id,data) VALUES(3, 'Y ')
INSERT INTO table_1 (id,data) VALUES(4, 'Y')

All I can think of is maybe the collations are different between the databases, but then it should always show the tables as different...

I don't think we could work this out unless we has all of your data compare settings and maybe even a backup of the database.

rvaiyapuri

Hi

I'm using the latest version - 10.4.8.62. I'm also facing the same issue when trying to compare the tables with 'Treat Empty Strings as NULL' option enabled.

I created tables similar to the ones that you have created and I'm getting incorrect results (high-lighting equal values as differences).

Can you please help.

Brian Donahue

Again, we'll probably need to see the databases to reproduce and fix the problem.

Brian Donahue

Treat empty strings as NULL appears to be broken and we have logged a bug SDC-1651.

The problem seems to be in the results display. When you actually use SQL Data Compare to create the script, it does not try to update any of these columns it has erroneously identified as being different.

chetmus

We are currently using version 10.7.0.23 and still seeing the issue. Is there a scheduled release date for the fix to this issue ?

jmeyer

I can confirm this behavior under 10.7.0.23 as well

yagerlin56

String a = null, it will only save a string type in the stack pointer, but the pointer is not just to any string heap.
String a = string. The Empty, in addition to save a string type in the stack pointer, the pointer points to the string data of pile, the Empty string, the string and a = "";Is the same.


_____________________________
http://www.mashgear.com