SQL error after commit 4c10b11

[superpuffin]

Hi!

After upgrading, i could no longer view dmarc reports. I traced commits and found out it's probally caused by commit 4c10b11, after which the bug occurs. Looking over the code, I can't for the life of me figure out what is causing this. Below you'll find debug info and the sql error. It would be great if you clould take a look at this!

Debug info

D=
O=

DMARC=all

sql where = 

(I prettified this to increase legibility)
Data List sql: 

SELECT
  report.*,
  rcount,
  dkim_align_min,
  spf_align_min,
  dkim_result_min,
  spf_result_min,
  dmarc_result_min,
  dmarc_result_max
FROM
  report
  LEFT JOIN (
    SELECT
      SUM(rcount) AS rcount,
      serial,
      dkim_align,
      spf_align,
      dkimresult,
      spfresult,
      MIN(
        (
          CASE
            WHEN dkim_align = 'fail' THEN 0
            WHEN dkim_align = 'pass' THEN 2
            ELSE 1
          END
        )
      ) AS dkim_align_min,
      MIN(
        (
          CASE
            WHEN spf_align = 'fail' THEN 0
            WHEN spf_align = 'pass' THEN 2
            ELSE 1
          END
        )
      ) AS spf_align_min,
      MIN(
        (
          CASE
            WHEN dkimresult = 'fail' THEN 0
            WHEN dkimresult = 'pass' THEN 2
            ELSE 1
          END
        )
      ) AS dkim_result_min,
      MIN(
        (
          CASE
            WHEN spfresult = 'fail' THEN 0
            WHEN spfresult = 'pass' THEN 2
            ELSE 1
          END
        )
      ) AS spf_result_min,
      MIN(
        (
          CASE
            WHEN dkim_align = 'fail' THEN 0
            WHEN dkim_align = 'pass' THEN 1
            ELSE 3
          END
        ) + (
          CASE
            WHEN spf_align = 'fail' THEN 0
            WHEN spf_align = 'pass' THEN 1
            ELSE 3
          END
        )
      ) AS dmarc_result_min,
      MAX(
        (
          CASE
            WHEN dkim_align = 'fail' THEN 0
            WHEN dkim_align = 'pass' THEN 1
            ELSE 3
          END
        ) + (
          CASE
            WHEN spf_align = 'fail' THEN 0
            WHEN spf_align = 'pass' THEN 1
            ELSE 3
          END
        )
      ) AS dmarc_result_max
    FROM
      rptrecord
    GROUP BY
      serial
  ) AS rptrecord ON report.serial = rptrecord.serial
GROUP BY
  serial
ORDER BY
  maxdate DESC

SQL error:

Query failed: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dmarc.rptrecord.dkim_align' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (Error #1055)

[jnew-gh]

I think I know what the problem is (the GROUP BY clause) but it is going to take me a bit to test a solution (rather than a workaround which would be quick but not right).

In the meantime, could you post the result of the following commands in your mysql: SELECT @@sql_mode;

You can run this in the mysql command line or in something like phpMyAdmin (with the "Full texts" option chosen). In both cases, please select the dmarc database first.

[jnew-gh]

Could you also post the result of SELECT @@GLOBAL.sql_mode;

Thanks.

[superpuffin]

Here are the results:

mysql> SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode
                    |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

and

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode
                    |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[jnew-gh]

Thanks, @superpuffin.

Your server has implemented the ONLY_FULL_GROUP_BY option which confirms that the GROUP BY clauses as written in the SQL are causing the error. From my research, implementing ONLY_FULL_GROUP_BY is a good thing. For a more detailed explanation, see something like this

Suspecting that this was the issue, I came up with an updated SQL statement and I will submit a pull request shortly. The update works on my server when I activate ONLY_FULL_GROUP_BY so please test it on yours and let us know if the problem is resolved.

[superpuffin]

Great!

I'll take a look at the page you mentioned. I just did a quick check; everything worked like expected after checking out #68.

Thanks!

[jnew-gh]

Good to hear it fixes the problem.

I'm not an SQL expert, so I learned something new about the GROUP BY clause that I didn't know before.

[techsneeze]

Changes have been merged, so I'll go ahead and close this issue.