Opened 9 years ago

Closed 7 years ago

#24194 closed Bug (invalid)

Oracle Backend Operations

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

Description

My Object have a filed named timestamp:

timestamp = models.DateTimeField(auto_now_add=True)

When I use a function like:

from django.db import connection
extract_hour = connection.ops.datetime_trunc_sql('hh24','timestamp', None) # None because USE_TZ = False

startdate = datetime.datetime.now() - datetime.timedelta(days=1)
    MyObject.objects.filter(timestamp__gt=startdate).extra({'hour':extract_hour}).values("hour", "status").annotate(Count('pk')).order_by('hour')

the result error is:

DatabaseError: ORA-00907: missing right parenthesis

I'm using django 1.7.3 and python 2.7.

Change History (10)

comment:1 by kl4us, 9 years ago

Summary: Oracle Backend OprationsOracle Backend Operations

comment:2 by Aymeric Augustin, 9 years ago

Can you show the generated SQL?

You can obtain it with:

qs = MyObject.objects.filter....   # the failing query
print(qs.query)

comment:3 by kl4us, 9 years ago

Yes sir

SELECT (('timestamp', [])) AS "HOUR", "SMS_SMS"."STATUS", COUNT("SMS_SMS"."ID") AS "PK__COUNT" FROM "SMS_SMS" WHERE "SMS_SMS"."TIMESTAMP" > 2015-01-20 15:48:17.746855 GROUP BY "SMS_SMS"."STATUS", ('timestamp', []), (('timestamp', [])) ORDER BY "HOUR" ASC

comment:4 by kl4us, 9 years ago

I think the problem was return sql, [] in datetime_trunc_sql and in all datetime_*_sql function.

If i use

    extract_hour = connection.ops.datetime_trunc_sql('hour','timestamp', None)
    startdate = datetime.datetime.now() - datetime.timedelta(days=1)
    status_report_2 = MyObject.objects.filter(timestamp__gt=startdate).extra({'hour':extract_hour[0]}).values("hour", "status").annotate(Count('pk')).order_by('hour')

with only the sql of return sql, [] all works fine.

comment:5 by Aymeric Augustin, 9 years ago

Triage Stage: UnreviewedAccepted

comment:6 by Shai Berger, 9 years ago

Actually, this ticket appears to require substantial corrections to its summary and description.

First of all, connection.ops.datetime_trunc_sql is internal, undocumented API. For this, my initial reaction was to want to close this ticket as invalid.

However, there is a real issue here -- connection.ops.date_trunc_sql and connection.ops.datetime_trunc_sql behave differently: The former returns just sql, and the latter sql and params. This is a wart and should be fixed, even though it is internal API. Also, I haven't reviewed other methods -- I'm not sure about the extent of the required clean-up.

However, the problem is general, and not Oracle-specific. The Oracle-specific parts of the description -- including even the name of the field (fields with keyword names are known to be problematic on Oracle) -- are red herrings.

Since correcting this ticket according to my understanding would involve a complete rewrite, I'm just leaving this comment here for now, waiting for approval or rebuttal from the original poster or others.

comment:7 by kl4us, 9 years ago

Shaib,
you are right and my english is bad. Ok, the fields with keyword names are known to be problematic on Oracle, but in this case the problem is (('my_datetime_field', [])) in datetime_extract_sql and datetime_trunc_sql functions with Orcle backend. I didn't test with other backend.

Ok, those are internal function, I felt it was only fair.

comment:8 by Mariusz Felisiak, 7 years ago

I created separate ticket for unify datetime functions behavior -> #27802.

comment:9 by Mariusz Felisiak, 7 years ago

IMO ticket doesn't contain bug report and it should be closed as invalid. connection.ops.datetime_trunc_sql is a part of internal API that works properly. If you take only sql from result tuple then everything works fine:

>>> extract_hour,_ = connection.ops.datetime_trunc_sql('hh24','sth', _get_timezone_name(get_current_timezone()))
>>> print(extract_hour)
CAST(TO_DATE(TO_CHAR((FROM_TZ(sth, '0:00') AT TIME ZONE 'Europe/Warsaw'), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP)

comment:10 by Tim Graham, 7 years ago

Resolution: invalid
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top