Opened 2 months ago

Closed 2 months ago

#32418 closed Bug (duplicate)

F() expressions crash due to some interval/duration error on MySQL.

Reported by: Hoshi Yamazaki Owned by: nobody
Component: Database layer (models, ORM) Version: 3.1
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Mariusz Felisiak)

My code:

duration_expression = ExpressionWrapper(
    datetime.timedelta(minutes=1) * F('duration'),
    output_field=DurationField())
booking_end = ExpressionWrapper(
    F('booking_dt') + duration_expression,
    output_field=DateTimeField())
booking_qs.annotate(
    booking_end=booking_end).get(
    Q(Q(booking_dt__lte=first_interval),
      Q(booking_dt__gte=next_interval)) |
    Q(Q(booking_end__lte=first_interval),
      Q(booking_end__gte=next_interval)))

Error I get:

(1064, "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 '* `carwashes_carwashbooking`.`duration`) MICROSECOND) AS `booking_end` FROM `car' at line 1")

SQL query:

('SELECT `carwashes_carwashbooking`.`id`, '
 '`carwashes_carwashbooking`.`carwash_id`, '
 '`carwashes_carwashbooking`.`created_dt`, '
 '`carwashes_carwashbooking`.`booking_dt`, '
 '`carwashes_carwashbooking`.`duration`, `carwashes_carwashbooking`.`car_id`, '
 '`carwashes_carwashbooking`.`car_name`, `carwashes_carwashbooking`.`plate`, '
 '`carwashes_carwashbooking`.`status`, `carwashes_carwashbooking`.`driver_id`, '
 '`carwashes_carwashbooking`.`phone_number`, '
 '`carwashes_carwashbooking`.`service_id`, `carwashes_carwashbooking`.`notes`, '
 '(`carwashes_carwashbooking`.`booking_dt` + INTERVAL (INTERVAL 60000000 '
 'MICROSECOND * `carwashes_carwashbooking`.`duration`) MICROSECOND) AS '
 '`booking_end` FROM `carwashes_carwashbooking` WHERE '
 '(`carwashes_carwashbooking`.`carwash_id` = %s AND '
 '`carwashes_carwashbooking`.`driver_id` = %s AND '
 '((`carwashes_carwashbooking`.`booking_dt` <= %s AND '
 '`carwashes_carwashbooking`.`booking_dt` >= %s) OR '
 '((`carwashes_carwashbooking`.`booking_dt` + INTERVAL (INTERVAL 60000000 '
 'MICROSECOND * `carwashes_carwashbooking`.`duration`) MICROSECOND) <= %s AND '
 '(`carwashes_carwashbooking`.`booking_dt` + INTERVAL (INTERVAL 60000000 '
 'MICROSECOND * `carwashes_carwashbooking`.`duration`) MICROSECOND) >= %s))) '
 'LIMIT 21')

Stackoverflow issue: https://stackoverflow.com/questions/66044255/django-returns-sql-syntax-error-when-using-expressionwrapper-and-f-expressions

Change History (3)

comment:1 Changed 2 months ago by Hoshi Yamazaki

Description: modified (diff)

comment:2 Changed 2 months ago by Hoshi Yamazaki

Summary: Django f expressions seems to not work with MySQLDjango f expressions seems to not work with MySQL due to some interval/duration error

comment:3 Changed 2 months ago by Mariusz Felisiak

Component: UncategorizedDatabase layer (models, ORM)
Description: modified (diff)
Resolution: duplicate
Status: newclosed
Summary: Django f expressions seems to not work with MySQL due to some interval/duration errorF() expressions crash due to some interval/duration error on MySQL.

Duplicate of #28925, fixed in dd5aa8cb5ffc0a89c4b9b8dee45c1c919d203489 (Django 3.2+).

Note: See TracTickets for help on using tickets.
Back to Top