Error occurs when creating a report in functional tests but the test doens't fail

[Jun Omae]

The following error is logged when functional tests running but the test doesn't fail.

2024-05-25 02:33:34,206 Trac[report] WARNING: Exception caught while executing Report {9}: "SELECT COUNT(*) FROM (\n\r\n              SELECT DISTINCT p.value AS __color__,\r\n               id AS ticket,\r\n               summary, component, milestone, t.type AS type,\r\n               reporter, time AS created,\r\n               changetime AS modified, description AS _description,\r\n               priority,\r\n               round(julianday('now') -\r\n                     julianday(changetime, 'unixepoch')) as days,\r\n               resolution,\r\n               owner as __group__\r\n              FROM ticket t\r\n              LEFT JOIN enum p ON p.name = t.priority AND\r\n                                  p.type = 'priority'\r\n              WHERE ((julianday('now') -\r\n                      julianday(changetime, 'unixepoch')) < 7)\r\n               AND status = 'closed'\r\n              ORDER BY __group__, changetime, p.value\r\n            \n) AS tab", args []
Traceback (most recent call last):
  File "/home/jun66j5/src/tracdev/svn/1.6-stable/trac/ticket/report.py", line 705, in execute_paginated_report
    cursor.execute(count_sql, args)
  File "/home/jun66j5/src/tracdev/svn/1.6-stable/trac/db/util.py", line 74, in execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jun66j5/src/tracdev/svn/1.6-stable/trac/db/sqlite_backend.py", line 89, in execute
    result = PyFormatCursor.execute(self, *args)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jun66j5/src/tracdev/svn/1.6-stable/trac/db/sqlite_backend.py", line 64, in execute
    return self._rollback_on_error(sqlite.Cursor.execute, sql,
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jun66j5/src/tracdev/svn/1.6-stable/trac/db/sqlite_backend.py", line 56, in _rollback_on_error
    return function(self, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
OperationalError: ambiguous column name: description

FunctionalTester.create_report has the following TODOs:

        # TODO: verify the url is correct
        # TODO: verify the report number is correct
        # TODO: verify the report does not cause an internal error
        # TODO: verify the title appears on the report list

It seems it's time to implement the TODOs.

[Jun Omae]

Proposed changes in [b18f5ada9/jomae.git] (jomae.git@t13752).

[Jun Omae]

The test is broken since [15933] (#11649).

[Jun Omae]

Fixed in [17810] and merged in [17811].

[Jun Omae]

Ah, the tests with PostgreSQL and MySQL fail due to lack of julianday function on the databases…. I didn't verify it with except SQLite.

PostgreSQL

2024-05-28 07:43:44,048 Trac[report] WARNING: Exception caught while executing Report {9}: "SELECT COUNT(*) FROM (\n\r\n
  SELECT DISTINCT p.value AS __color__,\r\n               id AS ticket,\r\n               summary, component, milestone, t.type
ype,\r\n               reporter, time AS created,\r\n               changetime AS modified, t.description AS _description,\r\n
         priority,\r\n               round(julianday('now') -\r\n                     julianday(changetime, 'unixepoch')) as day
\n               resolution,\r\n               owner as __group__\r\n              FROM ticket t\r\n              LEFT JOIN enum
N p.name = t.priority AND\r\n                                  p.type = 'priority'\r\n              WHERE ((julianday('now') -\r
                    julianday(changetime, 'unixepoch')) < 7)\r\n               AND status = 'closed'\r\n              ORDER BY _
up__, changetime, p.value\r\n            \n) AS tab", args []
Traceback (most recent call last):
  File "/home/jun66j5/src/tracdev/git/trac/ticket/report.py", line 705, in execute_paginated_report
    cursor.execute(count_sql, args)
  File "/home/jun66j5/src/tracdev/git/trac/db/util.py", line 74, in execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.UndefinedFunction: function julianday(unknown) does not exist
LINE 9:                round(julianday('now') -
                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
UndefinedFunction: function julianday(unknown) does not exist
LINE 9:                round(julianday('now') -
                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

MySQL

2024-05-28 07:45:00,199 Trac[report] WARNING: Exception caught while executing Report {9}: "SELECT COUNT(*) FROM (\n\r\n
  SELECT DISTINCT p.value AS __color__,\r\n               id AS ticket,\r\n               summary, component, milestone, t.type
ype,\r\n               reporter, time AS created,\r\n               changetime AS modified, t.description AS _description,\r\n
         priority,\r\n               round(julianday('now') -\r\n                     julianday(changetime, 'unixepoch')) as day
\n               resolution,\r\n               owner as __group__\r\n              FROM ticket t\r\n              LEFT JOIN enum
N p.name = t.priority AND\r\n                                  p.type = 'priority'\r\n              WHERE ((julianday('now') -\r
                    julianday(changetime, 'unixepoch')) < 7)\r\n               AND status = 'closed'\r\n              ORDER BY _
up__, changetime, p.value\r\n            \n) AS tab", args []
Traceback (most recent call last):
  File "/home/jun66j5/src/tracdev/git/trac/ticket/report.py", line 705, in execute_paginated_report
    cursor.execute(count_sql, args)
  File "/home/jun66j5/src/tracdev/git/trac/db/util.py", line 74, in execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jun66j5/src/tracdev/git/trac/db/mysql_backend.py", line 50, in execute
    return super().execute(query, args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jun66j5/venv/py312/lib/python3.12/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
             ^^^^^^^^^^^^^^^^^^
  File "/home/jun66j5/venv/py312/lib/python3.12/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/home/jun66j5/venv/py312/lib/python3.12/site-packages/pymysql/connections.py", line 558, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jun66j5/venv/py312/lib/python3.12/site-packages/pymysql/connections.py", line 822, in _read_query_result
    result.read()
  File "/home/jun66j5/venv/py312/lib/python3.12/site-packages/pymysql/connections.py", line 1200, in read
    first_packet = self.connection._read_packet()
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jun66j5/venv/py312/lib/python3.12/site-packages/pymysql/connections.py", line 772, in _read_packet
    packet.raise_for_error()
  File "/home/jun66j5/venv/py312/lib/python3.12/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/home/jun66j5/venv/py312/lib/python3.12/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
OperationalError: (1305, 'FUNCTION trac_utf8mb4.julianday does not exist')

[Jun Omae]

Fixed in [17812] and merged in [17813].