Illegal mix of collations for operation

[Nuranto]

Preconditions and environment

• Magento version 2.4.8-p1
• PHP 8.3

Steps to reproduce

1. Add a special character (multybyte) in postcode field
2. go to checkout to trigger a shipping estimatation

Expected result

No error

Actual result

SQLSTATE[HY000]: General error: 1271 Illegal mix of collations for operation 'in', query was:

SELECT main_table.tax_calculation_rate_id, main_table.tax_calculation_rule_id, main_table.customer_tax_class_id, main_table.product_tax_class_id, rule.priority, rule.position, rule.calculate_subtotal, rate.rate AS value, rate.tax_country_id, rate.tax_region_id, rate.tax_postcode, rate.tax_calculation_rate_id, rate.code, IF(title_table.value IS NULL, rate.code, title_table.value) AS title FROM tax_calculation AS main_table
 INNER JOIN tax_calculation_rule AS rule ON rule.tax_calculation_rule_id = main_table.tax_calculation_rule_id
 INNER JOIN tax_calculation_rate AS rate ON rate.tax_calculation_rate_id = main_table.tax_calculation_rate_id
 LEFT JOIN tax_calculation_rate_title AS title_table ON rate.tax_calculation_rate_id = title_table.tax_calculation_rate_id AND title_table.store_id = '1' WHERE (customer_tax_class_id = 3) AND (product_tax_class_id IN ('2')) AND (rate.tax_country_id = 'FR') AND (rate.tax_region_id IN(0, 0)) AND (rate.zip_is_range IS NULL) AND (rate.tax_postcode IS NULL OR rate.tax_postcode IN('*', '', '⠀', '⠀*', '�*', '�*')) ORDER BY priority ASC, tax_calculation_rule_id ASC, tax_country_id DESC, tax_region_id DESC, tax_postcode DESC, value DESC

Additional information

Solution is in src/vendor/magento/module-tax/Model/ResourceModel/Calculation.php::_createSearchPostCodeTemplates : replacing string methods by their mb_ equivalent solves the issue. I will create a PR asap.

Release note

No response

Triage and priority

• Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
• Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
• Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
• Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.

[m2-assistant]

Hi @Nuranto. Thank you for your report.
To speed up processing of this issue, make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce.

• For more details, review the Magento Contributor Assistant documentation.
• Add a comment to assign the issue: @magento I am working on this
• To learn more about issue processing workflow, refer to the Code Contributions.

---

Join Magento Community Engineering Slack and ask your questions in #github channel.
⚠️ According to the Magento Contribution requirements, all issues must go through the Community Contributions Triage process. Community Contributions Triage is a public meeting.
🕙 You can find the schedule on the Magento Community Calendar page.
📞 The triage of issues happens in the queue order. If you want to speed up the delivery of your contribution, join the Community Contributions Triage session to discuss the appropriate ticket.

[m2-assistant]

Hi @engcom-November. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: 👇

• 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).
• 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue.
• 3. Add Area: XXXXX label to the ticket, indicating the functional areas it may be related to.
• 4. Verify that the issue is reproducible on 2.4-develop branch
  Details

  - If the issue is reproducible on 2.4-develop branch, please, add the label Reproduced on 2.4.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!
• 5. Add label Issue: Confirmed once verification is complete.
• 6. Make sure that automatic system confirms that report has been added to the backlog.

[engcom-November]

Hello @Nuranto ,

Thank you for the report and your collaboration.

We attempted to reproduce the reported issue using our latest 2.4-develop build. However, the issue could not be reproduced. Please find the attached screenshot for your reference.

We followed the provided pre-conditions and steps to reproduce, but did not encounter the issue. Kindly let us know if we might have missed any specific detail or step during the reproduction process.

As a result, we are marking this issue as "Needs Update".

Thank you once again!

[Nuranto]

Yes, the data in my database was imported, not saved via a form. So I guess you should add the mb char in database directly (in active quote address) in order to reproduce.

Anyway, I think the pull request is quite self-sufficient, and could be merged as a code quality fix or something if we can't find a way to reproduce.
Actually, we could do that work (using multibyte - mb_ - string function instead of onebyte equivalent) in every other core modules. Nevertheless, it will require some attention since classical one-byte string functions still have some utility in some specific/rare cases.