Opened 5 years ago
Last modified 5 years ago
#32418 closed Bug
Django f expressions seems to not work with MySQL — at Initial Version
| 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
My code:
`python
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_dtlte=first_interval),
Q(booking_dtgte=next_interval)) |
Q(Q(booking_endlte=first_interval),
Q(booking_endgte=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_dtlte=first_interval),
Q(booking_dtgte=next_interval)) |
Q(Q(booking_endlte=first_interval),
Q(booking_endgte=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:
`sql
('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_endFROMcarwashes_carwashbookingWHERE '
'(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