MySQL Bugs: #13678: Query that work in 5.0.11 does not work in 5.0.12 and 5.0.13

Bug #13678	Query that work in 5.0.11 does not work in 5.0.12 and 5.0.13
Submitted:	1 Oct 2005 6:28	Modified:	7 Oct 2005 9:26
Reporter:	Conrad Winchester	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.12	OS:	Linux (Redhat 9)
Assigned to:		CPU Architecture:	Any

Description:
I have a webshop on my server running x-cart shopping cart software.

the home page is at www.prongjewellery.com

With all version of mySQL 4.0-5.0.11 the front page shows correctly but with 5.0.12 and 5.0.13 I get the following error message.

INVALID SQL: 1054 : Unknown column 'xcart_products.productid' in 'on clause'
SQL QUERY FAILURE: SELECT COUNT(xcart_products.productid), MIN(xcart_pricing.price) as price , xcart_products_lng.product as product_lng, xcart_products_lng.descr as descr_lng, xcart_products_lng.full_descr as fulldescr_lng, IF(xcart_variants.variantid IS NOT NULL,'Y','') as is_variant, IF(xcart_classes.classid IS NOT NULL,'Y','') as is_product_options, v_pricing.price as v_price FROM xcart_products, xcart_pricing , xcart_featured_products, xcart_products_categories, xcart_categories LEFT JOIN xcart_products_lng ON xcart_products_lng.productid = xcart_products.productid AND xcart_products_lng.code = 'US' LEFT JOIN xcart_classes ON xcart_classes.productid = xcart_products.productid LEFT JOIN xcart_variants ON xcart_variants.productid = xcart_products.productid LEFT JOIN xcart_pricing as v_pricing ON v_pricing.variantid = xcart_variants.variantid AND v_pricing.quantity = 1 AND v_pricing.membership IN ('','') WHERE xcart_products.productid=xcart_featured_products.productid AND xcart_featured_products.avail='Y' AND xcart_featured_products.categoryid='0' AND xcart_pricing.productid=xcart_products.productid AND xcart_pricing.quantity=1 AND xcart_pricing.membership IN ('','') AND (xcart_pricing.variantid = 0 OR (xcart_variants.variantid = xcart_pricing.variantid)) AND xcart_products_categories.productid=xcart_products.productid AND xcart_products_categories.categoryid = xcart_categories.categoryid AND xcart_categories.membership IN ('','') AND xcart_products.forsale='Y' GROUP BY xcart_products.productid 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /raid/sites/www.prongjewellery.com/www/shop/include/func.php on line 102

It is a massively complex query but it used to work and now does not.

How to repeat:
1) do make install of 5.0.12 or 5.0.13

2) restart mysqld

3) Front page does not show correctly

4) do make instal of 5.0.11

5) restart mysqld

6) Front page shows correctly

Suggested fix:
Reall haven't got a clue

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

It is yet another example of problems that appeared in many applications since 5.0.12, because of JOIN behaviour changed to conform to the SQL:2003 standard. Look at the http://bugs.mysql.com/bug.php?id=12964, for example.

Please, read the 5.0.12 change log carefully (http://dev.mysql.com/doc/mysql/en/news-5-0-12.html):

"Note: Natural joins and joins with USING, including outer join variants, now are processed according to the SQL:2003 standard. The changes include elimination of redundant output columns specified in USING clauses and proper ordering of output columns. (Bug #4789, Bug #6136, Bug #6276, Bug #6489, Bug #6495, Bug #6558, Bug #9978, Bug #10646, Bug #10972, Bug #11710)

This change may necessitate rewriting of certain queries."

and the following examples.

Sorry, I need to look at it more carefully.

I am sure the problem is with changed JOIN behaviour of MySQL as of 5.0.12, but my first guess of the reason was probably wrong. 

To be able to understand the real reason and, may be, to propose a workaround (in case you can change the queries), I need more information. Please, send the structure of all those xcart_* tables referenced in your query. SHOW CREATE TABLE for each of them will be enough.

Looking at this part of SELECT: 

... LEFT JOIN xcart_classes ON xcart_classes.productid = xcart_products.productid...

and the error message:

INVALID SQL: 1054 : Unknown column 'xcart_products.productid' in 'on clause'

I will mark it as a duplicate of newer simply repeatable bug:

http://bugs.mysql.com/bug.php?id=13832

In fact, this is a duplicate of BUG#13551

I have a webshop on my server running x-cart shopping cart software.

the home page is at www.indusbooks.com

Note: All file is running local server good but website is show Error

INVALID SQL: 1054 : Unknown column 'xcart_products.productid' in 'on clause'
SQL QUERY FAILURE: SELECT COUNT(xcart_products.productid), MIN(xcart_pricing.price) as price , xcart_products_lng.product as product_lng, xcart_products_lng.descr as descr_lng, xcart_products_lng.full_descr as fulldescr_lng, IF(xcart_variants.variantid IS NOT NULL,'Y','') as is_variant, IF(xcart_classes.classid IS NOT NULL,'Y','') as is_product_options, v_pricing.price as v_price FROM xcart_products, xcart_pricing , xcart_featured_products, xcart_products_categories, xcart_categories LEFT JOIN xcart_products_lng ON xcart_products_lng.productid = xcart_products.productid AND xcart_products_lng.code = 'US' LEFT JOIN xcart_classes ON xcart_classes.productid = xcart_products.productid LEFT JOIN xcart_variants ON xcart_variants.productid = xcart_products.productid LEFT JOIN xcart_pricing as v_pricing ON v_pricing.variantid = xcart_variants.variantid AND v_pricing.quantity = 1 AND v_pricing.membership IN ('','') WHERE xcart_products.productid=xcart_featured_products.productid AND xcart_featured_products.avail='Y' AND xcart_featured_products.categoryid='0' AND xcart_pricing.productid=xcart_products.productid AND xcart_pricing.quantity=1 AND xcart_pricing.membership IN ('','') AND (xcart_pricing.variantid = 0 OR (xcart_variants.variantid = xcart_pricing.variantid)) AND xcart_products_categories.productid=xcart_products.productid AND xcart_products_categories.categoryid = xcart_categories.categoryid AND xcart_categories.membership IN ('','') AND xcart_products.forsale='Y' GROUP BY xcart_products.productid 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\Inetpub\vhosts\indusbooks.com\httpdocs\include\func.php on line 94
INVALID SQL: 1054 : Unknown column 'xcart_products.productid' in 'on clause'
SQL QUERY FAILURE: SELECT xcart_products.productid, xcart_products.product, xcart_products.productcode, xcart_products.avail, MIN(xcart_pricing.price) AS price, IF (xcart_classes.classid IS NULL,'','Y') as is_product_options, IF(xcart_variants.variantid IS NULL,'','Y') as is_variant FROM xcart_products, xcart_categories, xcart_products_categories, xcart_pricing LEFT JOIN xcart_classes ON xcart_classes.productid = xcart_products.productid LEFT JOIN xcart_variants ON xcart_variants.productid = xcart_products.productid WHERE xcart_products.productid=xcart_products_categories.productid AND xcart_products_categories.categoryid=xcart_categories.categoryid AND xcart_products.productid=xcart_pricing.productid AND xcart_pricing.quantity=1 AND (xcart_pricing.membership='' OR xcart_pricing.membership='') AND (xcart_categories.membership='' OR xcart_categories.membership='') AND xcart_products.forsale='Y' AND (xcart_pricing.variantid = 0 OR (xcart_variants.variantid = xcart_pricing.variantid)) AND xcart_products.sales_stats>0 GROUP BY xcart_products.productid ORDER BY xcart_products.sales_stats DESC, xcart_products.views_stats DESC LIMIT 12