Different results of running pure SQL query from Django and from PGAdmin

[Pierre-Louis K.]

Hello django-users,

I have been encountering a strange behaviour when trying to run SQL directly with a cursor. I am using Django 1.11 with porstgres 9.5.The query normally returns 7 rows with 2 columns.

Symptoms:

- When reading the queryset of a query I get wrong results -> I still have 7 rows with two columns but values of the second column are weirdly similar in pair where they should be all different

ex: I get 500-500-800-800 where nomal results should be 500 400 800 700

- If I run the query directly (extracted from the cursor object in debug) with pgadmin I get the expected results

- The first columns behaves as expected

I use cursor.execute() and cursor.fetchall() to run the query and read the results.

I don't know if it's a bug or if I do something wrong, that's why I ask it there before posting a ticket.

If anyone has encountered the bug or has an idea I am interested.

Cheers,

Pierre-Louis K.

[Jason]

Hard to say what's happening without any code.  

[pierre-louis...@terabee.com]

Here is the body of the code that runs the query and fetch results:

with connection.cursor() as cursor:
    sql = (
        ...
    )
    cursor.execute(sql, [params...])

    columns = [col[0] for col in cursor.description]
    for row in cursor.fetchall():
        print(row)

The results from this code is the following :

('Tue', Decimal('331.0000000000000000'))
('Wed', Decimal('331.0000000000000000'))
('Thu', Decimal('403.0000000000000000'))
('Fri', Decimal('403.0000000000000000'))

And if I extract the generated query (from cursor.cursor.query) and run it in pgadmin I get:

"Tue";331.0000000000000000
"Wed";288.0000000000000000
"Thu";403.0000000000000000
"Fri";51.0000000000000000

I checked that is it the same DB (same docker container at 0.0.0.0 in both case),
I am quite surprised to see such an unexpected behaviour, this breaks a bit the trust I had in the Django Db layer :/

Hopes this gives you ideas,

Cheers

On Saturday, 8 September 2018 13:54:02 UTC+2, Jason wrote:
Hard to say what's happening without any code.

---

This message including attachments is confidential and may be legally protected from disclosure. If you are not the intended recipient, please notify the sender by return email immediately. Any disclosure, reproduction, copying, distribution, or other dissemination use of this communication is strictly prohibited.
The sender of this email is representing Terabee SAS and can only conclude any binding agreement under final approval by the company's management authority.

[Bill-Torcaso-Oxfam]

I'd be interested to see a printout of 'columns' and 'cursor.description'.

One explanation would be that your for-loop is not actually accessing the data that you think it is, or that your query is not actually fetching the data that you think it is.  

No criticism of your query implied - just trying to get more visibility into the actual results.  

[Pierre-Louis K.]

Hello Bill,

I managed to solve this in the end, the issue was that a cursor created in Django would enforce TIME_ZONE settings in the PostgreSQL session, where by default it is set to 'UTC' in my case. This means that all date manipulation was impacted, hence the unexpected results.

The work around is to set the SQL session settings just before running the query with something like:

cursor.execute("SET TIMEZONE to UTC")

There is a nice explanation about this here: http://blog.untrod.com/2016/08/actually-understanding-timezones-in-postgresql.html

Quoting :

"For instance, when Django connects to Postgres, it 'ensures timezone' and slaps the application time zone onto the psycopg2 connection so you get back timestamps in the same timezone you put them in (assuming you are inserting local times). That's clever, but can lead to incredibly confusion if you are trying to compare results from the same database, but via 2 different connections, which are set to two different time zones."

And a similar bug ticket : https://code.djangoproject.com/ticket/23524

Hope this will help someone else,