January 27, 2004 at 8:55 am
I have played around with the bit problem discussed in a recent QOD. This is where a bit column being set to 2 gets set to 1 and no error is presented. I think this a bug in the code. Why does it operate this way. I ran some tests.
2 should be fail a domain check for bit just like 5,000 fails below. But it doesn't. An alphabetic character does fail. A bit cannot be assigned an 'A' as below.
Since SQL Server can check other data types for limits, why doesn't it check for BIT?
Here is the code and results.
If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoice]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Invoice]
GO
CREATE TABLE [dbo].[Invoice] (
[InvoiceID] [int] ,
[OrderDt] [datetime] ,
[InvoiceAmt] [money] ,
[PaidFg] [bit]
) ON [PRIMARY]
GO
INSERT INVOICE ([InvoiceID], [InvoiceAmt]) values (1,100)
GO
SELECT [InvoiceID], [OrderDt], [InvoiceAmt], [PaidFg] FROM [SWLTESTER].[dbo].[Invoice]
GO
UPDATE Invoice
SET PaidFg = 2,
InvoiceAmt = (InvoiceAmt * .8)
WHERE InvoiceId = 1
GO
SELECT [InvoiceID], [OrderDt], [InvoiceAmt], [PaidFg] FROM [SWLTESTER].[dbo].[Invoice]
GO
The result here is the answer to the QOD
1 NULL 80.0000 1
Then I made a change
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoice]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Invoice]
GO
CREATE TABLE [dbo].[Invoice] (
[InvoiceID] [int] ,
[OrderDt] [datetime] ,
[InvoiceAmt] [money] ,
[PaidFg] [tinyint]
) ON [PRIMARY]
GO
INSERT INVOICE ([InvoiceID], [InvoiceAmt]) values (1,100)
GO
SELECT [InvoiceID], [OrderDt], [InvoiceAmt], [PaidFg] FROM [SWLTESTER].[dbo].[Invoice]
GO
UPDATE Invoice
SET PaidFg = 2,
InvoiceAmt = (InvoiceAmt * .8)
WHERE InvoiceId = 1
GO
SELECT [InvoiceID], [OrderDt], [InvoiceAmt], [PaidFg] FROM [SWLTESTER].[dbo].[Invoice]
GO
The reults here are
1 NULL 80.0000 2
Another change
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoice]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Invoice]
GO
CREATE TABLE [dbo].[Invoice] (
[InvoiceID] [int] ,
[OrderDt] [datetime] ,
[InvoiceAmt] [money] ,
[PaidFg] [bit]
) ON [PRIMARY]
GO
INSERT INVOICE ([InvoiceID], [InvoiceAmt]) values (1,100)
GO
SELECT [InvoiceID], [OrderDt], [InvoiceAmt], [PaidFg] FROM [SWLTESTER].[dbo].[Invoice]
GO
UPDATE Invoice
SET PaidFg = 'A',
InvoiceAmt = (InvoiceAmt * .8)
WHERE InvoiceId = 1
GO
results are:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'A' to a column of data type bit.
SELECT [InvoiceID], [OrderDt], [InvoiceAmt], [PaidFg] FROM [SWLTESTER].[dbo].[Invoice]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoice]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Invoice]
GO
CREATE TABLE [dbo].[Invoice] (
[InvoiceID] [int] ,
[OrderDt] [datetime] ,
[InvoiceAmt] [money] ,
[PaidFg] [tinyint]
) ON [PRIMARY]
GO
INSERT INVOICE ([InvoiceID], [InvoiceAmt]) values (1,100)
GO
SELECT [InvoiceID], [OrderDt], [InvoiceAmt], [PaidFg] FROM [SWLTESTER].[dbo].[Invoice]
GO
UPDATE Invoice
SET PaidFg = 5000,
InvoiceAmt = (InvoiceAmt * .8)
WHERE InvoiceId = 1
GO
Results here
Server: Msg 220, Level 16, State 2, Line 1
Arithmetic overflow error for data type tinyint, value = 5000.
The statement has been terminated.
SELECT [InvoiceID], [OrderDt], [InvoiceAmt], [PaidFg] FROM [SWLTESTER].[dbo].[Invoice]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoice]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Invoice]
GO
January 27, 2004 at 2:51 pm
Possible explanation: It is common in many languages (including C++ and Perl) to "convert" integers into a true/false value by saying 0 = FALSE, anything else = TRUE. If this same conversion logic is applied in the creation of the bit value in MS SQL, then presumably any numeric not equal to 0 would be true.
SQL Books Online comes right out and says, "Converting to bit promotes any nonzero value to 1."
Defined behavior that is not what you want or expect is not necessarily a bug. As a developer, I have had to face clients, and step them through the logic they signed off on, and show them that this is what they are getting. When they decide that it's not what they wanted, they don't automatically get it changed for free; they're going to have to pay, because it is not a bug, it's a change.
Is bit a valid ANSI-SQL type, does anyone know if that standard describes the appropriate behavior in a case like this?
R David Francis
January 27, 2004 at 4:03 pm
> Is bit a valid ANSI-SQL type?
No.
--Jonathan
January 28, 2004 at 8:05 am
Yes, I would agree that documented outcomes that are not expected are not a bug. It might be nice if a message could warn the poor coder that this is happening.
Definitely falls within the old adage. "Read the Documentation!"
It is interesting that bit is not an ANSI-SQL standard data type, perhaps it should be. I had asked one of our Oracle DBAs and they do not have the bit or Boolean data type. They do have a dynamically changing numeric type that promotes itself depending on the value.
Thanks for the discussion.
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply