PostgreSQL problem: SELECT DISTINCT, ORDER BY

I have no idea what that error even means. Someone with some pgsql knowledge care to help out?

I don't know postgres, but I have used other databases where anything in the ORDER BY must also be in the SELECT list, so maybe it means node.created must be added to the SELECT clause.

add after line 539 :
$fields .= ",".implode(', ', $this->groupby);
$fields .= implode(', ', $this->orderby);
$fields = preg_replace("/DESC/","",$fields);
$fields = preg_replace("/ASC/","",$fields);

In fact postgres needs to have the ordered column in the select to work.
The second problem is that in $this->orderby you get the DESC or ASC after the field name. It should be splitted in two, with $this->orderby that contains field name and a $this->order that would contain ASC or DESC.

I m gonna look to make a real patch for that issue.

This hooks into add_orderby, and calls add_field for each of the field that we ask PostgreSQL to order by. I think that's the correct fix.

Sorry for messing with the title of the issue.

Hi, I'm not sure if I should make a new posting or not but I get a very similar error when I add a "Taxonomy: Term" filter when I'm on the Edit View page.

 warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /orangepics/drupal/drupal-4.7.2/includes/database.pgsql.inc on line 84. 
user warning: query: SELECT DISTINCT(td.tid), td.name, v.name as vocabname FROM term_data td LEFT JOIN vocabulary v ON v.vid = td.vid ORDER BY v.weight, v.name, td.weight, td.name in /orangepics/drupal/drupal-4.7.2/includes/database.pgsql.inc on line 103. 

I tried the above patch but it didn't help my bug. Anyone have a patch to help out? I'm running PostgreSQL 8.1 and Drupal 4.7.2.

The patch I attached only dealt with the query generated by the view module, not the one that are hard-coded in the different view_x.inc files located in the modules/ subdirectory of views :-/

This one also fixes the file view_taxonomy.inc, but I think the others .inc should be reviewed as well. I'm probably going to do this, and attach another patch when I'm done.

According to a quick grep -R on the views directory, every other hardcoded queries using "ORDER BY" should be worki, so the last patch I sent should be all Views need to be fully compatible with PostgreSQL.

Terriffic! Thanks, that patch solved my problem.

Both a mysql and a pgsql installation have been running with this patch for a while, without problems. Given that it also solves a real problem ( it makes Views unusable in pgsql, if you want to sort ) and the patch doesn't seem to break anything, I think it's ready to be commited.

+1 Patch worked for me on PostgreSQL 7.4.7.

Sammy Spets
Synerger
http://www.synerger.com

I have a feeling this is going to break stuff elsewhere when the same field gets added twice, but we'll see what happens.