python/lib/django_1_4/tests/regressiontests/queries/tests.py

   1 from __future__ import absolute_import
   2
   3 import datetime
   4 import pickle
   5 import sys
   6
   7 from django.conf import settings
   8 from django.core.exceptions import FieldError
   9 from django.db import DatabaseError, connection, connections, DEFAULT_DB_ALIAS
  10 from django.db.models import Count
  11 from django.db.models.query import Q, ITER_CHUNK_SIZE, EmptyQuerySet
  12 from django.test import TestCase, skipUnlessDBFeature
  13 from django.utils import unittest
  14 from django.utils.datastructures import SortedDict
  15
  16 from .models import (Annotation, Article, Author, Celebrity, Child, Cover,
  17     Detail, DumbCategory, ExtraInfo, Fan, Item, LeafA, LoopX, LoopZ,
  18     ManagedModel, Member, NamedCategory, Note, Number, Plaything, PointerA,
  19     Ranking, Related, Report, ReservedName, Tag, TvChef, Valid, X, Food, Eaten,
  20     Node, ObjectA, ObjectB, ObjectC, CategoryItem, SimpleCategory,
  21     SpecialCategory, OneToOneCategory)
  22
  23
  24 class BaseQuerysetTest(TestCase):
  25     def assertValueQuerysetEqual(self, qs, values):
  26         return self.assertQuerysetEqual(qs, values, transform=lambda x: x)
  27
  28
  29 class Queries1Tests(BaseQuerysetTest):
  30     def setUp(self):
  31         generic = NamedCategory.objects.create(name="Generic")
  32         self.t1 = Tag.objects.create(name='t1', category=generic)
  33         self.t2 = Tag.objects.create(name='t2', parent=self.t1, category=generic)
  34         self.t3 = Tag.objects.create(name='t3', parent=self.t1)
  35         t4 = Tag.objects.create(name='t4', parent=self.t3)
  36         self.t5 = Tag.objects.create(name='t5', parent=self.t3)
  37
  38         self.n1 = Note.objects.create(note='n1', misc='foo', id=1)
  39         n2 = Note.objects.create(note='n2', misc='bar', id=2)
  40         self.n3 = Note.objects.create(note='n3', misc='foo', id=3)
  41
  42         ann1 = Annotation.objects.create(name='a1', tag=self.t1)
  43         ann1.notes.add(self.n1)
  44         ann2 = Annotation.objects.create(name='a2', tag=t4)
  45         ann2.notes.add(n2, self.n3)
  46
  47         # Create these out of order so that sorting by 'id' will be different to sorting
  48         # by 'info'. Helps detect some problems later.
  49         self.e2 = ExtraInfo.objects.create(info='e2', note=n2)
  50         e1 = ExtraInfo.objects.create(info='e1', note=self.n1)
  51
  52         self.a1 = Author.objects.create(name='a1', num=1001, extra=e1)
  53         self.a2 = Author.objects.create(name='a2', num=2002, extra=e1)
  54         a3 = Author.objects.create(name='a3', num=3003, extra=self.e2)
  55         self.a4 = Author.objects.create(name='a4', num=4004, extra=self.e2)
  56
  57         self.time1 = datetime.datetime(2007, 12, 19, 22, 25, 0)
  58         self.time2 = datetime.datetime(2007, 12, 19, 21, 0, 0)
  59         time3 = datetime.datetime(2007, 12, 20, 22, 25, 0)
  60         time4 = datetime.datetime(2007, 12, 20, 21, 0, 0)
  61         self.i1 = Item.objects.create(name='one', created=self.time1, modified=self.time1, creator=self.a1, note=self.n3)
  62         self.i1.tags = [self.t1, self.t2]
  63         self.i2 = Item.objects.create(name='two', created=self.time2, creator=self.a2, note=n2)
  64         self.i2.tags = [self.t1, self.t3]
  65         self.i3 = Item.objects.create(name='three', created=time3, creator=self.a2, note=self.n3)
  66         i4 = Item.objects.create(name='four', created=time4, creator=self.a4, note=self.n3)
  67         i4.tags = [t4]
  68
  69         self.r1 = Report.objects.create(name='r1', creator=self.a1)
  70         Report.objects.create(name='r2', creator=a3)
  71         Report.objects.create(name='r3')
  72
  73         # Ordering by 'rank' gives us rank2, rank1, rank3. Ordering by the Meta.ordering
  74         # will be rank3, rank2, rank1.
  75         self.rank1 = Ranking.objects.create(rank=2, author=self.a2)
  76
  77         Cover.objects.create(title="first", item=i4)
  78         Cover.objects.create(title="second", item=self.i2)
  79
  80     def test_ticket1050(self):
  81         self.assertQuerysetEqual(
  82             Item.objects.filter(tags__isnull=True),
  83             ['<Item: three>']
  84         )
  85         self.assertQuerysetEqual(
  86             Item.objects.filter(tags__id__isnull=True),
  87             ['<Item: three>']
  88         )
  89
  90     def test_ticket1801(self):
  91         self.assertQuerysetEqual(
  92             Author.objects.filter(item=self.i2),
  93             ['<Author: a2>']
  94         )
  95         self.assertQuerysetEqual(
  96             Author.objects.filter(item=self.i3),
  97             ['<Author: a2>']
  98         )
  99         self.assertQuerysetEqual(
 100             Author.objects.filter(item=self.i2) & Author.objects.filter(item=self.i3),
 101             ['<Author: a2>']
 102         )
 103
 104     def test_ticket2306(self):
 105         # Checking that no join types are "left outer" joins.
 106         query = Item.objects.filter(tags=self.t2).query
 107         self.assertTrue(query.LOUTER not in [x[2] for x in query.alias_map.values()])
 108
 109         self.assertQuerysetEqual(
 110             Item.objects.filter(Q(tags=self.t1)).order_by('name'),
 111             ['<Item: one>', '<Item: two>']
 112         )
 113         self.assertQuerysetEqual(
 114             Item.objects.filter(Q(tags=self.t1)).filter(Q(tags=self.t2)),
 115             ['<Item: one>']
 116         )
 117         self.assertQuerysetEqual(
 118             Item.objects.filter(Q(tags=self.t1)).filter(Q(creator__name='fred')|Q(tags=self.t2)),
 119             ['<Item: one>']
 120         )
 121
 122         # Each filter call is processed "at once" against a single table, so this is
 123         # different from the previous example as it tries to find tags that are two
 124         # things at once (rather than two tags).
 125         self.assertQuerysetEqual(
 126             Item.objects.filter(Q(tags=self.t1) & Q(tags=self.t2)),
 127             []
 128         )
 129         self.assertQuerysetEqual(
 130             Item.objects.filter(Q(tags=self.t1), Q(creator__name='fred')|Q(tags=self.t2)),
 131             []
 132         )
 133
 134         qs = Author.objects.filter(ranking__rank=2, ranking__id=self.rank1.id)
 135         self.assertQuerysetEqual(list(qs), ['<Author: a2>'])
 136         self.assertEqual(2, qs.query.count_active_tables(), 2)
 137         qs = Author.objects.filter(ranking__rank=2).filter(ranking__id=self.rank1.id)
 138         self.assertEqual(qs.query.count_active_tables(), 3)
 139
 140     def test_ticket4464(self):
 141         self.assertQuerysetEqual(
 142             Item.objects.filter(tags=self.t1).filter(tags=self.t2),
 143             ['<Item: one>']
 144         )
 145         self.assertQuerysetEqual(
 146             Item.objects.filter(tags__in=[self.t1, self.t2]).distinct().order_by('name'),
 147             ['<Item: one>', '<Item: two>']
 148         )
 149         self.assertQuerysetEqual(
 150             Item.objects.filter(tags__in=[self.t1, self.t2]).filter(tags=self.t3),
 151             ['<Item: two>']
 152         )
 153
 154         # Make sure .distinct() works with slicing (this was broken in Oracle).
 155         self.assertQuerysetEqual(
 156             Item.objects.filter(tags__in=[self.t1, self.t2]).order_by('name')[:3],
 157             ['<Item: one>', '<Item: one>', '<Item: two>']
 158         )
 159         self.assertQuerysetEqual(
 160             Item.objects.filter(tags__in=[self.t1, self.t2]).distinct().order_by('name')[:3],
 161             ['<Item: one>', '<Item: two>']
 162         )
 163
 164     def test_tickets_2080_3592(self):
 165         self.assertQuerysetEqual(
 166             Author.objects.filter(item__name='one') | Author.objects.filter(name='a3'),
 167             ['<Author: a1>', '<Author: a3>']
 168         )
 169         self.assertQuerysetEqual(
 170             Author.objects.filter(Q(item__name='one') | Q(name='a3')),
 171             ['<Author: a1>', '<Author: a3>']
 172         )
 173         self.assertQuerysetEqual(
 174             Author.objects.filter(Q(name='a3') | Q(item__name='one')),
 175             ['<Author: a1>', '<Author: a3>']
 176         )
 177         self.assertQuerysetEqual(
 178             Author.objects.filter(Q(item__name='three') | Q(report__name='r3')),
 179             ['<Author: a2>']
 180         )
 181
 182     def test_ticket6074(self):
 183         # Merging two empty result sets shouldn't leave a queryset with no constraints
 184         # (which would match everything).
 185         self.assertQuerysetEqual(Author.objects.filter(Q(id__in=[])), [])
 186         self.assertQuerysetEqual(
 187             Author.objects.filter(Q(id__in=[])|Q(id__in=[])),
 188             []
 189         )
 190
 191     def test_tickets_1878_2939(self):
 192         self.assertEqual(Item.objects.values('creator').distinct().count(), 3)
 193
 194         # Create something with a duplicate 'name' so that we can test multi-column
 195         # cases (which require some tricky SQL transformations under the covers).
 196         xx = Item(name='four', created=self.time1, creator=self.a2, note=self.n1)
 197         xx.save()
 198         self.assertEqual(
 199             Item.objects.exclude(name='two').values('creator', 'name').distinct().count(),
 200             4
 201         )
 202         self.assertEqual(
 203             Item.objects.exclude(name='two').extra(select={'foo': '%s'}, select_params=(1,)).values('creator', 'name', 'foo').distinct().count(),
 204             4
 205         )
 206         self.assertEqual(
 207             Item.objects.exclude(name='two').extra(select={'foo': '%s'}, select_params=(1,)).values('creator', 'name').distinct().count(),
 208             4
 209         )
 210         xx.delete()
 211
 212     def test_ticket7323(self):
 213         self.assertEqual(Item.objects.values('creator', 'name').count(), 4)
 214
 215     def test_ticket2253(self):
 216         q1 = Item.objects.order_by('name')
 217         q2 = Item.objects.filter(id=self.i1.id)
 218         self.assertQuerysetEqual(
 219             q1,
 220             ['<Item: four>', '<Item: one>', '<Item: three>', '<Item: two>']
 221         )
 222         self.assertQuerysetEqual(q2, ['<Item: one>'])
 223         self.assertQuerysetEqual(
 224             (q1 | q2).order_by('name'),
 225             ['<Item: four>', '<Item: one>', '<Item: three>', '<Item: two>']
 226         )
 227         self.assertQuerysetEqual((q1 & q2).order_by('name'), ['<Item: one>'])
 228
 229         q1 = Item.objects.filter(tags=self.t1)
 230         q2 = Item.objects.filter(note=self.n3, tags=self.t2)
 231         q3 = Item.objects.filter(creator=self.a4)
 232         self.assertQuerysetEqual(
 233             ((q1 & q2) | q3).order_by('name'),
 234             ['<Item: four>', '<Item: one>']
 235         )
 236
 237     def test_order_by_tables(self):
 238         q1 = Item.objects.order_by('name')
 239         q2 = Item.objects.filter(id=self.i1.id)
 240         list(q2)
 241         self.assertEqual(len((q1 & q2).order_by('name').query.tables), 1)
 242
 243     def test_order_by_join_unref(self):
 244         """
 245         This test is related to the above one, testing that there aren't
 246         old JOINs in the query.
 247         """
 248         qs = Celebrity.objects.order_by('greatest_fan__fan_of')
 249         self.assertIn('OUTER JOIN', str(qs.query))
 250         qs = qs.order_by('id')
 251         self.assertNotIn('OUTER JOIN', str(qs.query))
 252
 253     def test_tickets_4088_4306(self):
 254         self.assertQuerysetEqual(
 255             Report.objects.filter(creator=1001),
 256             ['<Report: r1>']
 257         )
 258         self.assertQuerysetEqual(
 259             Report.objects.filter(creator__num=1001),
 260             ['<Report: r1>']
 261         )
 262         self.assertQuerysetEqual(Report.objects.filter(creator__id=1001), [])
 263         self.assertQuerysetEqual(
 264             Report.objects.filter(creator__id=self.a1.id),
 265             ['<Report: r1>']
 266         )
 267         self.assertQuerysetEqual(
 268             Report.objects.filter(creator__name='a1'),
 269             ['<Report: r1>']
 270         )
 271
 272     def test_ticket4510(self):
 273         self.assertQuerysetEqual(
 274             Author.objects.filter(report__name='r1'),
 275             ['<Author: a1>']
 276         )
 277
 278     def test_ticket7378(self):
 279         self.assertQuerysetEqual(self.a1.report_set.all(), ['<Report: r1>'])
 280
 281     def test_tickets_5324_6704(self):
 282         self.assertQuerysetEqual(
 283             Item.objects.filter(tags__name='t4'),
 284             ['<Item: four>']
 285         )
 286         self.assertQuerysetEqual(
 287             Item.objects.exclude(tags__name='t4').order_by('name').distinct(),
 288             ['<Item: one>', '<Item: three>', '<Item: two>']
 289         )
 290         self.assertQuerysetEqual(
 291             Item.objects.exclude(tags__name='t4').order_by('name').distinct().reverse(),
 292             ['<Item: two>', '<Item: three>', '<Item: one>']
 293         )
 294         self.assertQuerysetEqual(
 295             Author.objects.exclude(item__name='one').distinct().order_by('name'),
 296             ['<Author: a2>', '<Author: a3>', '<Author: a4>']
 297         )
 298
 299         # Excluding across a m2m relation when there is more than one related
 300         # object associated was problematic.
 301         self.assertQuerysetEqual(
 302             Item.objects.exclude(tags__name='t1').order_by('name'),
 303             ['<Item: four>', '<Item: three>']
 304         )
 305         self.assertQuerysetEqual(
 306             Item.objects.exclude(tags__name='t1').exclude(tags__name='t4'),
 307             ['<Item: three>']
 308         )
 309
 310         # Excluding from a relation that cannot be NULL should not use outer joins.
 311         query = Item.objects.exclude(creator__in=[self.a1, self.a2]).query
 312         self.assertTrue(query.LOUTER not in [x[2] for x in query.alias_map.values()])
 313
 314         # Similarly, when one of the joins cannot possibly, ever, involve NULL
 315         # values (Author -> ExtraInfo, in the following), it should never be
 316         # promoted to a left outer join. So the following query should only
 317         # involve one "left outer" join (Author -> Item is 0-to-many).
 318         qs = Author.objects.filter(id=self.a1.id).filter(Q(extra__note=self.n1)|Q(item__note=self.n3))
 319         self.assertEqual(
 320             len([x[2] for x in qs.query.alias_map.values() if x[2] == query.LOUTER and qs.query.alias_refcount[x[1]]]),
 321             1
 322         )
 323
 324         # The previous changes shouldn't affect nullable foreign key joins.
 325         self.assertQuerysetEqual(
 326             Tag.objects.filter(parent__isnull=True).order_by('name'),
 327             ['<Tag: t1>']
 328         )
 329         self.assertQuerysetEqual(
 330             Tag.objects.exclude(parent__isnull=True).order_by('name'),
 331             ['<Tag: t2>', '<Tag: t3>', '<Tag: t4>', '<Tag: t5>']
 332         )
 333         self.assertQuerysetEqual(
 334             Tag.objects.exclude(Q(parent__name='t1') | Q(parent__isnull=True)).order_by('name'),
 335             ['<Tag: t4>', '<Tag: t5>']
 336         )
 337         self.assertQuerysetEqual(
 338             Tag.objects.exclude(Q(parent__isnull=True) | Q(parent__name='t1')).order_by('name'),
 339             ['<Tag: t4>', '<Tag: t5>']
 340         )
 341         self.assertQuerysetEqual(
 342             Tag.objects.exclude(Q(parent__parent__isnull=True)).order_by('name'),
 343             ['<Tag: t4>', '<Tag: t5>']
 344         )
 345         self.assertQuerysetEqual(
 346             Tag.objects.filter(~Q(parent__parent__isnull=True)).order_by('name'),
 347             ['<Tag: t4>', '<Tag: t5>']
 348         )
 349
 350     def test_ticket2091(self):
 351         t = Tag.objects.get(name='t4')
 352         self.assertQuerysetEqual(
 353             Item.objects.filter(tags__in=[t]),
 354             ['<Item: four>']
 355         )
 356
 357     def test_heterogeneous_qs_combination(self):
 358         # Combining querysets built on different models should behave in a well-defined
 359         # fashion. We raise an error.
 360         self.assertRaisesMessage(
 361             AssertionError,
 362             'Cannot combine queries on two different base models.',
 363             lambda: Author.objects.all() & Tag.objects.all()
 364         )
 365         self.assertRaisesMessage(
 366             AssertionError,
 367             'Cannot combine queries on two different base models.',
 368             lambda: Author.objects.all() | Tag.objects.all()
 369         )
 370
 371     def test_ticket3141(self):
 372         self.assertEqual(Author.objects.extra(select={'foo': '1'}).count(), 4)
 373         self.assertEqual(
 374             Author.objects.extra(select={'foo': '%s'}, select_params=(1,)).count(),
 375             4
 376         )
 377
 378     def test_ticket2400(self):
 379         self.assertQuerysetEqual(
 380             Author.objects.filter(item__isnull=True),
 381             ['<Author: a3>']
 382         )
 383         self.assertQuerysetEqual(
 384             Tag.objects.filter(item__isnull=True),
 385             ['<Tag: t5>']
 386         )
 387
 388     def test_ticket2496(self):
 389         self.assertQuerysetEqual(
 390             Item.objects.extra(tables=['queries_author']).select_related().order_by('name')[:1],
 391             ['<Item: four>']
 392         )
 393
 394     def test_tickets_2076_7256(self):
 395         # Ordering on related tables should be possible, even if the table is
 396         # not otherwise involved.
 397         self.assertQuerysetEqual(
 398             Item.objects.order_by('note__note', 'name'),
 399             ['<Item: two>', '<Item: four>', '<Item: one>', '<Item: three>']
 400         )
 401
 402         # Ordering on a related field should use the remote model's default
 403         # ordering as a final step.
 404         self.assertQuerysetEqual(
 405             Author.objects.order_by('extra', '-name'),
 406             ['<Author: a2>', '<Author: a1>', '<Author: a4>', '<Author: a3>']
 407         )
 408
 409         # Using remote model default ordering can span multiple models (in this
 410         # case, Cover is ordered by Item's default, which uses Note's default).
 411         self.assertQuerysetEqual(
 412             Cover.objects.all(),
 413             ['<Cover: first>', '<Cover: second>']
 414         )
 415
 416         # If the remote model does not have a default ordering, we order by its 'id'
 417         # field.
 418         self.assertQuerysetEqual(
 419             Item.objects.order_by('creator', 'name'),
 420             ['<Item: one>', '<Item: three>', '<Item: two>', '<Item: four>']
 421         )
 422
 423         # Ordering by a many-valued attribute (e.g. a many-to-many or reverse
 424         # ForeignKey) is legal, but the results might not make sense. That
 425         # isn't Django's problem. Garbage in, garbage out.
 426         self.assertQuerysetEqual(
 427             Item.objects.filter(tags__isnull=False).order_by('tags', 'id'),
 428             ['<Item: one>', '<Item: two>', '<Item: one>', '<Item: two>', '<Item: four>']
 429         )
 430
 431         # If we replace the default ordering, Django adjusts the required
 432         # tables automatically. Item normally requires a join with Note to do
 433         # the default ordering, but that isn't needed here.
 434         qs = Item.objects.order_by('name')
 435         self.assertQuerysetEqual(
 436             qs,
 437             ['<Item: four>', '<Item: one>', '<Item: three>', '<Item: two>']
 438         )
 439         self.assertEqual(len(qs.query.tables), 1)
 440
 441     def test_tickets_2874_3002(self):
 442         qs = Item.objects.select_related().order_by('note__note', 'name')
 443         self.assertQuerysetEqual(
 444             qs,
 445             ['<Item: two>', '<Item: four>', '<Item: one>', '<Item: three>']
 446         )
 447
 448         # This is also a good select_related() test because there are multiple
 449         # Note entries in the SQL. The two Note items should be different.
 450         self.assertTrue(repr(qs[0].note), '<Note: n2>')
 451         self.assertEqual(repr(qs[0].creator.extra.note), '<Note: n1>')
 452
 453     def test_ticket3037(self):
 454         self.assertQuerysetEqual(
 455             Item.objects.filter(Q(creator__name='a3', name='two')|Q(creator__name='a4', name='four')),
 456             ['<Item: four>']
 457         )
 458
 459     def test_tickets_5321_7070(self):
 460         # Ordering columns must be included in the output columns. Note that
 461         # this means results that might otherwise be distinct are not (if there
 462         # are multiple values in the ordering cols), as in this example. This
 463         # isn't a bug; it's a warning to be careful with the selection of
 464         # ordering columns.
 465         self.assertValueQuerysetEqual(
 466             Note.objects.values('misc').distinct().order_by('note', '-misc'),
 467             [{'misc': u'foo'}, {'misc': u'bar'}, {'misc': u'foo'}]
 468         )
 469
 470     def test_ticket4358(self):
 471         # If you don't pass any fields to values(), relation fields are
 472         # returned as "foo_id" keys, not "foo". For consistency, you should be
 473         # able to pass "foo_id" in the fields list and have it work, too. We
 474         # actually allow both "foo" and "foo_id".
 475
 476         # The *_id version is returned by default.
 477         self.assertTrue('note_id' in ExtraInfo.objects.values()[0])
 478
 479         # You can also pass it in explicitly.
 480         self.assertValueQuerysetEqual(
 481             ExtraInfo.objects.values('note_id'),
 482             [{'note_id': 1}, {'note_id': 2}]
 483         )
 484
 485         # ...or use the field name.
 486         self.assertValueQuerysetEqual(
 487             ExtraInfo.objects.values('note'),
 488             [{'note': 1}, {'note': 2}]
 489         )
 490
 491     def test_ticket2902(self):
 492         # Parameters can be given to extra_select, *if* you use a SortedDict.
 493
 494         # (First we need to know which order the keys fall in "naturally" on
 495         # your system, so we can put things in the wrong way around from
 496         # normal. A normal dict would thus fail.)
 497         s = [('a', '%s'), ('b', '%s')]
 498         params = ['one', 'two']
 499         if {'a': 1, 'b': 2}.keys() == ['a', 'b']:
 500             s.reverse()
 501             params.reverse()
 502
 503         # This slightly odd comparison works around the fact that PostgreSQL will
 504         # return 'one' and 'two' as strings, not Unicode objects. It's a side-effect of
 505         # using constants here and not a real concern.
 506         d = Item.objects.extra(select=SortedDict(s), select_params=params).values('a', 'b')[0]
 507         self.assertEqual(d, {'a': u'one', 'b': u'two'})
 508
 509         # Order by the number of tags attached to an item.
 510         l = Item.objects.extra(select={'count': 'select count(*) from queries_item_tags where queries_item_tags.item_id = queries_item.id'}).order_by('-count')
 511         self.assertEqual([o.count for o in l], [2, 2, 1, 0])
 512
 513     def test_ticket6154(self):
 514         # Multiple filter statements are joined using "AND" all the time.
 515
 516         self.assertQuerysetEqual(
 517             Author.objects.filter(id=self.a1.id).filter(Q(extra__note=self.n1)|Q(item__note=self.n3)),
 518             ['<Author: a1>']
 519         )
 520         self.assertQuerysetEqual(
 521                 Author.objects.filter(Q(extra__note=self.n1)|Q(item__note=self.n3)).filter(id=self.a1.id),
 522             ['<Author: a1>']
 523         )
 524
 525     def test_ticket6981(self):
 526         self.assertQuerysetEqual(
 527             Tag.objects.select_related('parent').order_by('name'),
 528             ['<Tag: t1>', '<Tag: t2>', '<Tag: t3>', '<Tag: t4>', '<Tag: t5>']
 529         )
 530
 531     def test_ticket9926(self):
 532         self.assertQuerysetEqual(
 533             Tag.objects.select_related("parent", "category").order_by('name'),
 534             ['<Tag: t1>', '<Tag: t2>', '<Tag: t3>', '<Tag: t4>', '<Tag: t5>']
 535         )
 536         self.assertQuerysetEqual(
 537             Tag.objects.select_related('parent', "parent__category").order_by('name'),
 538             ['<Tag: t1>', '<Tag: t2>', '<Tag: t3>', '<Tag: t4>', '<Tag: t5>']
 539         )
 540
 541     def test_tickets_6180_6203(self):
 542         # Dates with limits and/or counts
 543         self.assertEqual(Item.objects.count(), 4)
 544         self.assertEqual(Item.objects.dates('created', 'month').count(), 1)
 545         self.assertEqual(Item.objects.dates('created', 'day').count(), 2)
 546         self.assertEqual(len(Item.objects.dates('created', 'day')), 2)
 547         self.assertEqual(Item.objects.dates('created', 'day')[0], datetime.datetime(2007, 12, 19, 0, 0))
 548
 549     def test_tickets_7087_12242(self):
 550         # Dates with extra select columns
 551         self.assertQuerysetEqual(
 552             Item.objects.dates('created', 'day').extra(select={'a': 1}),
 553             ['datetime.datetime(2007, 12, 19, 0, 0)', 'datetime.datetime(2007, 12, 20, 0, 0)']
 554         )
 555         self.assertQuerysetEqual(
 556             Item.objects.extra(select={'a': 1}).dates('created', 'day'),
 557             ['datetime.datetime(2007, 12, 19, 0, 0)', 'datetime.datetime(2007, 12, 20, 0, 0)']
 558         )
 559
 560         name="one"
 561         self.assertQuerysetEqual(
 562             Item.objects.dates('created', 'day').extra(where=['name=%s'], params=[name]),
 563             ['datetime.datetime(2007, 12, 19, 0, 0)']
 564         )
 565
 566         self.assertQuerysetEqual(
 567             Item.objects.extra(where=['name=%s'], params=[name]).dates('created', 'day'),
 568             ['datetime.datetime(2007, 12, 19, 0, 0)']
 569         )
 570
 571     def test_ticket7155(self):
 572         # Nullable dates
 573         self.assertQuerysetEqual(
 574             Item.objects.dates('modified', 'day'),
 575             ['datetime.datetime(2007, 12, 19, 0, 0)']
 576         )
 577
 578     def test_ticket7098(self):
 579         # Make sure semi-deprecated ordering by related models syntax still
 580         # works.
 581         self.assertValueQuerysetEqual(
 582             Item.objects.values('note__note').order_by('queries_note.note', 'id'),
 583             [{'note__note': u'n2'}, {'note__note': u'n3'}, {'note__note': u'n3'}, {'note__note': u'n3'}]
 584         )
 585
 586     def test_ticket7096(self):
 587         # Make sure exclude() with multiple conditions continues to work.
 588         self.assertQuerysetEqual(
 589             Tag.objects.filter(parent=self.t1, name='t3').order_by('name'),
 590             ['<Tag: t3>']
 591         )
 592         self.assertQuerysetEqual(
 593             Tag.objects.exclude(parent=self.t1, name='t3').order_by('name'),
 594             ['<Tag: t1>', '<Tag: t2>', '<Tag: t4>', '<Tag: t5>']
 595         )
 596         self.assertQuerysetEqual(
 597             Item.objects.exclude(tags__name='t1', name='one').order_by('name').distinct(),
 598             ['<Item: four>', '<Item: three>', '<Item: two>']
 599         )
 600         self.assertQuerysetEqual(
 601             Item.objects.filter(name__in=['three', 'four']).exclude(tags__name='t1').order_by('name'),
 602             ['<Item: four>', '<Item: three>']
 603         )
 604
 605         # More twisted cases, involving nested negations.
 606         self.assertQuerysetEqual(
 607             Item.objects.exclude(~Q(tags__name='t1', name='one')),
 608             ['<Item: one>']
 609         )
 610         self.assertQuerysetEqual(
 611             Item.objects.filter(~Q(tags__name='t1', name='one'), name='two'),
 612             ['<Item: two>']
 613         )
 614         self.assertQuerysetEqual(
 615             Item.objects.exclude(~Q(tags__name='t1', name='one'), name='two'),
 616             ['<Item: four>', '<Item: one>', '<Item: three>']
 617         )
 618
 619     def test_tickets_7204_7506(self):
 620         # Make sure querysets with related fields can be pickled. If this
 621         # doesn't crash, it's a Good Thing.
 622         pickle.dumps(Item.objects.all())
 623
 624     def test_ticket7813(self):
 625         # We should also be able to pickle things that use select_related().
 626         # The only tricky thing here is to ensure that we do the related
 627         # selections properly after unpickling.
 628         qs = Item.objects.select_related()
 629         query = qs.query.get_compiler(qs.db).as_sql()[0]
 630         query2 = pickle.loads(pickle.dumps(qs.query))
 631         self.assertEqual(
 632             query2.get_compiler(qs.db).as_sql()[0],
 633             query
 634         )
 635
 636     def test_deferred_load_qs_pickling(self):
 637         # Check pickling of deferred-loading querysets
 638         qs = Item.objects.defer('name', 'creator')
 639         q2 = pickle.loads(pickle.dumps(qs))
 640         self.assertEqual(list(qs), list(q2))
 641         q3 = pickle.loads(pickle.dumps(qs, pickle.HIGHEST_PROTOCOL))
 642         self.assertEqual(list(qs), list(q3))
 643
 644     def test_ticket7277(self):
 645         self.assertQuerysetEqual(
 646             self.n1.annotation_set.filter(Q(tag=self.t5) | Q(tag__children=self.t5) | Q(tag__children__children=self.t5)),
 647             ['<Annotation: a1>']
 648         )
 649
 650     def test_tickets_7448_7707(self):
 651         # Complex objects should be converted to strings before being used in
 652         # lookups.
 653         self.assertQuerysetEqual(
 654             Item.objects.filter(created__in=[self.time1, self.time2]),
 655             ['<Item: one>', '<Item: two>']
 656         )
 657
 658     def test_ticket7235(self):
 659         # An EmptyQuerySet should not raise exceptions if it is filtered.
 660         q = EmptyQuerySet()
 661         self.assertQuerysetEqual(q.all(), [])
 662         self.assertQuerysetEqual(q.filter(x=10), [])
 663         self.assertQuerysetEqual(q.exclude(y=3), [])
 664         self.assertQuerysetEqual(q.complex_filter({'pk': 1}), [])
 665         self.assertQuerysetEqual(q.select_related('spam', 'eggs'), [])
 666         self.assertQuerysetEqual(q.annotate(Count('eggs')), [])
 667         self.assertQuerysetEqual(q.order_by('-pub_date', 'headline'), [])
 668         self.assertQuerysetEqual(q.distinct(), [])
 669         self.assertQuerysetEqual(
 670             q.extra(select={'is_recent': "pub_date > '2006-01-01'"}),
 671             []
 672         )
 673         q.query.low_mark = 1
 674         self.assertRaisesMessage(
 675             AssertionError,
 676             'Cannot change a query once a slice has been taken',
 677             q.extra, select={'is_recent': "pub_date > '2006-01-01'"}
 678         )
 679         self.assertQuerysetEqual(q.reverse(), [])
 680         self.assertQuerysetEqual(q.defer('spam', 'eggs'), [])
 681         self.assertQuerysetEqual(q.only('spam', 'eggs'), [])
 682
 683     def test_ticket7791(self):
 684         # There were "issues" when ordering and distinct-ing on fields related
 685         # via ForeignKeys.
 686         self.assertEqual(
 687             len(Note.objects.order_by('extrainfo__info').distinct()),
 688             3
 689         )
 690
 691         # Pickling of DateQuerySets used to fail
 692         qs = Item.objects.dates('created', 'month')
 693         _ = pickle.loads(pickle.dumps(qs))
 694
 695     def test_ticket9997(self):
 696         # If a ValuesList or Values queryset is passed as an inner query, we
 697         # make sure it's only requesting a single value and use that as the
 698         # thing to select.
 699         self.assertQuerysetEqual(
 700             Tag.objects.filter(name__in=Tag.objects.filter(parent=self.t1).values('name')),
 701             ['<Tag: t2>', '<Tag: t3>']
 702         )
 703
 704         # Multi-valued values() and values_list() querysets should raise errors.
 705         self.assertRaisesMessage(
 706             TypeError,
 707             'Cannot use a multi-field ValuesQuerySet as a filter value.',
 708             lambda: Tag.objects.filter(name__in=Tag.objects.filter(parent=self.t1).values('name', 'id'))
 709         )
 710         self.assertRaisesMessage(
 711             TypeError,
 712             'Cannot use a multi-field ValuesListQuerySet as a filter value.',
 713             lambda: Tag.objects.filter(name__in=Tag.objects.filter(parent=self.t1).values_list('name', 'id'))
 714         )
 715
 716     def test_ticket9985(self):
 717         # qs.values_list(...).values(...) combinations should work.
 718         self.assertValueQuerysetEqual(
 719             Note.objects.values_list("note", flat=True).values("id").order_by("id"),
 720             [{'id': 1}, {'id': 2}, {'id': 3}]
 721         )
 722         self.assertQuerysetEqual(
 723             Annotation.objects.filter(notes__in=Note.objects.filter(note="n1").values_list('note').values('id')),
 724             ['<Annotation: a1>']
 725         )
 726
 727     def test_ticket10205(self):
 728         # When bailing out early because of an empty "__in" filter, we need
 729         # to set things up correctly internally so that subqueries can continue properly.
 730         self.assertEqual(Tag.objects.filter(name__in=()).update(name="foo"), 0)
 731
 732     def test_ticket10432(self):
 733         # Testing an empty "__in" filter with a generator as the value.
 734         def f():
 735             return iter([])
 736         n_obj = Note.objects.all()[0]
 737         def g():
 738             for i in [n_obj.pk]:
 739                 yield i
 740         self.assertQuerysetEqual(Note.objects.filter(pk__in=f()), [])
 741         self.assertEqual(list(Note.objects.filter(pk__in=g())), [n_obj])
 742
 743     def test_ticket10742(self):
 744         # Queries used in an __in clause don't execute subqueries
 745
 746         subq = Author.objects.filter(num__lt=3000)
 747         qs = Author.objects.filter(pk__in=subq)
 748         self.assertQuerysetEqual(qs, ['<Author: a1>', '<Author: a2>'])
 749
 750         # The subquery result cache should not be populated
 751         self.assertTrue(subq._result_cache is None)
 752
 753         subq = Author.objects.filter(num__lt=3000)
 754         qs = Author.objects.exclude(pk__in=subq)
 755         self.assertQuerysetEqual(qs, ['<Author: a3>', '<Author: a4>'])
 756
 757         # The subquery result cache should not be populated
 758         self.assertTrue(subq._result_cache is None)
 759
 760         subq = Author.objects.filter(num__lt=3000)
 761         self.assertQuerysetEqual(
 762             Author.objects.filter(Q(pk__in=subq) & Q(name='a1')),
 763             ['<Author: a1>']
 764         )
 765
 766         # The subquery result cache should not be populated
 767         self.assertTrue(subq._result_cache is None)
 768
 769     def test_ticket7076(self):
 770         # Excluding shouldn't eliminate NULL entries.
 771         self.assertQuerysetEqual(
 772             Item.objects.exclude(modified=self.time1).order_by('name'),
 773             ['<Item: four>', '<Item: three>', '<Item: two>']
 774         )
 775         self.assertQuerysetEqual(
 776             Tag.objects.exclude(parent__name=self.t1.name),
 777             ['<Tag: t1>', '<Tag: t4>', '<Tag: t5>']
 778         )
 779
 780     def test_ticket7181(self):
 781         # Ordering by related tables should accomodate nullable fields (this
 782         # test is a little tricky, since NULL ordering is database dependent.
 783         # Instead, we just count the number of results).
 784         self.assertEqual(len(Tag.objects.order_by('parent__name')), 5)
 785
 786         # Empty querysets can be merged with others.
 787         self.assertQuerysetEqual(
 788             Note.objects.none() | Note.objects.all(),
 789             ['<Note: n1>', '<Note: n2>', '<Note: n3>']
 790         )
 791         self.assertQuerysetEqual(
 792             Note.objects.all() | Note.objects.none(),
 793             ['<Note: n1>', '<Note: n2>', '<Note: n3>']
 794         )
 795         self.assertQuerysetEqual(Note.objects.none() & Note.objects.all(), [])
 796         self.assertQuerysetEqual(Note.objects.all() & Note.objects.none(), [])
 797
 798     def test_ticket9411(self):
 799         # Make sure bump_prefix() (an internal Query method) doesn't (re-)break. It's
 800         # sufficient that this query runs without error.
 801         qs = Tag.objects.values_list('id', flat=True).order_by('id')
 802         qs.query.bump_prefix()
 803         first = qs[0]
 804         self.assertEqual(list(qs), range(first, first+5))
 805
 806     def test_ticket8439(self):
 807         # Complex combinations of conjunctions, disjunctions and nullable
 808         # relations.
 809         self.assertQuerysetEqual(
 810             Author.objects.filter(Q(item__note__extrainfo=self.e2)|Q(report=self.r1, name='xyz')),
 811             ['<Author: a2>']
 812         )
 813         self.assertQuerysetEqual(
 814             Author.objects.filter(Q(report=self.r1, name='xyz')|Q(item__note__extrainfo=self.e2)),
 815             ['<Author: a2>']
 816         )
 817         self.assertQuerysetEqual(
 818             Annotation.objects.filter(Q(tag__parent=self.t1)|Q(notes__note='n1', name='a1')),
 819             ['<Annotation: a1>']
 820         )
 821         xx = ExtraInfo.objects.create(info='xx', note=self.n3)
 822         self.assertQuerysetEqual(
 823             Note.objects.filter(Q(extrainfo__author=self.a1)|Q(extrainfo=xx)),
 824             ['<Note: n1>', '<Note: n3>']
 825         )
 826         xx.delete()
 827         q = Note.objects.filter(Q(extrainfo__author=self.a1)|Q(extrainfo=xx)).query
 828         self.assertEqual(
 829             len([x[2] for x in q.alias_map.values() if x[2] == q.LOUTER and q.alias_refcount[x[1]]]),
 830             1
 831         )
 832
 833     def test_ticket17429(self):
 834         """
 835         Ensure that Meta.ordering=None works the same as Meta.ordering=[]
 836         """
 837         original_ordering = Tag._meta.ordering
 838         Tag._meta.ordering = None
 839         self.assertQuerysetEqual(
 840             Tag.objects.all(),
 841             ['<Tag: t1>', '<Tag: t2>', '<Tag: t3>', '<Tag: t4>', '<Tag: t5>'],
 842         )
 843         Tag._meta.ordering = original_ordering
 844
 845 class Queries2Tests(TestCase):
 846     def setUp(self):
 847         Number.objects.create(num=4)
 848         Number.objects.create(num=8)
 849         Number.objects.create(num=12)
 850
 851     def test_ticket4289(self):
 852         # A slight variation on the restricting the filtering choices by the
 853         # lookup constraints.
 854         self.assertQuerysetEqual(Number.objects.filter(num__lt=4), [])
 855         self.assertQuerysetEqual(Number.objects.filter(num__gt=8, num__lt=12), [])
 856         self.assertQuerysetEqual(
 857             Number.objects.filter(num__gt=8, num__lt=13),
 858             ['<Number: 12>']
 859         )
 860         self.assertQuerysetEqual(
 861             Number.objects.filter(Q(num__lt=4) | Q(num__gt=8, num__lt=12)),
 862             []
 863         )
 864         self.assertQuerysetEqual(
 865             Number.objects.filter(Q(num__gt=8, num__lt=12) | Q(num__lt=4)),
 866             []
 867         )
 868         self.assertQuerysetEqual(
 869             Number.objects.filter(Q(num__gt=8) & Q(num__lt=12) | Q(num__lt=4)),
 870             []
 871         )
 872         self.assertQuerysetEqual(
 873             Number.objects.filter(Q(num__gt=7) & Q(num__lt=12) | Q(num__lt=4)),
 874             ['<Number: 8>']
 875         )
 876
 877     def test_ticket12239(self):
 878         # Float was being rounded to integer on gte queries on integer field.  Tests
 879         # show that gt, lt, gte, and lte work as desired.  Note that the fix changes
 880         # get_prep_lookup for gte and lt queries only.
 881         self.assertQuerysetEqual(
 882             Number.objects.filter(num__gt=11.9),
 883             ['<Number: 12>']
 884         )
 885         self.assertQuerysetEqual(Number.objects.filter(num__gt=12), [])
 886         self.assertQuerysetEqual(Number.objects.filter(num__gt=12.0), [])
 887         self.assertQuerysetEqual(Number.objects.filter(num__gt=12.1), [])
 888         self.assertQuerysetEqual(
 889             Number.objects.filter(num__lt=12),
 890             ['<Number: 4>', '<Number: 8>']
 891         )
 892         self.assertQuerysetEqual(
 893             Number.objects.filter(num__lt=12.0),
 894             ['<Number: 4>', '<Number: 8>']
 895         )
 896         self.assertQuerysetEqual(
 897             Number.objects.filter(num__lt=12.1),
 898             ['<Number: 4>', '<Number: 8>', '<Number: 12>']
 899         )
 900         self.assertQuerysetEqual(
 901             Number.objects.filter(num__gte=11.9),
 902             ['<Number: 12>']
 903         )
 904         self.assertQuerysetEqual(
 905             Number.objects.filter(num__gte=12),
 906             ['<Number: 12>']
 907         )
 908         self.assertQuerysetEqual(
 909             Number.objects.filter(num__gte=12.0),
 910             ['<Number: 12>']
 911         )
 912         self.assertQuerysetEqual(Number.objects.filter(num__gte=12.1), [])
 913         self.assertQuerysetEqual(Number.objects.filter(num__gte=12.9), [])
 914         self.assertQuerysetEqual(
 915             Number.objects.filter(num__lte=11.9),
 916             ['<Number: 4>', '<Number: 8>']
 917         )
 918         self.assertQuerysetEqual(
 919             Number.objects.filter(num__lte=12),
 920             ['<Number: 4>', '<Number: 8>', '<Number: 12>']
 921         )
 922         self.assertQuerysetEqual(
 923             Number.objects.filter(num__lte=12.0),
 924             ['<Number: 4>', '<Number: 8>', '<Number: 12>']
 925         )
 926         self.assertQuerysetEqual(
 927             Number.objects.filter(num__lte=12.1),
 928             ['<Number: 4>', '<Number: 8>', '<Number: 12>']
 929         )
 930         self.assertQuerysetEqual(
 931             Number.objects.filter(num__lte=12.9),
 932             ['<Number: 4>', '<Number: 8>', '<Number: 12>']
 933         )
 934
 935     def test_ticket7411(self):
 936         # Saving to db must work even with partially read result set in another
 937         # cursor.
 938         for num in range(2 * ITER_CHUNK_SIZE + 1):
 939             _ = Number.objects.create(num=num)
 940
 941         for i, obj in enumerate(Number.objects.all()):
 942             obj.save()
 943             if i > 10: break
 944
 945     def test_ticket7759(self):
 946         # Count should work with a partially read result set.
 947         count = Number.objects.count()
 948         qs = Number.objects.all()
 949         def run():
 950             for obj in qs:
 951                 return qs.count() == count
 952         self.assertTrue(run())
 953
 954
 955 class Queries3Tests(BaseQuerysetTest):
 956     def test_ticket7107(self):
 957         # This shouldn't create an infinite loop.
 958         self.assertQuerysetEqual(Valid.objects.all(), [])
 959
 960     def test_ticket8683(self):
 961         # Raise proper error when a DateQuerySet gets passed a wrong type of
 962         # field
 963         self.assertRaisesMessage(
 964             AssertionError,
 965             "'name' isn't a DateField.",
 966             Item.objects.dates, 'name', 'month'
 967         )
 968
 969 class Queries4Tests(BaseQuerysetTest):
 970     def setUp(self):
 971         generic = NamedCategory.objects.create(name="Generic")
 972         self.t1 = Tag.objects.create(name='t1', category=generic)
 973
 974         n1 = Note.objects.create(note='n1', misc='foo', id=1)
 975         n2 = Note.objects.create(note='n2', misc='bar', id=2)
 976
 977         e1 = ExtraInfo.objects.create(info='e1', note=n1)
 978         e2 = ExtraInfo.objects.create(info='e2', note=n2)
 979
 980         self.a1 = Author.objects.create(name='a1', num=1001, extra=e1)
 981         self.a3 = Author.objects.create(name='a3', num=3003, extra=e2)
 982
 983         self.r1 = Report.objects.create(name='r1', creator=self.a1)
 984         self.r2 = Report.objects.create(name='r2', creator=self.a3)
 985         self.r3 = Report.objects.create(name='r3')
 986
 987         Item.objects.create(name='i1', created=datetime.datetime.now(), note=n1, creator=self.a1)
 988         Item.objects.create(name='i2', created=datetime.datetime.now(), note=n1, creator=self.a3)
 989
 990     def test_ticket14876(self):
 991         q1 = Report.objects.filter(Q(creator__isnull=True) | Q(creator__extra__info='e1'))
 992         q2 = Report.objects.filter(Q(creator__isnull=True)) | Report.objects.filter(Q(creator__extra__info='e1'))
 993         self.assertQuerysetEqual(q1, ["<Report: r1>", "<Report: r3>"])
 994         self.assertEqual(str(q1.query), str(q2.query))
 995
 996         q1 = Report.objects.filter(Q(creator__extra__info='e1') | Q(creator__isnull=True))
 997         q2 = Report.objects.filter(Q(creator__extra__info='e1')) | Report.objects.filter(Q(creator__isnull=True))
 998         self.assertQuerysetEqual(q1, ["<Report: r1>", "<Report: r3>"])
 999         self.assertEqual(str(q1.query), str(q2.query))
1000
1001         q1 = Item.objects.filter(Q(creator=self.a1) | Q(creator__report__name='r1')).order_by()
1002         q2 = Item.objects.filter(Q(creator=self.a1)).order_by() | Item.objects.filter(Q(creator__report__name='r1')).order_by()
1003         self.assertQuerysetEqual(q1, ["<Item: i1>"])
1004         self.assertEqual(str(q1.query), str(q2.query))
1005
1006         q1 = Item.objects.filter(Q(creator__report__name='e1') | Q(creator=self.a1)).order_by()
1007         q2 = Item.objects.filter(Q(creator__report__name='e1')).order_by() | Item.objects.filter(Q(creator=self.a1)).order_by()
1008         self.assertQuerysetEqual(q1, ["<Item: i1>"])
1009         self.assertEqual(str(q1.query), str(q2.query))
1010
1011     def test_ticket7095(self):
1012         # Updates that are filtered on the model being updated are somewhat
1013         # tricky in MySQL. This exercises that case.
1014         ManagedModel.objects.create(data='mm1', tag=self.t1, public=True)
1015         self.assertEqual(ManagedModel.objects.update(data='mm'), 1)
1016
1017         # A values() or values_list() query across joined models must use outer
1018         # joins appropriately.
1019         # Note: In Oracle, we expect a null CharField to return u'' instead of
1020         # None.
1021         if connection.features.interprets_empty_strings_as_nulls:
1022             expected_null_charfield_repr = u''
1023         else:
1024             expected_null_charfield_repr = None
1025         self.assertValueQuerysetEqual(
1026             Report.objects.values_list("creator__extra__info", flat=True).order_by("name"),
1027             [u'e1', u'e2', expected_null_charfield_repr],
1028         )
1029
1030         # Similarly for select_related(), joins beyond an initial nullable join
1031         # must use outer joins so that all results are included.
1032         self.assertQuerysetEqual(
1033             Report.objects.select_related("creator", "creator__extra").order_by("name"),
1034             ['<Report: r1>', '<Report: r2>', '<Report: r3>']
1035         )
1036
1037         # When there are multiple paths to a table from another table, we have
1038         # to be careful not to accidentally reuse an inappropriate join when
1039         # using select_related(). We used to return the parent's Detail record
1040         # here by mistake.
1041
1042         d1 = Detail.objects.create(data="d1")
1043         d2 = Detail.objects.create(data="d2")
1044         m1 = Member.objects.create(name="m1", details=d1)
1045         m2 = Member.objects.create(name="m2", details=d2)
1046         Child.objects.create(person=m2, parent=m1)
1047         obj = m1.children.select_related("person__details")[0]
1048         self.assertEqual(obj.person.details.data, u'd2')
1049
1050     def test_order_by_resetting(self):
1051         # Calling order_by() with no parameters removes any existing ordering on the
1052         # model. But it should still be possible to add new ordering after that.
1053         qs = Author.objects.order_by().order_by('name')
1054         self.assertTrue('ORDER BY' in qs.query.get_compiler(qs.db).as_sql()[0])
1055
1056     def test_ticket10181(self):
1057         # Avoid raising an EmptyResultSet if an inner query is probably
1058         # empty (and hence, not executed).
1059         self.assertQuerysetEqual(
1060             Tag.objects.filter(id__in=Tag.objects.filter(id__in=[])),
1061             []
1062         )
1063
1064     def test_ticket15316_filter_false(self):
1065         c1 = SimpleCategory.objects.create(name="category1")
1066         c2 = SpecialCategory.objects.create(name="named category1",
1067                 special_name="special1")
1068         c3 = SpecialCategory.objects.create(name="named category2",
1069                 special_name="special2")
1070
1071         ci1 = CategoryItem.objects.create(category=c1)
1072         ci2 = CategoryItem.objects.create(category=c2)
1073         ci3 = CategoryItem.objects.create(category=c3)
1074
1075         qs = CategoryItem.objects.filter(category__specialcategory__isnull=False)
1076         self.assertEqual(qs.count(), 2)
1077         self.assertQuerysetEqual(qs, [ci2.pk, ci3.pk], lambda x: x.pk, False)
1078
1079     def test_ticket15316_exclude_false(self):
1080         c1 = SimpleCategory.objects.create(name="category1")
1081         c2 = SpecialCategory.objects.create(name="named category1",
1082                 special_name="special1")
1083         c3 = SpecialCategory.objects.create(name="named category2",
1084                 special_name="special2")
1085
1086         ci1 = CategoryItem.objects.create(category=c1)
1087         ci2 = CategoryItem.objects.create(category=c2)
1088         ci3 = CategoryItem.objects.create(category=c3)
1089
1090         qs = CategoryItem.objects.exclude(category__specialcategory__isnull=False)
1091         self.assertEqual(qs.count(), 1)
1092         self.assertQuerysetEqual(qs, [ci1.pk], lambda x: x.pk)
1093
1094     def test_ticket15316_filter_true(self):
1095         c1 = SimpleCategory.objects.create(name="category1")
1096         c2 = SpecialCategory.objects.create(name="named category1",
1097                 special_name="special1")
1098         c3 = SpecialCategory.objects.create(name="named category2",
1099                 special_name="special2")
1100
1101         ci1 = CategoryItem.objects.create(category=c1)
1102         ci2 = CategoryItem.objects.create(category=c2)
1103         ci3 = CategoryItem.objects.create(category=c3)
1104
1105         qs = CategoryItem.objects.filter(category__specialcategory__isnull=True)
1106         self.assertEqual(qs.count(), 1)
1107         self.assertQuerysetEqual(qs, [ci1.pk], lambda x: x.pk)
1108
1109     def test_ticket15316_exclude_true(self):
1110         c1 = SimpleCategory.objects.create(name="category1")
1111         c2 = SpecialCategory.objects.create(name="named category1",
1112                 special_name="special1")
1113         c3 = SpecialCategory.objects.create(name="named category2",
1114                 special_name="special2")
1115
1116         ci1 = CategoryItem.objects.create(category=c1)
1117         ci2 = CategoryItem.objects.create(category=c2)
1118         ci3 = CategoryItem.objects.create(category=c3)
1119
1120         qs = CategoryItem.objects.exclude(category__specialcategory__isnull=True)
1121         self.assertEqual(qs.count(), 2)
1122         self.assertQuerysetEqual(qs, [ci2.pk, ci3.pk], lambda x: x.pk, False)
1123
1124     def test_ticket15316_one2one_filter_false(self):
1125         c  = SimpleCategory.objects.create(name="cat")
1126         c0 = SimpleCategory.objects.create(name="cat0")
1127         c1 = SimpleCategory.objects.create(name="category1")
1128
1129         c2 = OneToOneCategory.objects.create(category = c1, new_name="new1")
1130         c3 = OneToOneCategory.objects.create(category = c0, new_name="new2")
1131
1132         ci1 = CategoryItem.objects.create(category=c)
1133         ci2 = CategoryItem.objects.create(category=c0)
1134         ci3 = CategoryItem.objects.create(category=c1)
1135
1136         qs = CategoryItem.objects.filter(category__onetoonecategory__isnull=False)
1137         self.assertEqual(qs.count(), 2)
1138         self.assertQuerysetEqual(qs, [ci2.pk, ci3.pk], lambda x: x.pk, False)
1139
1140     def test_ticket15316_one2one_exclude_false(self):
1141         c  = SimpleCategory.objects.create(name="cat")
1142         c0 = SimpleCategory.objects.create(name="cat0")
1143         c1 = SimpleCategory.objects.create(name="category1")
1144
1145         c2 = OneToOneCategory.objects.create(category = c1, new_name="new1")
1146         c3 = OneToOneCategory.objects.create(category = c0, new_name="new2")
1147
1148         ci1 = CategoryItem.objects.create(category=c)
1149         ci2 = CategoryItem.objects.create(category=c0)
1150         ci3 = CategoryItem.objects.create(category=c1)
1151
1152         qs = CategoryItem.objects.exclude(category__onetoonecategory__isnull=False)
1153         self.assertEqual(qs.count(), 1)
1154         self.assertQuerysetEqual(qs, [ci1.pk], lambda x: x.pk)
1155
1156     def test_ticket15316_one2one_filter_true(self):
1157         c  = SimpleCategory.objects.create(name="cat")
1158         c0 = SimpleCategory.objects.create(name="cat0")
1159         c1 = SimpleCategory.objects.create(name="category1")
1160
1161         c2 = OneToOneCategory.objects.create(category = c1, new_name="new1")
1162         c3 = OneToOneCategory.objects.create(category = c0, new_name="new2")
1163
1164         ci1 = CategoryItem.objects.create(category=c)
1165         ci2 = CategoryItem.objects.create(category=c0)
1166         ci3 = CategoryItem.objects.create(category=c1)
1167
1168         qs = CategoryItem.objects.filter(category__onetoonecategory__isnull=True)
1169         self.assertEqual(qs.count(), 1)
1170         self.assertQuerysetEqual(qs, [ci1.pk], lambda x: x.pk)
1171
1172     def test_ticket15316_one2one_exclude_true(self):
1173         c  = SimpleCategory.objects.create(name="cat")
1174         c0 = SimpleCategory.objects.create(name="cat0")
1175         c1 = SimpleCategory.objects.create(name="category1")
1176
1177         c2 = OneToOneCategory.objects.create(category = c1, new_name="new1")
1178         c3 = OneToOneCategory.objects.create(category = c0, new_name="new2")
1179
1180         ci1 = CategoryItem.objects.create(category=c)
1181         ci2 = CategoryItem.objects.create(category=c0)
1182         ci3 = CategoryItem.objects.create(category=c1)
1183
1184         qs = CategoryItem.objects.exclude(category__onetoonecategory__isnull=True)
1185         self.assertEqual(qs.count(), 2)
1186         self.assertQuerysetEqual(qs, [ci2.pk, ci3.pk], lambda x: x.pk, False)
1187
1188
1189 class Queries5Tests(TestCase):
1190     def setUp(self):
1191         # Ordering by 'rank' gives us rank2, rank1, rank3. Ordering by the
1192         # Meta.ordering will be rank3, rank2, rank1.
1193         n1 = Note.objects.create(note='n1', misc='foo', id=1)
1194         n2 = Note.objects.create(note='n2', misc='bar', id=2)
1195         e1 = ExtraInfo.objects.create(info='e1', note=n1)
1196         e2 = ExtraInfo.objects.create(info='e2', note=n2)
1197         a1 = Author.objects.create(name='a1', num=1001, extra=e1)
1198         a2 = Author.objects.create(name='a2', num=2002, extra=e1)
1199         a3 = Author.objects.create(name='a3', num=3003, extra=e2)
1200         self.rank1 = Ranking.objects.create(rank=2, author=a2)
1201         Ranking.objects.create(rank=1, author=a3)
1202         Ranking.objects.create(rank=3, author=a1)
1203
1204     def test_ordering(self):
1205         # Cross model ordering is possible in Meta, too.
1206         self.assertQuerysetEqual(
1207             Ranking.objects.all(),
1208             ['<Ranking: 3: a1>', '<Ranking: 2: a2>', '<Ranking: 1: a3>']
1209         )
1210         self.assertQuerysetEqual(
1211             Ranking.objects.all().order_by('rank'),
1212             ['<Ranking: 1: a3>', '<Ranking: 2: a2>', '<Ranking: 3: a1>']
1213         )
1214
1215
1216         # Ordering of extra() pieces is possible, too and you can mix extra
1217         # fields and model fields in the ordering.
1218         self.assertQuerysetEqual(
1219             Ranking.objects.extra(tables=['django_site'], order_by=['-django_site.id', 'rank']),
1220             ['<Ranking: 1: a3>', '<Ranking: 2: a2>', '<Ranking: 3: a1>']
1221         )
1222
1223         qs = Ranking.objects.extra(select={'good': 'case when rank > 2 then 1 else 0 end'})
1224         self.assertEqual(
1225             [o.good for o in qs.extra(order_by=('-good',))],
1226             [True, False, False]
1227         )
1228         self.assertQuerysetEqual(
1229             qs.extra(order_by=('-good', 'id')),
1230             ['<Ranking: 3: a1>', '<Ranking: 2: a2>', '<Ranking: 1: a3>']
1231         )
1232
1233         # Despite having some extra aliases in the query, we can still omit
1234         # them in a values() query.
1235         dicts = qs.values('id', 'rank').order_by('id')
1236         self.assertEqual(
1237             [d.items()[1] for d in dicts],
1238             [('rank', 2), ('rank', 1), ('rank', 3)]
1239         )
1240
1241     def test_ticket7256(self):
1242         # An empty values() call includes all aliases, including those from an
1243         # extra()
1244         qs = Ranking.objects.extra(select={'good': 'case when rank > 2 then 1 else 0 end'})
1245         dicts = qs.values().order_by('id')
1246         for d in dicts: del d['id']; del d['author_id']
1247         self.assertEqual(
1248             [sorted(d.items()) for d in dicts],
1249             [[('good', 0), ('rank', 2)], [('good', 0), ('rank', 1)], [('good', 1), ('rank', 3)]]
1250         )
1251
1252     def test_ticket7045(self):
1253         # Extra tables used to crash SQL construction on the second use.
1254         qs = Ranking.objects.extra(tables=['django_site'])
1255         qs.query.get_compiler(qs.db).as_sql()
1256         # test passes if this doesn't raise an exception.
1257         qs.query.get_compiler(qs.db).as_sql()
1258
1259     def test_ticket9848(self):
1260         # Make sure that updates which only filter on sub-tables don't
1261         # inadvertently update the wrong records (bug #9848).
1262
1263         # Make sure that the IDs from different tables don't happen to match.
1264         self.assertQuerysetEqual(
1265             Ranking.objects.filter(author__name='a1'),
1266             ['<Ranking: 3: a1>']
1267         )
1268         self.assertEqual(
1269             Ranking.objects.filter(author__name='a1').update(rank='4'),
1270             1
1271         )
1272         r = Ranking.objects.filter(author__name='a1')[0]
1273         self.assertNotEqual(r.id, r.author.id)
1274         self.assertEqual(r.rank, 4)
1275         r.rank = 3
1276         r.save()
1277         self.assertQuerysetEqual(
1278             Ranking.objects.all(),
1279             ['<Ranking: 3: a1>', '<Ranking: 2: a2>', '<Ranking: 1: a3>']
1280         )
1281
1282     def test_ticket5261(self):
1283         self.assertQuerysetEqual(
1284             Note.objects.exclude(Q()),
1285             ['<Note: n1>', '<Note: n2>']
1286         )
1287
1288
1289 class SelectRelatedTests(TestCase):
1290     def test_tickets_3045_3288(self):
1291         # Once upon a time, select_related() with circular relations would loop
1292         # infinitely if you forgot to specify "depth". Now we set an arbitrary
1293         # default upper bound.
1294         self.assertQuerysetEqual(X.objects.all(), [])
1295         self.assertQuerysetEqual(X.objects.select_related(), [])
1296
1297
1298 class SubclassFKTests(TestCase):
1299     def test_ticket7778(self):
1300         # Model subclasses could not be deleted if a nullable foreign key
1301         # relates to a model that relates back.
1302
1303         num_celebs = Celebrity.objects.count()
1304         tvc = TvChef.objects.create(name="Huey")
1305         self.assertEqual(Celebrity.objects.count(), num_celebs + 1)
1306         Fan.objects.create(fan_of=tvc)
1307         Fan.objects.create(fan_of=tvc)
1308         tvc.delete()
1309
1310         # The parent object should have been deleted as well.
1311         self.assertEqual(Celebrity.objects.count(), num_celebs)
1312
1313
1314 class CustomPkTests(TestCase):
1315     def test_ticket7371(self):
1316         self.assertQuerysetEqual(Related.objects.order_by('custom'), [])
1317
1318
1319 class NullableRelOrderingTests(TestCase):
1320     def test_ticket10028(self):
1321         # Ordering by model related to nullable relations(!) should use outer
1322         # joins, so that all results are included.
1323         _ = Plaything.objects.create(name="p1")
1324         self.assertQuerysetEqual(
1325             Plaything.objects.all(),
1326             ['<Plaything: p1>']
1327         )
1328
1329
1330 class DisjunctiveFilterTests(TestCase):
1331     def setUp(self):
1332         self.n1 = Note.objects.create(note='n1', misc='foo', id=1)
1333         ExtraInfo.objects.create(info='e1', note=self.n1)
1334
1335     def test_ticket7872(self):
1336         # Another variation on the disjunctive filtering theme.
1337
1338         # For the purposes of this regression test, it's important that there is no
1339         # Join object releated to the LeafA we create.
1340         LeafA.objects.create(data='first')
1341         self.assertQuerysetEqual(LeafA.objects.all(), ['<LeafA: first>'])
1342         self.assertQuerysetEqual(
1343             LeafA.objects.filter(Q(data='first')|Q(join__b__data='second')),
1344             ['<LeafA: first>']
1345         )
1346
1347     def test_ticket8283(self):
1348         # Checking that applying filters after a disjunction works correctly.
1349         self.assertQuerysetEqual(
1350             (ExtraInfo.objects.filter(note=self.n1)|ExtraInfo.objects.filter(info='e2')).filter(note=self.n1),
1351             ['<ExtraInfo: e1>']
1352         )
1353         self.assertQuerysetEqual(
1354             (ExtraInfo.objects.filter(info='e2')|ExtraInfo.objects.filter(note=self.n1)).filter(note=self.n1),
1355             ['<ExtraInfo: e1>']
1356         )
1357
1358
1359 class Queries6Tests(TestCase):
1360     def setUp(self):
1361         generic = NamedCategory.objects.create(name="Generic")
1362         t1 = Tag.objects.create(name='t1', category=generic)
1363         t2 = Tag.objects.create(name='t2', parent=t1, category=generic)
1364         t3 = Tag.objects.create(name='t3', parent=t1)
1365         t4 = Tag.objects.create(name='t4', parent=t3)
1366         t5 = Tag.objects.create(name='t5', parent=t3)
1367         n1 = Note.objects.create(note='n1', misc='foo', id=1)
1368         ann1 = Annotation.objects.create(name='a1', tag=t1)
1369         ann1.notes.add(n1)
1370         ann2 = Annotation.objects.create(name='a2', tag=t4)
1371
1372     # This next test used to cause really weird PostgreSQL behavior, but it was
1373     # only apparent much later when the full test suite ran.
1374     #@unittest.expectedFailure
1375     def test_slicing_and_cache_interaction(self):
1376         # We can do slicing beyond what is currently in the result cache,
1377         # too.
1378
1379         # We need to mess with the implementation internals a bit here to decrease the
1380         # cache fill size so that we don't read all the results at once.
1381         from django.db.models import query
1382         query.ITER_CHUNK_SIZE = 2
1383         qs = Tag.objects.all()
1384
1385         # Fill the cache with the first chunk.
1386         self.assertTrue(bool(qs))
1387         self.assertEqual(len(qs._result_cache), 2)
1388
1389         # Query beyond the end of the cache and check that it is filled out as required.
1390         self.assertEqual(repr(qs[4]), '<Tag: t5>')
1391         self.assertEqual(len(qs._result_cache), 5)
1392
1393         # But querying beyond the end of the result set will fail.
1394         self.assertRaises(IndexError, lambda: qs[100])
1395
1396     def test_parallel_iterators(self):
1397         # Test that parallel iterators work.
1398         qs = Tag.objects.all()
1399         i1, i2 = iter(qs), iter(qs)
1400         self.assertEqual(repr(i1.next()), '<Tag: t1>')
1401         self.assertEqual(repr(i1.next()), '<Tag: t2>')
1402         self.assertEqual(repr(i2.next()), '<Tag: t1>')
1403         self.assertEqual(repr(i2.next()), '<Tag: t2>')
1404         self.assertEqual(repr(i2.next()), '<Tag: t3>')
1405         self.assertEqual(repr(i1.next()), '<Tag: t3>')
1406
1407         qs = X.objects.all()
1408         self.assertEqual(bool(qs), False)
1409         self.assertEqual(bool(qs), False)
1410
1411     def test_nested_queries_sql(self):
1412         # Nested queries should not evaluate the inner query as part of constructing the
1413         # SQL (so we should see a nested query here, indicated by two "SELECT" calls).
1414         qs = Annotation.objects.filter(notes__in=Note.objects.filter(note="xyzzy"))
1415         self.assertEqual(
1416             qs.query.get_compiler(qs.db).as_sql()[0].count('SELECT'),
1417             2
1418         )
1419
1420     def test_tickets_8921_9188(self):
1421         # Incorrect SQL was being generated for certain types of exclude()
1422         # queries that crossed multi-valued relations (#8921, #9188 and some
1423         # pre-emptively discovered cases).
1424
1425         self.assertQuerysetEqual(
1426             PointerA.objects.filter(connection__pointerb__id=1),
1427             []
1428         )
1429         self.assertQuerysetEqual(
1430             PointerA.objects.exclude(connection__pointerb__id=1),
1431             []
1432         )
1433
1434         self.assertQuerysetEqual(
1435             Tag.objects.exclude(children=None),
1436             ['<Tag: t1>', '<Tag: t3>']
1437         )
1438
1439         # This example is tricky because the parent could be NULL, so only checking
1440         # parents with annotations omits some results (tag t1, in this case).
1441         self.assertQuerysetEqual(
1442             Tag.objects.exclude(parent__annotation__name="a1"),
1443             ['<Tag: t1>', '<Tag: t4>', '<Tag: t5>']
1444         )
1445
1446         # The annotation->tag link is single values and tag->children links is
1447         # multi-valued. So we have to split the exclude filter in the middle
1448         # and then optimize the inner query without losing results.
1449         self.assertQuerysetEqual(
1450             Annotation.objects.exclude(tag__children__name="t2"),
1451             ['<Annotation: a2>']
1452         )
1453
1454         # Nested queries are possible (although should be used with care, since
1455         # they have performance problems on backends like MySQL.
1456
1457         self.assertQuerysetEqual(
1458             Annotation.objects.filter(notes__in=Note.objects.filter(note="n1")),
1459             ['<Annotation: a1>']
1460         )
1461
1462     def test_ticket3739(self):
1463         # The all() method on querysets returns a copy of the queryset.
1464         q1 = Tag.objects.order_by('name')
1465         self.assertIsNot(q1, q1.all())
1466
1467
1468 class RawQueriesTests(TestCase):
1469     def setUp(self):
1470         n1 = Note.objects.create(note='n1', misc='foo', id=1)
1471
1472     def test_ticket14729(self):
1473         # Test representation of raw query with one or few parameters passed as list
1474         query = "SELECT * FROM queries_note WHERE note = %s"
1475         params = ['n1']
1476         qs = Note.objects.raw(query, params=params)
1477         self.assertEqual(repr(qs), "<RawQuerySet: 'SELECT * FROM queries_note WHERE note = n1'>")
1478
1479         query = "SELECT * FROM queries_note WHERE note = %s and misc = %s"
1480         params = ['n1', 'foo']
1481         qs = Note.objects.raw(query, params=params)
1482         self.assertEqual(repr(qs), "<RawQuerySet: 'SELECT * FROM queries_note WHERE note = n1 and misc = foo'>")
1483
1484
1485 class GeneratorExpressionTests(TestCase):
1486     def test_ticket10432(self):
1487         # Using an empty generator expression as the rvalue for an "__in"
1488         # lookup is legal.
1489         self.assertQuerysetEqual(
1490             Note.objects.filter(pk__in=(x for x in ())),
1491             []
1492         )
1493
1494
1495 class ComparisonTests(TestCase):
1496     def setUp(self):
1497         self.n1 = Note.objects.create(note='n1', misc='foo', id=1)
1498         e1 = ExtraInfo.objects.create(info='e1', note=self.n1)
1499         self.a2 = Author.objects.create(name='a2', num=2002, extra=e1)
1500
1501     def test_ticket8597(self):
1502         # Regression tests for case-insensitive comparisons
1503         _ = Item.objects.create(name="a_b", created=datetime.datetime.now(), creator=self.a2, note=self.n1)
1504         _ = Item.objects.create(name="x%y", created=datetime.datetime.now(), creator=self.a2, note=self.n1)
1505         self.assertQuerysetEqual(
1506             Item.objects.filter(name__iexact="A_b"),
1507             ['<Item: a_b>']
1508         )
1509         self.assertQuerysetEqual(
1510             Item.objects.filter(name__iexact="x%Y"),
1511             ['<Item: x%y>']
1512         )
1513         self.assertQuerysetEqual(
1514             Item.objects.filter(name__istartswith="A_b"),
1515             ['<Item: a_b>']
1516         )
1517         self.assertQuerysetEqual(
1518             Item.objects.filter(name__iendswith="A_b"),
1519             ['<Item: a_b>']
1520         )
1521
1522
1523 class ExistsSql(TestCase):
1524     def setUp(self):
1525         settings.DEBUG = True
1526
1527     def test_exists(self):
1528         self.assertFalse(Tag.objects.exists())
1529         # Ok - so the exist query worked - but did it include too many columns?
1530         self.assertTrue("id" not in connection.queries[-1]['sql'] and "name" not in connection.queries[-1]['sql'])
1531
1532     def tearDown(self):
1533         settings.DEBUG = False
1534
1535
1536 class QuerysetOrderedTests(unittest.TestCase):
1537     """
1538     Tests for the Queryset.ordered attribute.
1539     """
1540
1541     def test_no_default_or_explicit_ordering(self):
1542         self.assertEqual(Annotation.objects.all().ordered, False)
1543
1544     def test_cleared_default_ordering(self):
1545         self.assertEqual(Tag.objects.all().ordered, True)
1546         self.assertEqual(Tag.objects.all().order_by().ordered, False)
1547
1548     def test_explicit_ordering(self):
1549         self.assertEqual(Annotation.objects.all().order_by('id').ordered, True)
1550
1551     def test_order_by_extra(self):
1552         self.assertEqual(Annotation.objects.all().extra(order_by=['id']).ordered, True)
1553
1554     def test_annotated_ordering(self):
1555         qs = Annotation.objects.annotate(num_notes=Count('notes'))
1556         self.assertEqual(qs.ordered, False)
1557         self.assertEqual(qs.order_by('num_notes').ordered, True)
1558
1559
1560 class SubqueryTests(TestCase):
1561     def setUp(self):
1562         DumbCategory.objects.create(id=1)
1563         DumbCategory.objects.create(id=2)
1564         DumbCategory.objects.create(id=3)
1565
1566     def test_ordered_subselect(self):
1567         "Subselects honor any manual ordering"
1568         try:
1569             query = DumbCategory.objects.filter(id__in=DumbCategory.objects.order_by('-id')[0:2])
1570             self.assertEqual(set(query.values_list('id', flat=True)), set([2,3]))
1571
1572             query = DumbCategory.objects.filter(id__in=DumbCategory.objects.order_by('-id')[:2])
1573             self.assertEqual(set(query.values_list('id', flat=True)), set([2,3]))
1574
1575             query = DumbCategory.objects.filter(id__in=DumbCategory.objects.order_by('-id')[2:])
1576             self.assertEqual(set(query.values_list('id', flat=True)), set([1]))
1577         except DatabaseError:
1578             # Oracle and MySQL both have problems with sliced subselects.
1579             # This prevents us from even evaluating this test case at all.
1580             # Refs #10099
1581             self.assertFalse(connections[DEFAULT_DB_ALIAS].features.allow_sliced_subqueries)
1582
1583     def test_sliced_delete(self):
1584         "Delete queries can safely contain sliced subqueries"
1585         try:
1586             DumbCategory.objects.filter(id__in=DumbCategory.objects.order_by('-id')[0:1]).delete()
1587             self.assertEqual(set(DumbCategory.objects.values_list('id', flat=True)), set([1,2]))
1588         except DatabaseError:
1589             # Oracle and MySQL both have problems with sliced subselects.
1590             # This prevents us from even evaluating this test case at all.
1591             # Refs #10099
1592             self.assertFalse(connections[DEFAULT_DB_ALIAS].features.allow_sliced_subqueries)
1593
1594
1595 class CloneTests(TestCase):
1596     def test_evaluated_queryset_as_argument(self):
1597         "#13227 -- If a queryset is already evaluated, it can still be used as a query arg"
1598         n = Note(note='Test1', misc='misc')
1599         n.save()
1600         e = ExtraInfo(info='good', note=n)
1601         e.save()
1602
1603         n_list = Note.objects.all()
1604         # Evaluate the Note queryset, populating the query cache
1605         list(n_list)
1606         # Use the note queryset in a query, and evalute
1607         # that query in a way that involves cloning.
1608         try:
1609             self.assertEqual(ExtraInfo.objects.filter(note__in=n_list)[0].info, 'good')
1610         except:
1611             self.fail('Query should be clonable')
1612
1613
1614 class EmptyQuerySetTests(TestCase):
1615     def test_emptyqueryset_values(self):
1616         # #14366 -- Calling .values() on an EmptyQuerySet and then cloning that
1617         # should not cause an error"
1618         self.assertQuerysetEqual(
1619             Number.objects.none().values('num').order_by('num'), []
1620         )
1621
1622     def test_values_subquery(self):
1623         self.assertQuerysetEqual(
1624             Number.objects.filter(pk__in=Number.objects.none().values("pk")),
1625             []
1626         )
1627         self.assertQuerysetEqual(
1628             Number.objects.filter(pk__in=Number.objects.none().values_list("pk")),
1629             []
1630         )
1631
1632
1633 class ValuesQuerysetTests(BaseQuerysetTest):
1634     def test_flat_values_lits(self):
1635         Number.objects.create(num=72)
1636         qs = Number.objects.values_list("num")
1637         qs = qs.values_list("num", flat=True)
1638         self.assertValueQuerysetEqual(
1639             qs, [72]
1640         )
1641
1642
1643 class WeirdQuerysetSlicingTests(BaseQuerysetTest):
1644     def setUp(self):
1645         Number.objects.create(num=1)
1646         Number.objects.create(num=2)
1647
1648         Article.objects.create(name='one', created=datetime.datetime.now())
1649         Article.objects.create(name='two', created=datetime.datetime.now())
1650         Article.objects.create(name='three', created=datetime.datetime.now())
1651         Article.objects.create(name='four', created=datetime.datetime.now())
1652
1653     def test_tickets_7698_10202(self):
1654         # People like to slice with '0' as the high-water mark.
1655         self.assertQuerysetEqual(Article.objects.all()[0:0], [])
1656         self.assertQuerysetEqual(Article.objects.all()[0:0][:10], [])
1657         self.assertEqual(Article.objects.all()[:0].count(), 0)
1658         self.assertRaisesMessage(
1659             AssertionError,
1660             'Cannot change a query once a slice has been taken.',
1661             Article.objects.all()[:0].latest, 'created'
1662         )
1663
1664     def test_empty_resultset_sql(self):
1665         # ticket #12192
1666         self.assertNumQueries(0, lambda: list(Number.objects.all()[1:1]))
1667
1668
1669 class EscapingTests(TestCase):
1670     def test_ticket_7302(self):
1671         # Reserved names are appropriately escaped
1672         _ = ReservedName.objects.create(name='a', order=42)
1673         ReservedName.objects.create(name='b', order=37)
1674         self.assertQuerysetEqual(
1675             ReservedName.objects.all().order_by('order'),
1676             ['<ReservedName: b>', '<ReservedName: a>']
1677         )
1678         self.assertQuerysetEqual(
1679             ReservedName.objects.extra(select={'stuff':'name'}, order_by=('order','stuff')),
1680             ['<ReservedName: b>', '<ReservedName: a>']
1681         )
1682
1683
1684 class ToFieldTests(TestCase):
1685     def test_in_query(self):
1686         apple = Food.objects.create(name="apple")
1687         pear = Food.objects.create(name="pear")
1688         lunch = Eaten.objects.create(food=apple, meal="lunch")
1689         dinner = Eaten.objects.create(food=pear, meal="dinner")
1690
1691         self.assertEqual(
1692             set(Eaten.objects.filter(food__in=[apple, pear])),
1693             set([lunch, dinner]),
1694         )
1695
1696     def test_reverse_in(self):
1697         apple = Food.objects.create(name="apple")
1698         pear = Food.objects.create(name="pear")
1699         lunch_apple = Eaten.objects.create(food=apple, meal="lunch")
1700         lunch_pear = Eaten.objects.create(food=pear, meal="dinner")
1701
1702         self.assertEqual(
1703             set(Food.objects.filter(eaten__in=[lunch_apple, lunch_pear])),
1704             set([apple, pear])
1705         )
1706
1707     def test_single_object(self):
1708         apple = Food.objects.create(name="apple")
1709         lunch = Eaten.objects.create(food=apple, meal="lunch")
1710         dinner = Eaten.objects.create(food=apple, meal="dinner")
1711
1712         self.assertEqual(
1713             set(Eaten.objects.filter(food=apple)),
1714             set([lunch, dinner])
1715         )
1716
1717     def test_single_object_reverse(self):
1718         apple = Food.objects.create(name="apple")
1719         lunch = Eaten.objects.create(food=apple, meal="lunch")
1720
1721         self.assertEqual(
1722             set(Food.objects.filter(eaten=lunch)),
1723             set([apple])
1724         )
1725
1726     def test_recursive_fk(self):
1727         node1 = Node.objects.create(num=42)
1728         node2 = Node.objects.create(num=1, parent=node1)
1729
1730         self.assertEqual(
1731             list(Node.objects.filter(parent=node1)),
1732             [node2]
1733         )
1734
1735     def test_recursive_fk_reverse(self):
1736         node1 = Node.objects.create(num=42)
1737         node2 = Node.objects.create(num=1, parent=node1)
1738
1739         self.assertEqual(
1740             list(Node.objects.filter(node=node2)),
1741             [node1]
1742         )
1743
1744
1745 class ConditionalTests(BaseQuerysetTest):
1746     """Tests whose execution depend on different environment conditions like
1747     Python version or DB backend features"""
1748
1749     def setUp(self):
1750         generic = NamedCategory.objects.create(name="Generic")
1751         t1 = Tag.objects.create(name='t1', category=generic)
1752         t2 = Tag.objects.create(name='t2', parent=t1, category=generic)
1753         t3 = Tag.objects.create(name='t3', parent=t1)
1754         t4 = Tag.objects.create(name='t4', parent=t3)
1755         t5 = Tag.objects.create(name='t5', parent=t3)
1756
1757
1758     # In Python 2.6 beta releases, exceptions raised in __len__ are swallowed
1759     # (Python issue 1242657), so these cases return an empty list, rather than
1760     # raising an exception. Not a lot we can do about that, unfortunately, due to
1761     # the way Python handles list() calls internally. Thus, we skip the tests for
1762     # Python 2.6.
1763     @unittest.skipIf(sys.version_info[:2] == (2, 6), "Python version is 2.6")
1764     def test_infinite_loop(self):
1765         # If you're not careful, it's possible to introduce infinite loops via
1766         # default ordering on foreign keys in a cycle. We detect that.
1767         self.assertRaisesMessage(
1768             FieldError,
1769             'Infinite loop caused by ordering.',
1770             lambda: list(LoopX.objects.all()) # Force queryset evaluation with list()
1771         )
1772         self.assertRaisesMessage(
1773             FieldError,
1774             'Infinite loop caused by ordering.',
1775             lambda: list(LoopZ.objects.all()) # Force queryset evaluation with list()
1776         )
1777
1778         # Note that this doesn't cause an infinite loop, since the default
1779         # ordering on the Tag model is empty (and thus defaults to using "id"
1780         # for the related field).
1781         self.assertEqual(len(Tag.objects.order_by('parent')), 5)
1782
1783         # ... but you can still order in a non-recursive fashion amongst linked
1784         # fields (the previous test failed because the default ordering was
1785         # recursive).
1786         self.assertQuerysetEqual(
1787             LoopX.objects.all().order_by('y__x__y__x__id'),
1788             []
1789         )
1790
1791     # When grouping without specifying ordering, we add an explicit "ORDER BY NULL"
1792     # portion in MySQL to prevent unnecessary sorting.
1793     @skipUnlessDBFeature('requires_explicit_null_ordering_when_grouping')
1794     def test_null_ordering_added(self):
1795         query = Tag.objects.values_list('parent_id', flat=True).order_by().query
1796         query.group_by = ['parent_id']
1797         sql = query.get_compiler(DEFAULT_DB_ALIAS).as_sql()[0]
1798         fragment = "ORDER BY "
1799         pos = sql.find(fragment)
1800         self.assertEqual(sql.find(fragment, pos + 1), -1)
1801         self.assertEqual(sql.find("NULL", pos + len(fragment)), pos + len(fragment))
1802
1803     # Sqlite 3 does not support passing in more than 1000 parameters except by
1804     # changing a parameter at compilation time.
1805     @skipUnlessDBFeature('supports_1000_query_parameters')
1806     def test_ticket14244(self):
1807         # Test that the "in" lookup works with lists of 1000 items or more.
1808         Number.objects.all().delete()
1809         numbers = range(2500)
1810         Number.objects.bulk_create(Number(num=num) for num in numbers)
1811         self.assertEqual(
1812             Number.objects.filter(num__in=numbers[:1000]).count(),
1813             1000
1814         )
1815         self.assertEqual(
1816             Number.objects.filter(num__in=numbers[:1001]).count(),
1817             1001
1818         )
1819         self.assertEqual(
1820             Number.objects.filter(num__in=numbers[:2000]).count(),
1821             2000
1822         )
1823         self.assertEqual(
1824             Number.objects.filter(num__in=numbers).count(),
1825             2500
1826         )
1827
1828
1829 class UnionTests(unittest.TestCase):
1830     """
1831     Tests for the union of two querysets. Bug #12252.
1832     """
1833     def setUp(self):
1834         objectas = []
1835         objectbs = []
1836         objectcs = []
1837         a_info = ['one', 'two', 'three']
1838         for name in a_info:
1839             o = ObjectA(name=name)
1840             o.save()
1841             objectas.append(o)
1842         b_info = [('un', 1, objectas[0]), ('deux', 2, objectas[0]), ('trois', 3, objectas[2])]
1843         for name, number, objecta in b_info:
1844             o = ObjectB(name=name, num=number, objecta=objecta)
1845             o.save()
1846             objectbs.append(o)
1847         c_info = [('ein', objectas[2], objectbs[2]), ('zwei', objectas[1], objectbs[1])]
1848         for name, objecta, objectb in c_info:
1849             o = ObjectC(name=name, objecta=objecta, objectb=objectb)
1850             o.save()
1851             objectcs.append(o)
1852
1853     def check_union(self, model, Q1, Q2):
1854         filter = model.objects.filter
1855         self.assertEqual(set(filter(Q1) | filter(Q2)), set(filter(Q1 | Q2)))
1856         self.assertEqual(set(filter(Q2) | filter(Q1)), set(filter(Q1 | Q2)))
1857
1858     def test_A_AB(self):
1859         Q1 = Q(name='two')
1860         Q2 = Q(objectb__name='deux')
1861         self.check_union(ObjectA, Q1, Q2)
1862
1863     def test_A_AB2(self):
1864         Q1 = Q(name='two')
1865         Q2 = Q(objectb__name='deux', objectb__num=2)
1866         self.check_union(ObjectA, Q1, Q2)
1867
1868     def test_AB_ACB(self):
1869         Q1 = Q(objectb__name='deux')
1870         Q2 = Q(objectc__objectb__name='deux')
1871         self.check_union(ObjectA, Q1, Q2)
1872
1873     def test_BAB_BAC(self):
1874         Q1 = Q(objecta__objectb__name='deux')
1875         Q2 = Q(objecta__objectc__name='ein')
1876         self.check_union(ObjectB, Q1, Q2)
1877
1878     def test_BAB_BACB(self):
1879         Q1 = Q(objecta__objectb__name='deux')
1880         Q2 = Q(objecta__objectc__objectb__name='trois')
1881         self.check_union(ObjectB, Q1, Q2)
1882
1883     def test_BA_BCA__BAB_BAC_BCA(self):
1884         Q1 = Q(objecta__name='one', objectc__objecta__name='two')
1885         Q2 = Q(objecta__objectc__name='ein', objectc__objecta__name='three', objecta__objectb__name='trois')
1886         self.check_union(ObjectB, Q1, Q2)
1887
1888
1889 class DefaultValuesInsertTest(TestCase):
1890     def test_no_extra_params(self):
1891         # Ticket #17056 -- affects Oracle
1892         try:
1893             DumbCategory.objects.create()
1894         except TypeError:
1895             self.fail("Creation of an instance of a model with only the PK field shouldn't error out after bulk insert refactoring (#17056)")