[On Sale Products] Mysql error when no variable products are displayed, since WC 6.0

[wuspum]

Describe the bug

On Sale Products & Best Selling Products blocks throw Mysql error since Woocommerce 6.0 when no variable products are present.

[20-Dec-2021 07:21:16 UTC] Chyba databáze WordPressu: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND meta_key LIKE 'attribute\\_%'' at line 1. Konkrétní dotaz: SELECT post_id as variation_id, meta_key as attribute_key, meta_value as attribute_value FROM wp_postmeta WHERE post_id IN () AND meta_key LIKE 'attribute\\_%'. Příslušná funkce: require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/storefront/template-fullwidth.php'), get_template_part, locate_template, load_template, require('/themes/storefront/content-page.php'), do_action('storefront_page'), WP_Hook->do_action, WP_Hook->apply_filters, storefront_page_content, the_content, apply_filters('the_content'), WP_Hook->apply_filters, do_blocks, render_block, WP_Block->render, Automattic\WooCommerce\Blocks\BlockTypes\AbstractBlock->render_callback, Automattic\WooCommerce\Blocks\BlockTypes\AbstractProductGrid->render, Automattic\WooCommerce\Blocks\BlockTypes\AbstractProductGrid->get_products, Automattic\WooCommerce\Blocks\BlockTypes\AbstractProductGrid->prime_product_variations.

To reproduce

Steps to reproduce the behavior:

On an empty store

1. Add 2 simple products on sale and publish them
2. Go to a page and add the On Sale Products block.
3. Go to the front-end and notice the warning below

Expected behavior

There should be no error and the block should just display the products

Screenshots

If applicable, add screenshots to help explain your problem.

Environment

WordPress (please complete the following information):

• WooCommerce version: 6.0
• WooCommerce Blocks version: 6.6
• Site language:
• Any other plugins installed: Query monitor

[vg-robin]

Yep, either this needs opening or #31402 re-opening. There's clearly a need to check that prime_product_variations() is actually checking for product variations...

[ralucaStan]

Hello @wuspum,

Thank you for reaching out to us with your request!

From what I was able to gather, you are having issues with the On Sale Products block, after you have switched to WC 6.0. Is that correct?

I tried using the block with WC Blocks 6.6.0 & WC 6.0 and I didn't receive any error.

Would you mind providing some testing steps.
It would be great if you could share your current System Status Report. You can get it by navigating to the WooCommerce / Status section of your site. Once there, click on the Get system report button and then copy it by clicking on the Copy for support button. Then paste it here in a comment.

[wuspum]

Hey @ralucaStan ,

Like reported in connected issue from woocommerce repo (woocommerce/woocommerce#31402) it happens when none of the products in block are variable type and $product_variations is assigned and empty array, causing error in SQL clause. IN() without values.

[ralucaStan]

The fix is now part of the new release. Please update to WooCommerce Blocks 6.7.0.

[wuspum]

The fix is now part of the new release. Please update to WooCommerce Blocks 6.7.0.

How do I update this inside woocommerce? (Not using the separate plugin)

[ralucaStan]

@wuspum You will need to wait until the next version of WC appears. The fix will be included there as well.