PostgreSQLとLIMIT用のサブクエリのバグ（との壮絶な戦いの記録）

doctrine

For complex order by clause (like '"d"."insert_datetime" DESC') limit subquery builder works incorrectly (with PostgreSQL), because of wrong operation sequence when try to preserve order by clause in select.

http://trac.doctrine-project.org/ticket/1948

Doctrine 1.0.7までのバグでひたすら悩んでいたので書いておきます。使用しているRDBMSはPostgreSQL 8.3.6。以下のようなクエリを動かそうとしたらエラーが出てしまいました。長くなりそうなので結論から言うと1.0.8からは直ってるので最新版にアップデートしました。

<?php
$query = Doctrine_Query::create()->from('Page p, p.Items i')
  ->orderBy('p.sort_order, i.sort_order')
  ->limit(10);

実際に作られたクエリは以下。

SELECT ... 
  FROM "page" "p"
    LEFT JOIN "page_item" "p2" ON "p"."id" = "p2"."page_id"
    LEFT JOIN "item" "i" ON "i"."id" = "p2"."item_id" 
  WHERE "p"."id" IN (
    SELECT "doctrine_subquery_alias"."id" 
      FROM (
        SELECT DISTINCT "p3"."id", p.sort_order, i.sort_order, p.sort_order, i.sort_order 
          FROM "page" "p3" 
            LEFT JOIN "page_item" "p4" ON "p3"."id" = "p4"."page_id" 
            LEFT JOIN "item" "i2" ON "i2"."id" = "p4"."item_id" 
          ORDER BY "p3"."sort_order", "i2"."sort_order", "p3"."sort_order", "i2"."sort_order" 
          LIMIT 10
      ) AS doctrine_subquery_alias
  ) 
  ORDER BY "p"."sort_order", "i"."sort_order", "p"."sort_order", "i"."sort_order"

このクエリを実行すると以下のようなエラーが。

SQLSTATE[42P10]: Invalid column reference: 7 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

作成されたクエリの中でサブクエリを使用しているのですが、調べたところPostgreSQLではクエリ中にORDER BYの指定がありSELECT DISTINCTを使用している場合、ORDER BYで指定しているカラムは全てSELECTに含める必要があるようです。

エラーの理由はわかったのですがなぜこのようなことになっているのかがわからず、ここからひたすら格闘しました。どのように解決していったかの手順を書いてみたいと思います。

まずはサブクエリ中に使われているdoctrine_subquery_aliasでgrepをかけてみました。で、ひっかかったのがDoctrine_QueryクラスのgetSqlQueryメソッド。

<?php
if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery) {
    $subquery = $this->getLimitSubquery();
    $idColumnName = $table->getColumnName($table->getIdentifier());

    switch (strtolower($this->_conn->getDriverName())) {
        case 'mysql':
            ...
            break;

        case 'pgsql':
            $subqueryAlias = $this->_conn->quoteIdentifier('doctrine_subquery_alias');

            // pgsql needs special nested LIMIT subquery
            $subquery = 'SELECT ' . $subqueryAlias . '.' . $this->_conn->quoteIdentifier($idColumnName)
                    . ' FROM (' . $subquery . ') AS ' . $subqueryAlias;

            break;
    }

で、Doctrineのサブクエリっていうのはクエリ中にDQLを入れてるだけなんですけど、元となるサブクエリを生成してるのが頭の方にあるgetLimitSubqueryメソッド。今回の犯人もこいつです。

こいつはかなり長くて、コメントに「@todo A little refactor to make the method easier to understand & maybe shorter?」なんてかかれてるくらいなのでソースは載せませんが、とにかくこいつの中にあちこち埋め込んでデバッグしてました。

<?php
$parts = $this->_tokenizer->quoteExplode($subquery, ' ', "'", "'");

foreach ($parts as $k => $part) {
    //...

    preg_match_all("/[a-zA-Z0-9_]+\.[a-z0-9_]+/i", $part, $m);

    foreach ($m[0] as $match) {
        $e = explode('.', $match);

        // Rebuild the original part without the newly generate alias and with quoting reapplied
        $e2 = array();
        foreach ($e as $k2 => $v2) {
          $e2[$k2] = $this->_conn->quoteIdentifier($v2);
        }
        $match = implode('.', $e2);

        // Generate new table alias
        $e[0] = $this->generateNewSqlTableAlias($e[0]);

        // Requote the part with the newly generated alias
        foreach ($e as $k2 => $v2) {
          $e[$k2] = $this->_conn->quoteIdentifier($v2);
        }

        $replace = implode('.' , $e);

        // Replace the original part with the new part with new sql table alias
        $parts[$k] = str_replace($match, $replace, $parts[$k]);
    }
}

次に見つけたのがここ。何をやってるかというとクエリを分解しまくって、サブクエリ用に新しくエイリアスを設定しているところ。ここでなぜか、SELECT句のsort_orderだけがなぜかエイリアスが古いままに。で、最後にstr_replaceをしている部分がありますがそこで

<?php
$match     = '"p"."sort_order"';
$replace   = '"p3"."sort_order"';
$parts[$k] = 'p.sort_order';

$parts[$k] = str_replace($match, $replace, $parts[$k]);

上記のようになってました。他のものは、第3引数にはクオートされた値が入っており正しくリプレースされていたのですが、SELECT句のやつらに関してはエスケープされていない状態で含まれていました。

で、原因を更に調べていたらちょっと上にありました。

<?php
foreach ($this->_sqlParts['orderby'] as $part) {
    // Remove identifier quoting if it exists
    $callback = create_function('$e', 'return trim($e, \'[]`"\');');
    $part = trim(implode('.', array_map($callback, explode('.', $part))));
    $e = $this->_tokenizer->bracketExplode($part, ' ');
    $part = trim($e[0]);

    if (strpos($part, '.') === false) {
        continue;
    }

    // don't add functions
    if (strpos($part, '(') !== false) {
        continue;
    }

    // don't add primarykey column (its already in the select clause)
    if ($part !== $primaryKey) {
        $subquery .= ', ' . $part;
    }
}

ORDER BYに指定されているカラムをSELECTに追加していくという部分です。ここでクオートされている値を外していました。なぜこれをやっているかというと最終的に$primaryKeyと比較を行うためなのですが、SELECT句に追加する歳にもクオートを外した状態で入れてしまっているのが問題でした。

というわけでバグチケットでも出すかとおもってDoctrineのtrac開いて、ふと調べてみるかと思ったら案の定既にチケットがあがっていて修正もされていました。。。symfonyとあわせて使っていたのですが、symfony1.2.7に含まれているDoctrineは1.0.7なので、とりあえず最新の1.0.10にアップデート。1.0.8以降ではクオートした状態の値をSELECT句に入れるという処理になっていますので、問題なく動作します。

これを見つけるまでに結構かかったのですが、このあたりはDoctrineの一番のキモになるところでもっとも複雑な部分なのでかなり手こずりました。むしろどうやって対処しようかということに悩みましたが、とりあえず直っていたので一安心です。

ちなみにLIMITにサブクエリを使っているのは、クエリ中のルートとなるモデルのみに対してLIMITをかけるためです。MySQLの場合はサブクエリをクエリ中に含めるのではなく、一度サブクエリのみ実行して取得してきたIDをimplodeしていれてます。理由は詳しくは知りません。

というわけで、生成するSQLがおかしいというバグにはまった場合は非常に苦労しますので、とりあえずtracのバグチケットを覗いてみて答えがないか探してみるのがいいと思います。