Closed
Bug 182136
Opened 22 years ago
Closed 20 years ago
Boolean comparisons in the SELECT clause are not cross-DB compatible
Categories
(Bugzilla :: Bugzilla-General, defect, P1)
Tracking
()
RESOLVED
FIXED
Bugzilla 2.20
People
(Reporter: justdave, Assigned: Tomas.Kopal)
References
(Blocks 1 open bug)
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'
Comment 1•22 years ago
|
||
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'
Reporter | ||
Comment 2•22 years ago
|
||
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•22 years ago
|
||
Is there a technote about this somewhere?
Comment 4•22 years ago
|
||
Can these be changed to IF(conditon,1,0) ???
Reporter | ||
Comment 5•22 years ago
|
||
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.
Reporter | ||
Comment 6•22 years ago
|
||
Re: comment 4 Nice thought, but Sybase apparently has no IF() function or equivalent, either.
Comment 7•22 years ago
|
||
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.
Reporter | ||
Comment 9•21 years ago
|
||
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.
Reporter | ||
Comment 10•21 years ago
|
||
There should be a function for it in DBCompat.pm actually... SQLIf() or something like that.
Reporter | ||
Comment 11•21 years ago
|
||
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
Comment 12•21 years ago
|
||
"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.
Assignee | ||
Updated•20 years ago
|
Blocks: bz-postgres
Comment 13•20 years ago
|
||
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
Assignee | ||
Comment 14•20 years ago
|
||
(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.
Updated•20 years ago
|
Assignee: justdave → Tomas.Kopal
Updated•20 years ago
|
Priority: -- → P1
Target Milestone: --- → Bugzilla 2.20
Assignee | ||
Comment 15•20 years ago
|
||
(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: 20 years ago
Resolution: --- → FIXED
Updated•12 years ago
|
QA Contact: matty_is_a_geek → default-qa
You need to log in
before you can comment on or make changes to this bug.
Description
•