MariaDB와 MySQL의 불일치

MariaDB와 MySQL의 불일치

나는 이 두 데이터베이스 사이에서 쿼리가 어떻게 해석되는지에 대해 다른 행동을 발견했고 여기서 무슨 일이 일어나고 있는지 누군가 밝혀줄 수 있는지 궁금했다.쿼리는 다음과 같습니다.

SELECT t1.id, t2.album_id
FROM t1
    LEFT OUTER JOIN t2
        ON t1.data_id = t2.id
        AND t1.event_type IN (1002, 1001, 1000)
WHERE
    t1.event_type IN (1000, 1001, 1002, 1200, 1201, 1202, 1203)
GROUP BY t1.id
ORDER BY t1.id DESC
LIMIT 0, 20;

MariaDB 결과는 다음과 같습니다.

+-----+----------+
| id  | album_id |
+-----+----------+
| 623 |     NULL |
| 622 |     NULL |
| 621 |     NULL |
| 620 |     NULL |
| 619 |     NULL |
| 618 |     NULL |
| 617 |     NULL |
| 616 |     NULL |
| 615 |     NULL |
| 614 |     NULL |
| 613 |     NULL |
| 612 |      194 |
| 611 |     NULL |
| 610 |     NULL |
| 609 |     NULL |
| 608 |      193 |
| 607 |     NULL |
| 606 |     NULL |
| 605 |     NULL |
| 604 |     NULL |
+-----+----------+

Oracle MySQL 결과는 다음과 같습니다.

+-----+----------+
| id  | album_id |
+-----+----------+
| 623 |     NULL |
| 622 |     NULL |
| 621 |     NULL |
| 620 |     NULL |
| 619 |     NULL |
| 618 |     NULL |
| 617 |     NULL |
| 616 |      196 |<-- different
| 615 |     NULL |
| 614 |     NULL |
| 613 |     NULL |
| 612 |      194 |
| 611 |      194 |<-- different
| 610 |     NULL |
| 609 |     NULL |
| 608 |      193 |
| 607 |      193 |<-- different
| 606 |     NULL |
| 605 |     NULL |
| 604 |     NULL |
+-----+----------+

또한 쿼리를 설명하면 두 데이터베이스가 쿼리를 다르게 해석하고 있음을 알 수 있습니다("추가" 열 참조).

마리아DB

+------+-------------+-------+--------+---------------+---------+---------+------------------------+------+-------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                    | rows | Extra       |
+------+-------------+-------+--------+---------------+---------+---------+------------------------+------+-------------+
|    1 | SIMPLE      | t1    | index  | NULL          | PRIMARY | 4       | NULL                   |   20 | Using where |
|    1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | foo.t1.data_id         |    1 | Using where |
+------+-------------+-------+--------+---------------+---------+---------+------------------------+------+-------------+

Oracle MySQL

+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                       | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------------+
|  1 | SIMPLE      | t1    | index  | NULL          | PRIMARY | 4       | NULL                      |   20 | Using where |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | foo.t1.data_id            |    1 |             |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------------+

이에 대한 해결 방법을 찾았지만, 여기서 무슨 일이 일어나고 있는지 정말 알고 싶습니다.좋은 생각 있는 사람?

직접 시도하려면 이 예에서 사용한 데이터 덤프를 여기에서 찾을 수 있습니다.

감사해요.

edit: 대부분의 데이터베이스에서는 쿼리가 유효하지 않은 SQL이지만 MySQL은 이를 허용하지만 데이터베이스는 GROUP BY에서 집계된 값을 자유롭게 반환할 수 있다고 코멘트에서 지적되었습니다.여기서 일어나고 있는 것처럼 보이는 것은 다르다는 것을 지적하고 싶습니다.왜냐하면 그 가치는 애매하지 않기 때문입니다.일치하는 행은 1개뿐이지만 MariaDB가 반환하는 값과 일치하지 않습니다.

SELECT t1.id, t2.album_id
FROM t1
    JOIN t2
        ON t1.data_id = t2.id
WHERE
    t1.id = 616
; 

+-----+----------+
| id  | album_id |
+-----+----------+
| 616 |      196 |
+-----+----------+
1 row in set (0.00 sec) 

이는 MariaDB의 버그로, 2가지 조건에서 group by와 left join을 사용하면 잘못된 결과를 얻을 수 있습니다.

이 쿼리는 GROUP BY에 대해 계산된 MySql 확장을 사용하고 있습니다.
상세한 것에 대하여는, 다음의 링크를 참조해 주세요.http://dev.mysql.com/doc/refman/5.7/en/group-by-extensions.html

그들은 분명히 말했다:

MySQL은 GROUP BY 절에서 이름이 지정되지 않은 집계되지 않은 열을 선택 목록에서 참조할 수 있도록 GROUP BY의 사용을 확장합니다.이는 이전 쿼리가 MySQL에서 합법적이라는 것을 의미합니다.이 기능을 사용하면 불필요한 열 정렬 및 그룹화를 방지하여 성능을 향상시킬 수 있습니다.그러나 GROUP BY에서 이름이 지정되지 않은 각 비집약 열의 모든 값이 각 그룹에 대해 동일한 경우 주로 유용합니다.서버는 각 그룹에서 임의의 값을 자유롭게 선택할 수 있기 때문에 같은 값이 아닌 한 선택한 값은 불확정입니다.

상기 사항을 고려하여 이 동작은 사양에 따릅니다.

언급URL : https://stackoverflow.com/questions/23834748/inconsistency-between-mariadb-and-mysql