SQLSTATE [HY093]:参数号无效:混合命名和位置参数

This error to me seems a bug because there are no positional parameters in my query. Here is the method:

public function getAll(User $user, DateTime $start = null, DateTime $end = null)
{
    $params = array('user_id' => $user->getId());

    $rsm = new \Doctrine\ORM\Query\ResultSetMapping(); // Result set mapping
    $rsm->addScalarResult('subtype', 'subtype');
    $rsm->addScalarResult('count',   'count');

    $sms_sql =
    "SELECT CONCAT('sms_', IF(is_auto = 0, 'user' , 'auto')) AS subtype, " .
    "SUM(messages_count * (customers_count + recipients_count)) AS count " .
    "FROM outgoing_message AS m INNER JOIN small_text_message AS s ON " . 
    "m.id = s.id WHERE status <> 'pending' AND user_id = :user_id";

    $news_sql =
    "SELECT CONCAT('news_', IF(is_auto = 0, 'user' , 'auto')) AS subtype, " .
    "SUM(customers_count + recipients_count) AS count " .
    "FROM outgoing_message AS m JOIN newsletter AS n ON m.id = n.id " .
    "WHERE status <> 'pending' AND user_id = :user_id";

    if($start) :
        $sms_sql        .= " AND sent_at >= :start";
        $news_sql       .= " AND sent_at >= :start";
        $params['start'] = $start->format('Y-m-d');
    endif;

    $sms_sql  .= ' GROUP BY type, is_auto';
    $news_sql .= ' GROUP BY type, is_auto';

    return $this->_em->createNativeQuery("$sms_sql UNION ALL $news_sql", $rsm)
        >setParameters($params)->getResult();
}

And this throws the exception:

SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

Array $params is OK and so generated SQL:

var_dump($params);

array (size=2)
  'user_id' => int 1
  'start' => string '2012-01-01' (length=10)

The strangest thing is that it works with "$sms_sql" only!

Update

Found another strange thing. If I change only the name (to start_date instead of start):

    if($start) :
        $sms_sql             .= " AND sent_at >= :start_date";
        $news_sql            .= " AND sent_at >= :start_date";
        $params['start_date'] = $start->format('Y-m-d');
    endif;

What happens is that Doctrine/PDO says:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sent1rt_date' in 'where clause'

... as string 1rt was added in the middle of the column name!

查看全部
dtnwm4807
dtnwm4807
2012/07/06 18:47
  • php
  • mysql
  • pdo
  • 点赞
  • 收藏
  • 回答
    私信
满意答案
查看全部

1个回复