$shibayu36->blog;

クラスター株式会社のソフトウェアエンジニアです。エンジニアリングや読書などについて書いています。

DBIのプレースホルダーで起こった謎な挙動

perl tech

先日DBIを使っていて、謎な挙動に遭遇したので書いてみる。LIMIT, OFFSET句でプレースホルダーを用いた時に起こった。DBI + DBD::mysqlを使っていた。

LIMITとOFFSETを両方にプレースホルダーを使う

LIMITとOFFSETを両方を?にするのはうまくいく。

$res = $dbh->prepare_cached(q[
    SELECT * FROM user
      WHERE name = ?
      LIMIT ?
      OFFSET ?
])->execute('shiba_yu36', 1, 1);

LIMITのみプレースホルダー

LIMITのみ?でもうまくいく。

$res = $dbh->prepare_cached(q[
    SELECT * FROM user
      WHERE name = ?
      LIMIT ?
      OFFSET 1
])->execute('shiba_yu36', 1);

OFFSETのみプレースホルダー

OFFSETのみ?だとOFFSET句がquoteされて、syntaxエラーになる。

$res = $dbh->prepare_cached(q[
    SELECT * FROM user
      WHERE name = ?
      LIMIT 1
      OFFSET ?
])->execute('shiba_yu36', 1);

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''1'' at line 4 at /Users/shibayu36/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/QueryLog.pm line 143.
[2013-02-28T00:16:01] [main] [0.000082] 
    SELECT * FROM user
      WHERE name = 'shiba_yu36'
      LIMIT 1
      OFFSET '1'

LIMITとOFFSET両方でプレースホルダーを使い、LIMITの後に半角が二つある

LIMITと?の間に半角スペースが2つあると両方共quoteされてsyntaxエラーになる。

$res = $dbh->prepare_cached(q[
    SELECT * FROM user
      WHERE name = ?
      LIMIT  ?
      OFFSET ?
])->execute('shiba_yu36', 1, 1);

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''1'
      OFFSET '1'' at line 3 at /Users/shibayu36/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/DBIx/QueryLog.pm line 143.
[2013-02-28T00:15:37] [main] [0.000086] 
    SELECT * FROM user
      WHERE name = 'shiba_yu36'
      LIMIT  '1'
      OFFSET '1'

まとめ

調べようと思ってDBIとかDBD::mysqlの中身を見ようとしたんだけど、結構難しい感じで理解できなくて挫折した。SQL::MakerとかのクエリビルダではLIMIT, OFFSET系は別扱いされていて、バリデーション+文字列結合でやっていたように見えた。
実は何かドキュメントを見逃していたりするかもしれない。何か知っている方がいましたら、教えてくれると嬉しいです。

追記

コメントやメンションで教えてもらえましたが、DBD::mysqlのLIMITまわりに特殊な処理がされてるせいっぽかったです。