PolarDB/PolarDBX SQL: Error 4501 After AVG(x) Change

by Alex Johnson 53 views

Have you ever encountered an unexpected error after making a seemingly minor change to your SQL query in PolarDB or PolarDBX? Specifically, error 4501 can pop up when you modify an aggregate function like AVG(x) to AVG(x) + 5. This article delves into the details of this issue, providing a clear explanation, steps to reproduce the error, and version information to help you understand the context. Let's explore why this happens and how you can address it.

Understanding the Issue

The core problem lies in how the optimizer handles the modified aggregate function. While AVG(alb59.c14) works perfectly fine, adding a constant value to it, like in AVG(alb59.c14) + 5, triggers an optimization error. The error message ERROR 4501 (HY000): [1ac921099a400000][172.17.0.7:8527][test]ERR-CODE: [PXC-4501][ERR_OPTIMIZER] optimize error by java.lang.IllegalArgumentException indicates that the optimizer, specifically the Java component, is encountering an IllegalArgumentException. This usually means that the query transformation or execution plan generation is failing due to an unexpected input or condition.

Steps to Reproduce the Error

To better understand the problem, let's walk through the steps to reproduce this error. This will give you a hands-on experience and help you grasp the issue more effectively.

1. Set up the Environment

First, we need to create a database and a table with some sample data. The following SQL script will set up the necessary environment:

DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
USE test;

CREATE TABLE t2 (
 c1 INT NOT NULL AUTO_INCREMENT,
 c2 INT NOT NULL,
 c3 DECIMAL(10,2) NOT NULL,
 c4 VARCHAR(50) NOT NULL,
 c5 DATE NOT NULL,
 c6 MEDIUMTEXT NULL,
 c7 LONGTEXT NULL,
 c8 MEDIUMBLOB NULL,
 c9 LONGBLOB NULL,
 c10 ENUM('value1','value2','value3') NULL,
 c11 SET('a','b','c','d') NULL,
 c12 BIT(8) NULL,
 c13 DATETIME NULL,
 c14 FLOAT(8,2) NULL,
 c15 DOUBLE(12,4) NULL,
 PRIMARY KEY (c1)
);

INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (994, 3423, 181.05, 'sample_wfxp8dzD7XrFnPLzr', '2024-12-16', 'sample_pVtuVRHgHyCTAdMjTX5uDmgDFTTcRtYK4MA3VzWGhxyRAthAan095ltHnWC2wZqFzmNIz2BhrrY2h0fxo8UzijtqT3Qzyc7ydCZ760AbwzyWxKRhxPNIiXBNmglw9ylwoKoF0Q5QMAuWKKHLT07TyALEjZEq9SXDPPHZkG637npBmt2TKOxONpcXFVVrsW3jgaSAVhmwZMYON8YAyhzkhs0fsDZAohNdcrXeb43HI5de5EWnnY2lj8xyjRyfvWyVnBrhfeRiVnwUHuk0bcwoN3ueyVTRnXwQSF56ZbxUsw4hUmzUQ4GGRNJqlHOzrdMFLZHnsp5SamGZ0WPFJC1Vx1RuAnB5RdThXSIL8o2Fgebw5VIlETIWBvsuSQqrlbUxl0DbKa4xwcIiP23T8cVxdUgU4xKNvx6nab1kO47gdMcGoSGyXyvr23yKaVpjtw6XNe0uwE6zNt4nqZ1IMHxo', 'sample_oIxWhZ5PNoCnBUDNNG1t4uGQ80Jm3dOMyFRe0xsG8bqvWYFDuMqCy715ukwGqx4YD0tNg95vNkY49I1JPAbh5BBrcbTCYpkH1R37hDwaEo0Lzl91rc0tCeXUzOy258wzk7DX3Z11LNDgWU4awzLAAqwgg6l6QUVNdWbDUN3MQYXgivI8FcfivR8MVM40OIkt9fO8BNRyM2wTSSdov5M69ahQHUactc9lQyhXQfKFssG6OK2qKe2LIxK7FT1nHxPTNh7d41fB2bHcrjQ51tWpKJcLUFVVansOZC4fsvYKAamSAEMg3CsdHRNxw9dNCn5504tI75YCiAZT9DJEv7eTX7R07OJUaKE95P4VCs4XzftyMlVR4sPXkCsh7k7MYYBRd5d9iJdQLLOKXV3FUX5qjMRwF0oltoDkNGhVzUwlRuOptO8ROwzJLwCo5Zc7ce9l3bVoWQ3LsFymuRfVPQNPDkec3GZtAddEZBmIvzojWCpi0vhTzdYojxEdQwMHomRvrrYI7XWkrLrOOYwgMnPfHTeyO9oBFhEksiqIvbJHAEP2xCD2J0NvGcap1lZc2ueOUe6pJbZ95HmiFyRwwxMOxfLY4VCD5vgXNZTzeP0q8G0caUPPmLnaPcrw0vcnvR1gJnPdl8YyggslnNMOFmhPU2TK8EO5Xn6QVpvb6zPaM3Bw6sK3u10cw3i3jPuAYGSdMFNIdrU6L0zMUiI1LO4T7us2MUaXM', X'4DD6A6D687C6A9C08CDA93E9B48429DAA8ECBF93E891B67DDEBCDEBCE79C8A20', X'CF8CCBA2C880ED9DA9CF8644ECA5BD53E6B0AAED898E10C398D0AD', 'value1', 'a,c,b,d', b'01101010', '2025-04-25 18:28:30', 90.91, 53.96);

CREATE INDEX idx_t2_c10 ON t2 (c10);

This script does the following:

  • Drops the database test if it exists.
  • Creates the database test.
  • Uses the database test.
  • Creates a table t2 with various data types.
  • Inserts a sample row into t2.
  • Creates an index idx_t2_c10 on the c10 column.

2. Run the Original Query

Now, let's run the original query that uses the AVG() function without modification:

WITH cte_993 AS (
 SELECT
  RANK() OVER (PARTITION BY alb59.c11 ORDER BY alb59.c5 DESC) AS col_1,
  alb59.c14 AS col_2,
  alb59.c14 AS col_3,
  AVG(alb59.c14) AS col_4
 FROM
  t2 AS alb59 USE INDEX (idx_t2_c10)
 GROUP BY
  alb59.c11,
  alb59.c5,
  alb59.c14
)
SELECT
 cte.col_3 AS col_3,
 'sample_76' AS col_1,
 cte.col_1 AS col_1_1
FROM
 cte_993 AS cte;

This query should execute successfully and return a result set, demonstrating that the basic aggregation works as expected.

3. Modify the Query and Observe the Error

Now, modify the query by adding + 5 to the AVG() function:

WITH cte_993 AS (
 SELECT
  RANK() OVER (PARTITION BY alb59.c11 ORDER BY alb59.c5 DESC) AS col_1,
  alb59.c14 AS col_2,
  alb59.c14 AS col_3,
  AVG(alb59.c14) + 5 AS col_4
 FROM
  t2 AS alb59 USE INDEX (idx_t2_c10)
 GROUP BY
  alb59.c11,
  alb59.c5,
  alb59.c14
)
SELECT
 cte.col_3 AS col_3,
 'sample_76' AS col_1,
 cte.col_1 AS col_1_1
FROM
 cte_993 AS cte;

Upon executing this modified query, you should encounter the infamous error 4501. This confirms that the addition of a constant to the AVG() function within this specific context triggers the optimizer error.

Version Information

The issue was observed in the following PolarDB version:

mysql> select polardb_version();
+---------+--------------+---------------------+
| TYPE    | VERSION      | RELEASE_DATE        |
+---------+--------------+---------------------+
| Product | PolarDB V2.0 | Distributed Edition |
| CN      | 2.4.0.5.4.19 | SNAPSHOT            |
| DN      | 2.4.0.8.4.19 | 20240430            |
| GMS     | 2.4.0.8.4.19 | 20240430            |
+---------+--------------+---------------------+
4 rows in set (0.12 sec)

mysql> select @@version;
+-------------------------+
| @@version               |
+-------------------------+
| 8.0.32-X-Cluster-8.4.19 |
+-------------------------+
1 row in set (0.01 sec)

This information is crucial for anyone trying to identify if they are running a version of PolarDB that is affected by this issue. Knowing the version helps in tracking down specific bugs and fixes.

Possible Causes and Workarounds

While the exact root cause may require deeper investigation by the PolarDB team, here are some potential reasons and workarounds:

  1. Optimizer Bug: It's possible that the optimizer has a bug in handling arithmetic operations on aggregate functions within Common Table Expressions (CTEs) or specific query structures. This is a common scenario for database systems, as query optimizers are complex pieces of software.
  2. Data Type Handling: The issue might be related to how data types are handled when a constant is added to the result of AVG(). Implicit type conversions or precision issues could be at play.
  3. Query Transformation Rules: The optimizer applies various transformation rules to rewrite the query for better performance. A specific transformation rule might be causing the IllegalArgumentException in this case.

Here are a few workarounds you can try:

  • Calculate Outside CTE: Instead of adding the constant within the CTE, perform the addition in the outer query. For example:

    WITH cte_993 AS (
 SELECT
  RANK() OVER (PARTITION BY alb59.c11 ORDER BY alb59.c5 DESC) AS col_1,
  alb59.c14 AS col_2,
  alb59.c14 AS col_3,
  AVG(alb59.c14) AS col_4
 FROM
  t2 AS alb59 USE INDEX (idx_t2_c10)
 GROUP BY
  alb59.c11,
  alb59.c5,
  alb59.c14
)
SELECT
 cte.col_3 AS col_3,
 'sample_76' AS col_1,
 cte.col_1 AS col_1_1,
 cte.col_4 + 5 AS col_5 -- Addition outside CTE
FROM
 cte_993 AS cte;

  • Use Subquery: Another approach is to use a subquery instead of a CTE for the aggregation part:

    SELECT
 col_3,
 'sample_76' AS col_1,
 col_1_1
FROM
 (
  SELECT
   RANK() OVER (PARTITION BY alb59.c11 ORDER BY alb59.c5 DESC) AS col_1,
   alb59.c14 AS col_2,
   alb59.c14 AS col_3,
   AVG(alb59.c14) + 5 AS col_4
  FROM
   t2 AS alb59 USE INDEX (idx_t2_c10)
  GROUP BY
   alb59.c11,
   alb59.c5,
   alb59.c14
 ) AS subquery;

  • Report the Issue: The best course of action is to report this issue to the PolarDB support team. They can investigate the root cause and provide a proper fix in a future release.

Conclusion

Encountering errors like 4501 can be frustrating, but understanding the context and having a systematic approach to troubleshooting can help you navigate these challenges. In this article, we've explored a specific scenario in PolarDB/PolarDBX where adding a constant to the AVG() function results in an optimization error. By reproducing the error, examining the version information, and discussing potential causes and workarounds, we aim to equip you with the knowledge to handle similar situations effectively.

Remember, database systems are constantly evolving, and issues like these are part of the journey. Staying informed, sharing your findings with the community, and working with database vendors are key to improving the overall experience. For further reading on database optimization and troubleshooting, you might find valuable resources on websites like Percona's Database Performance Blog.