FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » FUDforum Development » Bug Reports » SQL bug in default theme
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
SQL bug in default theme [message #34873] Tue, 21 November 2006 10:49 Go to next message
jyoder is currently offline  jyoder   United States
Messages: 5
Registered: November 2006
Location: Michigan, USA
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
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 Smile

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 #34876 is a reply to message #34873] Tue, 21 November 2006 11:39 Go to previous messageGo to next message
Ernesto is currently offline  Ernesto   Sweden
Messages: 413
Registered: August 2005
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
Nice, but you patched the wrong file, you patched the compiled file, you need to add changes to the uncompiled files located in the DATA_ROOT if you want your change to stick Wink

Re: SQL bug in default theme [message #34880 is a reply to message #34876] Tue, 21 November 2006 12:31 Go to previous messageGo to next message
jyoder is currently offline  jyoder   United States
Messages: 5
Registered: November 2006
Location: Michigan, USA
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
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 #34892 is a reply to message #34880] Wed, 22 November 2006 10:12 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
This bug was fixed in CVS earlier, the official patch can be found here:
http://cvs.prohost.org/c/index.cgi/FUDforum/chngview?cn=11611


FUDforum Core Developer
Re: SQL bug in default theme [message #34902 is a reply to message #34892] Wed, 22 November 2006 11:44 Go to previous messageGo to next message
jyoder is currently offline  jyoder   United States
Messages: 5
Registered: November 2006
Location: Michigan, USA
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Ilia wrote on Wed, 22 November 2006 10:12

This bug was fixed in CVS earlier, the official patch can be found here:
http://cvs.prohost.org/c/index.cgi/FUDforum/chngview?cn=11611


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 Smile
                        ((!$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 11:55]

Report message to a moderator

Re: SQL bug in default theme [message #34919 is a reply to message #34902] Thu, 23 November 2006 09:27 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Thanks for the detailed analysis, I've added another tweak that should make PostgreSQL happy.

http://cvs.prohost.org/c/index.cgi/FUDforum/chngview?cn=11655


FUDforum Core Developer
Re: SQL bug in default theme [message #34997 is a reply to message #34919] Thu, 30 November 2006 22:50 Go to previous messageGo to next message
jyoder is currently offline  jyoder   United States
Messages: 5
Registered: November 2006
Location: Michigan, USA
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Ilia wrote on Thu, 23 November 2006 09:27

Thanks for the detailed analysis, I've added another tweak that should make PostgreSQL happy.

http://cvs.prohost.org/c/index.cgi/FUDforum/chngview?cn=11655


Close! Delete one more character and it'll work. There are two closing parenthesis after the COALESCE statement (just before the single-quote), there should be only one.

e.g.

g1.group_cache_opt) & 1 > 0)'

Thanks!
Re: SQL bug in default theme [message #35016 is a reply to message #34997] Fri, 01 December 2006 10:39 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
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
Re: SQL bug in default theme [message #35019 is a reply to message #35016] Fri, 01 December 2006 11:45 Go to previous messageGo to next message
jyoder is currently offline  jyoder   United States
Messages: 5
Registered: November 2006
Location: Michigan, USA
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Doh! I didn't read it closely enough. I didn't notice that you merged the >0 part into both statements. It's much easier to read now.

Nevermind! Should be fine Smile

Jeremy
Re: SQL bug in default theme [message #35087 is a reply to message #34892] Wed, 06 December 2006 03:39 Go to previous messageGo to next message
hoanhnd is currently offline  hoanhnd   Vietnam
Messages: 2
Registered: December 2006
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
many thanks. I had same proplem
Re: SQL bug in default theme [message #35088 is a reply to message #35019] Wed, 06 December 2006 03:49 Go to previous message
hoanhnd is currently offline  hoanhnd   Vietnam
Messages: 2
Registered: December 2006
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
jyoder wrote on Fri, 01 December 2006 11:45

Doh! I didn't read it closely enough.

a
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: problems with CSS after changing document root for FUDforum
Next Topic: Inbox - Sort by xxxxxxx
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Oct 19 01:36:42 EDT 2017

Total time taken to generate the page: 0.00998 seconds