Use of MySQL-specific SQL in forum.module
intosi - October 24, 2005 - 10:37
| Project: | Drupal |
| Version: | 4.6.3 |
| Component: | forum.module |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | closed |
Description
The SQL statemens in forum.module:622 and forum.module:672 use the IF(a,b,c) statement, which is MySQL specific. I run Drupal using PostgreSQL 8. It can be rewritten as
CASE
WHEN a THEN b
ELSE c
ENDPerhaps someone who is more skilled in Drupal-hacking can point out where I need to implement this (my guess: a custom db_rewrite_sql in includes/database.pgsql.inc).
#1
The attached patch fixes it on the database-level, by adding two custom functions to the database definition.
#2
Hi,
there already is IF() function in drupal's database.pgsql. The problem is that it expects a bool type not an integer.
Adding another IF() function that expects integer would fix the problem, but that would require database update in 4.6 series, and I'd prefer not to do it.
Another solution is to change the sql that calls the if() function from
IF(l.last_comment_uid, ...)toIF(l.last_comment_uid != 0, .... Attached patch implements this.last_comment_uid is defined as NOT NULL DEFAULT 0, so it contains only integers. IF(foo, ...) checks if foo is 0 or NULL, so it is the same as IF(foo != 0, ...) in this case (because foo can't be NULL).
I've tested that it works for postgresql.
#3
Since the situation is the same for HEAD here's the patch for that version.
#4
If anyone wanted to test the patch here are the instructions on how to reproduce the bug: http://drupal.org/node/28996
#5
query gives same results for mysql.
#6
Commited to HEAD and DRUPAL-4-6. Thanks.
#7