Django

Code

Changeset 9221

Show
Ignore:
Timestamp:
10/10/08 12:15:58 (1 month ago)
Author:
ikelly
Message:

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

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • django/trunk/django/db/backends/oracle/query.py

    r8471 r9221  
    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/trunk/tests/regressiontests/queries/models.py

    r9206 r9221  
    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