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 Changed 9 years ago by kl4us

Summary: Oracle Backend OprationsOracle Backend Operations

comment:2 Changed 9 years ago by Aymeric Augustin

Can you show the generated SQL?

You can obtain it with:

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

comment:3 Changed 9 years ago by kl4us

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 Changed 9 years ago by kl4us

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 Changed 9 years ago by Aymeric Augustin

Triage Stage: UnreviewedAccepted

comment:6 Changed 9 years ago by Shai Berger

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 Changed 9 years ago by kl4us

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 Changed 7 years ago by Mariusz Felisiak

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

comment:9 Changed 7 years ago by Mariusz Felisiak

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 Changed 7 years ago by Tim Graham

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