1

My database has a number of sessions.

@schema
class Session(dj.Manual):
    definition = """
    -> Subject
    session_datetime: datetime
    """

I could fetch or delete them as follows.

total_sessions = session.Session # N=4
total_sessions.fetch()   # Successful output
total_session.delete()   # Successful cascading delete prompt

If I'm only interested in sessions after a given year/month, I can generate a query with a relative restriction, fully specifying datetime.

later_sessions1 = (session.Session & 'session_datetime > "2021-06-01 12:00:00"') # N=2
later_sessions1.fetch()   # Successful output
later_sessions1.delete()  # Successful cascading delete prompt

If I don't fully specify datetime, I can still fetch the data, but deletion fails.

later_sessions2 = (session.Session & 'session_datetime > "2021-06"') # N=2
later_sessions2.fetch()   # Successful output
later_sessions2.delete()  # OperationalError: (1292, "Incorrect datetime value: '2021-06' for column 'session_datetime' at row 1")

Is this intended? Should fetch() and delete() fail under the same conditions?

EDIT: calling make_sql() on later_sessions2 returns the following

'SELECT DISTINCT `subject`,`session_datetime` FROM `test_session`.`session` WHERE(session_datetime > "2021-06")'

If called on the fully specified later_sessions1, the last WHERE component includes the full datetime.

Chris Broz
  • 136
  • 8
  • Interesting. That doesn't seem expected but curious what is the output of `later_sessions.make_sql()`. – Raphael Guzman Mar 22 '22 at 16:43
  • Edited to include the `make_sql()`, and differentiate `later_sessions` 1 and 2. – Chris Broz Mar 22 '22 at 16:51
  • This seems like a quirk of MySQL rather than DataJoint. Can you reproduce this in SQL directly? – Dimitri Yatsenko Mar 22 '22 at 17:18
  • Right, what happens if you try as Dimitri is suggesting: `DELETE FROM \`test_session\`.\`session\` WHERE session_datetime > '2021-06'` – Raphael Guzman Mar 22 '22 at 20:36
  • To run this directly with DataJoint you can do: `dj.conn().query("DELETE FROM \`test_session\`.\`session\` WHERE session_datetime > '2021-06'").fetchall()` – Raphael Guzman Mar 22 '22 at 20:54
  • With the datetime fully specified, I get an `IntegrityError: Cannot delete or update a parent row`. With `2021-06` alone, I get the same `OperationalError: (1292`, so yes, it does seem to be a quirk of MySQL – Chris Broz Mar 22 '22 at 20:59

1 Answers1

0

Per comment thread above with Raphael and Dimitri, this is a quirk of MySQL rather than DataJoint

Chris Broz
  • 136
  • 8