Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

BUG : Magento 1.7 Shipping Rates “lucky dip” Query
 
daveime
Jr. Member
 
Total Posts:  2
Joined:  2013-02-25
 

Hi, I’m a freelance programmer, and I have a client who’s using Magento 1.7 Community edition. He was asking me to setup the Shipping Rates for him, and I discovered what I think is a serious bug in the way the correct rate is selected.

So, if we look at System -> Configuration -> Shipping Methods, I have Table Rates enabled, and all other methods disabled. The mode is set to “Price vs Destination”, and I select Italy as the applicable country . I upload a CSV file with the rates thus (header row omitted) :-

IT    *    *    0    10
IT    
*    *    150    5

This is a very simple ruleset, if country is Italy, and price is less than 150 then shipping is 10 - if price is 150 or more, then shipping is 5. This is just a very simple ruleset, obviously the full blown one would contain multiple countries and order subtotal “ranges”.

However, it’s not working on the frontend, seems like whatever the subtotal, the shipping cost is always reported as 10.

So I delved into the code to see what was going on, and discovered a huge logic flaw.

Take a look at this file app/code/core/Mage/Shipping/Model/Resource/Carrier/Tablerate.php, lines 116 - 178 where he creates the database query for matching rows in the shipping rate table against the supplied parameters.

Notwithstanding the double identical condition for

dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = ‘*’

which doesn’t actually cause any harm, if we translate those conditions into a raw SQL query, using the parameters

:country_id 'IT'
:region_id ''
:postcode ''

we end up with an SQL query like this :-

(
dest_country_id 'IT' AND dest_region_id '' AND dest_zip '' OR 
dest_country_id 'IT' AND dest_region_id '' AND dest_zip '' OR 
dest_country_id 'IT' AND dest_region_id '' AND dest_zip '*' OR 
dest_country_id 'IT' AND dest_region_id AND dest_zip '*' OR 
dest_country_id '0' AND dest_region_id '' AND dest_zip '*' OR 
dest_country_id '0' AND dest_region_id AND dest_zip '*' OR 
dest_country_id 'IT' AND dest_region_id AND dest_zip '' OR 
dest_country_id 'IT' AND dest_region_id AND dest_zip '' OR 
dest_country_id 'IT' AND dest_region_id AND dest_zip '*'
)
AND (
condition_name 'package_value'
AND (
condition_value <= 150)

HOWEVER, and this is the problem ... line 172 makes a call to $adapter->fetchRow($select, $bind); and this call adds the following extra bits onto the SQL

SELECT FROM shipping_tablerate WHERE (website_id 1) AND 
(
dest_country_id 'IT' AND dest_region_id '' AND dest_zip '' OR 
dest_country_id 'IT' AND dest_region_id '' AND dest_zip '' OR 
dest_country_id 'IT' AND dest_region_id '' AND dest_zip '*' OR 
dest_country_id 'IT' AND dest_region_id AND dest_zip '*' OR 
dest_country_id '0' AND dest_region_id '' AND dest_zip '*' OR 
dest_country_id '0' AND dest_region_id AND dest_zip '*' OR 
dest_country_id 'IT' AND dest_region_id AND dest_zip '' OR 
dest_country_id 'IT' AND dest_region_id AND dest_zip '' OR 
dest_country_id 'IT' AND dest_region_id AND dest_zip '*'
)
AND (
condition_name 'package_value'
AND (
condition_value <= 150)
ORDER BY dest_country_id DESCdest_region_id DESCdest_zip DESC
LIMIT 0
,1

If you run that SQL without the LIMIT clause, BOTH rows are returned, because the <= 150 condition applies to both rules ... only when you add the LIMIT clause does he limit the result to a single row from the table ! But this is logically wrong - the ORDER BY clause achieves nothing, as the values for dest_country_id, dest_region_id and dest_zip are identical for both rows, and the system leaves the final selection of WHICH row to choose in the LIMIT clause up to MySQL !!!

It’s basically a lucky dip as to which one will actually get chosen, and is logically incorrect, and the situation becomes far worse the more rules apply, as he’s basically picking one at random.

I’m pretty sure the solution is as simple as adding an extra sort column i.e. condition_value DESC onto the end of the SORT clause - this would ensure that the BIGGEST matching condition was always the first row and was always selected.

But seriously ? I didn’t expect these kind of logical mistakes from a system like Magento ?

Has this issue been recognized before, or a patch realized ? I wasn’t sure exactly how to post an official bug report, hope I’m in the right place !

 
Magento Community Magento Community
Magento Community
Magento Community
 
daveime
Jr. Member
 
Total Posts:  2
Joined:  2013-02-25
 

Update :

Seems like this is already a known issue for 6 months or more ...

http://www.magentocommerce.com/bug-tracking/issue/?issue=14069

Still, good to know others have encountered the problem and recognized the same fix required.

 
Magento Community Magento Community
Magento Community
Magento Community
 
sanyamkohli
Jr. Member
 
Total Posts:  1
Joined:  2013-04-05
 

Hi good to see the problem is found out but what is the solution I am getting my website built and shipping is the main concern now..

I want free shipping to be enabled for sub total above Rs 350
and below that a fee of Rs 50 will be levied.

If a customer wants to avail a cash on delivery for a subtotal up to Rs 500 an additional fee of Rs 70 will be levied apart from the shipping which is up to Rs 350 level.

At last my problem is that I will be selling bulk products like Beds, Tables these are heavy items so for them I will be charging a shipping charge now how to apply this only for selected products and for rest the above shipping rules will be applicable .

Can you please suggest I have to run this store in India.
Thanks in advance.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Sean_irl
Jr. Member
 
Total Posts:  1
Joined:  2013-05-13
 

Found this solution a few weeks ago and can’t find it again. It works for a fairly straight forward setup.
Just reverse the order in the csv file....ie put larger Order Subtotal first in the file

Country,Region/State,"Zip/Postal Code","Order Subtotal (and above)","Shipping Price”
IRL,*,*,90.000,0.0000
IRL,*,*,25.0000,6.1500
IRL,*,*,0.0000,8.6000

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top