I receive the following errors on the FAQ page and none of my FAQ nodes show up. I have set up Categories as required and have Categorize questions checked. Category Layout is "Clicking on category opens/hides questions and answers under category". Miscellaneous Layout Settings have "Show FAQ count" and "Only show sub-categories when parent category is selected" checked. Page Layout is set to "Clicking on question opens/hides answer under question". I am on Drupal 5.5 with PostgreSQL.

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ON" LINE 1: SELECT t.tid, COUNT( DISTINCT ON (n.nid) n.nid) AS c FROM te... ^ in /usr/local/apache2/htdocs/corporate_portal/includes/database.pgsql.inc on line 125.
    * user warning: query: 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 = 'faq' GROUP BY t.tid in /usr/local/apache2/htdocs/corporate_portal/includes/database.pgsql.inc on line 144.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ON" LINE 1: SELECT t.tid, COUNT( DISTINCT ON (n.nid) n.nid) AS c FROM te... ^ in /usr/local/apache2/htdocs/corporate_portal/includes/database.pgsql.inc on line 125.
    * user warning: query: 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 = 'faq' GROUP BY t.tid in /usr/local/apache2/htdocs/corporate_portal/includes/database.pgsql.inc on line 144.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ON" LINE 1: SELECT t.tid, COUNT( DISTINCT ON (n.nid) n.nid) AS c FROM te... ^ in /usr/local/apache2/htdocs/corporate_portal/includes/database.pgsql.inc on line 125.
    * user warning: query: 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 = 'faq' GROUP BY t.tid in /usr/local/apache2/htdocs/corporate_portal/includes/database.pgsql.inc on line 144.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ON" LINE 1: SELECT t.tid, COUNT( DISTINCT ON (n.nid) n.nid) AS c FROM te... ^ in /usr/local/apache2/htdocs/corporate_portal/includes/database.pgsql.inc on line 125.
    * user warning: query: 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 = 'faq' GROUP BY t.tid in /usr/local/apache2/htdocs/corporate_portal/includes/database.pgsql.inc on line 144.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ON" LINE 1: SELECT t.tid, COUNT( DISTINCT ON (n.nid) n.nid) AS c FROM te... ^ in /usr/local/apache2/htdocs/corporate_portal/includes/database.pgsql.inc on line 125.
    * user warning: query: 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 = 'faq' GROUP BY t.tid in /usr/local/apache2/htdocs/corporate_portal/includes/database.pgsql.inc on line 144.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ON" LINE 1: SELECT t.tid, COUNT( DISTINCT ON (n.nid) n.nid) AS c FROM te... ^ in /usr/local/apache2/htdocs/corporate_portal/includes/database.pgsql.inc on line 125.
    * user warning: query: 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 = 'faq' GROUP BY t.tid in /usr/local/apache2/htdocs/corporate_portal/includes/database.pgsql.inc on line 144.

Comments

stella’s picture

stella commented

Looks like the sql query is getting rewritten somewhere - original one in the faq.module code is different from that. I'm suspecting it's something to do with using categories. Can you tell me what taxonomy modules you are using?

Thanks,
Stella

WISEOZ’s picture

WISEOZ commented

I have two separate Drupal 5.5 installations and both are running Taxonomy 5.5. This is happening with both sites.

stella’s picture

stella commented
Assigned: Unassigned » stella

It's going to take me a bit of time to set up a test drupal site running postgres. Any chance you could upgrade to Drupal 5.6 on the off chance that it fixes it? Have you just installed the faq module, or was it working previously with older versions of faq / drupal?

Cheers,
Stella

stella’s picture

stella commented
Status: Active » Postponed (maintainer needs more info)

Actually it didn't take me as long to set up a drupal test site with postgres as I thought it would. However, I'm unable to reproduce the issue. I'm running Postgres 8.1, Drupal 5.6 (hence Taxonomy 5.6) and FAQ module 5.x.2.x-dev. Do you have any other taxonomy related modules installed?

Cheers,
Stella

WISEOZ’s picture

WISEOZ commented

Actually, I've taken this step with one of my sites. For my site that has very few contrib modules installed, I get the same results. Contrib modules installed to this site that require Taxonomy are Taxonomy Access Control (enabled), Docs (disabled), and Forum (disabled). Of those modules, the only common one between my two sites is Forum. So, I'm thinking it must be something else. Let me know anything else you'd like me to look at.

stella’s picture

stella commented
Project: Frequently Asked Questions » Taxonomy Access Control
Version: 5.x-2.6 » 5.x-1.1
Status: Postponed (maintainer needs more info) » Active

Ok I enabled the Taxonomy Access Control module on my test drupal site (with postgres database) and I was able to reproduce the error. It doesn't happen on my other drupal test site which uses a mysql database.

The original sql query in the faq module (line 2161) was:

$result = db_query(db_rewrite_sql("SELECT 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 = 'faq' AND t.tid = '%d' ", "n", "nid"), $term->tid);

The error I get after enabling the Taxonomy Access Control module is:

# warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ON" LINE 1: SELECT t.tid, COUNT( DISTINCT ON (n.nid) n.nid) AS c FROM te... ^ in /var/www/drupal_5.7_20080212/html/includes/database.pgsql.inc on line 125.
# user warning: query: 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 = 'faq' GROUP BY t.tid in /var/www/drupal_5.7_20080212/html/includes/database.pgsql.inc on line 144.

So it looks like the sql 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 = 'faq' GROUP BY t.tid;

I think it should be:

select t.tid, count(distinct (n.nid)) as c from term_node t inner join node n on t.nid = n.nid where n.status=1 and n.type='faq' group by t.tid;

Cheers,
Stella

stella’s picture

stella commented

Oh I also tested it with the 5.x-2.x version of the taxonomy access control module and the same error occurred.

Cheers,
Stella

WISEOZ’s picture

WISEOZ commented

Thanks for taking a closer look. I see that you assigned this over to the Taxonomy Access Control queue.

I disabled and uninstalled TAC on one of my sites and still get the same error originally mentioned.

On my other site, I am getting this error on the /faq page:


* warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 125.
* user warning: query: SELECT DISTINCT (n.nid), if((w.weight IS NULL), 0, w.weight) as weight FROM node n LEFT JOIN faq_weights w ON w.nid = n.nid WHERE n.type='faq' AND n.status = 1 AND (w.tid = 0 OR w.tid IS NULL) ORDER BY n.nid, weight, n.sticky DESC, n.created DESC in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 144.

stella’s picture

stella commented
Project: Taxonomy Access Control » Frequently Asked Questions
Version: 5.x-1.1 » 5.x-2.6
Status: Active » Fixed

Hi WiSeOz,

That's a separate issue. Again it's another module rewriting the sql query (faq module doesn't use DISTINCT anywhere). However a sql containing a SELECT DISTINCT with an ORDER BY needs each "order by" field to be also in the select list. I've updated the faq.module so it now includes all order by fields in the select list in all sql queries just in case the queries are ever rewritten to contain a DISTINCT by another module again.

Anyway, try out the latest dev version of the faq module to see if it fixes the error in comment #8.

As for the original error, more investigation suggests that it's not actually the TAC module - though i'm still not sure why it only started happening once i enabled that module. It no longer occurs now that it's disabled. Strange. Anyway, this is a known issue with drupal core, see #128846 for more details. Sorry I can't be of more help.

Marking as fixed because (a) the first issue is actually a drupal core problem and (b) should now work with the latest dev version.

Cheers,
Stella

WISEOZ’s picture

WISEOZ commented

Hello Stella,

You can consider this closed! Your latest version of FAQ fixed the error reported in #8 and the patch available at node you mentioned fixed the original errors reported on my other site. Thanks so much for your help! You really stuck it out with me. Feel free to call on me for PostgreSQL testing of this module as you continue to make enhancements.

stella’s picture

stella commented

Released in FAQ 5.x-2.7 and FAQ 6.x-1.3.

Cheers,
Stella

Anonymous’s picture

Anonymous (not verified) commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.