#6051: GenericRelation field lookups
------------------------+---------------------------------------------------
Reporter: litnimax | Owner: nobody
Status: new | Component: Uncategorized
Version: SVN | Resolution:
Keywords: generic | Stage: Unreviewed
Has_patch: 1 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
------------------------+---------------------------------------------------
Changes (by litnimax):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
== New issue ==
2 lines of code will tell better:
{{{
mysql> select provider_numberpool.* from provider_numberpool left join
billing_subscription on provider_numberpool.id = billing_subscription.id
where billing_subscription.id IS NULL;
+----+-----------------+--------------+------+
| id | name | is_published | note |
+----+-----------------+--------------+------+
| 1 | range 100 - 101 | 1 | |
+----+-----------------+--------------+------+
1 row in set (0.00 sec)
mysql> select provider_numberpool.* from provider_numberpool left join
billing_subscription on provider_numberpool.id =
billing_subscription.object_id where billing_subscription.id IS NULL;
Empty set (0.00 sec)
}}}
What is the difference? Only ON clause of JOIN. In !GenericObject we have
object_id equal to some other model's primary key. So object_id is not a
strict foreign key
Why
{{{
provider_numberpool.id = billing_subscription.id
}}}
works (but has no sense), but
{{{
provider_numberpool.id = billing_subscription.object_id
}}}
does not!? Both fields are INT, both have index. The only differency that
id is primary key, and object_id is simple index.
Let see what EXPLAIN will explain.
Working case:
{{{
mysql> explain extended select provider_numberpool.* from
provider_numberpool left join billing_subscription on
provider_numberpool.id = billing_subscription.id where
billing_subscription.id IS NULL;
+----+-------------+----------------------+--------+---------------+---------+---------+-------+------+----------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+----------------------+--------+---------------+---------+---------+-------+------+----------------------+
| 1 | SIMPLE | provider_numberpool | system | NULL | NULL
| NULL | NULL | 1 | |
| 1 | SIMPLE | billing_subscription | const | PRIMARY |
PRIMARY | 4 | const | 0 | unique row not found |
+----+-------------+----------------------+--------+---------------+---------+---------+-------+------+----------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select '1' AS `id`,'range 100 - 101' AS `name`,'1' AS
`is_published`,'' AS `note` from `billing`.`provider_numberpool` left join
`billing`.`billing_subscription` on(multiple equal('1')) where isnull('0')
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
}}}
Not working case:
{{{
mysql> explain extended select provider_numberpool.* from
provider_numberpool left join billing_subscription on
provider_numberpool.id = billing_subscription.object_id where
billing_subscription.id IS NULL;
+----+-------------+----------------------+--------+--------------------------------+--------------------------------+---------+-------+------+-------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+----+-------------+----------------------+--------+--------------------------------+--------------------------------+---------+-------+------+-------------------------+
| 1 | SIMPLE | provider_numberpool | system | NULL
| NULL | NULL | NULL | 1 |
|
| 1 | SIMPLE | billing_subscription | ref |
billing_subscription_object_id | billing_subscription_object_id | 4
| const | 15 | Using where; Not exists |
+----+-------------+----------------------+--------+--------------------------------+--------------------------------+---------+-------+------+-------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select '1' AS `id`,'range 100 - 101' AS `name`,'1' AS
`is_published`,'' AS `note` from `billing`.`provider_numberpool` left join
`billing`.`billing_subscription`
on((`billing`.`billing_subscription`.`object_id` = '1')) where
isnull(`billing`.`billing_subscription`.`id`) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
}}}
And finally table definition:
{{{
#!sql
CREATE TABLE `billing_subscription` (
`id` int(11) NOT NULL auto_increment,
`content_type_id` int(11) NOT NULL,
`object_id` int(10) NOT NULL,
`account_id` int(11) NOT NULL,
`currency_id` int(11) NOT NULL,
`period` int(11) NOT NULL,
`setup` decimal(10,2) NOT NULL,
`rate` decimal(10,2) NOT NULL,
`create_date` datetime NOT NULL,
`paidtill_date` date NOT NULL,
`is_enabled` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `billing_subscription_content_type_id` (`content_type_id`),
KEY `billing_subscription_account_id` (`account_id`),
KEY `billing_subscription_currency_id` (`currency_id`),
KEY `billing_subscription_object_id` (`object_id`)
) ENGINE=MyISAM AUTO_INCREMENT=56 DEFAULT CHARSET=utf8
}}}
== Conclusion ==
MySQL bug? What about Postgres? How to solve it!?
--
Ticket URL: <http://code.djangoproject.com/ticket/6051#comment:1>
Django Code <http://code.djangoproject.com/>
The web framework for perfectionists with deadlines
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
