Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2214

extra single quotation in sql when using EntityRepository::findBy

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.4
    • Component/s: None
    • Security Level: All
    • Labels:
      None

      Description

      I'm using symfony 2.1 with mysql.

      I have following code:

      $related = 
      $this->getDoctrine()->getRepository('MyWebBundle:LineRelated')
      ->findBy(array('line' => $lines), array('count' => 'DESC'), 20);
      

      that generate the sql like this:

      SELECT *
      FROM line_related t0 
      WHERE t0.line_id IN ('6059', 126352, '5677', '6058') 
      ORDER BY t0.count DESC 
      LIMIT 20
      

      please notice that the sql has extra single quotation around the number 6059,5677 and 6058. which make the sql very slow.

      I did a test, when using single quotation,the sql takes 300ms,when using without single quotation,the sql takes 1 ms.

        Activity

        Hide
        Fabio B. Silva added a comment -

        Hi

        Could you please attach your entities or a failing test case ?

        Cheers

        Show
        Fabio B. Silva added a comment - Hi Could you please attach your entities or a failing test case ? Cheers
        Hide
        scourgen added a comment -

        sure

        LineRelated.php :

        class LineRelated
        {
            /**
             * @ORM\Id
             * @ORM\Column(type="integer")
             * @ORM\GeneratedValue(strategy="AUTO")
             */
             protected $id;
        
            /**
             * @ORM\ManyToOne(targetEntity="Line", inversedBy="line_related")
             * @ORM\JoinColumn(name="line_id", referencedColumnName="id",nullable=false)
             */
             protected $line;
             
            /**
             * @ORM\Column(name="line_id_related", type="integer")
             */
            protected $line_related;
        
            /**
             * @ORM\Column(type="smallint",nullable=false)
             */
             protected $count = 0;
        
        ###### get/set etc....... #######
        

        Line.php

        class Line
        {
            /**
             * @ORM\Id
             * @ORM\Column(type="integer")
             * @ORM\GeneratedValue(strategy="AUTO")
             */
            protected $id;
        ########## blablabla #############
        
        

        my action:

            public function right_line_relatedAction($line = null, $title='相关线路')
            {
        
                $lines = $l->getByUser($user, array());
        //anyway,$lines is an array,It has several elements,each element is an instance of LineEntity.
        
                $related = $this->getDoctrine()->getRepository('MyWebBundle:LineRelated')->findBy(array('line' => $lines), array('count' => 'DESC'), 20);
        //this findBy function generate the sql which is slow.
        
                return $related;
            }
        
        Show
        scourgen added a comment - sure LineRelated.php : class LineRelated { /** * @ORM\Id * @ORM\Column(type= "integer" ) * @ORM\GeneratedValue(strategy= "AUTO" ) */ protected $id; /** * @ORM\ManyToOne(targetEntity= "Line" , inversedBy= "line_related" ) * @ORM\JoinColumn(name= "line_id" , referencedColumnName= "id" ,nullable= false ) */ protected $line; /** * @ORM\Column(name= "line_id_related" , type= "integer" ) */ protected $line_related; /** * @ORM\Column(type= "smallint" ,nullable= false ) */ protected $count = 0; ###### get/set etc....... ####### Line.php class Line { /** * @ORM\Id * @ORM\Column(type= "integer" ) * @ORM\GeneratedValue(strategy= "AUTO" ) */ protected $id; ########## blablabla ############# my action: public function right_line_relatedAction($line = null , $title='相关线路') { $lines = $l->getByUser($user, array()); //anyway,$lines is an array,It has several elements,each element is an instance of LineEntity. $related = $ this ->getDoctrine()->getRepository('MyWebBundle:LineRelated')->findBy(array('line' => $lines), array('count' => 'DESC'), 20); // this findBy function generate the sql which is slow. return $related; }
        Hide
        Fabio B. Silva added a comment -

        Hi,

        How did you get this query string ?

        Repository#findBy does not quote the values, It uses PDO:bindParam.
        so the expected query string should be someting like :

        WHERE t0.line_id IN (?, ? ,?) 
        

        I tried to reproduce but in my tests the generated Query binds the parameters as "PDO::PARAM_INT".

        I have added a test case.
        Could you please can try to change it and make fails.

        Cheers

        Show
        Fabio B. Silva added a comment - Hi, How did you get this query string ? Repository#findBy does not quote the values, It uses PDO:bindParam. so the expected query string should be someting like : WHERE t0.line_id IN (?, ? ,?) I tried to reproduce but in my tests the generated Query binds the parameters as "PDO::PARAM_INT". I have added a test case. Could you please can try to change it and make fails. Cheers
        Hide
        scourgen added a comment -

        reproduced :

        SELECT t0.id AS id1, t0.line_id_related AS line_id_related2, t0.count AS count3, t0.line_id AS line_id4 FROM line_related t0 WHERE t0.line_id IN ('6059', 4851, '6068', 126352, '6060', '1000000') ORDER BY t0.count DESC LIMIT 20
        Parameters: [['6059', 4851, '6068', 126352, '6060', '1000000']] 
        [Hide runnable query]
        Time: 234.53 ms [   Explain query ]
        

        let me have a look on what's going on

        Show
        scourgen added a comment - reproduced : SELECT t0.id AS id1, t0.line_id_related AS line_id_related2, t0.count AS count3, t0.line_id AS line_id4 FROM line_related t0 WHERE t0.line_id IN ('6059', 4851, '6068', 126352, '6060', '1000000') ORDER BY t0.count DESC LIMIT 20 Parameters: [['6059', 4851, '6068', 126352, '6060', '1000000']] [Hide runnable query] Time: 234.53 ms [ Explain query ] let me have a look on what's going on
        Hide
        scourgen added a comment - - edited

        interesting. I've dump(using ladybug_dump) the $lines,and I found out that when the element is a Proxies Object(Object(Proxies_GC_\My\WebBundle\Entity\Line)),then the id of that Object will be with quoted,when the elememt is an Real Entity,then It will be without quote.

        for example,in my last comment, the parameters is [['6059', 4851, '6068', 126352, '6060', '1000000']]
        the result of dumping $lines is :

        array(6)
        [0]: object(Proxies_CG_\Zuo\WebBundle\Entity\Line)
        [1]: object(Zuo\WebBundle\Entity\Line)
        [2]: object(Proxies_CG_\Zuo\WebBundle\Entity\Line)
        [3]: object(Zuo\WebBundle\Entity\Line)
        [4]: object(Proxies_CG_\Zuo\WebBundle\Entity\Line)
        [5]: object(Proxies_CG_\Zuo\WebBundle\Entity\Line)

        tell me if you need more information. thanks

        Show
        scourgen added a comment - - edited interesting. I've dump(using ladybug_dump) the $lines,and I found out that when the element is a Proxies Object(Object(Proxies_ GC _\My\WebBundle\Entity\Line)),then the id of that Object will be with quoted,when the elememt is an Real Entity,then It will be without quote. for example,in my last comment, the parameters is [ ['6059', 4851, '6068', 126352, '6060', '1000000'] ] the result of dumping $lines is : array(6) [0] : object(Proxies_ CG _\Zuo\WebBundle\Entity\Line) [1] : object(Zuo\WebBundle\Entity\Line) [2] : object(Proxies_ CG _\Zuo\WebBundle\Entity\Line) [3] : object(Zuo\WebBundle\Entity\Line) [4] : object(Proxies_ CG _\Zuo\WebBundle\Entity\Line) [5] : object(Proxies_ CG _\Zuo\WebBundle\Entity\Line) tell me if you need more information. thanks
        Hide
        Marco Pivetta added a comment - - edited

        This may be because

        $_identifier

        in proxies ( https://github.com/doctrine/doctrine2/blob/42e83a2716d19eada4f1cd49ece77d5f5229a239/lib/Doctrine/ORM/Proxy/ProxyFactory.php#L383 ) is not necessarily composed by integers. This could be fixed with DCOM-96. I'll add the tests to my development branch and will ping you back tomorrow

        Show
        Marco Pivetta added a comment - - edited This may be because $_identifier in proxies ( https://github.com/doctrine/doctrine2/blob/42e83a2716d19eada4f1cd49ece77d5f5229a239/lib/Doctrine/ORM/Proxy/ProxyFactory.php#L383 ) is not necessarily composed by integers. This could be fixed with DCOM-96 . I'll add the tests to my development branch and will ping you back tomorrow
        Hide
        scourgen added a comment -

        thanks

        Show
        scourgen added a comment - thanks
        Hide
        Marco Pivetta added a comment -

        I see what is going on here... But this should not be a problem anyway, since they're bound anyway as "PDO::PARAM_INT", as Fabio B. Silva told you.

        That's only a problem with the logger showing them as string. PDO will handle the conversion before the value hits the DB as far as I know.

        Show
        Marco Pivetta added a comment - I see what is going on here... But this should not be a problem anyway, since they're bound anyway as "PDO::PARAM_INT", as Fabio B. Silva told you. That's only a problem with the logger showing them as string. PDO will handle the conversion before the value hits the DB as far as I know.
        Hide
        scourgen added a comment -

        I can understand your point,but what I don't really get is that the execute time of sql is very long,that explained the quote should be in the sql,not like what you said,that's only a problem with the logger.

        Show
        scourgen added a comment - I can understand your point,but what I don't really get is that the execute time of sql is very long,that explained the quote should be in the sql,not like what you said,that's only a problem with the logger.
        Hide
        Marco Pivetta added a comment -

        scourgen can you profile the difference directly in CLI? What about checking the bound parameter type? Are those values bound as INTs in your case?

        Show
        Marco Pivetta added a comment - scourgen can you profile the difference directly in CLI? What about checking the bound parameter type? Are those values bound as INTs in your case?
        Hide
        scourgen added a comment -

        @ocramius I wish I could, but I was using doctrine2 with symfony2,So It looks like It will takes some time to simulating all environment and settings that could allow me to reproduced the problem.

        but anyway,I will have a try and tell you what happen when I found something.

        Show
        scourgen added a comment - @ocramius I wish I could, but I was using doctrine2 with symfony2,So It looks like It will takes some time to simulating all environment and settings that could allow me to reproduced the problem. but anyway,I will have a try and tell you what happen when I found something.
        Hide
        Marco Pivetta added a comment -

        scourgen ok, awaiting your reply then

        Show
        Marco Pivetta added a comment - scourgen ok, awaiting your reply then
        Hide
        scourgen added a comment - - edited

        I've spent some time on playing with native doctrine2. It took me awhile to setup everything. but I just don't get that how to retrive data with its Proxy ojbect(for example Proxies_CG_\My\WebBundle\Entity\Line).

        I mean the result of

        $this->_em->getRepository("something")->findxxx()

        always return an array of real object. I can't reproduced the situation(#comment-19186) that happens on symfony2+doctrine2.

        anyway,I can make sure the problem is real exist,Because the execute time of that slow sql from the tool bar of symfony2 is same as I executed it at mysql cli. If the sql shows up on log with quote but running at mysql without quote,the execute time won't be same(actually It will be much more faster,in my case,20x times,from 2xxms to 10ms).

        Show
        scourgen added a comment - - edited I've spent some time on playing with native doctrine2. It took me awhile to setup everything. but I just don't get that how to retrive data with its Proxy ojbect(for example Proxies_CG_\My\WebBundle\Entity\Line). I mean the result of $ this ->_em->getRepository( "something" )->findxxx() always return an array of real object. I can't reproduced the situation(#comment-19186) that happens on symfony2+doctrine2. anyway,I can make sure the problem is real exist,Because the execute time of that slow sql from the tool bar of symfony2 is same as I executed it at mysql cli. If the sql shows up on log with quote but running at mysql without quote,the execute time won't be same(actually It will be much more faster,in my case,20x times,from 2xxms to 10ms).
        Hide
        Marco Pivetta added a comment -

        scourgen you can use

        $em->getReference($className, $identifier)

        (identifier being a key=>value array) to force proxies.

        Give it a try

        Show
        Marco Pivetta added a comment - scourgen you can use $em->getReference($className, $identifier) (identifier being a key=>value array) to force proxies. Give it a try
        Hide
        scourgen added a comment -

        looks like I reproduced it.

            public function testIssue()
            {   
                 $no_used=   $this->_em->getRepository(__NAMESPACE__. '\DDC2214Line')->findOneById(1);
                $lines=array(
                    //$this->_em->getRepository(__NAMESPACE__. '\DDC2214Line')->findOneById(1),
                    $this->_em->getReference(__NAMESPACE__. '\DDC2214Line',1),
                    $this->_em->getReference(__NAMESPACE__. '\DDC2214Line','2'),
                    $this->_em->getReference(__NAMESPACE__. '\DDC2214Line',3),
                );  
                $logger  = $this->_em->getConnection()->getConfiguration()->getSQLLogger();
                $ids     = array_map(function($r){
                    return $r->id;
                }, $this->relatedList);
        
                //$related = $this->_em->getRepository(__NAMESPACE__ . '\DDC2214LineRelated')->findBy(array('line' => $lines), array('count' => 'DESC'), 20);
                $related = $this->_em->createQuery('select lr from '.__NAMESPACE__ . '\DDC2214LineRelated lr where lr.id in (:ids)')->setParameter('ids',$lines)->getResult();
                    
                $query   = end($logger->queries);
        //\Doctrine\Common\Util\Debug::dump($query['params']);
        
                $this->assertCount(3, $related);
                $this->assertEquals($ids, $query['params'][0]);
                $this->assertEquals(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY, $query['types'][0]);
            }   
        }
        

        I use MySql Query log to see what's really happen in database(http://dev.mysql.com/doc/refman/5.5/en/query-log.html)

        this is the log from table mysql.general_log

        2013-01-08 12:23:44	[root] @ localhost [127.0.0.1]	59	0	Connect	root@localhost on doctrine_tests
        2013-01-08 12:23:44	root[root] @ localhost [127.0.0.1]	59	0	Query	CREATE TABLE DDC2214Line (id INT AUTO_INCREMENT NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB
        2013-01-08 12:23:44	root[root] @ localhost [127.0.0.1]	59	0	Query	CREATE TABLE DDC2214LineRelated (id INT AUTO_INCREMENT NOT NULL, line_id INT NOT NULL, count SMALLINT NOT NULL, line_id_related INT NOT NULL, INDEX IDX_D31307994D7B7542 (line_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB
        2013-01-08 12:23:44	root[root] @ localhost [127.0.0.1]	59	0	Query	ALTER TABLE DDC2214LineRelated ADD CONSTRAINT FK_D31307994D7B7542 FOREIGN KEY (line_id) REFERENCES DDC2214Line (id)
        2013-01-08 12:23:44	root[root] @ localhost [127.0.0.1]	59	0	Query	START TRANSACTION
        2013-01-08 12:23:44	root[root] @ localhost [127.0.0.1]	59	0	Query	INSERT INTO DDC2214Line (id) VALUES (null)
        2013-01-08 12:23:44	root[root] @ localhost [127.0.0.1]	59	0	Query	INSERT INTO DDC2214Line (id) VALUES (null)
        2013-01-08 12:23:44	root[root] @ localhost [127.0.0.1]	59	0	Query	INSERT INTO DDC2214Line (id) VALUES (null)
        2013-01-08 12:23:44	root[root] @ localhost [127.0.0.1]	59	0	Query	INSERT INTO DDC2214LineRelated (count, line_id_related, line_id) VALUES (1, 1, 1)
        2013-01-08 12:23:44	root[root] @ localhost [127.0.0.1]	59	0	Query	INSERT INTO DDC2214LineRelated (count, line_id_related, line_id) VALUES (2, 2, 2)
        2013-01-08 12:23:44	root[root] @ localhost [127.0.0.1]	59	0	Query	INSERT INTO DDC2214LineRelated (count, line_id_related, line_id) VALUES (3, 3, 3)
        2013-01-08 12:23:44	root[root] @ localhost [127.0.0.1]	59	0	Query	COMMIT
        2013-01-08 12:23:44	root[root] @ localhost [127.0.0.1]	59	0	Query	SELECT t0.id AS id1 FROM DDC2214Line t0 WHERE t0.id = 1 LIMIT 1
        2013-01-08 12:23:44	root[root] @ localhost [127.0.0.1]	59	0	Query	SELECT d0_.id AS id0, d0_.count AS count1, d0_.line_id_related AS line_id_related2, d0_.line_id AS line_id3 FROM DDC2214LineRelated d0_ WHERE d0_.id IN (1, '2', 3)
        2013-01-08 12:23:44	root[root] @ localhost [127.0.0.1]	59	0	Quit	
        

        you can see,in database level,the second parameter of last query but two has quote ( (1, '2', 3) )

        Show
        scourgen added a comment - looks like I reproduced it. public function testIssue() { $no_used= $ this ->_em->getRepository(__NAMESPACE__. '\DDC2214Line')->findOneById(1); $lines=array( //$ this ->_em->getRepository(__NAMESPACE__. '\DDC2214Line')->findOneById(1), $ this ->_em->getReference(__NAMESPACE__. '\DDC2214Line',1), $ this ->_em->getReference(__NAMESPACE__. '\DDC2214Line','2'), $ this ->_em->getReference(__NAMESPACE__. '\DDC2214Line',3), ); $logger = $ this ->_em->getConnection()->getConfiguration()->getSQLLogger(); $ids = array_map(function($r){ return $r->id; }, $ this ->relatedList); //$related = $ this ->_em->getRepository(__NAMESPACE__ . '\DDC2214LineRelated')->findBy(array('line' => $lines), array('count' => 'DESC'), 20); $related = $ this ->_em->createQuery('select lr from '.__NAMESPACE__ . '\DDC2214LineRelated lr where lr.id in (:ids)')->setParameter('ids',$lines)->getResult(); $query = end($logger->queries); //\Doctrine\Common\Util\Debug::dump($query['params']); $ this ->assertCount(3, $related); $ this ->assertEquals($ids, $query['params'][0]); $ this ->assertEquals(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY, $query['types'][0]); } } I use MySql Query log to see what's really happen in database( http://dev.mysql.com/doc/refman/5.5/en/query-log.html ) this is the log from table mysql.general_log 2013-01-08 12:23:44 [root] @ localhost [127.0.0.1] 59 0 Connect root@localhost on doctrine_tests 2013-01-08 12:23:44 root[root] @ localhost [127.0.0.1] 59 0 Query CREATE TABLE DDC2214Line (id INT AUTO_INCREMENT NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB 2013-01-08 12:23:44 root[root] @ localhost [127.0.0.1] 59 0 Query CREATE TABLE DDC2214LineRelated (id INT AUTO_INCREMENT NOT NULL, line_id INT NOT NULL, count SMALLINT NOT NULL, line_id_related INT NOT NULL, INDEX IDX_D31307994D7B7542 (line_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB 2013-01-08 12:23:44 root[root] @ localhost [127.0.0.1] 59 0 Query ALTER TABLE DDC2214LineRelated ADD CONSTRAINT FK_D31307994D7B7542 FOREIGN KEY (line_id) REFERENCES DDC2214Line (id) 2013-01-08 12:23:44 root[root] @ localhost [127.0.0.1] 59 0 Query START TRANSACTION 2013-01-08 12:23:44 root[root] @ localhost [127.0.0.1] 59 0 Query INSERT INTO DDC2214Line (id) VALUES ( null ) 2013-01-08 12:23:44 root[root] @ localhost [127.0.0.1] 59 0 Query INSERT INTO DDC2214Line (id) VALUES ( null ) 2013-01-08 12:23:44 root[root] @ localhost [127.0.0.1] 59 0 Query INSERT INTO DDC2214Line (id) VALUES ( null ) 2013-01-08 12:23:44 root[root] @ localhost [127.0.0.1] 59 0 Query INSERT INTO DDC2214LineRelated (count, line_id_related, line_id) VALUES (1, 1, 1) 2013-01-08 12:23:44 root[root] @ localhost [127.0.0.1] 59 0 Query INSERT INTO DDC2214LineRelated (count, line_id_related, line_id) VALUES (2, 2, 2) 2013-01-08 12:23:44 root[root] @ localhost [127.0.0.1] 59 0 Query INSERT INTO DDC2214LineRelated (count, line_id_related, line_id) VALUES (3, 3, 3) 2013-01-08 12:23:44 root[root] @ localhost [127.0.0.1] 59 0 Query COMMIT 2013-01-08 12:23:44 root[root] @ localhost [127.0.0.1] 59 0 Query SELECT t0.id AS id1 FROM DDC2214Line t0 WHERE t0.id = 1 LIMIT 1 2013-01-08 12:23:44 root[root] @ localhost [127.0.0.1] 59 0 Query SELECT d0_.id AS id0, d0_.count AS count1, d0_.line_id_related AS line_id_related2, d0_.line_id AS line_id3 FROM DDC2214LineRelated d0_ WHERE d0_.id IN (1, '2', 3) 2013-01-08 12:23:44 root[root] @ localhost [127.0.0.1] 59 0 Quit you can see,in database level,the second parameter of last query but two has quote ( (1, '2', 3) )
        Hide
        Benjamin Eberlei added a comment -

        A related Github Pull-Request [GH-247] was opened
        https://github.com/doctrine/common/pull/247

        Show
        Benjamin Eberlei added a comment - A related Github Pull-Request [GH-247] was opened https://github.com/doctrine/common/pull/247
        Hide
        Benjamin Eberlei added a comment -

        A related Github Pull-Request [GH-247] was closed
        https://github.com/doctrine/common/pull/247

        Show
        Benjamin Eberlei added a comment - A related Github Pull-Request [GH-247] was closed https://github.com/doctrine/common/pull/247
        Hide
        Marco Pivetta added a comment -
        Show
        Marco Pivetta added a comment - Added failing test at https://github.com/doctrine/doctrine2/pull/693
        Hide
        Marco Pivetta added a comment -
        Show
        Marco Pivetta added a comment - Added test to prevent regressions at https://github.com/doctrine/doctrine2/commit/0a90279a99e58bac00258fbc17d6ab01fa466d70

          People

          • Assignee:
            Marco Pivetta
            Reporter:
            scourgen
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: