SQLite でも列名を生成する時の quote には backtick (`) を使ったほうが良いぽい気がしてきた

軽くハマったのでメモ。

まずは実験用のテーブル tab1 を作ります。(中身は空のままにします)

sqlite> create table tab1(foo, bar);
sqlite> select * from tab1;
sqlite> select * from tab1 where foo = 3;
sqlite>

次に、意図的に列名を打ち間違って入れてみましょう。 例えば fooo みたいに。

sqlite> select * from tab1 where fooo = 3;
Error: no such column: fooo
sqlite>

ちゃんとエラーになります。では、今度は(間違った列名を) double-quote で囲んで "fooo" のように変えてみましょう。これは SQL 標準に従った書き方です。ところが…

sqlite> select * from tab1 where "fooo" = 3;
sqlite>

なんと、 エラーにならない！

なぜこうなるかは SQLite の公式マニュアル SQLite Query Language: SQLite Keywords にあるように、

If a keyword in double quotes (ex: "key" or "glob") is used in a context where it cannot be resolved to an identifier but where a string literal is allowed, then the token is understood to be a string literal instead of an identifier.

"..." の指す列が存在しないなら、代わりに 文字列として解釈し直して しまうから。…超、迷惑…

このように、SQLite において、列名を double-quote で囲むと 列名の誤りがエラーにならないため、問題に気づくのが遅くなるのでよろしくないのでは？という話です。

むしろ mysql 風の backtick (`) で囲ったほうが (SQL標準から外れても) エラーになるだけマシではないか？と。

sqlite> select * from tab1 where `fooo` = 3;
Error: no such column: fooo
sqlite> select * from tab1 where `foo` = 3;
sqlite>

SQL::Maker で引用符を切り替えるには

例えば Perl の SQL::Maker - Yet another SQL builder - metacpan.org は SQLite に対してはデフォルトでは double-quote (") を使います。 これを backtick に切り替えるには、明示的にオプション "quote_char => q{`}" を渡して

use strict;
use SQL::Maker;
my $mk = SQL::Maker->new(driver => "SQLite", quote_char => q{`});
print join "\n", $mk->select(tab1 => ["*"], +{fooo => 3});
# SELECT *
# FROM `tab1`
# WHERE (`fooo` = ?)
# 3

とする必要が有ります。