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
        Marco Pivetta added a comment -
        Show
        Marco Pivetta added a comment - Added test to prevent regressions at https://github.com/doctrine/doctrine2/commit/0a90279a99e58bac00258fbc17d6ab01fa466d70
        Hide
        Marco Pivetta added a comment -
        Show
        Marco Pivetta added a comment - Added failing test at https://github.com/doctrine/doctrine2/pull/693
        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
        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
        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) )

          People

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

            Dates

            • Created:
              Updated:
              Resolved: