Skip to content

django-pyodbc query with OFFSET+LIMIT issue #157

@CrVik

Description

@CrVik

Hello!
I have issue after try get the query with OFFSET+LIMIT
if i try to get with offset = 0 and limit=3 then i get normal queryset and in this queryset values in their places, i.e. field.id=id, field.name=name
But if i try get queryset with offset more then zero + limit, i get the the queryset with additional field ROW_Number(i check it in sql server profiler) and i find it ({row_num_col}) in the compiler.py code:

sql = "SELECT {row_num_col}, {outer} FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY {order}) as {row_num_col}, {inner}) as QQQ where {where}".format(
                outer=outer_fields,
                order=order,
                inner=inner_select,
                where=where_row_num,
                row_num_col=row_num_col
            )

and this filed gives the offset for get values in qeuryset.
Because if i try to get value from ID field i get value of ROW_Number, and if i try to get value from field NAME i get value ID field.

example on the picture:
photo_2018-03-07_00-37-12

I try to remove in compiler.py filed {row_num_col} from query, after that works fine, but I'm afraid of doing this, all of a sudden it can affect something else.

Help please!

MS SQL server 2014 (firstly tested on MS SQL server 2005)
pyodbc version 1.1.3
settings:

DATABASES = {
    'default': {
        'ENGINE': 'django_pyodbc',
        'HOST': '10.8.0.201\DATABASES',
        'PORT': '1433',
        'USER': 'sa',
        'PASSWORD': '123321',
        'NAME': 'test_db',
        'OPTIONS': {
            'driver': 'ODBC Driver 13 for SQL Server',
            'host_is_server': True,
            'autocommit': True,
            'unicode_results': True,
        },
    }
}

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions