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?
Related Issues
- #2001350: [meta] Drupal cannot be installed on PostgreSQL
- #2003482: Convert hook_search_info to plugin system
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
Comment #1
mradcliffeRemoved plural in title.
Comment #2
jhodgdonLet'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.
Comment #3
xjm(Merging "node system" and "node.module" components for 8.x; disregard.)
Comment #4
jhodgdonHuh. 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.
Comment #5
jhodgdonThings 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():
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:
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?
Comment #6
poker10 CreditAttribution: poker10 at ActivIT s.r.o. commentedThis 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:
D8 code was fixed by: #2158229: Site search indexing broken on PostgreSQL. Therefore I think this can be closed.