MySQL to KingbaseES: Verify SQL Functions by Running Them, Not by Memory
Nulls, Dates, Strings: Run Business SQL Functions Once First
When writing business SQL, select * quickly stops being enough. List fields need display names, amounts need discounts subtracted, statuses need to become Chinese text, times need formatting, and empty strings in remarks should be treated as no remark. By the time data reaches the API, many fields are not returned as-is but are processed through an expression layer.
After migrating from MySQL to KingbaseES, this area cannot rely on memory alone. coalesce, case when, to_char, round are not complicated by themselves, but once mixed with NULL, empty strings, type conversions, and MySQL-habit functions, the results do not always follow old experience. For example, the three-argument concat did not run directly in the current environment, and date_format did not match a function either. These are not conceptual questions; run them in the terminal and the answer becomes clear.
This time I am not organizing a complete function reference, only picking the categories most likely to appear in business SQL: nulls, conditional expressions, strings, date-time, numerics, type conversion, and separately testing MySQL-habit syntax.
First confirm the environment
The current connection is still app_db, the user is app_user, and the default schema is app_schema. The version returns KingbaseES V009R001C010. Function behavior depends on the compatibility mode and version, so fixing the environment first gives the later results something to discuss.
This kind of small experiment is not suitable for just writing select coalesce(null, 'x'). That proves the function runs but is too far from business fields. Below I prepare a test table that deliberately stores several common cases: NULL values, empty strings, leading/trailing spaces, unpaid time, and amounts with discounts.
Prepare a test table
The table is named t_expr_demo, with few fields but covering the expression types used later: name, nickname, mobile, city, order status, amount, discount, creation time, payment time, and remark.
There are a few cases to remember. bob has no nickname, cindy has no mobile or remark, and the 4th row has no username, city, amount, or payment time, but has nickname NoName. remark also varies: row 2 is an empty string, row 3 is NULL, and rows 1 and 4 have leading and trailing spaces.
These look like small issues, but they appear often when writing APIs. Whether to merge empty string and NULL handling, whether amount NULL can directly participate in calculations, and what to display when payment time is empty, all need to be specified in SQL.
COALESCE first solves "which value to take"
Display names usually do not take only one field. If nickname has a value, use nickname; if no nickname, use username; if neither, give a fallback. coalesce fits this scenario:
coalesce(nick_name, user_name, 'unknown') as display_name
Amount calculation is similar. amount - discount looks simple, but when discount is NULL, direct subtraction yields NULL. Here both amount and discount get fallbacks:
coalesce(amount, 0) - coalesce(discount, 0) as real_amount
In the result, alice's display name takes Alice A, bob has no nickname, so falls back to bob, and row 4 has no username but has a nickname, so displays NoName. For amounts, bob's discount is empty, but the actual payment still computes to 399.80; row 4 has an empty amount, and after treating it as 0 the actual payment is 0.00.
nullif handles another type of problem: a field is not NULL but its value no longer has business meaning. Row 2's remark is an empty string; if displayed directly, it is not NULL, but it is also not suitable as a normal remark. nullif(remark, '') converts the empty string to NULL, then coalesce uniformly falls back to no remark.
One practical detail: nullif does not turn strings with spaces into NULL. Row 4's remark original value is need check with leading/trailing spaces, left intentionally when creating the data. To handle spaces, first trim; to handle empty strings, then use nullif.
Null handling should not be skipped. When the application layer sees empty string, NULL, and unknown, it usually branches differently. Without rules defined in the database, interface fields become sometimes blank, sometimes NULL, sometimes fallback text. Here I unify empty strings and NULL for remark first, making the display field much more stable later.
CASE WHEN for status and tiers
If order status is returned directly as paid, created, closed, the API can still work, but pages usually need business text. case when does this lightweight conversion in SQL:
case order_status
when 'paid' then '已支付'
when 'created' then '待支付'
when 'closed' then '已关闭'
else '未知'
end as status_text
The same SQL can also tier by amount. Handle NULL amount first, then judge high, medium, and low amounts.
The result is straightforward: paid becomes 已支付, created becomes 待支付, closed becomes 已关闭. In the amount tiers, 399.80 goes to high, 199.00 to medium, 88.66 to low, and row 4 with NULL amount returns no amount.
This writing is suitable for small display fields, not for stuffing complex business dictionaries into SQL. When statuses are many, text changes often, and multi-language is involved, go back to a dictionary table or the application layer. Here I only verify a common scenario: temporarily adding a readable status in query results.
Three-argument concat did not run directly
String concatenation is the easiest place for MySQL users to have preconceptions. Following MySQL habits, one might write:
concat(coalesce(user_name, 'unknown'), '-', coalesce(city, 'unknown')) as user_city
In the current KingbaseES environment, this SQL did not pass. The error is function sys.concat(varchar, unknown, varchar) does not exist, with a hint that no matching function and argument types were found.
This result is more valuable than simply saying "a function works or not." The problem is not that strings cannot be concatenated, but that the three-argument concat form did not match a function in this environment. When migrating SQL, if an old project heavily uses concat(a, '-', b), you cannot assume it will carry over directly.
There is no need to blow this up into "function incompatibility." First stick to the concrete fact: in this test, three-argument concat did not run; later I use || for string concatenation.
This small error also reminds that same-name functions do not equal same parameter signatures. When migrating SQL, many problems are not that keywords are completely absent, but that argument counts, argument types, and implicit conversion rules do not match. Seeing hints like unknown, do not rush to change business logic; first confirm the function signature and literal types.
String concatenation switch to ||
After switching to ||, the string-handling SQL runs fine:
coalesce(user_name, 'unknown') || '-' || coalesce(city, 'unknown') as user_city
In the same SQL I also verify several common string functions: upper, lower, substring, length, trim.
alice becomes alice-beijing, bob becomes bob-shanghai, and row 4 with both username and city empty produces unknown-unknown. upper(user_name) uppercases the username, lower(nick_name) lowercases the nickname, and the first three digits of the mobile are extracted with substring(mobile from 1 for 3).
Row 3 has no mobile, so mobile_prefix is empty, and length(coalesce(mobile, '')) returns 0. trim(remark) removes leading and trailing spaces from rows 1 and 4, but does not turn NULL into a default. In string processing, concatenation, substring, trimming, and null fallback usually need to be used together.
For mobile number masking, this is not enough. Here I only verify extracting the first three digits and length calculation. Real masking would need concatenating middle asterisks, keeping the last four digits, and first handling empty or insufficient-length mobile numbers. Functions running is one layer; becoming a reliable display field depends on handling abnormal values.
Date-time uses to_char and interval first
Date-time is another area prone to MySQL habits. Currently I use the already-verified KingbaseES syntax: to_char for formatting, interval for date arithmetic, and subtracting two timestamps to get a duration.
Row 1's creation time is 2026-06-01 09:00:00, payment time is 2026-06-01 09:05:30, and paid_at - created_at returns 5 minutes 30 seconds. Row 3 returns 25 minutes. Rows 2 and 4 have no payment time, so the payment duration is empty.
created_at + interval '1 day' is also clear: one day added, time part unchanged. The next query uses date(created_at) to get the date and extract(hour from created_at) to get the hour. Row 1's hour is 9, row 2 is 10, row 3 is 11, and row 4 is 12.
Timezone is not expanded here. The current table fields are timestamp; the test goal is formatting, date arithmetic, and extracting values. Timezone, daylight saving, and cross-region display need separate design, not mixed into this small experiment.
One habit should be fixed early: if only for page display, convert to string; if continuing to sort, compare, or filter by range later, keep the time type and do not format too early. to_char suits the final display layer, not a replacement for all time calculations.
In numeric expressions, NULL continues to affect results
First look at the incomplete-fallback amount calculation:
amount - coalesce(discount, 0) as raw_real_amount
When discount is empty, using 0 as fallback works, so bob's actual payment is 399.80. For row 4, the amount itself is empty; even with discount fallback, amount - 0 is still NULL.
The results of round, floor, and ceil are visible. 399.80 rounded to one decimal is 399.8, floor is 399, ceil is 400. 88.66 floor is 88, ceil is 89.
The fully-fallback calculation below uses coalesce for both amount and discount:
coalesce(amount, 0) - coalesce(discount, 0) as safe_real_amount
This makes row 4's actual payment 0.00. Whether to write this depends on business semantics. NULL amount might mean not entered, or genuinely no amount. SQL can provide a default, but whether the default is reasonable cannot be decided by functions alone.
Type conversion should let errors surface
Here I verify two conversion syntaxes: standard cast(...) and the :: form.
cast(amount as varchar(20)) converts amount to string, cast('2026-06-10 08:30:00' as timestamp) converts a valid string to time, and '123.45'::numeric(10, 2) converts to numeric.
Then converting 'not-a-number' to numeric makes KingbaseES error directly: invalid input syntax for type numeric. This error is normal and should be kept. During migration or data cleanup, implicit conversion is the most dangerous when it silently swallows dirty values; an explicit error at least tells you which data cannot be treated as numbers.
When writing business SQL, do not guess type conversion. When mixing string dates, character numbers, and amount fields, write the conversion explicitly. Separate convertible values from non-convertible ones, and save a lot of time later in troubleshooting.
Especially when importing historical data, fields that look numeric may contain spaces, Chinese characters, hyphens, or other placeholders. Direct cast will error, forcing you to clean data first; if you swallow exceptions in the application layer, locating the problem during reconciliation or statistics becomes much harder.
date_format also did not run directly
The common MySQL date_format also did not pass directly in the current environment:
date_format(created_at, '%Y-%m-%d %H:%i:%s')
The error is function date_format(timestamp without time zone, unknown) does not exist.
This is similar to the earlier concat: you cannot judge from memory. The current measured result is that this MySQL-style syntax did not match a function. The verified replacement is:
to_char(created_at, 'YYYY-MM-DD HH24:MI:SS')
Also note the format string difference. In MySQL, %Y-%m-%d %H:%i:%s is common; to_char uses YYYY-MM-DD HH24:MI:SS. Both express year-month-day hour:minute:second, but the format templates are not the same set.
Finally combine into API fields
After individual functions run, it remains to see if they combine into a realistic query. The final SQL puts together display name, city-status, status text, actual payment amount, creation date, and payment duration:
display_name still uses coalesce(nick_name, user_name, 'unknown'). Row 4 has nickname NoName, so display name is NoName. city_status uses || to concatenate city and status; row 4 has empty city, so the result is unknown-paid.
Status text is generated by case when: paid is 已支付, created is 待支付, closed is 已关闭. Actual payment uses coalesce(amount, 0) - coalesce(discount, 0), giving alice 189.00, bob 399.80, cindy 80.00, and row 4 0.00.
Creation date is generated with to_char(created_at, 'YYYY-MM-DD'). Payment duration has a fallback: if there is a payment time, show the difference; if not, show 未支付. This SQL is close to common field processing in APIs.
Functions should be considered together with field meaning
Functions and expressions are not the more the better. The real thing to confirm is field meaning: which fields back up the display name, whether empty strings count as NULL, whether NULL amount can be treated as 0, what to display when payment time is empty, and whether date formats should be strings for the frontend or kept as timestamp.
In this round of testing, coalesce, nullif, case when, ||, upper/lower, substring, length, trim, to_char, interval, extract, round/floor/ceil, and cast all produced clear results. The MySQL-habit three-argument concat and date_format did not run directly in the current KingbaseES environment, and verified replacements exist: use || for string concatenation and to_char for time formatting.
When switching from MySQL, run business SQL functions group by group. First handle NULL, then confirm types, then decide which function to use. This way the SQL you write not only executes but also has clearer field semantics.