Boolean/Bitwise Expressions

>> SQL does however have a BIT datatype which, when NULLs are not allowed, is exactly the same thing. <<

No, it is not even close:
1) I cannot use AND, OR and NOT on a BIT because it is numeric and not
BOOLEAN.
2) I can create NULLs in BIT columns with OUTER JOINs, ROLLUP, etc. in
the results You cannot escape the problem and that is why we required
all data types to be NULL-able.

>> You might be interested to know that, while all SQL datatypes have to be NULL-able, you can still specify NOT NULL if you need to. Once you do that, all those contradictions vanish. <<

See (2) above. The problems stay. Hey, when you spend a decade on a
language committee, you have considered all kinds of situations. And
telling people to do or not do something that is legal syntax is not a
solution.

>> Because obviously no developer is going to take the time to map the value in SQL to the value in the application. <<

Which application? Given many application languages with different
implementations, you would want to go thru some middleware to be
safe. Now, since BIT is a numeric but gets used as a BOOLEAN, which
way do you translate it into each host language? Are you sure
everyone else did it the same way in their code or their middleware?

>> I find it interesting that you ignore portability when it comes to date formats, but use it as an argument here. <<

What are you talking about? I am the champion of ISO Standards for
temporal data types. I am obsessed with portable formats over local
dialects.

> What are you talking about? I am the champion of ISO Standards for
> temporal data types. I am obsessed with portable formats over local
> dialects.
>

ANSI date literal is specified as DATE 'YYYY-MM-DD'.

ISO format is YYYYMMDD or YYYY-MM-DD - either is acceptable - this is fully
documented on the ISO website.

SQL Server suffers a regional setting problem on conversion from a string
value to a datetime when using YYYY-MM-DD.

SQL Server does not support the DATE prefix so in SQL Server you cannot
specify a date literal as definied by the ANSI SQL standard, instead you
have specify a string literal and rely on implicit data type conversion
(string to datetime).

SQL Server suffers a regional setting problem on conversion from a string
value to a datetime when using YYYY-MM-DD.

Why is it so difficult for you to accept this? Probably because all your
books are wrong and if people follow them they will end up in all sorts of
inconsistency bother.

You are an arrogant and ignorant idiot with little regard to the IT
Professional, all that matters is that you are right even though you've been
proved wrong over and over again.

And I'm still waiting for you to answer the real world question I posed - do
you need me to start another thread?

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

On Feb 7, 11:37 am, --CELKO-- wrote:
> The truth is that BOOLEAN is not widely used in application code. It
> shows up in low-level code which is closer to the hardware -- assembly
> and C being the main users. This is why it is not appearing in the
> newer languages, even for backward compatibility.

Incorrect - boolean is widely used.

On Feb 7, 2:44*pm, --CELKO-- wrote:
> >> SQL does however have a BIT datatype which, when NULLs are not allowed,is exactly the same thing. <<
>
> No, it is not even close:
> 1) I cannot use AND, OR and NOT on a BIT because it is numeric and not
> BOOLEAN.

AND, OR, and NOT can be all replicated within the language.

> 2) I can create NULLs in BIT columns with OUTER JOINs, ROLLUP, etc. in
> the results *You cannot escape the problem and that is why we required
> all data types to be NULL-able.

Which means you have to decide ahead of time what NULL should
represent.

> >> You might be interested to know that, while all SQL datatypes have to be NULL-able, you can still specify NOT NULL if you need to. *Once you do that, all those contradictions vanish. <<
>
> See (2) above. *The problems stay. *Hey, when you spend a decade on a
> language committee, you have considered all kinds of situations. *And
> telling people to do or not do something that is legal syntax is not a
> solution.

The fact that you sat on a language committe how many versions ago is
meaningless. You have demonstrated time and again that you are stuck
on old code and are unwilling to accept the fact that "standard" SQL
is a dinosaur that is seen in the real world about as often as it's
Jurassic Park counterpart.

> >> Because obviously no developer is going to take the time to map the value in SQL to the value in the application. <<
>
> Which application? *Given many application languages with different
> implementations, you would want to go thru some middleware to be
> safe. *Now, since BIT is a numeric but gets used as a BOOLEAN, which
> way do you translate it into each host language? *Are you sure
> everyone else did it the same way in their code or their middleware?

Right... because lord knows developers never actually talk to one
another during a project! In all seriousness, how many projects have
you worked on (I could stop the sentence there...) where nobody knew
how true and false was being passed to the application?

I have yet to see 3rd party software that did not correctly translate
1/0 values from a SQL data source it was written for; and I sure as
heck wouldn't allow one to be released from my own shop. It's a make-
believe issue and you know it.

> >> I find it interesting that you ignore portability when it comes to dateformats, but use it as an argument here. <<
>
> What are you talking about? I am the champion of ISO Standards for
> temporal data types. *I am obsessed with portable formats over local
> dialects.

Then why do you keep pushing a format that you have been told time and
time again will not work properly across the board? You do realize
what "standard" means, right?

Believe it or not there is a whole world out there that does not use
US English.... It's a bit bigger than a "local dialect" issue.

But Joe you say things that are such utter rubbish and offer no
foundation or evidence yourself.

Let's test this:

> Instead of infantile name calling, perhaps you could give an example
> that supports whatever your position is?

Ok: VB.Net, C#.Net, C++, Delphi, Visual Objects... would like any other
language examples? Oh yes, T-SQL...

> Perhaps a scan of existing application code that shows the use of
> BOOLEAN data types? That would be an easy piece of research that

How many billions of lines of code do you want to see? This is where you
destroy your own credibility.

> should have been done already. I vaguely remember something like this
> for COBOL and FORTRAN data types done by Burroughs so they could

And this is where my dinosaur criticism comes from. You don't seem to be
aware of modern languages. Your whole approach is Jurassic.

> design hardware that would favor the most common ones -- why have more
> floating point registers than you need 90% of the time?

Excuse me? Again :-). What has hardware got to do with anything???? We
are talking language. How it is represented internally is utterly
irrelevant.

> The truth is that BOOLEAN is not widely used in application code.

You see? This is what makes you a laughing stock. This is just a sick
joke and so obviously wrong that I don't think anyone could take you
seriously. All modern languages have a Boolean data type of one name or
other and it would have to be universally used. Even T-SQL.

Geoff

> No, it is not even close:
> 1) I cannot use AND, OR and NOT on a BIT because it is numeric and not
> BOOLEAN.

You are sad. So you can't think of ways to deal with this in t-sql?

Hi Joe,

On Thu, 7 Feb 2008 09:24:14 -0800 (PST), --CELKO-- wrote:

>>> What hardware considerations are involved in a datatype BOOLEAN that is defined to hold the truth values TRUE and FALSE, to use NULL to represent the 3VL truth value UNKNOWN, and to store these values in an implementation-defined way? <<
>
>Unh? I just listed the design philosophy for SQL and the newer
>languages. I never mentioned implementations at all.

And neither did I.

You wrote:

>>1) Languages that are as free from hardware considerations (i.e. "bits
>>and bytes") as possible for portability.

I then asked you what hardware considerations (i.e. what "bits and
bytes") are involved in a BOOLEAN datatype. You're not trying to dodge
my questions, are you?

> What does have
>to be considered for BOOLEANs at this level is how an INDICATOR will
>be used with embedded SQL in each host language. Is a Boolean really
>a numeric type (Microsoft) or true BOOLEAN (Pascal) in the host
>language? Is it an atom (LISP, Erlang, Prolog, etc.)?

Why do you feel that this has to be considered? And why do you not feel
that one also has to consider how INTEGER or DATE data will be used in
host languages?

SQL defines datatypes in an implementation-independant way. The
interfaces between SQL and the host language will have to translate the
data to a format that is suitable for that host language. That holds for
BOOLEAN just as it does for all other data types.

> It turns out
>to be messy is less and less important as we move away from
>traditional programming to languages based on predicates.

Despite a continuous stream of new languages and programming concepts
hitting the market over last decades, very few survive the initial wave
of enthousiasm. Most development work nowadays is still done in a
somewhat updated version of either C or Basic - languages that have had
plenty of years to show their value.

I've learned, from experience, not to call a language a success or a
foreboding of the future until it is at least 10 years old and still
popular.

>>> What is procedural about this query
>
>SELECT something
>FROM sometable
>WHERE (onecolumn anothercolumn) IS UNKNOWN;
>
>AFAIU, this is allowed in SQL:2003. <<
>
>Actually, it is in Full SQL-92. Nothing is procedural; the "IS [NOT]
>{TRUE | FALSE | UNKNOWN}" is a predicate and not a BOOLEAN. It was
>added to allow matching between the DDL and DML rules, and perhaps to
>give us a way to do Date's MAYBE() operator. Notice that you did not
>put it in the SELECT list as an expression. What is your point?

My bad. Bad example. Here is an example that is also allowed by
SQL:2003, does require the use of a boolean data type, and is purely
declarative. (Note - the syntax below is ANSI-compliant and won't run on
SQL Server for several reasons, one of them being the lack of a BOOLEAN
data type).

CREATE TABLE xxx
(Col1 INTEGER NOT NULL,
Col2 INTEGER,
Col3 INTEGER,
Col4 BOOLEAN GENERATED ALWAYS AS (Col2 < Col3),
CONSTRAINT PK_xxx PRIMARY KEY (Col1)
);
-- Allow users a few months to enter data
SELECT Col1, Col2, Col3
FROM xxx
WHERE Col4 IS NOT FALSE;

>>> High level abstractions such as parallelism and state changes rather than sequential control flow. Stateless programming models, Petri net models and guarded commands (non-deterministic models) are favored too.
>..
> And this relates to a BOOLEAN data type in SQL, how? <<
>
>That was not my topic. I simply pointed out that modern languages use
>predicates and not BOOLEAN data types.

Maybe you did - but since this thread is titled "Boolean/Bitwise
Expressions" and the newsgroup has "sqlserver" in its name, I assumed
you were somehow making a point about boolean or bitwise expression in
either SQL in general, or SQL Server in specific. Obviously, I was
wrong.

> I gave one example of the
>current "cool language" and mentioned an SD column by a programming
>expert who has been around for a few decades "Erlang: What the Cool
>Kids Are Doing By Larry O'Brien" (details at:
>http://www.sdtimes.com/printArticle/...071015-01.html).

I'm not a cool kid and I'm not doing Erlang. That's why I'm in a SQL
Server group and not in an Erlang group.

>I believe because of the problems with BOOLEAN that it will be
>deprecated, just like BIT and BIT VARYING were and for much the same
>reasons.

What "problems with BOOLEAN" are you refering to? Those I debunked in my
other message in this thread, the one you did not yet reply to (but I'm
sure you will, once you have done the research to adequately answer my
questions about the SQL-92 standard). Or are there any other problems
that you so far have failed to mention?

>Your reply seems off-topic.

If this has suddenly become a newsgroup where a thread titled
"Boolean/Bitwise Expressions" is supposed to discuss Erlang and other
stuff for cool kids, then I will proudly admit to being guilty as
charged. Sorry for thinking that we were discussing boolean data in
relation to SQL Server.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Hi Joe,

On Thu, 7 Feb 2008 11:44:56 -0800 (PST), --CELKO-- wrote:

(snip)
>>> You might be interested to know that, while all SQL datatypes have to be NULL-able, you can still specify NOT NULL if you need to. Once you do that, all those contradictions vanish. <<
>
>See (2) above. The problems stay.

What problems are you refering to? Those I debunked in my other message
in this thread, the one you did not yet reply to (but I'm sure you will,
once you have done the research to adequately answer my questions about
the SQL-92 standard). Or are there any other problems that you so far
have failed to mention?

(snip)
>>> I find it interesting that you ignore portability when it comes to date formats, but use it as an argument here. <<
>
>What are you talking about? I am the champion of ISO Standards for
>temporal data types. I am obsessed with portable formats over local
>dialects.

Obsessed is the right word, indeed. So obsessed that you prefer a format
that ports without change to Oracle, DB2, and MySQL but won't work on
SQL Server in many countries, over a format that is just as easily
ported to other platforms *AND* works in all countries - all because the
latter is not supported by the official standard and the former is.

Yes, Microsoft did screw up when they failed to implement ISO standards
for date literals properly. Hooray, point for Joe. Now wipe that smile
off your face and start posting code that recognises this and works
around the issue, or take your business to a newsgroup for a DBMS that
does know that 2008-02-08 is not 6 months in the future, not even in the
UK.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

>> Let's scan each solution for "^[ \t]*If", using regex search. <<

Good; that will find **predicates**, not BOOLEANs. So will a scan for
"while", "CASE", "switch", etc. and you will find more predicates
being used as controls. What you want to look for is a data type
declaration and an assignment of TRUE or FALSE to it. So how many
business apps (not operating system or lower level systems) have those
declarations?

I think there is a problem in understanding the difference between a
first-order logic and simple Boolean algebra. It is kinda like
constants versus variables versus functions in math.

>> And I don't have to put much effort in, because Microsoft (who you claim are all confused about this) have done the mapping work for me. I can just GetBoolean() from a datareader. <<

And since Microsoft is the entire world, this will never be a
problem? Some of us work in a bigger world.

>> It may not be appearing in the "newer languages" of your choice, but people are inventing new languages all of the time. And half the time (it seems) the new language is a procedural language. <<

Again, the trend is toward declarative languages, not OO or procedural
ones. The reasons are a straightforward results of the hardware
changes. Parallelism is not well supported in OO or procedural models
-- they are too deterministic and sequential.

>> Personally, I've no need to move on to a new language at the moment, .. <<

That's the way I feel about SQL :) But my idea of SQL includes ~50
different products I have to consult on. Arrgh!

>> .. so I'll stick with any number of .Net languages which all share System.Boolean. <<

And I am being asked to learn enough SPARQL to write a book on it!
Geesh, just when you get comfortable, that damn paradigm you were
sitting on for so long gets kick out form under you!