MariaDB의 IN 문에 잘못된 결과가 있습니까?

MariaDB의 IN 문에 잘못된 결과가 있습니까?

IN 문을 사용할 때 MariaDB 인스턴스에서 상당히 이상한 문제가 발생했습니다.

예를 들어 일부 집계된 제품 볼륨 판매 장치에 대한 정보가 필요한 경우 다음과 같이 쿼리를 실행합니다.

SELECT
SUM( l.volume_given ) AS volume,
COUNT( l.id ) AS sessions,
l.order AS container,
MIN( dev.NAME ) AS NAME,
MIN( dev.address ) AS address,
dev.id AS id
FROM
devices AS dev
JOIN my_logs AS l ON dev.id = l.device_id
WHERE
l.created_at >= '2020-08-31 21:00:00'
AND l.created_at <= '2020-10-21 21:00:00'
AND l.water_type = 0
AND l.device_id IN (*some 20 IDs here*)
GROUP BY container
ORDER BY sessions DESC;

이상한 것은 ID = 114의 경우 총 232개의 행 중 이 장치에 대한 행이 4개밖에 없다는 것입니다.그러나 동일한 쿼리를 실행할 경우

AND l.device_id = 114

저는 이 ID에 44행을 얻었고, 이것이 실제 결과입니다.

제가 시도한 것은 다음과 같습니다.

1. JOIN 항목을 건너뛰고 사용만my_logs테이블;
2. 다른 날짜 간격을 시도합니다.
3. 다른 테이블에서 유사한 쿼리를 시도합니다.

이 모든 것들과 차이가 없습니다.

당신은 무엇을 확인해야 할지 생각해 보셨습니까?감사합니다!

만약 내가 당신을 올바르게 따른다면, 당신은 원하는 것입니다.id에서GROUP BY절:

GROUP BY l.order, dev.id

SQL에서 집계되지 않은 모든 열은SELECT절은 다음에서 반복되어야 합니다.GROUP BY절 (기능적으로 해당 절에 속하는 다른 열에 종속되지 않는 한 - 하지만 그것은 다른 이야기입니다.)

MySQL은 초기 버전에서 이 문제에 대해 느슨하게 대처하여, 이러한 규칙을 염두에 두지 않으면 디버깅하기 어려운 반직관적인 동작을 유발합니다.항상 sql 모드를 사용하도록 설정해야 합니다.ONLY_FULL_GROUP_BY따라서 이러한 문제는 구문 오류로 처리됩니다.

수정된 내용입니다.GROUP BY절은 별칭이 아닌 실제 열 이름을 사용합니다. 이는 표준 SQL이기도 합니다(MySQL은 그렇지 않습니다).

뒤집으면 속도가 빨라질 수 있습니다.

SELECT  l2.volume, l2.sessions, l2.container,
        dev.NAME, dev.address, dev.id
    FROM  
    (
        SELECT  l.device_id, l.order AS container,
                SUM( l.volume_given ) AS volume,
                COUNT(*) AS sessions
            FROM  logs AS l
            WHERE  l.created_at >= '2020-08-31 21:00:00'
              AND  l.created_at <= '2020-10-21 21:00:00'
              AND  l.water_type = 0
              AND  l.device_id IN (*some 20 IDs here*)
            GROUP BY  l.device_id, container 
    ) AS l2
    JOIN  devices AS dev  ON dev.id = l2.device_id
    ORDER BY  l2.sessions DESC

또한 카운트(*) 초과 카운트를 수정할 수 있습니다.

추천합니다

INDEX(water_type, device_id)
INDEX(water_type, created_at)

언급URL : https://stackoverflow.com/questions/64577440/incorrect-results-with-in-statement-in-mariadb