Uploaded image for project: 'Doctrine 2 - ORM'
  1. Doctrine 2 - ORM
  2. DDC-1695

SQLs for PostgreSQL case sensite tables/fields are wrongly generated

    Details

    • Type: Bug
    • Status: Reopened
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 2.1.6, 2.4.2
    • Fix Version/s: 2.1.7, 2.2.2
    • Component/s: None
    • Security Level: All
    • Labels:
      None
    • Environment:
      PostgreSQL 9.x, Symfony 2

      Description

      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.

      1. doctrine-2.1.6.patch
        4 kB
        Ignacio Larranaga
      2. SqlWalker.patch
        2 kB
        Ignacio Larranaga

        Activity

        ignaciolarranaga Ignacio Larranaga created issue -
        ignaciolarranaga Ignacio Larranaga made changes -
        Field Original Value New Value
        Attachment SqlWalker.patch [ 11168 ]
        ignaciolarranaga Ignacio Larranaga made changes -
        Summary SQLs for PostgreSQL case sensite schemas are wrongly generated SQLs for PostgreSQL case sensite tables/fields are wrongly generated
        ignaciolarranaga Ignacio Larranaga made changes -
        Attachment doctrine-2.1.6.patch [ 11169 ]
        beberlei Benjamin Eberlei made changes -
        Description 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.
        The SQLs for case sensitive schemas in postgreSQL are wronly generated.

        Example:
        Schema:

        {code}
        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" ));
        {code}

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

        {code}
        <?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;

            ....

        }
        {code}

        DQL:

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

        Generated SQL:

        {code}
        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
        {code}

        Notice there are unmattched " in the SQL.
        beberlei Benjamin Eberlei made changes -
        Project Doctrine DBAL [ 10040 ] Doctrine 2 - ORM [ 10032 ]
        Key DBAL-236 DDC-1695
        Affects Version/s 2.1.6 [ 10182 ]
        Affects Version/s 2.1.6 [ 10181 ]
        beberlei Benjamin Eberlei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.1.7 [ 10198 ]
        Fix Version/s 2.2.2 [ 10195 ]
        Resolution Fixed [ 1 ]
        beberlei Benjamin Eberlei made changes -
        Workflow jira [ 13513 ] jira-feedback [ 15264 ]
        beberlei Benjamin Eberlei made changes -
        Workflow jira-feedback [ 15264 ] jira-feedback2 [ 17128 ]
        beberlei Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 17128 ] jira-feedback3 [ 19381 ]
        ocramius Marco Pivetta made changes -
        Resolution Fixed [ 1 ]
        Status Resolved [ 5 ] Reopened [ 4 ]
        Assignee Benjamin Eberlei [ beberlei ] Marco Pivetta [ ocramius ]
        ocramius Marco Pivetta made changes -
        Affects Version/s 2.4.2 [ 10621 ]
        ocramius Marco Pivetta made changes -
        Priority Blocker [ 1 ] Major [ 3 ]

          People

          • Assignee:
            ocramius Marco Pivetta
            Reporter:
            ignaciolarranaga Ignacio Larranaga
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: