Issue 90: PATCH Order.search return wrong result when counting orders in some situations.
Status:  Fixed
Owner: ----
Closed:  Jun 2008
Reported by edmundo...@gmail.com, Jun 1, 2008
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.
use_distinct_when_counting_orders.patch
1.3 KB   View   Download
Jun 1, 2008
Project Member #1 subim...@gmail.com
DISTINCT sounds like a good idea, however I believe the where is necessary so you can search on FIRST, LAST, or 
both names together.

Jun 2, 2008
#2 edmundo...@gmail.com
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.
Jun 2, 2008
#3 edmundo...@gmail.com
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.
Jun 2, 2008
#4 edmundo...@gmail.com
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.
dont_associate_with_order_users_directly.patch
1.7 KB   View   Download
Jun 15, 2008
Project Member #5 subim...@gmail.com
Thanks for the update, great patch. Fixed r97.
Status: Fixed