I have drupal 4.7 based on postgresql. I have installed this module (cvs version), enabled it, and when went into image gallery (http://myhost/?q=image/) I have following errors/warnings:

* warning: pg_query(): Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /home/maho/workspace/open/includes/database.pgsql.inc on line 84.
* user warning: query: SELECT DISTINCT(n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid LEFT JOIN i18n_node i18n ON n.nid = i18n.nid WHERE (i18n.language ='pl' OR i18n.language ='pl' OR i18n.language ='' OR i18n.language IS NULL) AND tn.tid IN (2,3) AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC LIMIT 1 OFFSET 0 in /home/maho/workspace/open/includes/database.pgsql.inc on line 103.
* warning: pg_query(): Query failed: ERROR: invalid input syntax for integer: "" in /home/maho/workspace/open/includes/database.pgsql.inc on line 84.
* user warning: query: SELECT DISTINCT(n.nid), n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moderate, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid LEFT JOIN i18n_node i18n ON n.nid = i18n.nid WHERE (i18n.language ='pl' OR i18n.language ='pl' OR i18n.language ='' OR i18n.language IS NULL) AND n.nid = '' in /home/maho/workspace/open/includes/database.pgsql.inc on line 103.
* warning: pg_query(): Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /home/maho/workspace/open/includes/database.pgsql.inc on line 84.
* user warning: query: SELECT DISTINCT(n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid LEFT JOIN i18n_node i18n ON n.nid = i18n.nid WHERE (i18n.language ='pl' OR i18n.language ='pl' OR i18n.language ='' OR i18n.language IS NULL) AND tn.tid IN (1) AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC LIMIT 1 OFFSET 0 in /home/maho/workspace/open/includes/database.pgsql.inc on line 103.
* warning: pg_query(): Query failed: ERROR: invalid input syntax for integer: "" in /home/maho/workspace/open/includes/database.pgsql.inc on line 84.
* user warning: query: SELECT DISTINCT(n.nid), n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moderate, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid LEFT JOIN i18n_node i18n ON n.nid = i18n.nid WHERE (i18n.language ='pl' OR i18n.language ='pl' OR i18n.language ='' OR i18n.language IS NULL) AND n.nid = '' in /home/maho/workspace/open/includes/database.pgsql.inc on line 103.

gallery counts proper amount of images, but doesn't display them.

Comments

keve’s picture

keve commented
Version: master » 4.7.x-1.x-dev
Priority: Critical » Normal

I do not see how this is connected with TAC. Can you check what function calls this sql query? (w/ devel.module).

Did you try to disbale TAC (taxonomy_access), does this disappear?

There might be an incompatiblity with i18n.
I changed this issue to version 4.7.x

maho’s picture

maho commented

It's connected with TAC in such way, that after installing TAC image gallery stops working, and after uninstalling TAC it works again. So, it's 99% relateded with TAC :).

However, I will try to dig into TAC to find place where query is called.

keve’s picture

keve commented

You wont find this query in TAC. (this might be a query that runs through the hook_db_rewrite_sql)
If you install devel.module, you can list all queries that were run during the php process, in the bottom of the page. It will also tell, wich function called that query.

Can you try disabling i18n, but leaving TAC enabled? What happens?

keve’s picture

keve commented
Title: SQL error when using postgresql » Tac + Image galery + i18n: SQL error when using postgresql

Changed the title

harlan-1’s picture

harlan-1 commented

Hi,

I have encounterd the same problem with TAC and postgres. I'm running 4.7.4 with postgres 8.0.8 and I'm using the category
module but I think it's not related to that.

I think I have seen this error message in a another postgres application. Postgres seems to be very special about what it
accepts in the 'order by' clause in relation to the selected fields, when using 'group by' clauses.

It is also not only related to the TAC module. The error messages showed up with the following modules:
taxonomy_access and simpleaccess. They are gone after deativating the AC-modules again. I think it is a problem
in the node.module code, but I'm no php expert to tell where.

btw. my error messages where reported from line 94 and 113, so somebody must have added 10 lines at the beginning
of database.pgsql.inc. But they look exactly the same.

wedge’s picture

wedge commented

Yes, something's fishy. I have the same problem drupal5+pgsql8.2. I have another issue that i think is related. As soon as I enable the TA module the nodes on my frontpage change order. I guess it's the following query:
SELECT DISTINCT ON (n.nid) n.nid, n.sticky, n.created FROM node n WHERE n.promote = 1 AND n.status = 1 ORDER BY n.nid, n.sticky DESC, n.created DESC LIMIT 10 OFFSET 0 Notice that there is no DESC after ORDER BY n.nid.
I think this might have something to do with the db_rewrite_sql and node_db_rewrite_sql functions which sets distinct and the new db_distinct_field function (http://drupal.org/node/76819).

Notice for instance how the following query from taxonomy_term_count_nodes:
SELECT t.tid, COUNT(n.nid) AS c FROM term_node t INNER JOIN node n ON t.nid = n.nid WHERE n.status = 1 AND n.type = 'image' GROUP BY t.tid
is rewritten to:
SELECT t.tid, COUNT( DISTINCT ON (n.nid) n.nid) AS c FROM term_node t INNER JOIN node n ON t.nid = n.nid WHERE n.status = 1 AND n.type = 'image' GROUP BY t.tid
which is not valid for postgresql.

wedge’s picture

wedge commented

If I remove the $return['distinct'] = 1; line from the node_db_rewrite_sql function in node.module. Both of these issues goes away.

keve’s picture

keve commented
Status: Active » Closed (fixed)

TAC for Drupal 4.6 or 4.7 is no longer supported.
Try using latest 5.x-2.x-dev or 6.x-dev version , the module has been totally rewritten.

If issue remains, reopen it for 5.x-2.x-dev or 6.x-dev version.