New type BOOLEAN

Gbenga Ajakaye

BCG14 wrote:Would be nice to have. Varchar NULL = 0 (no) NOT NULL = 1 (YES)?

I disagree.

That's yet another lame workaround for the missing boolean type in SQL.

bye

TPD

You're entitled to your own opinion!!

William Robertson

Would be nice to have. Varchar NULL = 0 (no) NOT NULL = 1 (YES)?

What is 'varchar', why would you use it to hold values 0 and 1, and where is it defined what those values mean?

Nikolaus Thiel

Wow, this is an old idea.

On the surface having Boolean datatype in SQL may seem like a good thing.  It's even specified in the SQL-92 standard.

However most RDBMS suppliers don't include Boolean as a datatype on the database, or for use in SQL (except the implied Boolean logic of conditions)

The problem stems from three-value-logic (aka 3VL).

Boolean is great where there is Bivelent logic (i.e. true/false) but becomes problematic when you get into the Trivalent logic (3VL) that SQL uses, mainly because of the ability to handle NULL values.

So, whilst everyone may be thinking "hey yeah, Boolean Datatype is a great idea for our database", they would soon find all sorts of logic problems arising and no doubt be raising ideas to try and get it removed again.

https://en.wikipedia.org/wiki/Three-valued_logic

https://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/

Plenty of other references for 3 value logic and the problems of using Boolean in SQL.  It's something that's been an ongoing discussion for many years, and when you get a good understanding of the reasons why RDBMS suppliers haven't included it, you'll appreciate that they've probably made the right choice.

Do the following two popular claims contradict each other ?

1. SQL uses 3-valued logic
2. NULL is not a value but a "place holder" for a value.

If NULL is not a value but SQL uses 3-valued logic, may someone please list the three values ?

The following PL/SQL example seems to prefer (2) instead of (1)

declare

  P boolean ;

begin

  dbms_output.put_line(case when (P=false)   then 'false'

                            when (P=true)    then 'true'

                            when (P = cast(null as boolean))  then 'null'

                            when (P is null) then 'is null'

                            else 'else'

                       end);

  P := (null=42) ;

  dbms_output.put_line(case when (P=false)   then 'false'

                            when (P=true)    then 'true'

                            when (P = cast(null as boolean))  then 'null'

                            when (P is null) then 'is null'

                            else 'else'

                       end) ;

end ;

/

produces the output

is null

is null

which hints at NULL not being a value.

Maybe instead of thinking of SQL using 3-valued logic it might be better to think of SQL using 2-valued (boolean) logic but allowing the usage of variables without an assigned value (instead of throwing an exception) with the following conventions:

SQL Query

A row is an element of the result set if and only if the where clause evaluates to TRUE.

Table Check Constraint

A row can not be inserted into a table if and only if at least one check constraint evaluates to FALSE (or any other constraint like PK, FK etc).

Logical Disjunction (e.g. in WHERE clauses)

• If one disjunct is TRUE then the disjunction is true.
• If one disjunct is FALSE and the other has no value then the disjunction has no value.

Negation

The negation of an expression without truth value has no truth value.

Nikolaus Thiel

Not only should the datatype BOOL(EAN) be added as SQL datatype but also to be used in where clause as such. For example if we had the following table

table ITEMS

(ID number(8) primary key

,IS_ACTIVE    bool

)

then the following statement should be valid

select * from ITEMS where atom(IS_ACTIVE) and ... ;

In addition I would like to use boolean valued PL/SQL functions or boolean values in a query:

declare

  P boolean ;

begin

...

  delete from <TABLE> where atom(P) ;

...

end ;

William Robertson

Not only should the datatype BOOL(EAN) be added as SQL datatype but also to be used in where clause as such. For example if we had the following table

table ITEMS

(ID number(8) primary key

,IS_ACTIVE    bool

)

then the following statement should be valid

select * from ITEMS where atom(IS_ACTIVE) and ... ;

In addition I would like to use boolean valued PL/SQL functions or boolean values in a query:

declare

  P boolean ;

begin

...

  delete from <TABLE> where atom(P) ;

...

end ;

Well obviously the suggestion is to add a Boolean type to the SQL language. You can't really do that without allowing it in WHERE clauses. I can't see the difference between your two examples either.

I can't see the rationale for your proposed ATOM keyword, though. Wouldn't it be simpler if the language just allowed 'where is_active', or 'where is_active = true', or 'where is_active is true'? What have atoms got to do with anything?

Nikolaus Thiel

Well obviously the suggestion is to add a Boolean type to the SQL language. You can't really do that without allowing it in WHERE clauses. I can't see the difference between your two examples either.

I can't see the rationale for your proposed ATOM keyword, though. Wouldn't it be simpler if the language just allowed 'where is_active', or 'where is_active = true', or 'where is_active is true'? What have atoms got to do with anything?

In symbolic (mathematical) logic you distinguish between terms and formulae. If the language contains typed terms and boole as base type then the operator Atom transforms any boolean term into an atomic formula.

You may think of Atom(p) being a unary predicate where the free variable p is of type bool.

Axiom systems (usually) contain then the truth axiom atom(true) and may define the negation of a formula F as  F → atom(false)<code>.

But maybe in SQL, we do not need to make this fine distinction between terms and formulae.

How would you enable the usage of a boolean valued PL/SQL function as predicated in the WHERE clause ?

Example

create or replace function IS_SOMETHING (x in pls_integer, y in varchar2) return boolean deterministic ... ;

select *

from   TABLE

where  IS_SOMETHING(TABLE.QUANTITY,TABLE.NAME)

;

select *

from   TABLE

where  atom(IS_SOMETHING(TABLE.QUANTITY,TABLE.NAME))

;

William Robertson

In symbolic (mathematical) logic you distinguish between terms and formulae. If the language contains typed terms and boole as base type then the operator Atom transforms any boolean term into an atomic formula.

You may think of Atom(p) being a unary predicate where the free variable p is of type bool.

Axiom systems (usually) contain then the truth axiom atom(true) and may define the negation of a formula F as  F → atom(false)<code>.

But maybe in SQL, we do not need to make this fine distinction between terms and formulae.

How would you enable the usage of a boolean valued PL/SQL function as predicated in the WHERE clause ?

Example

create or replace function IS_SOMETHING (x in pls_integer, y in varchar2) return boolean deterministic ... ;

select *

from   TABLE

where  IS_SOMETHING(TABLE.QUANTITY,TABLE.NAME)

;

select *

from   TABLE

where  atom(IS_SOMETHING(TABLE.QUANTITY,TABLE.NAME))

;

I don't know what an 'atomic formula' is either, so if the purpose of your 'atom()' operator is to convert a Boolean value or expression to one of those I'm still not seeing the point.

We already have

    where regexp_like(somecolumn, 'someregex')

so it doesn't seem much of a stretch to allow our own Boolean functions, expressions and columns.

Nikolaus Thiel

I don't know what an 'atomic formula' is either, so if the purpose of your 'atom()' operator is to convert a Boolean value or expression to one of those I'm still not seeing the point.

We already have

    where regexp_like(somecolumn, 'someregex')

so it doesn't seem much of a stretch to allow our own Boolean functions, expressions and columns.

Never mind. To explain this would divert to much from the original topic of this dicussion.

Yes, it would be great to use user-defined boolean valued PL/SQL functions as regexp_like

TPD-Opitz

Never mind. To explain this would divert to much from the original topic of this dicussion.

Yes, it would be great to use user-defined boolean valued PL/SQL functions as regexp_like

as I earlier wrote: I'd like this function:

TO_BOOLEAN(the_legacy_value_to_convert, THE_LEGACY_TRUE_VALUES_AS_CSV_LIST)

bye

TPD

BluShadow

as I earlier wrote: I'd like this function:

TO_BOOLEAN(the_legacy_value_to_convert, THE_LEGACY_TRUE_VALUES_AS_CSV_LIST)

bye

TPD

TPD-Opitz wrote:as I earlier wrote: I'd like this function:TO_BOOLEAN(the_legacy_value_to_convert, THE_LEGACY_TRUE_VALUES_AS_CSV_LIST)byeTPD

That would be pointless.

If Boolean were to be acceptable in SQL, then just doing

update tablename set column = (legacycolumn in (truevalue1, truevalue2, truevalue3...));

would do the job.  And CSV lists are evil, as we end up with the mother in chemotherapy issue.

As long as the expression evaluates to true or false, then there is no need for some 'special' TO_BOOLEAN function.