Django

Code

Changeset 9228

Show
Ignore:
Timestamp:
10/10/08 19:26:11 (1 month ago)
Author:
mtredinnick
Message:

[1.0.X] Fixed .distinct() not working with slicing in Oracle, due to the
row numbers necessarily being distinct.

Backport of r9221 from trunk (since Ian doesn't have commit privileges at the
moment).

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • django/branches/releases/1.0.X/django/db/backends/oracle/query.py

    r8471 r9228  
    2727    class OracleQuery(QueryClass): 
    2828        def resolve_columns(self, row, fields=()): 
    29             index_start = len(self.extra_select.keys()) 
    30             values = [self.convert_values(v, None) for v in row[:index_start]] 
     29            # If this query has limit/offset information, then we expect the 
     30            # first column to be an extra "_RN" column that we need to throw 
     31            # away. 
     32            if self.high_mark is not None or self.low_mark: 
     33                rn_offset = 1 
     34            else: 
     35                rn_offset = 0 
     36            index_start = rn_offset + len(self.extra_select.keys()) 
     37            values = [self.convert_values(v, None) 
     38                      for v in row[rn_offset:index_start]] 
    3139            for value, field in map(None, row[index_start:], fields): 
    3240                values.append(self.convert_values(value, field)) 
    …… 
    98106                        with_col_aliases=with_col_aliases) 
    99107            else: 
    100                 # `get_columns` needs to be called before `get_ordering` to 
    101                 # populate `_select_alias`. 
    102                 self.pre_sql_setup() 
    103                 self.get_columns() 
    104                 ordering = self.get_ordering() 
    105  
    106                 # Oracle's ROW_NUMBER() function requires an ORDER BY clause. 
    107                 if ordering: 
    108                     rn_orderby = ', '.join(ordering) 
    109                 else: 
    110                     # Create a default ORDER BY since none was specified. 
    111                     qn = self.quote_name_unless_alias 
    112                     opts = self.model._meta 
    113                     rn_orderby = '%s.%s' % (qn(opts.db_table), 
    114                         qn(opts.fields[0].db_column or opts.fields[0].column)) 
    115  
    116                 # Ensure the base query SELECTs our special "_RN" column 
    117                 self.extra_select['_RN'] = ('ROW_NUMBER() OVER (ORDER BY %s)' 
    118                                             % rn_orderby, '') 
    119108                sql, params = super(OracleQuery, self).as_sql(with_limits=False, 
    120109                                                        with_col_aliases=True) 
    …… 
    123112                # the "_RN" column.  This is the canonical way to emulate LIMIT 
    124113                # and OFFSET on Oracle. 
    125                 sql = 'SELECT * FROM (%s) WHERE "_RN" > %d' % (sql, self.low_mark) 
     114                sql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) AS "_RN", "_SUB".* FROM (%s) "_SUB") WHERE "_RN" > %d' % (sql, self.low_mark) 
    126115                if self.high_mark is not None: 
    127116                    sql = '%s AND "_RN" <= %d' % (sql, self.high_mark) 
    …… 
    129118            return sql, params 
    130119 
    131         def set_limits(self, low=None, high=None): 
    132             super(OracleQuery, self).set_limits(low, high) 
    133             # We need to select the row number for the LIMIT/OFFSET sql. 
    134             # A placeholder is added to extra_select now, because as_sql is 
    135             # too late to be modifying extra_select.  However, the actual sql 
    136             # depends on the ordering, so that is generated in as_sql. 
    137             self.extra_select['_RN'] = ('1', '') 
    138  
    139         def clear_limits(self): 
    140             super(OracleQuery, self).clear_limits() 
    141             if '_RN' in self.extra_select: 
    142                 del self.extra_select['_RN'] 
    143  
    144120    _classes[QueryClass] = OracleQuery 
    145121    return OracleQuery 

  • django/branches/releases/1.0.X/tests/regressiontests/queries/models.py

    r9207 r9228  
    334334>>> Item.objects.filter(tags__in=[t1, t2]).filter(tags=t3) 
    335335[<Item: two>] 
     336 
     337Make sure .distinct() works with slicing (this was broken in Oracle). 
     338>>> Item.objects.filter(tags__in=[t1, t2]).order_by('name')[:3] 
     339[<Item: one>, <Item: one>, <Item: two>] 
     340>>> Item.objects.filter(tags__in=[t1, t2]).distinct().order_by('name')[:3] 
     341[<Item: one>, <Item: two>] 
    336342 
    337343Bug #2080, #3592