Problem/Motivation

The NodeSearch::updateIndex() and node_update_index() db_query_range() queries fail on PostgreSQL because order by columns must appear in the select list. This may also effect SQLite.

PDOException: SQLSTATE[42P10]: Invalid column reference: 7 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 1: ...id WHERE d.sid IS NULL OR d.reindex <> 0 ORDER BY d.reindex ... ^: SELECT DISTINCT n.nid FROM {node} n LEFT JOIN {search_dataset} d ON d.type = :type AND d.sid = n.nid WHERE d.sid IS NULL OR d.reindex <> 0 ORDER BY d.reindex ASC, n.nid ASC LIMIT 100 OFFSET 0; Array ( [:type] => node_search ) in Drupal\node\Plugin\Search\NodeSearch->updateIndex() (line 293 of core/modules/node/lib/Drupal/node/Plugin/Search/NodeSearch.php).

Proposed resolution

Pending

Remaining tasks

  • Come up with a solution. Would adding d.reindex column to SELECT cause any performance issues?
  • Write a patch
  • Backport to Drupal 7

User interface changes

None

API changes

None?

Original report by burningdog

Original issue report from #2001350: [meta] Drupal cannot be installed on PostgreSQL

I can't comment on the patch, because I don't understand the code, but after applying it installation works for me.

Postgres: 9.2.4
PHP: 5.3.27
Mac OS 10.6.8

Thank you! The only error I have in the drupal error log is generated by cron at http://drupal8.local/core/install.php?langcode=en&profile=standard and reads:

See above

Comments

mradcliffe’s picture

Title: NodeSearch::updateIndex does not include order by columns in select » NodeSearch::updateIndex does not include order by column in select

Removed plural in title.

jhodgdon’s picture

Let's just do this. If we need it for PostgreSQL, we need it, and I don't think it will cause any performance issues, because MySQL is obviously reading that column too if it is being used by an OrderBy.

xjm’s picture

Component: node.module » node system
Issue summary: View changes

(Merging "node system" and "node.module" components for 8.x; disregard.)

jhodgdon’s picture

Component: node system » search.module

Huh. The node.system component doesn't exist now. xjm it looks like this went the wrong way?

Anyway, we normally put all search-related stuff into search.module component.

jhodgdon’s picture

Version: 8.x-dev » 7.x-dev
Priority: Normal » Major
Issue tags: -Needs backport to D7, -Testbot environments

Things being broken on PostgreSQL should be considered Major issues.

However, in this case I am unable to reproduce this issue in a clean new 8.x PostgreSQL install. I am able to index content, and with the patch on #2158339: Search results page broken on PostgreSQL, node search works. Also, the Search module tests are running and seem to be passing so far (with that patch added). And looking at the query that is currently in NodeSearch::updateIndex():

    $result = $this->database->queryRange("SELECT DISTINCT n.nid, d.reindex FROM {node} n LEFT JOIN {search_dataset} d ON d.type = :type AND d.sid = n.nid WHERE d.sid IS NULL OR d.reindex <> 0 ORDER BY d.reindex ASC, n.nid ASC", 0, $limit, array(':type' => $this->getPluginId()), array('target' => 'slave'));

It has an ORDER BY d.reindex, and this field is in the SELECT list, so I think for whatever reason, this bug was fixed in 8.

So I'm moving this issue to 7 for evaluation... The query there is in node.module :: node_update_index() and looks like it would still have this problem:

  $result = db_query_range("SELECT n.nid FROM {node} n LEFT JOIN {search_dataset} d ON d.type = 'node' AND d.sid = n.nid WHERE d.sid IS NULL OR d.reindex <> 0 ORDER BY d.reindex ASC, n.nid ASC", 0, $limit, array(), array('target' => 'slave'));

Here there is an ORDER BY d.reindex but d.reindex is not in the SELECT fields list.

Should be an easy fix to make... probably just need to add d.reindex to the SELECT part of the query?

poker10’s picture

Status: Active » Closed (works as designed)

This bug is not present in D7, because SELECT DISTINCT is not used there. In case of standard SELECT, order by arguments does not need to be in the select clause.

So this D7 code is fine and working:

$result = db_query_range("SELECT n.nid FROM {node} n LEFT JOIN {search_dataset} d ON d.type = 'node' AND d.sid = n.nid WHERE d.sid IS NULL OR d.reindex <> 0 ORDER BY d.reindex ASC, n.nid ASC", 0, $limit, array(), array('target' => 'slave'));

D8 code was fixed by: #2158229: Site search indexing broken on PostgreSQL. Therefore I think this can be closed.