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