Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Boolean data type [CORE726] #1101

Closed
firebird-automations opened this issue Sep 17, 2003 · 13 comments
Closed

Boolean data type [CORE726] #1101

firebird-automations opened this issue Sep 17, 2003 · 13 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pcisar

Is related to QA503

Votes: 27

SFID: 807929#⁠
Submitted By: pcisar

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

Commits: 81fb754 c9ec26c FirebirdSQL/fbt-repository@99cf5b7

@firebird-automations
Copy link
Collaborator Author

Commented by: Alexander Tyutik (tut)

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?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Alexander Tyutik (tut)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Fidel Viegas (araujofh)

It would be nice to be able to use 'true' and 'false'. Are there any plans to add support for this?

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 10750 ] => Firebird [ 15147 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Cosmin Apreutesei (cosmin_ap2)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: maziar (maziar)

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 ?

@firebird-automations
Copy link
Collaborator Author

Commented by: Ivan (patuljak)

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Component: API / Client Library [ 10040 ]

summary: True Boolean Datatype => Boolean data type

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @ibprovider

Please add the support for blr_sql_bool into sdl_desc [sdl.cpp]

Thanks.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA503 [ QA503 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants