3

I am running a MySQL 5.7 server with two tables:

Table_A

id   name
.........
1    foo
2    bar

Table_B

  id   parent_id   name    created_at (default: CURRENT_TIMESTAMP)
  ................................................................
  1    1           x       2019-01-01 10:00:00  
  2    1           y       2019-12-31 22:00:00

I have this query to get a Table_B entry count grouped by Table_A name with the timestamp of the most recent Table_B entry:

SELECT 
    a.name,
    COUNT(b.id) AS entry_count_in_b,
    MAX(b.created_at) AS last_entry_in_b
FROM 
    Table_A a
LEFT JOIN 
    Table_B b ON b.parent_id = a.id
GROUP BY 1

The MySQL command line client returns the expected output:

+------+------------------+---------------------+
| name | entry_count_in_b | last_entry_in_b     |
+------+------------------+---------------------+
| bar  |                0 | NULL                |
| foo  |                2 | 2019-12-31 22:00:00 |
+------+------------------+---------------------+
2 rows in set (0.0267 sec)

When I run the same query in Python (3.7) using PyMySql (0.9.3) however, the cursor generates the following warning:

lib/python3.7/site-packages/pymysql/cursors.py:329: Warning: (1292, "Incorrect datetime value: '0000-00-00 00:00:00' for column 'last_entry_in_b' at row 3")

It might be a bug in PyMySql since the warning is referencing row 3 in a 2 row result set.

Trying to narrow down the root cause, I thought it might have to do with a bug in the aggregation of NULL values in a timestamp column so I updated the query to only calculate MAX(b.created_at) when COUNT(table_b.id) > 1:

SELECT 
    a.name,
    COUNT(b.id) AS entry_count_in_b,
    CASE
        WHEN COUNT(b.id) = 0 THEN NULL
        ELSE MAX(b.created_at) 
    END AS last_entry_in_b
FROM 
    Table_A a
LEFT JOIN 
    Table_B b ON b.parent_id = a.id
GROUP BY 1

This generates the same warning but now referencing a column named 'tmp_field_0'.

lib/python3.7/site-packages/pymysql/cursors.py:329: Warning: (1292, "Incorrect datetime value: '0000-00-00 00:00:00' for column 'tmp_field_0' at row 3")

I am wondering if anyone has seen / solved this issue before? If not, I will file an issue for PyMySql.

I found other questions discussing this same warning but they cover cases where people try to insert / update '0000-00-00 00:00:00' values or filtering for them.

Stipy
  • 51
  • 3
  • You don't need the `CASE` expression. If there are no rows for a name, `MAX(b.created_at)` will be `NULL` automatically. I'm not sure if that will make a difference for PyMySQL, though. – Barmar Mar 09 '19 at 01:33
  • Correct, that is why it is not in the initial query. I added the `CASE` expression in the second query because it changes the warning and might help identify the root cause. – Stipy Mar 09 '19 at 01:35
  • Seems like a bug in PyMySQL. It's getting the type of the column, and not dealing with null values properly. – Barmar Mar 09 '19 at 20:40
  • I can confirm that I'm getting the same error. I print my sql string query out which runs well on the mysql server itself, but gives an error from the pymysql code: insert into media (name, platform, dateadded, type) values ("games", "steam", "2019/04/29:12:28:18", "Game"); /Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pymysql/cursors.py:329: Warning: (1292, "Incorrect date value: '2019/04/29:12:28:18' for column 'dateAdded' at row 1") self._do_get_result() – Adam Apr 29 '19 at 11:34

0 Answers0