MySQLのクエリで疑問に思った事象について

MySQLのクエリで疑問に思った事象を、備忘録として記載。

GROUP BYでカラム指定を省略してSELECT

mauticのインストールで当初MariaDB(10.2.5)を利用していたが、DB・ユーザ設定完了後、ダッシュボードの画面が表示されず、調べてみるとPHPエラーが発生していた。
エラー内容の詳細はこちら。
少し調べると、GROUP BYでカラム指定を省略して（この場合は、ll.name）SELECTしていたためエラーとなっていた。

SELECT
  COUNT(t.date_added) AS leads, ll.id, ll.name 
FROM lead_lists_leads t 
INNER JOIN lead_lists ll
  ON ll.id = t.leadlist_id WHERE (ll.is_published = true)
  AND (t.date_added BETWEEN "2017-04-02 00:00:00" 
  AND "2017-05-02 23:59:59")
GROUP BY ll.id
ORDER BY leads DESC
LIMIT 9;

githubで不具合報告されていた（詳細はこちら）。

MariaDBではなくMySQL(5.7)にするとなぜ発生しなかったどうか調べてみたところ、sql_modeでONLY_FULL_GROUP_BYの指定を偶然にも外していたためだった。
sql_modeにONLY_FULL_GROUP_BYを指定すると同様のエラーが発生した。

他のDBに移行するなど、今後の運用を加味すると、はまりそうなので、ONLY_FULL_GROUP_BYは指定したほうが無難かもしれない。

参考資料：13.19.3 MySQL Handling of GROUP BY

GROUP BYなしでHAVINGする場合

GROUP BYといえば、過去になぜか以下のようなクエリをプログラムで実行して、まったく本来の意図通りにならなかった痛い思いをしたことがある。

user_itemテーブルにこんな感じでデータがあるとする。

user_id	item_id
1	1
1	2
1	3
2	1
3	2
3	3

SELECT
 DISTINCT user_id
FROM user_item
HAVING COUNT(user_id) > 1;

を実行すると、以下となり、user_idに3が表示されない（当然のことだけど）。

+---------+
| user_id |
+---------+
|       1 |
+---------+

本来意図したことを実現するクエリは以下。

SELECT
 user_id
FROM user_item
GROUP BY user_id
HAVING COUNT(user_id) > 1;

出力は以下の通り。

+---------+
| user_id |
+---------+
|       1 |
|       3 |
+---------+

なお、stackoverflowで、こちらやこちらでも取り上げられているが、GROUP BYがなくてもHAVINGは指定できるようだ。

CROSS JOINと他のJOINの組み合わせ

テーブルにこんな感じでデータがあるとする。

テーブル名：test

id	category_id	region_id
1	1	1
2	2	2
3	3	3
4	1	3
5	2	2
6	3	1

テーブル名：category

id	name
1	りんご
2	バナナ
3	パイナップル

テーブル名：region

id	name
1	北海道
2	青森
3	岩手

下記のようにCROSS JOINとINNER JOINを1つのSQL文で実行するとエラーとなる。

SELECT
  category.name, region.name
FROM
  test AS test, category AS category
INNER JOIN
  region
ON
  test.region_id = region.id
WHERE
  test.category_id = category.id AND test.id = 2;

出力結果

ERROR 1054 (42S22): Unknown column 'test.region_id' in 'on clause'

LEFT JOINでも同様にエラーとなる。

SELECT
  category.name, region.name
FROM
  test AS test, category AS category
LEFT JOIN
  region
ON
  test.region_id = region.id
WHERE
  test.category_id = category.id AND test.id = 2;

出力結果

ERROR 1054 (42S22): Unknown column 'test.region_id' in 'on clause'

この場合、ON句ではなく、WHERE句で条件指定することで解決できる。

SELECT
  category.name, region.name
FROM
  test AS test, category AS category
INNER JOIN
  region
WHERE
  test.category_id = category.id AND test.id = 2 AND test.region_id = region.id;

出力結果

+-----------+--------+
| name      | name   |
+-----------+--------+
| バナナ    | 青森   |
+-----------+--------+

UNIQUE制約とNULL

下記のようなUNIQUE制約の属性（NOT NULLは指定しない）を持つテーブルを作成する。

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `unique_no` int(10) unsigned,
  PRIMARY KEY (`id`),
  UNIQUE KEY `test_unique_no` (`unique_no`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

以下のようにUNIQUE制約の属性に対して、同じ値を追加するとERROR 1062 (23000): Duplicate entry '1' for key 'test_unique_no'が発生する。

insert into test (unique_no) values (1);
insert into test (unique_no) values (1);

しかし、UNIQUE制約の属性に対して、nullを指定してもエラーにならない。

insert into test (unique_no) values (null);
insert into test (unique_no) values (null);

UNIQUE属性なのに、NULLデータが複数ある状態が作れる。

mysql> SELECT * FROM test;
+----+-----------+
| id | unique_no |
+----+-----------+
| 12 |      NULL |
| 13 |      NULL |
| 10 |         1 |
+----+-----------+