A problem with PostgreSQL & db_distinct_field() function [#115117]

Greetings,

I've found an issue in database.pgsql.inc, db_distinct_field() function.

When visiting the page with a table&pager populated by views.module there is an error:

* warning: pg_query(): Query failed: ERROR: syntax error at or near "ON" at character 23 in /home/infokves/public_html/includes/database.pgsql.inc on line 94.

* user warning: query: SELECT count(DISTINCT ON (node.nid) node.nid) FROM node node LEFT JOIN node_content_qa node_data_field_question ON node.vid = node_data_field_question.vid LEFT JOIN node_content_qa node_data_field_answer ON node.vid = node_data_field_answer.vid LEFT JOIN node_content_qa node_data_field_answer_adv ON node.vid = node_data_field_answer_adv.vid LEFT JOIN i18n_node i18n ON node.nid = i18n.nid WHERE (i18n.language ='ru' OR i18n.language ='' OR i18n.language IS NULL) AND ( (UPPER(node_data_field_question.field_question_value) LIKE UPPER('%''''%')) AND (UPPER(node_data_field_answer.field_answer_value) LIKE UPPER('%''''%')) AND (node.type IN ('content_qa')) ) in /home/infokves/public_html/includes/database.pgsql.inc on line 113.

Looks like the reason is "count(DISTINCT ON (node.nid) node.nid)". SELECT works correctly if the count statement is changed to ""count(DISTINCT (node.nid))".

A small research shows that your are making

  $field_to_select = 'DISTINCT ON ('. $table .'.'. $field .") $table.$field";

for PostgreSQL and it causes the problem when count select is automatically built for a table pager. I've changed it to the one from MySQL version:

  $field_to_select = 'DISTINCT('. $table .'.'. $field .')';

and it seems to work correctly for PostgreSQL.

I wonder, why did you do DISTINCT ON for PostgreSQL instead of traditional DISTINCT?

Please, give me any feedback ... is it really a defect or I'm missing something and this problem should be fixed in another place?

Thank you.

Comment	File	Size	Author
#5	drupal51_pgsql_fix.patch	1.3 KB	Anonymous (not verified)

Comments

Comment #1

deece commented 12 March 2007 at 13:46

Version:	4.7.x-dev	» 5.1
Priority:	Normal	» Critical

I have confirmed that the same problem exists in Drupal 5.1, with PostgreSQL 8.2.3, and is evident when testing the Views module with paging enabled.

The quoted fix in the parent appears to work.

This is the error I got (and a backtrace, which may or may not be useful):

* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ON" LINE 1: SELECT count( DISTINCT ON (node.nid) node.nid) FROM node nod... ^ in /home/deece/nela/includes/database.pgsql.inc on line 125.
File Line Call
/home/deece/nela/includes/database.pgsql.inc 125 pg_query(Resource: Resource id #7, 'SELECT count( DISTINCT ON (node.nid) node.nid) FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid WHERE (term_node.tid = '29') ')
/home/deece/nela/includes/database.inc 200 _db_query('SELECT count( DISTINCT ON (node.nid) node.nid) FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid WHERE (term_node.tid = '29') ')
/home/deece/nela/includes/pager.inc 72 db_query('SELECT count( DISTINCT ON (node.nid) node.nid) FROM {node} node LEFT JOIN {term_node} term_node ON node.nid = term_node.nid WHERE (term_node.tid = '29') ', (null))
/home/deece/nela/modules/views/views.module 458 pager_query('SELECT DISTINCT ON (node.nid) node.nid FROM {node} node LEFT JOIN {term_node} term_node ON node.nid = term_node.nid WHERE (term_node.tid = '29') ', '10', 0, 'SELECT count( DISTINCT ON (node.nid) node.nid) FROM {node} node LEFT JOIN {term_node} term_node ON node.nid = term_node.nid WHERE (term_node.tid = '29') ', null)
/home/deece/nela/modules/views/views.module 290 views_build_view('page', Object: stdClass, (), '1', '10')
views_view_page('WA_Submissions')
/home/deece/nela/includes/menu.inc 418 call_user_func_array('views_view_page', ('WA_Submissions'))
/home/deece/nela/index.php 15 menu_execute_active_handler()
backtrace
* user warning: query: SELECT count( DISTINCT ON (node.nid) node.nid) FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid WHERE (term_node.tid = '29') in /home/deece/nela/includes/database.pgsql.inc on line 144.
backtrace

Comment #2

Anonymous (not verified) commented 4 May 2007 at 22:21

I can also confirm this issue and fix with PostgreSQL 7.4.16

Comment #3

Anonymous (not verified) commented 10 May 2007 at 13:04

There is an additional problem with the db_distinct_field.

First of all, it seems the inline documentation in database.pgsql.inc is incorrect, as it seems db_distinct_field does apply the order by rewrite regardless of whether or not a distinct was in the query before the rewrite.

The problem is that the way the missing distinct select list values are added to the order by list, destroys any ordering already in the query.

This is particularly problomatic in queries such as this one from the taxonomy module:

$order = 'n.sticky DESC, n.created DESC'
'SELECT DISTINCT(n.nid), n.sticky, n.title, n.created FROM {node} n '. $joins .' WHERE n.status = 1 '. $wheres .' ORDER BY '. $order

which becomes:

SELECT DISTINCT(n.nid), n.sticky, n.title, n.created FROM node n INNER JOIN term_node tn0 ON n.nid = tn0.nid WHERE n.status = 1 AND tn0.tid IN (9) ORDER BY n.nid, n.sticky DESC, n.created DESC LIMIT 10 OFFSET 0

destroying the date ordering.