MySQLのクエリで疑問に思った事象のまとめ

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

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

```sql
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で不具合報告されていた（詳細は[こちら](https://github.com/mautic/mautic/issues/3969)）。

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](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html)

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

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

| user_id | item_id |
|:--------:|:------:|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 3 | 2 |
| 3 | 3 |

```sql
SELECT
 DISTINCT user_id
FROM user_item
HAVING COUNT(user_id) > 1;
```
を実行すると、以下となり、user_idに3が表示されない（当然のことだけど）。

```sql
+---------+
| user_id |
+---------+
|       1 |
+---------+
```
本来意図したことを実現するクエリは以下。

```sql
SELECT
 user_id
FROM user_item
GROUP BY user_id
HAVING COUNT(user_id) > 1;
```
出力は以下の通り。

```sql
+---------+
| user_id |
+---------+
|       1 |
|       3 |
+---------+
```

なお、stackoverflowで、[こちら](https://dba.stackexchange.com/questions/57445/use-of-having-without-group-by-in-sql-queries/57453)や[こちら](http://stackoverflow.com/questions/6924896/having-without-group-by)でも取り上げられているが、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文で実行するとエラーとなる。

```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;
```

```sql:出力結果
ERROR 1054 (42S22): Unknown column 'test.region_id' in 'on clause'
```

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

```sql
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;
```

```sql:出力結果
ERROR 1054 (42S22): Unknown column 'test.region_id' in 'on clause'
```

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

```sql
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;
```

```sql:出力結果
+-----------+--------+
| name      | name   |
+-----------+--------+
| バナナ    | 青森   |
+-----------+--------+
```

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

```sql
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'`が発生する。


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

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


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

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

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