language_list() SQL contain backtick and breaks PostgreSQL [#1741068]

PostgreSQL 9.14
PHP 5.3.15 and PHP 5.4.5
Apache 2.2.x

An AJAX HTTP error occurred. HTTP Result Code: 200 Debugging information follows. Path: http://d8.dev/core/install.php?langcode=nb&profile=standard&id=1&op=do_nojs&op=do StatusText: OK ResponseText: User account | Drupal @import url("http://d8.dev/core/modules/system/system.theme.css?0"); @import url("http://d8.dev/core/modules/system/system.base.css?0"); @import url("http://d8.dev/core/modules/user/user.css?0"); @import url("http://d8.dev/core/modules/system/system.admin.css?0"); @import url("http://d8.dev/core/modules/system/system.maintenance.css?0"); @import url("http://d8.dev/core/themes/seven/reset.css?0"); @import url("http://d8.dev/core/themes/seven/style.css?0"); User account Installation tasksVelg språk(done)Velg profil(done)Sjekk forhåndskrav(done)Sett opp database(done)Installeringsprofil(aktiv)Sett opp oversettelserSett opp nettstedetFullfører oversettelserFullført SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "`" LINE 1: SELECT *, 0 as `default` FROM language ORDER BY weight ASC, ... ^
Attached screenshot shows the same. Installing on MariaDB and sqlite works as expected.

Comment	File	Size	Author
#7	d8-1741068_pg_language_list-7.patch	821 bytes	steinmb
#7
#4	d8-1741068_pg_language_list.patch	840 bytes	steinmb
#4
	pg_inst_lang.jpg	106.15 KB	steinmb

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

steinmb CreditAttribution: steinmb commented 20 August 2012 at 14:25

Mental note, language_list() have postgres problems. This query is the one that fails.

<?php
$languages = db_query('SELECT *, 0 as `default` FROM {language} ORDER BY weight ASC, name ASC')->fetchAllAssoc('langcode', PDO::FETCH_ASSOC);
?>

Comment #2

Berdir

German

Switzerland

CreditAttribution: Berdir commented 20 August 2012 at 14:51

The backticks in there are clearly wrong. Should be enough to simply remove them, they aren't necessary.

Comment #3

steinmb CreditAttribution: steinmb commented 20 August 2012 at 15:46

Title:	Installing on other languages then english blows up with Postgres	» language_list() SQL contain backtick and breaks PostgreSQL
Component:	install system	» database system

The table contain a reserved SQL name (http://drupal.org/node/141051) so Mysql needs those backticks.
This issue discuss the problem: #1426084: Provide backtick escaping for MySQL in DB abstraction layer

Comment #4

steinmb CreditAttribution: steinmb commented 20 August 2012 at 16:18

Status:

Active

» Needs review

File	Size
d8-1741068_pg_language_list.patch	840 bytes

Renaming the alias to a not reserved name. Tested on MariaDB, sqlite and postgres.

Comment #5

Berdir

German

Switzerland

CreditAttribution: Berdir commented 20 August 2012 at 21:18

Yes, but I guess we need need to change the code there as well that relies on this? Or remove it from the SQL completely and just enforce default => 0 in a foreach loop? Might actually be the easiest way..

Comment #6

Damien Tournoud CreditAttribution: Damien Tournoud commented 21 August 2012 at 10:22

Status:

Needs review

» Needs work

      $languages = db_query('SELECT *, 0 as `default` FROM {language} ORDER BY weight ASC, name ASC')->fetchAllAssoc('langcode', PDO::FETCH_ASSOC);

      // Initialize default property so callers have an easy reference and can
      // save the same object without data loss.
      foreach ($languages as $langcode => $info) {
        $info['default'] = ($langcode == $default->langcode);
        $languages[$langcode] = new Language($info);
      }

Just remove the default column, it is initialized just below. The query should just be SELECT * FROM {language} ORDER BY weight ASC, name ASC.