Forum Stats

  • 3,825,472 Users
  • 2,260,522 Discussions
  • 7,896,544 Comments

Discussions

New type BOOLEAN

245678

Comments

  • I agree. It would improve readability and prevent mistakes.

    amadis
  • 3229097
    3229097 Member Posts: 1

    After 15 years of coding finding myself now again typing Java "something.getSomething().equals((byte)1))" in Java really makes me want to vomit and change back to Postgre ASAP .

    Yes you can say I'm lazy biaatch and should normalize the hell out of the database or code myself a proper ORM that abstracts this DB bloat out of my data model. But realistically there are all kind's of considerations you have weigh when developing/maintaining IT systems with databases. The decision to NOT NORMALIZE my database to having table named BOOLEAN or some ORM code hack has to me mine to make not the vendors!

    Will probably find a new job working on a system with Postgre for backend.

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

    After 15 years of coding finding myself now again typing Java "something.getSomething().equals((byte)1))" in Java really makes me want to vomit and change back to Postgre ASAP .

    Yes you can say I'm lazy biaatch and should normalize the hell out of the database or code myself a proper ORM that abstracts this DB bloat out of my data model. But realistically there are all kind's of considerations you have weigh when developing/maintaining IT systems with databases. The decision to NOT NORMALIZE my database to having table named BOOLEAN or some ORM code hack has to me mine to make not the vendors!

    Will probably find a new job working on a system with Postgre for backend.

    I'm afraid I didn't quite follow your point. You want to switch to Postgres after 15 years using Oracle because PL/SQL has a Boolean type and Java doesn't, is that it?

    What does that have to do with adding a Boolean type to SQL, or with normalisation?

    user9130477FatMartinR
  • I'm afraid I didn't quite follow your point. You want to switch to Postgres after 15 years using Oracle because PL/SQL has a Boolean type and Java doesn't, is that it?

    What does that have to do with adding a Boolean type to SQL, or with normalisation?

    Exactly, William!

    I also don't understand what he's trying to say (?!).

    Oracle has boolean in PL/SQL, on the right place where it is useful.

    Does not have it for table columns , because it is not needed there.

    What's the issue, i don't understand ?!

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    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.

    Chris MentenMarcus Rangel
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    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.

    Can you give an example of a logic problem caused by a nullable Boolean column?

    Aren't we already handling this situation in SQL with ad hoc indicator columns and in PL/SQL using Boolean variable?

    TPD-Opitz
  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    Can you give an example of a logic problem caused by a nullable Boolean column?

    Aren't we already handling this situation in SQL with ad hoc indicator columns and in PL/SQL using Boolean variable?

    To be honest, there's little point in me regurgitating what is already out there on the web.

    Joe Celko's article generally explains the issue (though he's made a couple of mistakes in the article - they can be overlooked)

    And as Tom has pointed out many times in the past on AskTom, those RDBMS that do implement Boolean types for SQL, are simply defining true and false as 0 and 1 (or suchlike) in the background, so there's not much point in it.  If people are desperate to have a true/false indicator, then they could implement their own custom type (with appropriate methods for true and false) and create their columns based on that type.  Ultimately, the values are still going to take up a byte, or word etc. as there is no concept of just storing bits.

    And apologies for my previous mistake, I think it was SQL-99 when Boolean was introduced to the standards, not SQL-92.

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

    To be honest, there's little point in me regurgitating what is already out there on the web.

    Joe Celko's article generally explains the issue (though he's made a couple of mistakes in the article - they can be overlooked)

    And as Tom has pointed out many times in the past on AskTom, those RDBMS that do implement Boolean types for SQL, are simply defining true and false as 0 and 1 (or suchlike) in the background, so there's not much point in it.  If people are desperate to have a true/false indicator, then they could implement their own custom type (with appropriate methods for true and false) and create their columns based on that type.  Ultimately, the values are still going to take up a byte, or word etc. as there is no concept of just storing bits.

    And apologies for my previous mistake, I think it was SQL-99 when Boolean was introduced to the standards, not SQL-92.

    BluShadow wrote:
    
    To be honest, there's little point in me regurgitating what is already out there on the web.
    Joe Celko's article generally explains the issue
    

    Not really. He waffles a bit about Lukasiewicz, UNKNOWNs and implication operations and concludes on the basis of an unintelligible truth table that the existence of nulls means that a false premise could lead to a true conclusion. I'm not sure what his point is. Does that mean Tom is wrong too in suggesting 0 for false and 1 for true, if the whole approach is flawed according to algrebraic logic and a 0 premise could lead to a 1 conclusion?

    Gbenga Ajakaye
  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    BluShadow wrote:
    
    To be honest, there's little point in me regurgitating what is already out there on the web.
    Joe Celko's article generally explains the issue
    

    Not really. He waffles a bit about Lukasiewicz, UNKNOWNs and implication operations and concludes on the basis of an unintelligible truth table that the existence of nulls means that a false premise could lead to a true conclusion. I'm not sure what his point is. Does that mean Tom is wrong too in suggesting 0 for false and 1 for true, if the whole approach is flawed according to algrebraic logic and a 0 premise could lead to a 1 conclusion?

    It's fairly heavy going I admit, especially as it get's into the mathematics of implied logic.

    Not sure how that would change Tom's suggestion to use 0 and 1 etc.

    Jeez, now you've even got me questioning it. LOL!  Gonna have to go and clarify things for myself again now.....

  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy

    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.

    I'm sorry, but I really don't see the extra complexity that a 3VL-Boolean type would cause.

    We currently handle this 3VL problems with the surrogate types too. How would that be changed by a 3VL boolean type?

    In contra I'd say that even 3VL bbolean type would make thing better that the "stange" approach we have to use by now.

    bye

    TPD

    ApexBineThorsten KettnerSentinel