doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-24 06:52:09 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

DDC-1695: SQLs for PostgreSQL case sensite tables/fields are wrongly generated #2124

New Issue
Closed
opened 2026-01-22 13:41:45 +01:00 by admin · 12 comments
admin commented 2026-01-22 13:41:45 +01:00
Owner
Copy Link

Originally created by @doctrinebot on GitHub (Mar 9, 2012).

Originally assigned to: @Ocramius on GitHub.

Jira issue originally created by user ignaciolarranaga:

The SQLs for case sensitive schemas in postgreSQL are wronly generated.

Example:
Schema:

CREATE TABLE "News" (
  "IdNews" serial NOT NULL,
  "IdUser" bigint NOT NULL,
  "IdLanguage" integer NOT NULL,
  "IdCondition" integer,
  "IdHealthProvider" integer,
  "IdSpeciality" integer,
  "IdMedicineType" integer,
  "IdTreatment" integer,
  "Title" character varying,
  "SmallText" character varying,
  "LongText" character varying,
  "PublishDate" timestamp with time zone,
  "IdxNews" tsvector,
  "Highlight" boolean NOT NULL DEFAULT false,
  "Order" integer NOT NULL DEFAULT 0,
  "Deleted" boolean NOT NULL DEFAULT false,
  "Active" boolean NOT NULL DEFAULT false,
  "UpdateToHighlighted" boolean DEFAULT false,
  CONSTRAINT "News_pkey" PRIMARY KEY ("IdNews" ));

Object (I added quotes trying to generate the SQLs quoted.:

<?php

namespace GlobalTreatments\ApplicationBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/****
 * @ORM\Table(name="""News""")
 * @ORM\Entity
 */
class News
{
    /****
     * @var integer $idNews
     *
     * @ORM\Column(name="""IdNews""", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="SEQUENCE")
     * @ORM\SequenceGenerator(sequenceName="""News*IdNews*seq""", allocationSize="1", initialValue="1")
     */
    private $idNews;

    /****
     * @var bigint $iduser
     *
     * @ORM\Column(name="""IdUser""", type="bigint", nullable=false)
     */
    private $idUser;

    /****
     * @var integer $idLanguage
     *
     * @ORM\Column(name="""IdLanguage""", type="integer", nullable=false)
     */
    private $idLanguage;

    /****
     * @var integer $idCondition
     *
     * @ORM\Column(name="""IdCondition""", type="integer", nullable=true)
     */
    private $idCondition;

    /****
     * @var integer $idHealthProvider
     *
     * @ORM\Column(name="""IdHealthProvider""", type="integer", nullable=true)
     */
    private $idHealthProvider;

    /****
     * @var integer $idSpeciality
     *
     * @ORM\Column(name="""IdSpeciality""", type="integer", nullable=true)
     */
    private $idSpeciality;

    /****
     * @var integer $idMedicineType
     *
     * @ORM\Column(name="""IdMedicineType""", type="integer", nullable=true)
     */
    private $idMedicineType;

    /****
     * @var integer $idTreatment
     *
     * @ORM\Column(name="""IdTreatment""", type="integer", nullable=true)
     */
    private $idTreatment;

    /****
     * @var string $title
     *
     * @ORM\Column(name="""Title""", type="string", nullable=true)
     */
    private $title;

    /****
     * @var string $smallText
     *
     * @ORM\Column(name="""SmallText""", type="string", nullable=true)
     */
    private $smallText;

    /****
     * @var string $longText
     *
     * @ORM\Column(name="""LongText""", type="string", nullable=true)
     */
    private $longText;

    /****
     * @var datetimetz $publishDate
     *
     * @ORM\Column(name="""PublishDate""", type="datetimetz", nullable=true)
     */
    private $publishDate;

    /****
     * @var tsvector $idxNews
     *
     * @ORM\Column(name="""IdxNews""", type="tsvector", nullable=true)
     */
    private $idxNews;

    /****
     * @var boolean $highlight
     *
     * @ORM\Column(name="""Highlight""", type="boolean", nullable=false)
     */
    private $highlight;

    /****
     * @var integer $order
     *
     * @ORM\Column(name="""Order""", type="integer", nullable=false)
     */
    private $order;

    /****
     * @var boolean $deleted
     *
     * @ORM\Column(name="""Deleted""", type="boolean", nullable=false)
     */
    private $deleted;

    /****
     * @var boolean $active
     *
     * @ORM\Column(name="""Active""", type="boolean", nullable=false)
     */
    private $active;

    /****
     * @var boolean $updateToHighlighted
     *
     * @ORM\Column(name="""UpdateToHighlighted""", type="boolean", nullable=true)
     */
    private $updateToHighlighted;

    /****
     * @var condition
     *
     * @ORM\ManyToOne(targetEntity="Condition")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="""IdCondition""", referencedColumnName="""IdCondition""")
     * })
     */
    private $condition;

    /****
     * @var healthProvider
     *
     * @ORM\ManyToOne(targetEntity="HealthProvider")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="""IdHealthProvider""", referencedColumnName="""IdHealthProvider""")
     * })
     */
    private $healthProvider;

    /****
     * @var language
     *
     * @ORM\ManyToOne(targetEntity="Language")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="""IdLanguage""", referencedColumnName="""IdLanguage""")
     * })
     */
    private $language;

    /****
     * @var medicineType
     *
     * @ORM\ManyToOne(targetEntity="MedicineType")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="""IdMedicineType""", referencedColumnName="""IdMedicineType""")
     * })
     */
    private $medicineType;

    /****
     * @var speciality
     *
     * @ORM\ManyToOne(targetEntity="Speciality")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="""IdSpeciality""", referencedColumnName="""IdSpeciality""")
     * })
     */
    private $speciality;

    /****
     * @var treatment
     *
     * @ORM\ManyToOne(targetEntity="Treatment")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="""IdTreatment""", referencedColumnName="""IdTreatment""")
     * })
     */
    private $treatment;

    /****
     * @var user
     *
     * @ORM\ManyToOne(targetEntity="User")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="""IdUser""", referencedColumnName="""IdUser""")
     * })
     */
    private $user;

    ....

}

DQL:

'SELECT n.smallText, n.publishDate ' .
'FROM News n ' .
    'INNER JOIN n.language la ' .
'WHERE la.languageCode = :languageCode ' .
'ORDER BY n.publishDate DESC'

Generated SQL:

SELECT "0*."SmallText" AS "SmallText"0, "0_."PublishDate" AS "PublishDate"1 FROM "News" "0_ INNER JOIN "Language" "1_ ON "0_."IdLanguage" = "1_."IdLanguage" WHERE "1_."LanguageCode" = ? ORDER BY "0*."PublishDate" DESC LIMIT 6

Notice there are unmattched " in the SQL.

Originally created by @doctrinebot on GitHub (Mar 9, 2012). Originally assigned to: @Ocramius on GitHub. Jira issue originally created by user ignaciolarranaga: The SQLs for case sensitive schemas in postgreSQL are wronly generated. Example: Schema: ``` CREATE TABLE "News" ( "IdNews" serial NOT NULL, "IdUser" bigint NOT NULL, "IdLanguage" integer NOT NULL, "IdCondition" integer, "IdHealthProvider" integer, "IdSpeciality" integer, "IdMedicineType" integer, "IdTreatment" integer, "Title" character varying, "SmallText" character varying, "LongText" character varying, "PublishDate" timestamp with time zone, "IdxNews" tsvector, "Highlight" boolean NOT NULL DEFAULT false, "Order" integer NOT NULL DEFAULT 0, "Deleted" boolean NOT NULL DEFAULT false, "Active" boolean NOT NULL DEFAULT false, "UpdateToHighlighted" boolean DEFAULT false, CONSTRAINT "News_pkey" PRIMARY KEY ("IdNews" )); ``` Object (I added quotes trying to generate the SQLs quoted.: ``` <?php namespace GlobalTreatments\ApplicationBundle\Entity; use Doctrine\ORM\Mapping as ORM; /**** * @ORM\Table(name="""News""") * @ORM\Entity */ class News { /**** * @var integer $idNews * * @ORM\Column(name="""IdNews""", type="integer", nullable=false) * @ORM\Id * @ORM\GeneratedValue(strategy="SEQUENCE") * @ORM\SequenceGenerator(sequenceName="""News*IdNews*seq""", allocationSize="1", initialValue="1") */ private $idNews; /**** * @var bigint $iduser * * @ORM\Column(name="""IdUser""", type="bigint", nullable=false) */ private $idUser; /**** * @var integer $idLanguage * * @ORM\Column(name="""IdLanguage""", type="integer", nullable=false) */ private $idLanguage; /**** * @var integer $idCondition * * @ORM\Column(name="""IdCondition""", type="integer", nullable=true) */ private $idCondition; /**** * @var integer $idHealthProvider * * @ORM\Column(name="""IdHealthProvider""", type="integer", nullable=true) */ private $idHealthProvider; /**** * @var integer $idSpeciality * * @ORM\Column(name="""IdSpeciality""", type="integer", nullable=true) */ private $idSpeciality; /**** * @var integer $idMedicineType * * @ORM\Column(name="""IdMedicineType""", type="integer", nullable=true) */ private $idMedicineType; /**** * @var integer $idTreatment * * @ORM\Column(name="""IdTreatment""", type="integer", nullable=true) */ private $idTreatment; /**** * @var string $title * * @ORM\Column(name="""Title""", type="string", nullable=true) */ private $title; /**** * @var string $smallText * * @ORM\Column(name="""SmallText""", type="string", nullable=true) */ private $smallText; /**** * @var string $longText * * @ORM\Column(name="""LongText""", type="string", nullable=true) */ private $longText; /**** * @var datetimetz $publishDate * * @ORM\Column(name="""PublishDate""", type="datetimetz", nullable=true) */ private $publishDate; /**** * @var tsvector $idxNews * * @ORM\Column(name="""IdxNews""", type="tsvector", nullable=true) */ private $idxNews; /**** * @var boolean $highlight * * @ORM\Column(name="""Highlight""", type="boolean", nullable=false) */ private $highlight; /**** * @var integer $order * * @ORM\Column(name="""Order""", type="integer", nullable=false) */ private $order; /**** * @var boolean $deleted * * @ORM\Column(name="""Deleted""", type="boolean", nullable=false) */ private $deleted; /**** * @var boolean $active * * @ORM\Column(name="""Active""", type="boolean", nullable=false) */ private $active; /**** * @var boolean $updateToHighlighted * * @ORM\Column(name="""UpdateToHighlighted""", type="boolean", nullable=true) */ private $updateToHighlighted; /**** * @var condition * * @ORM\ManyToOne(targetEntity="Condition") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="""IdCondition""", referencedColumnName="""IdCondition""") * }) */ private $condition; /**** * @var healthProvider * * @ORM\ManyToOne(targetEntity="HealthProvider") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="""IdHealthProvider""", referencedColumnName="""IdHealthProvider""") * }) */ private $healthProvider; /**** * @var language * * @ORM\ManyToOne(targetEntity="Language") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="""IdLanguage""", referencedColumnName="""IdLanguage""") * }) */ private $language; /**** * @var medicineType * * @ORM\ManyToOne(targetEntity="MedicineType") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="""IdMedicineType""", referencedColumnName="""IdMedicineType""") * }) */ private $medicineType; /**** * @var speciality * * @ORM\ManyToOne(targetEntity="Speciality") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="""IdSpeciality""", referencedColumnName="""IdSpeciality""") * }) */ private $speciality; /**** * @var treatment * * @ORM\ManyToOne(targetEntity="Treatment") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="""IdTreatment""", referencedColumnName="""IdTreatment""") * }) */ private $treatment; /**** * @var user * * @ORM\ManyToOne(targetEntity="User") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="""IdUser""", referencedColumnName="""IdUser""") * }) */ private $user; .... } ``` DQL: ``` 'SELECT n.smallText, n.publishDate ' . 'FROM News n ' . 'INNER JOIN n.language la ' . 'WHERE la.languageCode = :languageCode ' . 'ORDER BY n.publishDate DESC' ``` Generated SQL: ``` SELECT "0*."SmallText" AS "SmallText"0, "0_."PublishDate" AS "PublishDate"1 FROM "News" "0_ INNER JOIN "Language" "1_ ON "0_."IdLanguage" = "1_."IdLanguage" WHERE "1_."LanguageCode" = ? ORDER BY "0*."PublishDate" DESC LIMIT 6 ``` Notice there are unmattched " in the SQL.
admin added the BugDuplicate labels 2026-01-22 13:41:45 +01:00
admin closed this issue 2026-01-22 13:41:45 +01:00
admin commented 2026-01-22 13:41:48 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Mar 9, 2012):

Comment created by ignaciolarranaga:

If there is another approach to specify the table/column names are case sensitive in PGSQL please let me know.

@doctrinebot commented on GitHub (Mar 9, 2012): Comment created by ignaciolarranaga: If there is another approach to specify the table/column names are case sensitive in PGSQL please let me know.
admin commented 2026-01-22 13:41:51 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Mar 9, 2012):

Comment created by ignaciolarranaga:

Just to comment. I also tried the normal quoting.

Example: @ORM\Column(name="IdNews", type="integer", nullable=false)

And does not work too because of the same reason.

@doctrinebot commented on GitHub (Mar 9, 2012): Comment created by ignaciolarranaga: Just to comment. I also tried the normal quoting. Example: @ORM\Column(name="`IdNews`", type="integer", nullable=false) And does not work too because of the same reason.
admin commented 2026-01-22 13:41:51 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Mar 9, 2012):

Comment created by ignaciolarranaga:

Hi, I generate this patch and seems to be working for me (at least what I'm testing right now).

I used ´ to quote tables and single attributes (not relationships) and the SQLs are correctly generated.

@doctrinebot commented on GitHub (Mar 9, 2012): Comment created by ignaciolarranaga: Hi, I generate this patch and seems to be working for me (at least what I'm testing right now). I used ´ to quote tables and single attributes (not relationships) and the SQLs are correctly generated.
admin commented 2026-01-22 13:41:51 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Mar 9, 2012):

Comment created by ignaciolarranaga:

Adding a new patch for another files I need to change.

@doctrinebot commented on GitHub (Mar 9, 2012): Comment created by ignaciolarranaga: Adding a new patch for another files I need to change.
admin commented 2026-01-22 13:41:53 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Mar 11, 2012):

Comment created by @beberlei:

Formatted code

@doctrinebot commented on GitHub (Mar 11, 2012): Comment created by @beberlei: Formatted code
admin commented 2026-01-22 13:41:53 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Mar 11, 2012):

Comment created by @beberlei:

Fixed and merged into 2.1.x and 2.2 branches

@doctrinebot commented on GitHub (Mar 11, 2012): Comment created by @beberlei: Fixed and merged into 2.1.x and 2.2 branches
admin commented 2026-01-22 13:41:53 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 5, 2014):

Comment created by julian:

This bug still unresolved for version 2.3.6-DEV included in Symfony 2.3.18.

The patches does not work with this version.

Generated query:

SELECT "0_.id AS id0, "0_."verFirmware" AS verfirmware1, "0_."idEstacion" AS idestacion2, "0_."fechaHora" AS fechahora3, "0_.global AS global4, "0_.directa AS directa5, "0_.difusa AS difusa6, "0_."tempSensDirecta" AS tempsensdirecta7, "0_.vbat1 AS vbat18, "0_.vbat2 AS vbat29, "0_.flags AS flags10, "0_."Estacion" AS estacion11 FROM "RegistroRS" "0_ WHERE "0_."idEstacion" = '1' AND "0_."fechaHora" >= '2014-02-01 03:00:00' AND "0_."fechaHora" <= '2014-08-01 03:00:00'

Notice there are unmattched " in the SQL.

@doctrinebot commented on GitHub (Aug 5, 2014): Comment created by julian: This bug still unresolved for version 2.3.6-DEV included in Symfony 2.3.18. The patches does not work with this version. Generated query: SELECT "0_.id AS id0, "0_."verFirmware" AS verfirmware1, "0_."idEstacion" AS idestacion2, "0_."fechaHora" AS fechahora3, "0_.global AS global4, "0_.directa AS directa5, "0_.difusa AS difusa6, "0_."tempSensDirecta" AS tempsensdirecta7, "0_.vbat1 AS vbat18, "0_.vbat2 AS vbat29, "0_.flags AS flags10, "0_."Estacion" AS estacion11 FROM "RegistroRS" "0_ WHERE "0_."idEstacion" = '1' AND "0_."fechaHora" >= '2014-02-01 03:00:00' AND "0_."fechaHora" <= '2014-08-01 03:00:00' Notice there are unmattched " in the SQL.
admin commented 2026-01-22 13:41:55 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 5, 2014):

Comment created by @ocramius:

[~julian] why are you using 2.3.6-DEV? Shouldn't you use a stable version? Is the bug reproducible also with later versions?

@doctrinebot commented on GitHub (Aug 5, 2014): Comment created by @ocramius: [~julian] why are you using `2.3.6-DEV`? Shouldn't you use a stable version? Is the bug reproducible also with later versions?
admin commented 2026-01-22 13:41:55 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 6, 2014):

Comment created by julian:

After changing to version 2.4.2 the bug still there. The same SQL query was generated.

@doctrinebot commented on GitHub (Aug 6, 2014): Comment created by julian: After changing to version 2.4.2 the bug still there. The same SQL query was generated.
admin commented 2026-01-22 13:41:55 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 6, 2014):

Comment created by julian:

The error occurs when try to get an alias based on case sensitive table name, because the alias is generated with the first character of table's name which is ".
Ex:
Table: car Alias: c0_
Table: "Car" Alias: "0_

Is in file SqlWalker.php line 295 in Doctrine 2.4.4:

public function getSQLTableAlias($tableName, $dqlAlias = '')
{
    $tableName .= ($dqlAlias) ? '@[' . $dqlAlias . ']' : '';

    if ( ! isset($this->tableAliasMap[$tableName])) {
        $this->tableAliasMap[$tableName] = strtolower(substr($tableName, 0, 1)) . $this->tableAliasCounter<ins></ins> . '_';
    }

    return $this->tableAliasMap[$tableName];
}
@doctrinebot commented on GitHub (Aug 6, 2014): Comment created by julian: The error occurs when try to get an alias based on case sensitive table name, because the alias is generated with the first character of table's name which is ". Ex: Table: car Alias: c0_ Table: "Car" Alias: "0_ Is in file SqlWalker.php line 295 in Doctrine 2.4.4: ``` public function getSQLTableAlias($tableName, $dqlAlias = '') { $tableName .= ($dqlAlias) ? '@[' . $dqlAlias . ']' : ''; if ( ! isset($this->tableAliasMap[$tableName])) { $this->tableAliasMap[$tableName] = strtolower(substr($tableName, 0, 1)) . $this->tableAliasCounter<ins></ins> . '_'; } return $this->tableAliasMap[$tableName]; } ```
admin commented 2026-01-22 13:41:56 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Dec 13, 2015):

Imported 2 attachments from Jira into https://gist.github.com/a1404730255435c27e91

@doctrinebot commented on GitHub (Dec 13, 2015): Imported 2 attachments from Jira into https://gist.github.com/a1404730255435c27e91 - [11168_SqlWalker.patch](https://gist.github.com/a1404730255435c27e91#file-11168_SqlWalker-patch) - [11169_doctrine-2.1.6.patch](https://gist.github.com/a1404730255435c27e91#file-11169_doctrine-2-1-6-patch)
admin commented 2026-01-22 13:42:00 +01:00
Author
Owner
Copy Link

@malukenho commented on GitHub (Jan 5, 2017):

@Ocramius @lcobucci it was fixed on commit 0a78f7bc11

@malukenho commented on GitHub (Jan 5, 2017): @Ocramius @lcobucci it was fixed on commit https://github.com/doctrine/doctrine2/commit/0a78f7bc116418406a5cae499dc221fca7be453b
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label Bug Duplicate
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#2124
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?