| SQL bug in default theme [message #34873] |
Tue, 21 November 2006 15:49  |
jyoder
Messages: 5
Registered: November 2006
Location: Michigan, USA
Karma: 0
|
Junior Member |
|
|
I stumbled across this bug in 2.7.6 when trying to register a new test user on my new forum. I've never used FUDforum before so this is a fresh install using Postgres as the database.
The SQL error was a bit cryptic, but it looks like the fix is pretty simple. Basically it looks like the parenthesis in the WHILE statement got a bit mixed up. I'm pretty confident my patch fixes both cases, but the COALESCE is what executes in my case so I couldn't test both.
What I did was remove the second closing parenthesis after the COALESCE (so the >0 applies to that and not the entire WHILE statement), added a second opening parenthesis before the (g1.group_cache_opt so that I could add a closing parenthesis after the >0. Okay, maybe I shouldn't have tried to explain it in english 
Here's a diff between the original index.php and my patched one:
diff -ru ./forum.orig/theme/default/index.php ./forum/theme/default/index.php
--- ./forum.orig/theme/default/index.php 2006-11-21 08:26:40.000000000 -0700
+++ ./forum/theme/default/index.php 2006-11-21 08:22:43.000000000 -0700
@@ -147,7 +147,7 @@
((!$is_a || $cat_id) ? ' WHERE ' : '') .
- ($is_a ? '' : (_uid ? ' (mo.id IS NOT NULL OR (COALESCE(g2.group_cache_opt, g1.group_cache_opt))' : ' (g1.group_cache_opt').' & 1)>0') .
+ ($is_a ? '' : (_uid ? ' (mo.id IS NOT NULL OR (COALESCE(g2.group_cache_opt, g1.group_cache_opt)' : ' ((g1.group_cache_opt').' & 1)>0)') .
($cat_id ? ($is_a ? '' : ' AND ') . ' v.c IN('.implode(',', ($cf = $cidxc[$cat_id][5])).') ' : '').' ORDER BY v.id');
|
|
|
|
|
|
| Re: SQL bug in default theme [message #34880 is a reply to message #34876] |
Tue, 21 November 2006 17:31  |
jyoder
Messages: 5
Registered: November 2006
Location: Michigan, USA
Karma: 0
|
Junior Member |
|
|
Ah, thanks! Okay, I've patched the correct file this time:
--- FUDforum.orig/src/index.php.t 2006-11-14 16:12:08.000000000 -0700
+++ FUDforum/src/index.php.t 2006-11-21 10:29:23.000000000 -0700
@@ -72,7 +72,7 @@
((!$is_a || $cat_id) ? ' WHERE ' : '') .
- ($is_a ? '' : (_uid ? ' (mo.id IS NOT NULL OR (COALESCE(g2.group_cache_opt, g1.group_cache_opt))' : ' (g1.group_cache_opt').' & 1)>0') .
+ ($is_a ? '' : (_uid ? ' (mo.id IS NOT NULL OR (COALESCE(g2.group_cache_opt, g1.group_cache_opt)' : ' ((g1.group_cache_opt').' & 1)>0)') .
($cat_id ? ($is_a ? '' : ' AND ') . ' v.c IN('.implode(',', ($cf = $cidxc[$cat_id][5])).') ' : '').' ORDER BY v.id');
|
|
|
|
|
|
| Re: SQL bug in default theme [message #34902 is a reply to message #34892] |
Wed, 22 November 2006 16:44 |
jyoder
Messages: 5
Registered: November 2006
Location: Michigan, USA
Karma: 0
|
Junior Member |
|
|
That fix merely made the number of parenthesis match, the resulting SQL is still broken. Here's the error you get:
ERROR: argument of OR must be type boolean, not type integer
Which is due to the WHERE clause:
WHERE (mo.id IS NOT NULL OR (COALESCE(g2.group_cache_opt, g1.group_cache_opt)) & 1)>0
This ties the >0 to the entire WHERE clause and not just the result of the COALESCE as intended.
My patch results in the following WHERE clauses (depending on the options) :
WHERE (mo.id IS NOT NULL OR (COALESCE(g2.group_cache_opt, g1.group_cache_opt) & 1)>0)
OR
WHERE ((g1.group_cache_opt & 1)>0)
Obviously the outer ()'s aren't required, but this was the least number of characters I could change to fix the statement.
EDIT: On further reflection, removing the outer parenthesis makes it a 2 character patch instead of 3
((!$is_a || $cat_id) ? ' WHERE ' : '') .
- ($is_a ? '' : (_uid ? ' (mo.id IS NOT NULL OR (COALESCE(g2.group_cache_opt, g1.group_cache_opt))' : ' (g1.group_cache_opt').' & 1)>0') .
+ ($is_a ? '' : (_uid ? ' mo.id IS NOT NULL OR (COALESCE(g2.group_cache_opt, g1.group_cache_opt)' : ' (g1.group_cache_opt').' & 1)>0') .
($cat_id ? ($is_a ? '' : ' AND ') . ' v.c IN('.implode(',', ($cf = $cidxc[$cat_id][5])).') ' : '').' ORDER BY v.id');
Thanks!
[Updated on: Wed, 22 November 2006 16:55] Report message to a moderator |
|
|
|
|
|
|
|
| Re: SQL bug in default theme [message #35016 is a reply to message #34997] |
Fri, 01 December 2006 15:39 |
Ilia
Messages: 13241
Registered: January 2002
Karma: 0
|
Senior Member
Administrator
Core Developer |
|
|
Looks perfectly fine to me:
(mo.id IS NOT NULL OR (COALESCE(g2.group_cache_opt, g1.group_cache_opt) & 1 > 0))
There are 3 braces open and 3 closed...
FUDforum Core Developer
|
|
|
|
|
|
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]