ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

The same story with image_gallery_handler_field_gallery_cover.inc

Adding n.sticky, n.created to select list in 215 line of image_gallery_handler_field_gallery_cover.inc fulfils PostgreSQL query correctness. The full function responsible for query is:

function get_cover_node_nid_query() {
    return 'SELECT n.nid, n.sticky, n.created from {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid ' .
        "WHERE ***WHERE_CLAUSE*** AND n.type = 'image' AND n.status = 1 " .
        'ORDER BY ***ORDER_CLAUSE***';
  }

http://www.postgresql.org/docs/8.2/static/sql-select.html#SQL-ORDERBY

> It is also possible to use arbitrary expressions in the ORDER BY clause, including columns that do not appear in the SELECT result list.

Are you guys on an older version of Postgres maybe?

I'm using 8.3.1. The problem is possibly related to the DISTINCT Clause. I've just looked to the Postres DOC you've posted and I can see:

The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).

I'm not a SQL guru, but I guess this might be the cause of the problem.

> # user warning: query: SELECT DISTINCT n.nid FROM drupal_node n INNER JOIN drupal_term_node tn ON n.nid = tn.nid WHERE tn.tid IN (7) AND n.type = 'image' AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC in /var/www/drupal-6.16/sites/all/modules/image/contrib/image_gallery/image_gallery.pages.inc on line 48.

Actually, I can't find this query. There is only one DISTINCT in that file, and it's:

    $count_query = "SELECT COUNT(DISTINCT(n.nid)) FROM {term_node} t INNER JOIN {node} n ON t.nid = n.nid WHERE n.status = 1 AND n.type = 'image' AND t.tid = %d ";

Can you check you're on the latest version please?

On the last dev version problem still exist. I had to additionally add another element to select list: n.created

The whole function is now:

  function get_cover_node_nid_query() {
    return 'SELECT n.nid, n.created, n.sticky from {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid ' .
        "WHERE ***WHERE_CLAUSE*** AND n.type = 'image' AND n.status = 1 " .
        'ORDER BY ***ORDER_CLAUSE***';
  }

Hi! Not need SQL guru for this... just think about it:

SELECT DISTINCT n.nid FROM drupal_node n ORDER BY n.sticky DESC, n.created DESC

Now imagine this table:

nid|sticky
111 |0
112 |1
111| 2
And now ,,execute'' the DISTINCT:

nid|sticky
111| 0, 2
112| 1
See: we have different values in the one row. (of course database server cant do that simply drop the unneeded column, do the distinct, and then cant find the field, referenced in the order)

You can order it on sticky? You can't, because of the different values.

Its not a real possibility, because nid is the primary key of the node, and we order on other node fields, anyway we have modify the query.

We can add the fields to the distinct if the order is realy needed, or remove from order by if not.

Other option is use group by instead of distinct, and do some aggregate (min/max/avg) to the order clause:

SELECT n.nid FROM drupal_node n .. etc .. GROUP by 1 ORDER BY min(n.sticky) DESC, min(n.created) DESC

Sorry, without further information this issue can only be closed as not reproducible.

Feel free to re-open this issue if you want to provide further information. Thanks.

Hi,

In "modules/image/contrib/image_gallery/image_gallery.pages.inc", I have replaced (but didn't verify if it is still working on MySQL)

  switch (variable_get('image_gallery_sort_order', IMAGE_GALLERY_SORT_CREATE_DESC)) {
    case IMAGE_GALLERY_SORT_CREATE_DESC:
      $order = 'ORDER BY n.sticky DESC, n.created DESC';
      break;

    case IMAGE_GALLERY_SORT_CREATE_ASC:
      $order = 'ORDER BY n.sticky DESC, n.created ASC';
      break;

    case IMAGE_GALLERY_SORT_FILENAME:
      $join = "INNER JOIN {image} i ON n.nid = i.nid INNER JOIN {files} f ON i.fid = f.fid";
      $where = "AND f.filename = '%s'";
      $args[] = IMAGE_ORIGINAL;
      $order = "ORDER BY n.sticky DESC, f.filepath ASC";
      break;

    case IMAGE_GALLERY_SORT_TITLE:
      $order = 'ORDER BY n.sticky DESC, n.title ASC';
      break;
  }

with

// 1
$image_fields = 'n.nid, n.sticky, n.created';

  switch (variable_get('image_gallery_sort_order', IMAGE_GALLERY_SORT_CREATE_DESC)) {
    case IMAGE_GALLERY_SORT_CREATE_DESC:
      $order = 'ORDER BY n.sticky DESC, n.created DESC';
      break;

    case IMAGE_GALLERY_SORT_CREATE_ASC:
      $order = 'ORDER BY n.sticky DESC, n.created ASC';
      break;

    case IMAGE_GALLERY_SORT_FILENAME:
      $join = "INNER JOIN {image} i ON n.nid = i.nid INNER JOIN {files} f ON i.fid = f.fid";
      $where = "AND f.filename = '%s'";
      $args[] = IMAGE_ORIGINAL;
      $order = "ORDER BY n.sticky DESC, f.filepath ASC";
// 2
      $image_fields = 'n.nid, n.sticky, f.filepath';
// 2
      break;

    case IMAGE_GALLERY_SORT_TITLE:
      $order = 'ORDER BY n.sticky DESC, n.title ASC';
// 3
      $image_fields = 'n.nid, n.sticky, n.title';
// 3
      break;
  }

$sql = "SELECT n.nid FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid $join AND tn.tid IN (" . implode(',', $descendant_tids) . ") AND n.type = 'image' AND n.status = 1 $where $order";

with

// 4
$sql = "SELECT nid from (SELECT $image_fields FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid $join WHERE tn.tid IN (" . implode(',', $descendant_tids) . ") AND n.type = 'image' AND n.status = 1 $where $order) as a";
// 4

And

 $query = "SELECT n.nid FROM {term_node} t INNER JOIN {node} n ON t.nid = n.nid $join AND n.status = 1 AND n.type = 'image' AND t.tid = %d $where $order";

with

// 5
$query = "SELECT nid from (SELECT $image_fields FROM {term_node} t INNER JOIN {node} n ON t.nid = n.nid $join WHERE n.status = 1 AND n.type = 'image' AND t.tid = %d $where $order) as a";
// 5