The following query fails under PostgreSQL:

query: SELECT op.order_product_id, pp.qty, pp.qty * op.weight AS weight, 
p.weight_units, op.nid, op.title, op.model, op.price, FROM 
uc_packaged_products AS pp LEFT JOIN uc_order_products AS op ON 
op.order_product_id = pp.order_product_id LEFT JOIN uc_products AS p ON 
op.nid = p.nid WHERE pp.package_id = 8 GROUP BY op.order_product_id

on line 396.

Read MySQL/PostgreSQL for explainations:
You cannot GROUP BY on one single column:

But when looking at PHP code, it seems that we are calculating weight.

385 function uc_shipping_package_load($package_id) {
386   static $packages = array();
388   if (!isset($packages[$package_id])) {
389     $result = db_query("SELECT * FROM {uc_packages} WHERE package_id = %d", $package_id);
390     if ($package = db_fetch_object($result)) {
391       $products = array();
392       $descripion = '';
393       $weight = 0;
394       $units = variable_get('uc_weight_unit', 'lb');
395       $addresses = array();
396       $result = db_query("SELECT op.order_product_id, pp.qty, pp.qty * op.weight AS weight, p.weight_units, op.nid, op.title, op.model, op.price,     FROM {uc_packaged_products} AS pp LEFT JOIN {uc_order_products} AS op ON op.order_product_id = pp.order_product_id LEFT JOIN {uc_products} AS p ON    d = p.nid WHERE pp.package_id = %d GROUP BY op.order_product_id", $package_id);
397       while ($product = db_fetch_object($result)) {
398         $address = uc_quote_get_default_shipping_address($product->nid);
399         // TODO: Lodge complaint that array_unique() compares as strings.
400         if (!in_array($address, $addresses)) {
401           $addresses[] = $address;
402         }
403         $description .= ', '. $product->qty .' x '. $product->model;
404         // Normalize all weights to default units.
405         $weight += $product->weight * uc_weight_conversion($product->weight_units, $units);
406         $product->data = unserialize($product->data);
407         $products[$product->order_product_id] = $product;
408       }
409       $package->addresses = $addresses;
410       $package->description = substr($description, 2);
411       $package->weight = $weight;
412       $package->weight_units = $units;
413       $package->products = $products;
414       $packages[$package_id] = $package;
415     }
416     else {
417       return FALSE;
418     }
419   }

Let's take the example of a parcel containing two similar products A.
In MySQL, if a GROUP BY is encountered, it may choose an arbitrary record.
So this query seems to be ambiguous.

MySQL wron't complain, PostgreSQL does complain.

Did you mean ORDER BY instead of GROUP BY?

Please note that I may be wrong.
What is your opinion?

Kind regards,

#7 patch.diff1.28 KBgrub3


TR’s picture

Yes, this looks like a bug - it works properly for MySQL but not for PostgreSQL, as explained in . It will take some research to figure out how to rewrite the query so it works for both.

grub3’s picture

Assigned:grub3» Unassigned

If you explain what you are trying to do, I can rewrite the query for you.
The GROUP BY will fail to produce good results, even on MySQL.

What were you looking for? DISTINCT results ? ORDER results?
Just explain a little bit more and I rewrite an SQL 99 query for you.

grub3’s picture

Assigned:Unassigned» grub3
TR’s picture

Assigned:Unassigned» grub3

I didn't write the code, so I'm not exactly sure what the original intent was. That's why I said I would have to do research, by looking into the tables. I'm *guessing* that, since the purpose of the function is to group products into packages based on the ship-from address stored in the product object, GROUP BY was intended to select only one of each product nid, even if multiple products with that nid have been ordered. You only need to examine the ship-from address from one instance of the product, it doesn't matter which one because all products with that nid will have the same ship-from address.

Island Usurper’s picture

Yeah, I think I expected it to work as a more efficient DISTINCT. The joins that are done can give duplicate rows for a particular product in the package, but I only want to count the weight and address once. I think the row duplication comes from having multiple versions of the product node. I didn't want to have to do a HAVING MAX(p.vid) or join to {node} to restrict the results to one particular version, but maybe that's what needs to be done.

Suggestions are welcome.

grub3’s picture

I think this should simply work:

SELECT op.order_product_id, pp.qty, pp.qty * op.weight AS weight,
p.weight_units, op.nid, op.title, op.model, op.price, FROM
uc_packaged_products AS pp

LEFT JOIN uc_order_products AS op ON
op.order_product_id = pp.order_product_id

LEFT JOIN uc_products AS p ON
op.nid = p.nid WHERE pp.package_id = 62

ORDER BY op.order_product_id

There is no need to group by.

grub3’s picture

Status:Active» Needs review
new1.28 KB

Atttached patch. This works fine under PostgreSQL and on my store (reviewed 10 orders and ran SQL commands).

grub3’s picture

Any news. Can you commit to repository quickly. My log is full of errors and I cannot handle this any longuer.

grub3’s picture

Priority:Normal» Critical

Any news. Can you commit to repository quickly. My log is full of errors and I cannot handle this any longuer.

Island Usurper’s picture

Status:Needs review» Fixed

OK, I just had to remember what I was thinking when that was written. I believe I was worried about having rows with the same order_product_id, which would cause some weird issues in package operations. But since there's the WHERE clause selecting one specific package_id, I don't think we have to worry about it. order_product_id should always be distinct on that query, so ORDER BY is all we need.

Thanks, and committed.

Status:Fixed» Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

grub3’s picture


joep.hendrix’s picture

Status:Closed (fixed)» Active

I am not sure whether to re-open this issue or create a new one.
The problem is that this query is not correct when revisions are enabled on the product node type.
You will get a result for each revision. I guess the join on uc_products should be on the vid instead of the nid. Hower, it will then not show the actual product revision model!

SELECT op.order_product_id                          
,      pp.qty                                       
,      pp.qty * op.weight AS weight                 
,      p.weight_units                               
,      op.nid                                       
,      op.title                                     
,      op.model                                     
,      op.price                                     
FROM uc_packaged_products AS pp                     
LEFT JOIN uc_order_products AS op                   
  ON op.order_product_id = pp.order_product_id      
LEFT JOIN uc_products AS p                          
  ON op.nid = p.vid                                 
WHERE pp.package_id = 36 ORDER BY op.order_product_id
TR’s picture

Assigned:grub3» Unassigned
Priority:Critical» Normal
longwave’s picture

Status:Active» Closed (won't fix)

Not worth fixing this in 6.x now, and this has been fixed in 7.x as weight_units are now in the uc_order_products table.