Issue Details (XML | Word | Printable)

Key: CORE-726
Type: New Feature New Feature
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Pavel Cisar
Votes: 27
Watchers: 10
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Boolean data type

Created: 17/Sep/03 12:00 AM   Updated: 27/Mar/14 01:33 PM
Component/s: API / Client Library, Engine
Affects Version/s: None
Fix Version/s: 3.0 Alpha 1

Issue Links:
Relate
 

SF_ID: 807929


 Description  « Hide
SFID: 807929#
Submitted By: pcisar

A new datatype which the three states -
true/false/null.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alexander Tyutik added a comment - 19/Apr/07 03:55 AM
Do not know what standart tells about boolean type, but seems (for me) true/false/null is not enough.

Look at next few samples:

DECLARE B1 BOOLEAN;
DECLARE B2 BOOLENA;

1) B1 = NULL;

2) B2 = FALSE OR NULL;


What do you think should be in case of 2? I think there should be fourth state UNKNOWN and B1IS DISTINCT FROM B2

So maby real boolean should have 4 posible values?


From other side look at microsoft's solution. Thay made BIT type instead of boolean. Client libraries use it as BOOLEAN type, but in database it used as simple flag. And in this case there are no problems with fourth state UNKNOWN.

But maybe i'm wring. What do you think?

Dmitry Yemanov added a comment - 19/Apr/07 04:05 AM
In the SQL specification, both NULL and UNKNOWN mean the same for the BOOLEAN datatype, so that (NULL IS NOT DISTINCT FROM UNKNOWN).

As for the BIT datatype, I fail to see why it's radically better than [SMALL]INT in terms of the BOOLEAN emulation.

Alexander Tyutik added a comment - 19/Apr/07 04:31 AM
About UNKNOWN i understood, thanks.

About SMALLINT: bacause client liraries, such as .NET Privider, automatically converts BIT into boolean datatype and with SMALLLINT it is impossible. But if UNKNOWN=NULL for BOOL than BIT type is unnecessary.

Fidel Viegas added a comment - 25/Jan/08 11:26 AM
It would be nice to be able to use 'true' and 'false'. Are there any plans to add support for this?

Cosmin Apreutesei added a comment - 07/Dec/08 11:53 AM - edited
There's little value (i.e no expressivity nor functional gain) in merely adding the BOOLEAN built-in type and the TRUE and FALSE constants. Everybody has a boolean domain in their databases and x = 1 vs x = TRUE changes nothing really. Besides, I get more expressivity by using (a + b + c > 0) than I would get from (a = TRUE and b = TRUE and c = TRUE).

Instead, here's what would really add power to the language:
  - ability to select expressions that evaluate to boolean(***); example: SELECT EXPIRE_DATE > CURRENT_DATE AS IS_ACTIVE FROM USERS;
  - a boolean field should evaluate as boolean by itself; example: SELECT A OR B AND NOT C FROM T instead of SELECT A = TRUE OR B = TRUE AND C = FALSE FROM T;
  - automatically cast all datatypes to boolean for operators which require boolean operands; example: SELECT COUNT_1 AND COUNT_2 AS HAS_ELEMENTS FROM T; in this example, I used C rules for typecasting integers.

(***)Currently, since boolean operators can only be used in the WHERE clause, you cannot determine (and it doesn't matter) if the expression (A = 1 AND NULL = 1) evaluates to FALSE or to NULL (by SQL definition, it would have to be NULL, but since the outcome is the same in a WHERE clause, how can you tell?). New semantics would have to be specified for such cases.

maziar added a comment - 03/May/09 09:55 AM
this really needed

specially when need port other database system link mysql to firebird

for example how use boolean typ via ODBC when needed data typ boolean ?

Ivan added a comment - 30/Oct/09 07:55 PM
this is maybe need but you can

create table mytable(

ID integer,
A integer check (A=0 or A=1),
B varchar(4) check (B=TRUE or B=FALSE)
);


Kovalenko Dmitry added a comment - 22/Mar/11 04:53 PM
Please add the support for blr_sql_bool into sdl_desc [sdl.cpp]

Thanks.