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

RESOLVED FIXED in Bugzilla 2.20

Status

()

defect
P1
normal
RESOLVED FIXED
17 years ago
7 years ago

People

(Reporter: justdave, Assigned: Tomas.Kopal)

Tracking

(Blocks 1 bug)

2.17.1
Bugzilla 2.20
Dependency tree / graph

Details

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 = 'justdave@netscape.com'
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 = 'justdave@netscape.com'
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.
Is there a technote about this somewhere?
Can these be changed to IF(conditon,1,0) ???
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.
Re: comment 4

Nice thought, but Sybase apparently has no IF() function or equivalent, either.
Also WRT comment #4:

PostgreSQL also doesnt support that. It would be:

CASE WHEN condition THEN 1 ELSE 0
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.
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.
There should be a function for it in DBCompat.pm actually... SQLIf() or
something like that.
Blocks: bz-oracle
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.
Summary: Boolean comparisons in the SELECT clause don't work in Sybase → Boolean comparisons in the SELECT clause don't work in Sybase or Oracle
"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.
Blocks: bz-postgres
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?
Summary: Boolean comparisons in the SELECT clause don't work in Sybase or Oracle → Boolean comparisons in the SELECT clause are not cross-DB compatible
(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.
Assignee: justdave → Tomas.Kopal
Depends on: 285678
Priority: -- → P1
Target Milestone: --- → Bugzilla 2.20
(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.
Status: NEW → RESOLVED
Closed: 14 years ago
Resolution: --- → FIXED
QA Contact: matty_is_a_geek → default-qa
You need to log in before you can comment on or make changes to this bug.