MySQL error subquery returns more than 1 row

[Ilya]

I just developed some code like:
MyModel.objects.filter(key_to_mymodel2 = MyModel2.objects.all()))
This query produce SQL:
SELECT * FROM `test_mymodel` WHERE key_to_mymodel2_id =(select `id`
from `test_mymodel2`)

It works fine on SQLLite, but in MySQL it produce error:
OperationalError: (1242, 'Subquery returns more than 1 row')
MySQL to work need query:
SELECT * FROM `test_mymodel` WHERE key_to_mymodel2_id = ANY (select
`id` from `test_mymodel2`)

Of course I can make something like:
list = MyModel2.objects.all()
res = []
for x in list:
res += MyModel.objects.filter(key_to_mymodel2 = x)

It there any other path to solve this?

[Tom Evans]

--

This would also work:

MyModel.objects.filter(key_to_mymodel2__in=MyModel2.objects.all().values_list('id', flat=True)))

Cheers

Tom

[Ilya]

M, thanks.
But should I create ticket on django trac about this issue?

On 17 ноя, 17:29, Tom Evans <tevans...@googlemail.com> wrote:

[Nick Arnett]

On Tue, Nov 17, 2009 at 7:17 AM, Ilya <ilblac...@gmail.com> wrote:

I just developed some code like:
 MyModel.objects.filter(key_to_mymodel2 = MyModel2.objects.all()))
This query produce SQL:
SELECT * FROM `test_mymodel` WHERE key_to_mymodel2_id =(select `id`
from `test_mymodel2`)

It works fine on SQLLite, but in MySQL it produce error:
OperationalError: (1242, 'Subquery returns more than 1 row')
MySQL to work need query:
SELECT * FROM `test_mymodel` WHERE key_to_mymodel2_id = ANY (select
`id` from `test_mymodel2`)

With the same data?  It seems like a legitimate error unless test_mymodel2 has just one row, which would be odd.  I would expect the subquery to have a WHERE or LIMIT clause that would restrict it to returning a single row, to be useful.

If you really expect the subquery to return multiple rows, perhaps you mean something like this:

SELECT * FROM `test_mymodel` t1, (SELECT `id` FROM `test_mymodel2`) t2 WHERE t1.id = t2.id

Nick

[Ilya Polosuhin]

I don't fully understood your question. Of course I have more than one row at test_mymodel2

SQL query was formed by django-ORM. I just call

print (MyModel.objects.filter(key_to_mymodel2 = MyModel2.objects.all()))._as_sql())

and write here output.

And I also googled for this MySQL error and found how to solve SQL-query (add ANY to subquery). But I not need SQL, I need django-ORM code that will do what I need ;)

So I think this is the issue of django-ORM and should I report about it?

--

You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=.

[Nick Arnett]

On Tue, Nov 17, 2009 at 9:03 AM, Ilya Polosuhin <ilblac...@gmail.com> wrote:

I don't fully understood your question. Of course I have more than one row at test_mymodel2

SQL query was formed by django-ORM. I just call

print (MyModel.objects.filter(key_to_mymodel2 = MyModel2.objects.all()))._as_sql())

and write here output.

Ah, sorry - I was confused... I thought you wrote the SQL yourself.  That does seem to be a problem with the ORM.

Nick