Image attach views filter fails on PostgreSQL; use "IS NULL" not "ISNULL()"
| Project: | Image |
| Version: | 5.x-2.0-alpha3 |
| Component: | image_attach |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | closed |
I have installed Image module and set "Image Attach: Attached image" to "Yes" in one of the view which should generate the most recently attached image to be returned.
However, I get:
warning: pg_query() [function.pg-query]: Query failed: ERROR: argument of AND must be type boolean, not type integer in /var/www/drupal5/includes/database.pgsql.inc on line 125.
user warning: query: SELECT count(node.nid) FROM node node LEFT JOIN image_attach image_attach ON node.nid = image_attach.nid WHERE (image_attach.iid) AND (node.status = '1') AND (node.promote = '1') in /var/www/drupal5/includes/database.pgsql.inc on line 144.
This is caused by:
... WHERE (image_attach.iid) AND ...
which does not considered as a boolean-able argument. It should work if you do something like:
... WHERE (image_attach.iid IS NOT NULL) AND ...
So I came to the conclusion that the following bit needs changing (the line below the comment line "//".):
function image_attach_views_handler_filter_iid_exist($op, $filter, $filterdata, &$query) {
switch ($op) {
case 'handler':
$query->ensure_table('image_attach');
if ($filter['value']) {
//$query->add_where('image_attach.iid');
$query->add_where('image_attach.iid IS NOT NULL');
}
else {
//$query->add_where('ISNULL(image_attach.iid)');
$query->add_where('image_attach.iid IS NULL');
}
break;
}
}Though I am not too familiar with how the hook for image_attach_views_tables works, but I want to get rid of this SQL error message. I would be grateful if anyone can guide me to rectify this!
The version I was using was:
PostgreSQL 8.2.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
#1
Thanks for reporting this, and for doing the bughunting -- that's a real help! :D
I've made a patch which works on MySQL.
Unfortunately, I have no means of testing PostgreSQL (wacky DB system... I can barely spell it! ;)
If you could test the attached patch to confirm it works on your system that would be great.
You'll need to restore you original image (I expect it should work on the alpha) to apply the patch. See http://drupal.org/patch/apply for help.
#2
Thanks for the quick response! I will apply that and let you know what happens tomorrow as this is at work.
The only problem I have at the moment with this is to get the new change kicked in so that it runs ok in views. Earlier I made the change to the relevant lines manually as I described above and I still get the same error messages as before without the query showing the new change. This is so despite I ran view cache refresh, recreated a view using the filter, etc. I would be grateful if you can advise what I should be doing here...
Image module sits at: site/all/modules/.
#3
Ok, the patch applied (the applicable lines verified) - but I still can't get the change to get kicked in, hence, still receive the same error message. Is it not enough to rebuild the view cache?
#4
I don't remember how Views caches stuff on D5... there may be a cache of available fields and filters etc, and I've no idea where that is.
Best bet is to go to modules admin, enable or disable a random module. That should force views to refresh its data.
#5
Would disabling/unabling Image_attach part of the Image module group count? That I have done...
#6
Yeah, definitely.
Try either devel or admin module; though I don't remember what they give you on D5: http://drupal.org/node/42055
#7
Hmm, a thought: does Postgres do any caching of its own?
#8
RE: caching, Not by default as far as I know. Not from my experience.
I am installing admin module.
#9
I ended up deleting caches by createing clear.php in the root with following code as the devel module leaves some cache records and I did not install Admin module all at the end:
<?phpinclude_once './includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);
cache_clear_all();
cache_clear_all('*', 'cache', TRUE);
cache_clear_all('*', 'cache_content', TRUE);
cache_clear_all('*', 'cache_page', TRUE);
cache_clear_all('*', 'cache_menu', TRUE);
cache_clear_all('*', 'cache_filter', TRUE);
cache_clear_all('*', 'cache_views', TRUE);
?>
No luck. (Verified the record counts to be 0 by directly running select statements against above tables.)
But I have a strong feeling *I* am doing something fundamentally wrong. The following thread says the user is trying to amend the views_tables hook but it won't get reflected because he is trying to amend the definition that already exists.
http://drupal.org/node/217693
Could mine be a related problem? The behaviour seems similar as he describes - I have left debug echos all over in image_attach.module (in sites/all/modules/image/contrib/image_attach). I have deleted image_attach via admin/build/modules/uninstall and reinstated in admin/build/modules, but the echos do not appear anywhere. Then, updated the database from update, and rebuild the cache by running above page.
I have even renamed sites/all/modules/image/contrib/image_attach/image_attach.module in hope to get a runtime error... But it behaves "normally." What do you think I am doing wrong?
#10
Install admin module.
At the very least, it has a nice menu item for clearing the caches :)
I have no clue what you might be doing wrong though, sorry.
#11
Ok - I was loosing clarity so I decided to remove everything to do Image module and re-added.
And it worked.
So, along the way above, I must have done something wrong. I will see if I can do this with a minimum disruption in the earlier state of the site before I apply this to the live site. Apologies for all the trouble and thanks for your help!
#12
So, just to check -- does the patch I posted work?
#13
Yep - it did.
I think I had rather too much crap stored in my development environment to work as it should. Cleared up a bit and I managed apply to the original environment and the live too without any disruption.
#14
Cool!
Got to run, will commit later.
Thanks for testing this!
#15
Committed to CVS:
#619150 by kotoponus: Fixed image attach views filters SQL for PostgreSQL.
This will be in the dev version shortly.
Thanks for all your help on this :)
#16
Committed to CVS:
#619150 by kotoponus: Fixed image attach views filters SQL for PostgreSQL.
This will be in the dev version shortly.
Thanks again for all your help on this :)
#17
Automatically closed -- issue fixed for 2 weeks with no activity.