By JamieR on

Hi, I'm trying to get more eyes on a problem I'm having with the Advanced Users module.
My issue: http://drupal.org/node/78040

The problem only appears when running on my postgreSQL database. Postgres doesen't like the AND 1 part of a select query:

warning: pg_query() [function.pg-query]: Query failed: ERROR: argument of AND must be type boolean, not type integer in /usr/share/drupal/includes/database.pgsql.inc on line 84.

Here is the full query: 

SELECT COUNT(*) 
FROM users u 
LEFT JOIN profile_values pv ON u.uid = pv.uid 
LEFT JOIN profile_fields pf on pv.fid = pf.fid 
WHERE u.uid > 1 AND 1 
GROUP BY u.uid 
HAVING SUM(IF(0,1,0)) = 0

I'm stumped, and have to agree with postgres, that this looks like a bad query - can anyone shed some light on what 'AND 1' is doing for a MySQL database so that I can fix it?

Thanks!!!
Jamie.

⋅ Categories: Drupal 4.7.x

Comments

JonathanDStopchick’s picture

JonathanDStopchick commented

When performing a default query where you require a "WHERE" for one reason or another, often in order to fulfill the WHERE clause programmers will append a 1 to the end of it.

Example:
SELECT *
FROM tables.pie
WHERE 1

This select will still select all from pie, but there is also a where clause anyway. Basically it's where "1 = 1". Warning, before you do anything save all work, and you should backup. That being said I'd try cutting it off and seeing if anything happens.

.o.oOo.o.

Me I'm dishonest, and a dishonest man you can always trust to be dishonest. Honestly it's the honest ones you have to watch out for, you never can predict if they're going to do something incredibly stupid. -Jack Sparrow

JamieR’s picture

JamieR commented

Okay - so going down the road of removing code, eventually works for that error... then I have to remove more code for this: SUM(IF(0,1,0)) = 0

Then there is another error...
But I'm sure a the end of all this code cutting I'll have a module that makes no errors, but doesn't do anything anymore either. :)

I guess I'm just not the guy to re-write this module. The issues seem bigger and deeper than at first glance...
Thanks for the suggestion and help!

Jamie.

JonathanDStopchick’s picture

JonathanDStopchick commented

One thing to keep in mind is that the goal of code cutting isn't so much to remove broken code but to figure out why that code was there in the first place. I really don't enough about the module to really go into it too deeply, but I wish you luck on it.

.o.oOo.o.

Me I'm dishonest, and a dishonest man you can always trust to be dishonest. Honestly it's the honest ones you have to watch out for, you never can predict if they're going to do something incredibly stupid. -Jack Sparrow

havran’s picture

havran commented

Must boolean type? Try change this AND 1=1 - it's boolean and it's same to AND 1 :) I have no idea why peoples use this syntax :).

--
My first drupal site - http://www.enigma.sk/

--
My site - Svoji.SK
My first Drupal site - http://www.enigma.sk/