PATCH Order.search return wrong result when counting orders in some situations.

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Place an order with different shipping and billing addresses that have
something in common in the first and last name (like a gift from John Doe
to John Doe Jr.)
2. Use Order.search to see how many orders John Doe have.

What is the expected output? What do you see instead?
It should be one. It shows two.

What version of the product are you using? On what operating system?
Trunk. Ubuntu 7.10

Please provide any additional information below.

Theres something wrong with the SQL that does the search. The order is
joined directly with the addresses using the order_user_id, to not have
duplicated results with different addresses that points to the same user,
was added a distinct clause to select, but when counting the distinct
clause is missing.

Theres more than a way to fix that, I choosed the simpler. Just add a
distinct clause instead of join two times with the addresses.

...
  sql = "SELECT COUNT(DISTINCT orders.id) "
...

It was just "SELECT COUNT(*)"

I deleted two WHERE clauses that I think doesn't make difference.

...
sql << "OR order_addresses.first_name LIKE ? "
sql << "OR order_addresses.last_name LIKE ? "
sql << "OR CONCAT(order_addresses.first_name, ' ',
order_addresses.last_name) LIKE ? "
...

If the names are being concatenated in a rule and the logical operator is
an "or" theres no need of the first two, as the third will be always
evaluated and will match anything that was matched by the first two too.

Follows a patch.

Original issue reported on code.google.com by edmundo...@gmail.com on 1 Jun 2008 at 9:03

Attachments:

• use_distinct_when_counting_orders.patch

[GoogleCodeExporter]

DISTINCT sounds like a good idea, however I believe the where is necessary so 
you can search on FIRST, LAST, or 
both names together.

Original comment by subim...@gmail.com on 2 Jun 2008 at 5:51

[GoogleCodeExporter]

I didn't got rid of the where clause completely, both names together was left 
there
(this is what CONCAT is meant for), but I didn't understood why do you want yet 
the
FIRST or LAST name alone.

Anyway, with or without it the result should be the same, I just think its not
needed. Unless you give me an example that I didn't though about and it gives
different results.

Original comment by edmundo...@gmail.com on 2 Jun 2008 at 4:04

[GoogleCodeExporter]

OK. I tried to make it give different results and found another problem.

Creating two orders with the same e-mail address, when you search by any field 
of one
order the other is returned too, even if it don't match the criteria (but it 
matches
the order_user_id owner of the e-mail address).

This will need to be redone. Both selects are wrong.

Original comment by edmundo...@gmail.com on 2 Jun 2008 at 4:32

[GoogleCodeExporter]

Here is the patch, I joined with the addresses table two times giving different
names, this way the distinct clause is not needed.

I still took out the where clauses that uses first or last name alone, only the
clause with concat was left.

Original comment by edmundo...@gmail.com on 2 Jun 2008 at 6:04

Attachments:

• dont_associate_with_order_users_directly.patch

[GoogleCodeExporter]

Thanks for the update, great patch. Fixed r97.

Original comment by subim...@gmail.com on 15 Jun 2008 at 7:12

• Changed state: Fixed