Forum Stats

  • 3,825,482 Users
  • 2,260,519 Discussions
  • 7,896,547 Comments

Discussions

New type BOOLEAN

124678

Comments

  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy
    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
    William Robertson Member Posts: 9,567 Bronze Crown

    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
    Nikolaus Thiel Member Posts: 19
    edited Oct 3, 2016 11:27AM

    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.

  • 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 ;

    CharlesM_Dayton
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    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?

    TPD-OpitzThorsten Kettner
  • Nikolaus Thiel
    Nikolaus Thiel Member Posts: 19
    edited Oct 4, 2016 5:40AM

    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
    William Robertson Member Posts: 9,567 Bronze Crown
    edited Oct 4, 2016 6:44AM

    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.

    Peter HraškoSentinel
  • 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
    TPD-Opitz Member Posts: 2,465 Silver Trophy

    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
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    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.

    TPD-OpitzPeter Hraško