Opened 4 years ago

Last modified 4 years ago

#32418 closed Bug

Django f expressions seems to not work with MySQL — at Version 1

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 Hoshi Yamazaki)

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)))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 (1)

comment:1 by Hoshi Yamazaki, 4 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top