Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.6K Security Software
New type BOOLEAN
Comments
-
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!!
-
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?
-
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 ?
- SQL uses 3-valued logic
- 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 ;
-
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?
-
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 asF → 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))
;
-
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 asF → 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.
-
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
-
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
-
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.