Problematic code in advanced search result ?

[angeloio]

The Zen Cart version is 1.3.8a but from what I checked it is the same code in the newest release.

The code part I am talking about is in includes\modules\pages\advanced_search_result\header_php.php lines 231-236

  $from_str .= " LEFT JOIN " . TABLE_TAX_RATES . " tr
                 ON p.products_tax_class_id = tr.tax_class_id
                 LEFT JOIN " . TABLE_ZONES_TO_GEO_ZONES . " gz
                 ON tr.tax_zone_id = gz.geo_zone_id
                 AND (gz.zone_country_id IS null OR gz.zone_country_id = 0 OR gz.zone_country_id = :zoneCountryID)
                 AND (gz.zone_id IS null OR gz.zone_id = 0 OR gz.zone_id = :zoneID)"; 

The above is executed when

DISPLAY_PRICE_WITH_TAX == 'true' 

as in my shop...

OK the comments now:

1. First of all the search function by prices does not work correctly in my shop. Products with prices outside of the searched range are returned....so I did a little debugging and found out the following:

2. The conditional statement in the JOIN syntax above is a little strange . Granted you can have LEFT JOIN ... ON ... AND ... AND... but it does produce weird results :
(I am living in Greece so we have a simple 23% VAT on top of all net prices, for all country so: net*1.23=final(displayable) price)

Take for example the following query (84 is the ID of Greece):

SELECT * FROM `tax_rates` tr LEFT JOIN `zones_to_geo_zones` gz ON  tr.tax_zone_id = gz.geo_zone_id AND (gz.zone_country_id IS null OR gz.zone_country_id = 0 OR gz.zone_country_id = 84)
                 AND (gz.zone_id IS null OR gz.zone_id = 0 OR gz.zone_id = 0)

It produces the very weird results (see clearly attached doc)

tax_rates_id tax_zone_id geo_zone_id zone_country_id
3 4 4 84
4 5 NULL NULL
5 6 NULL NULL

Now, on line 365+ the returned products are found based on the :

    $where_str .= " AND (p.products_price_sorter * IF(gz.geo_zone_id IS null, 1, 1 + (tr.tax_rate / 100)) >= :price)"; 

which (because of the IS null condition and the NULL lines above) for some products DOES NOT add the VAT on top of the net price and hence produces WRONG results!!!

The normal according to my view would have been a more "logical SQL":

SELECT * FROM `tax_rates` tr, `zones_to_geo_zones` gz WHERE  tr.tax_zone_id = gz.geo_zone_id AND (gz.zone_country_id IS null OR gz.zone_country_id = 0 OR gz.zone_country_id = 84)
                 AND (gz.zone_id IS null OR gz.zone_id = 0 OR gz.zone_id = 0)

which DOES RETURN ONLY ONE LINE, the correct one:

tax_rates_id tax_zone_id geo_zone_id zone_country_id
3 4 4 84

and everything works correctly !

Any ideas if this is really a bug, or if there is some logic behind the NULL values that I could not see ???

Many thanks to all for your time !

Kind Regards
John

[BlindSide]

Could be a smaller typo than you imagine. Try this modification:

SELECT * FROM `tax_rates` tr 
LEFT JOIN `zones_to_geo_zones` gz ON  tr.tax_zone_id = gz.geo_zone_id 
WHERE (gz.zone_country_id IS null OR gz.zone_country_id = 0 OR gz.zone_country_id = 84)
AND (gz.zone_id IS null OR gz.zone_id = 0 OR gz.zone_id = 0)

[angeloio]

Yes ! That works too !

I just wanted to be as....analytic as I could ...

Can the members of the development team verify that is indeed a typo and not some hidden logic we fail to comprehend ?

Many thanks BlindSide for looking into this

[angeloio]

Hmmm...another one....

Just before the problematic (?) SELECT, there is another strange thing...

SUM(tr.tax_rate) AS tax_rate 

I have no idea why it sums the tax rate, especially since this returned column (tax_rate) is not used anywhere below

Any ideas to close this are most welcomed....

John

[BlindSide]

You're welcome! I have passed along your thread to the team to make sure they see it, in order to confirm it's a bug.

On your second one, I'd wager a guess that a whoever did that was copy/pasting code from another section, and simply left it alone. In a project of this size, reusing code in this fashion is dogma. Why run the risk of introducing a problem when you borrow something that you know already works?

[DrByte]

Are you still seeing this on v1.3.9?

[wilt]

Also, could you share some more details of your exact tax_rate setup.

From the details you posted you have multiple tax rates, even though you should need only one ?