How do I map true/false/unknown to -1/0/null without repetition?

Question

I am currently working on a tool to help my users port their SQL code to SQL-Server 2005. For this purpose, I parse the SQL into a syntax tree, analyze it for constructs which need attentions, modify it and transform it back into T-SQL.

On thing that I want to support, is the "bools are values too" semantics of other RDBMS. For example, MS-Access allows me to write select A.x and A.y as r from A, which is impossible in T-SQL because:

1. Columns can't have boolean type (column values can't be and'ed)
2. Logical predicates can not be used where expressions are expected.

Therefore, my transformation routine converts the above statement into this:

select case 
  when (A.x<>0) and (A.y<>0)
   then -1 
  when not((A.x<>0) and (A.y<>0)) 
   then 0 
  else 
   null 
  end as r 
 from A;

Which works, but is annoying, because I have to duplicate the logical expression (which can be very complex or contain subqueries etc.) in order to distinguish between true, false and unknown - the latter shall map to null. So I wonder if the T-SQL pro's here know a better way to achieve this?

UPDATE: I would like to point out, that solutions which try to keep the operands in the integer domain have to take into account, that some operands may be logical expressions in the first place. This means that a efficient solution to convert a bool to a value is stil required. For example:

select A.x and exists (select * from B where B.y=A.y) from A;

Answer 1

I don't think there's a good answer, really it's a limitation of TSQL.

You could create a UDF for each boolean expression you need

CREATE FUNCTION AndIntInt
(
    @x as int,@y as int
)
RETURNS int
AS
BEGIN

    if (@x<>0) and (@y<>0)
        return -1
    if not((@x<>0) and (@y<>0)) 
        return  0 
    return null
END

used via

select AndIntInt(A.x,A.y) as r from A

Answer 2

Boolean handling

Access seems to use the logic that given 2 booleans

• Both have to be true to return true
• Either being false returns false (regardless of nulls)
• Otherwise return null

I'm not sure if this is how other DBMS (Oracle, DB2, PostgreSQL) deal with bool+null, but this answer is based on the Access determination (MySQL and SQLite agree). The table of outcomes is presented below.

X      Y      A.X AND B.Y
0      0      0
0      -1     0
0      (null) 0
-1     0      0
-1     -1     -1
-1     (null) (null)
(null) 0      0
(null) -1     (null)
(null) (null) (null)

SQL Server helper 1: function for boolean from any "single value"

In SQL Server in general, this function will fill the gap for the missing any value as boolean functionality. It returns a ternary result, either 1/0/null - 1 and 0 being the SQL Server equivalent of true/false (without actually being boolean).

drop function dbo.BoolFromAny
GO
create function dbo.BoolFromAny(@v varchar(max)) returns bit as
begin
return (case
    when @v is null then null
    when isnumeric(@v) = 1 and @v like '[0-9]%' and (@v * 1.0 = 0) then 0
    else 1 end)
end
GO

Note: taking Access as a starting point, only the numeric value 0 evaluates to FALSE This uses some SQL Server tricks

1. everything is convertible to varchar. Therefore only one function taking varchar input is required.
2. isnumeric is not comprehensive, '.' returns 1 for isnumeric but will fail at @v * 1.0, so an explicit test for LIKE [0-9]%`` is required to "fix" isnumeric.
3. @v * 1.0 is required to overcome some arithmetic issues. If you pass the string "1" into the function without *1.0, it will bomb

Now we can test the function.

select dbo.BoolFromAny('abc')
select dbo.BoolFromAny(1)
select dbo.BoolFromAny(0)  -- the only false
select dbo.BoolFromAny(0.1)
select dbo.BoolFromAny(-1)
select dbo.BoolFromAny('')
select dbo.BoolFromAny('.')
select dbo.BoolFromAny(null)  -- the only null

You can now safely use it in a query against ANY SINGLE COLUMN, such as

SELECT dbo.BoolFromAny(X) = 1

SQL Server helper 2: function to return result of BOOL AND BOOL

Now the next part is creating the same truth table in SQL Server. This query shows you how two bit columns interact and the simple CASE statement to produce the same table as Access and your more complicated one.

select a.a, b.a,
  case
  when a.a = 0 or b.a = 0 then 0
  when a.a = b.a then 1
  end
from
(select 1 A union all select 0 union all select null) a,
(select 1 A union all select 0 union all select null) b
order by a.a, b.a

This is easily expressed as a function

create function dbo.BoolFromBits(@a bit, @b bit) returns bit as
begin
  return case
    when @a = 0 or @b = 0 then 0
    when @a = @b then 1
    end
end

SQL Server conversion of other expressions (not of a single value)

Due to lack of support for bit-from-boolean conversion, expressions that are already [true/false/null] in SQL Server require repetition in a CASE statement.

An example is a "true boolean" in SQL Server, which cannot be the result for a column.

select A > B -- A=B resolves to one of true/false/null
from C

Needs to be expressed as

select case when A is null or B is null then null when A > B then 1 else 0 end
from C

But if A is not a scalar value but a subquery like (select sum(x)...), then as you can see A will appear twice and be evaluated twice in the CASE statement (repeated).

FINAL TEST Now we put all the conversion rules to use in this long expression

SELECT X AND Y=Z AND C FROM ..
( assume X is numeric 5, and C is varchar "H" )
( note C contributes either TRUE or NULL in Access )

This translates to SQL Server (chaining the two functions and using CASE)

SELECT dbo.BoolFromBits(
       dbo.BoolFromBits(dbo.BoolFromAny(X), CASE WHEN Y=Z then 1 else 0 end),
                       dbo.BoolFromAny(C))
FROM ...

Access Bool or bool

For completeness, here is the truth table for Access bool OR bool. Essentially, it is the opposite of AND, so

• Both have to be false to return false
• Either being true returns true (regardless of nulls)
• Otherwise return null

The SQL SERVER case statement would therefore be

  case
  when a.a = 1 or b.a = 1 then 1
  when a.a = b.a then 0
  end

(the omission of an ELSE clause is intentional as the result is NULL when omitted)