Project

General

Profile

Bug(バグ) #3339

Doctrine で WHERE IN を用いたサブクエリの場合に配列が正しく展開されない

Added by Yuya Watanabe about 9 years ago. Updated over 2 years ago.

Status:
New(新規)
Priority:
Normal(通常)
Assignee:
-
Target version:
Start date:
2013-05-07
Due date:
% Done:

0%

3.6 で発生するか:
Unknown (未調査)
3.8 で発生するか:
Unknown (未調査)

Description

概要

Doctrine で WHERE IN を用いたサブクエリの場合に配列が正しく展開されない.
厳密にいうと WHERE IN でなくともサブクエリに配列を指定する場合に正しく展開されない.

$q = Doctrine_Core::getTable('MemberConfig')->createQuery('c'); 
$q->andWhere('c.member_id IN (SELECT m.id FROM Member m WHERE m.id IN ?)', array(array('1', '2')));
$q->execute();

発行される SQL は下記の通り

SELECT m.id AS m__id, m.member_id AS m__member_id, m.name AS m__name, m.value AS m__value, m.value_datetime AS m__value_datetime, m.name_value_hash AS m__name_value_hash, m.created_at AS m__created_at, m.updated_at AS m__updated_at FROM member_config m WHERE (m.member_id IN (SELECT m2.id AS m2__id FROM member m2 WHERE (m2.id IN (?, ?))) AND (m2.is_active = ? OR m2.is_active IS NULL))

エラー

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

サブクエリ部分に着目すると,WHERE IN のパラメータホルダは2つあり,正しく展開されている.

WHERE (m2.id IN (?, ?))) AND (m2.is_active = ? OR m2.is_active IS NULL))

実際に渡されるパラメータは下記のような構成になっており,配列となっている部分が存在する.この部分が 'Array' となり,パラメータとの数があわないという状態になると思われる.

array(2) {
  [0] =>
  array(2) {
    [0] =>
    string(1) "1" 
    [1] =>
    string(1) "2" 
  }
  [1] =>
  int(1)
}


Related issues

Related to OpenPNE 3 - Bug(バグ) #3135: サブクエリのWHERE節に「field IN ?」の形式のDQLがあると Invalid parameter number エラーが発生する New(新規) 2012-07-27

History

#1 Updated by Yuya Watanabe about 9 years ago

  • Description updated (diff)

#2 Updated by Yuya Watanabe about 9 years ago

  • Description updated (diff)

サブクエリ出ない場合は正しく動いているようなので本文から記述を削除.

#3 Updated by Yuya Watanabe about 9 years ago

サブクエリでない場合

$q = Doctrine_Core::getTable('MemberConfig')->createQuery('c');       
$q->select('m.id')                                                    
  ->from('Member m')                                                  
  ->andWhereIn('m.id', array('1', '2'));
$q->execute();
SELECT m.id AS m__id FROM member m WHERE (m.id IN (?, ?) AND (m.is_active = ? OR m.is_active IS NULL))
array(3) {
  [0] =>
  string(1) "1" 
  [1] =>
  string(1) "2" 
  [2] =>
  int(1)
}

#4 Updated by Yuya Watanabe about 9 years ago

クエリやパラメータを確認する際に行った変更.

diff --git a/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Abstract.php b/lib/vendor/symfony/lib/plugins
/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Abstract.php
index 015f2c9..3234150 100644
--- a/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Abstract.php
+++ b/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Abstract.php
@@ -980,6 +980,8 @@ abstract class Doctrine_Query_Abstract
             return $this->_conn->exec($query, $params);
         }

+        var_dump($query);
+        var_dump($params);
         $stmt = $this->_conn->execute($query, $params);

         $this->_params['exec'] = array();

#5 Updated by kaoru n over 2 years ago

  • Target version set to OpenPNE 3.10.x

Also available in: Atom PDF