跪拜 Guibai
← All articles
Database

MySQL to KingbaseES: Verify SQL Functions by Running Them, Not by Memory

By 一只牛博 ·
Read original on juejin.cn ↗ Google Translate ↗ Alt translation

Teams migrating from MySQL to KingbaseES will hit silent breakage in everyday SQL if they assume function compatibility. Concrete failures like concat and date_format show that same-name functions differ in signatures and format templates, so testing in the target environment before rollout saves debugging time and avoids shipping wrong display values.

Summary

When business SQL moves from MySQL to KingbaseES, function behavior cannot be trusted from memory. A developer ran a systematic experiment on KingbaseES V009R001C010 using a test table with NULLs, empty strings, spaces, and missing timestamps to see how common expressions behave. The results: coalesce, nullif, case when, ||, upper/lower, substring, length, trim, to_char, interval, extract, round/floor/ceil, and cast all work as expected, but MySQL's three-argument concat and date_format do not match any function in the current environment.

The failures are not conceptual; they are signature and type mismatches. The error messages show no matching function for concat(varchar, unknown, varchar) and date_format(timestamp without time zone, unknown). The practical replacements are || for string concatenation and to_char with the 'YYYY-MM-DD HH24:MI:SS' format for date formatting. The article also emphasizes handling NULL carefully: coalesce around both amount and discount prevents NULL propagation, and nullif(remark, '') converts empty strings to NULL before a coalesce fallback.

The final combined query shows how these pieces fit into API-facing fields: display name, city-status, status text, actual amount, creation date, and payment duration. The takeaway is that migration teams should run function tests in the actual environment, confirm function signatures and type conversion rules, and treat SQL expressions as part of field semantics rather than isolated syntax.

Takeaways
— KingbaseES V009R001C010 does not support MySQL's three-argument concat(varchar, unknown, varchar); use || for string concatenation instead.
— date_format is not available in the tested KingbaseES environment; use to_char(created_at, 'YYYY-MM-DD HH24:MI:SS') with the different format template.
— coalesce(amount, 0) - coalesce(discount, 0) prevents NULL from propagating in amount calculations, but a NULL amount with a discount fallback still yields NULL unless both sides are coalesced.
— nullif(remark, '') converts an empty string to NULL, but does not trim spaces; trim first before nullif if spaces should also be treated as empty.
— Type conversion with cast or :: throws an explicit error for invalid input like 'not-a-number', which is preferable to silently swallowing dirty data.
— The final combined SQL uses coalesce for display names, case when for status text, to_char for dates, and interval plus timestamp subtraction for payment duration.
— When migrating SQL, run function tests in the target environment because same-name functions can have different argument counts and implicit conversion rules.
Conclusions

The error message 'function sys.concat(varchar, unknown, varchar) does not exist' is more informative than a generic incompatibility claim; it pinpoints the signature mismatch that migration teams will face repeatedly.

The pattern of testing with realistic data rows that include NULL, empty strings, and leading/trailing spaces is what makes the results reliable; a simple select coalesce(null, 'x') would miss the business-critical edge cases.

Choosing to let invalid type conversions error out in the database is a deliberate design decision that protects data quality, since application-layer swallowing of exceptions only delays failures to reporting time.

The author's rule that formatting should happen at the final display layer, not earlier, is a sensible guideline that preserves the ability to sort and filter on timestamps.

Concepts & terms
coalesce
A function that returns the first non-NULL argument among its inputs. Used for fallback values, e.g., coalesce(nick_name, user_name, 'unknown') tries nickname, then username, then the literal 'unknown'.
nullif
A function that returns NULL when its two arguments are equal, otherwise returns the first argument. Common usage: nullif(remark, '') turns an empty string into NULL so it can be treated uniformly with other missing values.
to_char
A date/time formatting function in PostgreSQL-style databases that converts a timestamp or interval to a string using format patterns like 'YYYY-MM-DD HH24:MI:SS'. In KingbaseES it replaces MySQL's date_format.
|| (string concatenation operator)
The standard SQL operator for concatenating two strings. In KingbaseES it serves as the replacement for MySQL's multi-argument concat() function, which is not available with the same signature.
Source: juejin.cn ↗ Google Translate ↗ Backup ↗