182136 - Boolean comparisons in the SELECT clause are not cross-DB compatible

Status: RESOLVED FIXED

(Bugzilla :: Bugzilla-General, defect, P1)

Description

[Dave Miller [:justdave]]

Bugzilla uses these all over the place...  most of them are things like "(column
IS NOT NULL)" which you can work around by just retreiving "column" and using
defined() on the result instead of just directly looking at it.

Some are not quite so easy, like the following SQL that gets run by processmail
when I submit a bug:

SELECT userid, (refreshed_when > 'Jan  1 1900 12:00AM') FROM profiles WHERE
login_name = '[e-mail]'

Comment 1

[Jacob Steenhagen]

Dave, do these work if you add an AS to the statement?

SELECT userid, (refreshed_when > 'Jan  1 1900 12:00AM') AS recent FROM profiles
WHERE login_name = '[e-mail]'

Comment 2

[Dave Miller [:justdave]]

Yes, we tried that.  It'll do numeric math (SELECT column1, column2 + 5, ...)
but it won't do boolean stuff.  With or without an AS.

Comment 3

[Matthew Tuck [:CodeMachine]]

Is there a technote about this somewhere?

Comment 4

[Joel Peshkin]

Can these be changed to IF(conditon,1,0) ???

Comment 5

[Dave Miller [:justdave]]

Re: comment 3

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookView

See Chapter 2, under the heading "Choosing columns: The select clause"

Note that mathematical and bitwise operators are allowed, nowhere does it
mention boolean operators.  And my brute force trying it on a live server agrees
that they don't work.

Comment 6

[Dave Miller [:justdave]]

Re: comment 4

Nice thought, but Sybase apparently has no IF() function or equivalent, either.

Comment 7

[David Lawrence [:dkl]]

Also WRT comment #4:

PostgreSQL also doesnt support that. It would be:

CASE WHEN condition THEN 1 ELSE 0

Comment 8

[Ed Sabol]

FYI, Sybase 11.5 added support for CASE. CASE is the way to go since it's pretty standard. Alternatively, 
you can emulate the boolean logic using a complex ISNULL() function.

Comment 9

[Dave Miller [:justdave]]

Yeah, the syntax dkl posted in comment 7 for Postgres also works in Sybase.  We
ended up using that in several places on Zippy's Bugzilla.

Comment 10

[Dave Miller [:justdave]]

There should be a function for it in DBCompat.pm actually... SQLIf() or
something like that.

Comment 11

[Dave Miller [:justdave]]

Jeff says boolean stuff doesn't work in the SELECT part on Oracle, either.  CASE
WHEN works the same as both Postgres and Sybase however.

Comment 12

[Mihail Daskalov]

"CASE WHEN" works in Oracle 9i and Oracle 10g as well.
 boolean expressions in the select list must be replaced with something that
produces a number (0/1) or a String (VARCHAR2 in Oracle) - e.g. ('Y','N'; or
'T'/'F').

bug 248001 already deals with some boolean expressions in the where clause.

Comment 13

[Max Kanat-Alexander]

What's up with this? I've noticed that we've switched to CASE WHEN for almost
all our new code. Are there still places in the code where this is a problem?

Comment 14

[Tomas Kopal]

(In reply to comment #13)
> What's up with this? I've noticed that we've switched to CASE WHEN for almost
> all our new code. Are there still places in the code where this is a problem?

Yes, and as stated earlier, bug 248001 fixed a lot of them. But I recall there
are still few places where this is a problem. I plan to dig the patch up from my
install and post it here, when I have time.

Comment 15

[Tomas Kopal]

(In reply to comment #14)
> (In reply to comment #13)
> > What's up with this? I've noticed that we've switched to CASE WHEN for almost
> > all our new code. Are there still places in the code where this is a problem?
> 
> Yes, and as stated earlier, bug 248001 fixed a lot of them. But I recall there
> are still few places where this is a problem. I plan to dig the patch up from my
> install and post it here, when I have time.

You were right, there doesn't seem to be any left. And if there are, we can
always reopen, or create a new bug.