RemindersScheduler generates invalid SQL

[jamiemccarthy]

I'm running MySQL 8, Rails 7.0.8, motor-admin 0.4.24 in production. I'm also testing against today's 0.4.25 locally, but looking at the diff for 0.4.25 I don't believe it affects this.

The Motor::Notes::RemindersScheduler.load_reminders method, here, generates SQL that's not valid for MySQL, namely:

SELECT `motor_reminders`.* FROM `motor_reminders`
LEFT JOIN motor_notifications
  ON cast(motor_notifications.record_id as int) = motor_reminders.id
    AND motor_notifications.record_type = 'Motor::Reminder'
WHERE `motor_reminders`.`scheduled_at` BETWEEN ? AND ?
  AND `motor_notifications`.`id` IS NULL

Locally I see

Loading development environment (Rails 7.0.8)
irb(main):001> Person.all.count
  Person Count (2.7ms)  SELECT COUNT(*) FROM `people`
=> 6
irb(main):002> Motor::Notes::RemindersScheduler.load_reminders
  Motor::Reminder Load (3.9ms)  SELECT `motor_reminders`.* FROM `motor_reminders` LEFT JOIN motor_notifications ON cast(motor_notifications.record_id as int) = motor_reminders.id AND motor_notifications.record_type = 'Motor::Reminder' WHERE `motor_reminders`.`scheduled_at` BETWEEN '2023-12-29 18:08:42.998672' AND '2023-12-30 00:08:59.999999' AND `motor_notifications`.`id` IS NULL
An error occurred when inspecting the object: #<ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int) = motor_reminders.id AND motor_notifications.record_type = 'Motor::Reminder' at line 1>

And in prod I see this same error in my honeycomb error events:

I guess this works in sqlite because int is a valid declared type.

I don't know if it works in postgres (but I don't think so?).

In MySQL, that cast type is spelled UNSIGNED INTEGER (or just UNSIGNED), not int:

mysql> select cast(record_id as int) from motor_notifications;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int) from motor_notifications' at line 1

mysql> select cast(record_id as unsigned) from motor_notifications;
Empty set (0.00 sec)

I don't know if Arel::Nodes::NamedFunction.new('CAST', ... would help in emitting the correct target type. I don't think so; I think you'd end up having to hard-code it for each of the supported databases anyway.

If I can help in reproducing this problem or testing a fix, please let me know, I'll be happy to.