Description:
A query in MySQL returns nothing when a result set with a single record is expected. The query is as follows:
---------------------------------------------
SELECT DISTINCT
L1.id, A.name
FROM
Logins L1
INNER JOIN Logins L2
ON L1.id = L2.id AND DATEDIFF(L2.login_date, L1.login_date) BETWEEN 0 AND 4
INNER JOIN Accounts A
ON L1.id = A.id
GROUP BY
L1.id, L1.login_date, A.name
HAVING
COUNT(DISTINCT L2.login_date) = 5;
---------------------------------------------
In the context of the schema provided below, the goal of the query above is to return accounts that have logged in at least 5 days consecutively.
It may not help, but I tested the query on another machine with a different MySQL install and the problem persisted. I tried the query on a few different websites as well, and the query worked. I'm not sure what the problem is. Version information for all of the environments I tested the query in is included below.
---------- FAILURES ----------
My personal machine
version: 8.0.26
version_comment: Homebrew
version_compile_machine: x86_64
version_compile_os: macos10.14
version_compile_zlib: 1.2.11
---
Another machine
version: 8.0.26
version_comment: MySQL Community Server - GPL
version_compile_machine: x86_64
version_compile_os: macos11
version_compile_zlib: 1.2.11
---------- SUCCESSES ----------
leetcode.com (problem 1454)
version: 8.0.21
version_comment: MySQL Community Server - GPL
version_compile_machine: x86_64
version_compile_os: Linux
version_compile_zlib: 1.2.11
---
paiza.io
version: 8.0.22-0ubuntu0.20.04.3
version_comment: (Ubuntu)
version_compile_machine: x86_64
version_compile_os: Linux
version_compile_zlib: 1.2.11
---
db-fiddle.com
version: 8.0.12
version_comment: Source distribution
version_compile_machine: x86_64
version_compile_os: Linux
version_compile_zlib: 1.2.11
How to repeat:
The schema for use with the query above is as follows:
---------------------------------------------
CREATE TABLE
Accounts (id int, name varchar(10));
INSERT INTO
Accounts (id, name)
VALUES
(1, 'Winston'),
(7, 'Jonathan');
CREATE TABLE
Logins (id int, login_date date);
INSERT INTO
Logins
VALUES
(7, '2020-05-30'),
(1, '2020-05-30'),
(7, '2020-05-31'),
(7, '2020-06-01'),
(7, '2020-06-02'),
(7, '2020-06-02'),
(7, '2020-06-03'),
(1, '2020-06-07'),
(7, '2020-06-10');
---------------------------------------------
The result set that should be returned is as follows:
+------+----------+
| id | name |
+------+----------+
| 7 | Jonathan |
+------+----------+
Instead, nothing at all is returned.
Description: A query in MySQL returns nothing when a result set with a single record is expected. The query is as follows: --------------------------------------------- SELECT DISTINCT L1.id, A.name FROM Logins L1 INNER JOIN Logins L2 ON L1.id = L2.id AND DATEDIFF(L2.login_date, L1.login_date) BETWEEN 0 AND 4 INNER JOIN Accounts A ON L1.id = A.id GROUP BY L1.id, L1.login_date, A.name HAVING COUNT(DISTINCT L2.login_date) = 5; --------------------------------------------- In the context of the schema provided below, the goal of the query above is to return accounts that have logged in at least 5 days consecutively. It may not help, but I tested the query on another machine with a different MySQL install and the problem persisted. I tried the query on a few different websites as well, and the query worked. I'm not sure what the problem is. Version information for all of the environments I tested the query in is included below. ---------- FAILURES ---------- My personal machine version: 8.0.26 version_comment: Homebrew version_compile_machine: x86_64 version_compile_os: macos10.14 version_compile_zlib: 1.2.11 --- Another machine version: 8.0.26 version_comment: MySQL Community Server - GPL version_compile_machine: x86_64 version_compile_os: macos11 version_compile_zlib: 1.2.11 ---------- SUCCESSES ---------- leetcode.com (problem 1454) version: 8.0.21 version_comment: MySQL Community Server - GPL version_compile_machine: x86_64 version_compile_os: Linux version_compile_zlib: 1.2.11 --- paiza.io version: 8.0.22-0ubuntu0.20.04.3 version_comment: (Ubuntu) version_compile_machine: x86_64 version_compile_os: Linux version_compile_zlib: 1.2.11 --- db-fiddle.com version: 8.0.12 version_comment: Source distribution version_compile_machine: x86_64 version_compile_os: Linux version_compile_zlib: 1.2.11 How to repeat: The schema for use with the query above is as follows: --------------------------------------------- CREATE TABLE Accounts (id int, name varchar(10)); INSERT INTO Accounts (id, name) VALUES (1, 'Winston'), (7, 'Jonathan'); CREATE TABLE Logins (id int, login_date date); INSERT INTO Logins VALUES (7, '2020-05-30'), (1, '2020-05-30'), (7, '2020-05-31'), (7, '2020-06-01'), (7, '2020-06-02'), (7, '2020-06-02'), (7, '2020-06-03'), (1, '2020-06-07'), (7, '2020-06-10'); --------------------------------------------- The result set that should be returned is as follows: +------+----------+ | id | name | +------+----------+ | 7 | Jonathan | +------+----------+ Instead, nothing at all is returned.