Skip to content

MySQL 8 admin summary compatibility

Max Kostikov requested to merge (removed):dev into dev

It fixes strange MySQL 8 behaviour with COUNT(*)

root@zotum:/usr/local/www/zotum.net # mysql -uroot -p hubzilla
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 257009
Server version: 8.0.14 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [hubzilla]> SELECT COUNT(*) AS total, COUNT(CASE WHEN account_expires < NOW() AND account_expires > '0001-01-01 00:00:00' THEN 1 ELSE NULL END) AS expired, COUNT(CASE WHEN (account_flags & 2)>0 THEN 1 ELSE NULL END) AS blocked, COUNT(CASE WHEN account_expires > NOW() THEN 1 ELSE NULL END) AS expiring FROM account;

+-------+---------+---------+----------+
| total | expired | blocked | expiring |
+-------+---------+---------+----------+
|   102 |     102 |     102 |      102 |
+-------+---------+---------+----------+
1 row in set (0.01 sec)

root@localhost [hubzilla]> SELECT COUNT(CASE WHEN account_id > 0 THEN 1 ELSE NULL END) AS total, COUNT(CASE WHEN account_expires < NOW() AND account_expires > '0001-01-01 00:00:00' THEN 1 ELSE NULL END) AS expired, COUNT(CASE WHEN (account_flags & 2)>0 THEN 1 ELSE NULL END) AS blocked, COUNT(CASE WHEN account_expires > NOW() THEN 1 ELSE NULL END) AS expiring FROM account;
+-------+---------+---------+----------+
| total | expired | blocked | expiring |
+-------+---------+---------+----------+
|   102 |       0 |       1 |        0 |
+-------+---------+---------+----------+
1 row in set (0.00 sec)

Merge request reports