MySQL to KingbaseES: Verify SQL Functions by Running Them, Not by Memory
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.
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.
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.