Dbal: DBAL-127: PostgreSQL: Quote "User" because it's a reserved word

[doctrinebot]

Jira issue originally created by user felicitus:

I have an entity "User" in D2-ORM which works fine on MySQL. However, when I tried to create the schema on PostgreSQL 9.0, it failed with the following error:

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "User"
LINE 1: CREATE TABLE User (id INT NOT NULL, username VARCHAR(50) NOT...
^' in /usr/share/php/Doctrine/DBAL/Connection.php:577

The generated statement caused this was: CREATE TABLE User (id INT NOT NULL, username VARCHAR(50) NOT NULL, password VARCHAR(32) NOT NULL, admin BOOLEAN NOT NULL, PRIMARY KEY(id))

"User" needs to be quoted in order to work (I tested against PostgreSQL 9.0).

[doctrinebot]

Comment created by @beberlei:

Doctrine does not do auto-escaping.

Please read the docs on quoting reserved words: http://www.doctrine-project.org/docs/orm/2.0/en/reference/basic-mapping.html#quoting-reserved-words

[doctrinebot]

Comment created by felicitus:

It's not that easy.

I can, of course, escape the table in the entity, e.g.

/** @Entity @Table(name="User") */

However, when using the SchemaTool, it will fail on the 2nd run because it tries to:

DROP TABLE User

That fails, of course.

[doctrinebot]

Issue was closed with resolution "Invalid"

[ihorsamusenko]

how are things so far with this issue ?

[deeky666]

@samusenkoiv this issue is old and should be fixed already considering that USER is registered as reserved keyword in Doctrine and the platform explicitly quoting table names in getCreateTableSQL(). What issue are you having?

[ihorsamusenko]

I've already found the way to define user table:
table: "user"

Thanks for your attention.

[deeky666]

@samusenkoiv you can do that by explicit quoting for sure but DBAL supports auto quoting reserved keywords. Can you please test if it works without quotes? If not, which statement(s) are failing? Because if this still is a bug, it needs our attention. Also please tell which DBAL version you are using. Thanks for your help!

[ihorsamusenko]

You're right, I tried it with:
table: user
table: "user"
table: 'user'
And everything worked well, I probably made a mistake.
Thank for a fast feedback.

[deeky666]

@samusenkoiv you're welcome. Thanks for investigating. Closing again as fixed, but as I do not know when it was fixes, no milestone attached.

[ihorsamusenko]

Run into this issue again.
When using Doctrine\ORM\Tools\Pagination\Paginator with Doctrine\ORM\EntityRepository .
I do something like that

$queryBuilder = $repository->createQueryBuilder();
$paginator = new Paginator($queryBuilder);
count($paginator); // here we go

I got this error

An exception occurred while executing 'SELECT count(DISTINCT u0_.id) AS sclr_0 FROM user u0_':
SQLSTATE[42703]: Undefined column: 7 ERROR: column u0_.id does not exist
LINE 1: SELECT count(DISTINCT u0_.id) AS sclr_0 FROM user u0_

It is solvable by specifying @ORM/Table("user") in User class

[ssgaur]

This issue still persist in Symfony 3.1.
I tried all the way as mentioned above
/**

• User
  *
• @ORM\Table(name="user") --> This also didn't work.
• @ORM\Entity(repositoryClass="AppBundle\RepositoryUserRepository")
  */

I am using postgres version 9.5.3 . Please look into it.

[ggarri]

To me using @ORM\Table(name="user") it works totally fine on Symfony 2.8 and Doctrine v2.5.6.

Where I am having issues is, when I try to use group as table name.

/**
 * Group
 *
 * @ORM\Table(name=" `group` ")
 * @ORM\Entity
 */
class Group{

It is identified as Lexer::T_GROUP and it goes to the SyntaxException because it is not any valid lookaheadType from _FROM_.

vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php::SelectExpression()

[Ocramius]

That's a different issue: you can't really use a GROUP field in DQL anyway, and DQL doesn't support quoting properties anyway. That's way before the DBAL is ever hit.

[ggarri]

So, is there not way I can use DQL if I have a table named Group ?

[Ocramius]

Yes, by simply using a namespaced entity for that. Unsure if it works for fields though (probably not)

[ggarri]

Yep, you are right, problem is actually the attribute name, I cannot have group as attribute name. Thanks for the help :)

[Kleinkind]

I'm still facing this Issue using symfony 4.0.1 & postgres 10.1. Am I missing something or is this still a problem?

Changing the Method Doctrine\ORM\Mapping\DefaultQuoteStrategy::getTableName to rely on the Platform-Implementation in order to decide, whether escaping the Identifier via $platform->quoteIdentifier(...) is necessary or not, solved the problem for me (of course changing vendors isn't a solution, but helps narrowing the problem)

Furthermore there is a TODO on this Method: Table names should be computed in DBAL depending on the platform which leads me to think the Implementation is leaking some Logic to rely on the specific Platform to escape specific keywords.

Steps to reproduce:

• create a user-entity:

/**
 * @ORM\Table
 * @ORM\Entity
 */
class User
{ ... }

• use QueryBuilder to query users:

$queryBuilder
      ->select('entity')
      ->from(User::class, 'entity')
      ->getQuery()
      ->getArrayResult();

Result:

SQLSTATE[42703]: Undefined column: 7 ERROR: column u0_.username does not exist
LINE 1: SELECT u0_.username AS username_0, u0_.id AS id_1 FROM user ...

[ihorsamusenko]

@Kleinkind I have the following annotation:

@ORM\Table("`user`")
and have no problems

[Kleinkind]

Yes, that works, but that can't (or at least shouldn't) be the solution, right?

edit: The specific implementation provides the necessary logic to decide, whether escaping is necessary, so why don't use it? (as stated in the TODO)

[Kleinkind]

Ok, maybe I was a little bit harsh on this, as it is explicitly mentioned in the Symfony- & Doctrine-Documentation

• https://symfony.com/doc/current/doctrine.html#doctrine-adding-mapping
• http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/basic-mapping.html#quoting-reserved-words

But as it seems to be a common pitfall, what is the reason not to rely on the platform to escape these keywords?

[er1z]

Any news on this issue?

[Ocramius]

The reason not to escape is "caSeSeNSITIviTY", which doesn't work consistently across platforms when quoting. doctrine/orm 3.x will quote by default and introduce that BC break.

Locking here.