Hi, I'm getting a postgreSQL error when using views and tac_lite on the default 'frontpage' view. It looks like the part in question is handled by views though. I know how the SQL should look, but I can't after hours of looking figure out where this select statement is getting formed...

Here's the error:

warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /usr/share/drupal/includes/database.pgsql.inc on line 84.

user warning: query: SELECT DISTINCT(node.nid) FROM node node INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = -1 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'tac_lite') OR (na.gid = 0 AND na.realm = 'tac_lite'))) AND (node.promote = '1') AND (node.status = '1') ORDER BY node.sticky DESC, node.created DESC LIMIT 10 OFFSET 0 in /usr/share/drupal/includes/database.pgsql.inc on line 110.

It should have the node.sticky and node.created in the select statement like this snippet:

SELECT DISTINCT(node.nid), node.sticky, node.created FROM ...

Anyone know where the erroring sql is being formed? There is this cool bit of code... but I added a drupal_set_message to it and it never fired.

  /*
   * Set the base field to be distinct.
   */
  function set_distinct() {
    if (count($this->fields) && substr($this->fields[0], 0, 7) != 'DISTINCT') {
      $field = $this->fields[0];
      $this->fields[0] = "DISTINCT($field)";
      $this->count_field = "DISTINCT($field)";
    }
  }

Other ideas? I've gotta get this working.
Thanks!

Jamie.

Comments

JamieR’s picture

Title: Views 'frontpage' postgreSQL error » Views ORDER BY postgreSQL error

This isn't just a problem on the frontpage view - it's any view that I add an order_by to... help?

merlinofchaos’s picture

Status: Active » Fixed

Sigh, postgres.

I think I've figured out how to fix this right, and it's committed to both branches. These fixes will go out in views-4.7.x-1.1 and views-5.x-1.1-beta

JamieR’s picture

Thank you! I look forward to the update.

gordon’s picture

Status: Fixed » Active

This patch has broken the random sort.

Now using the random sort you get something like

SELECT rand() AS _rand() FROM node node ORDER BY rand()

This is wrong.

fago’s picture

yes, I've also reported this there

merlinofchaos’s picture

Status: Active » Fixed

Marking this back to fixed; the rand() has its own issue above.

Anonymous’s picture

Status: Fixed » Closed (fixed)