[bug] When converting SQL statements from PostgreSQL (pgsql) to MySQL, there might be issues due to differences between the two database systems that can cause the converted statements to fail when executed about sqlglot HOT 6 CLOSED

pgsql:
SELECT ods_day,
modified,
task_code,
transaction_code,
CAST(content AS JSON) ->> '$.eventType' AS eventType,
CAST(content AS JSON) ->> '$.unionCodeResults' AS unionCodeResults
FROM edw_f_zone.gugu_yf_transaction_detail_dd
WHERE transaction_label = 'unioncodelist'
AND CAST(ods_day AS DATE) = '20240505';

from sqlglot.

mysql:
SELECT ods_day, modified, task_code, transaction_code, CAST(content AS JSON) ->> '$."$.eventType"' AS eventType, CAST(content AS JSON) ->> '$."$.unionCodeResults"' AS unionCodeResults FROM edw_f_zone.gugu_yf_transaction_detail_dd WHERE transaction_label = 'unioncodelist' AND CAST(ods_day AS DATE) = '20240505'

from sqlglot.

more,pgsql:
SELECT
SUBSTR(CAST((CAST('2024-04-15' AS DATE) + INTERVAL '1 month ago') AS varchar), 1,7);

   mysql:
   SELECT
   SUBSTR(CAST((CAST('2024-04-15' AS DATE) + INTERVAL '1 month ago') AS varchar), 1,7);

from sqlglot.

pgsql:`select *
from (select 1 as obs,
substring((current_time - interval '16 min') ::text, 1, 2) as value
,
(substring(current_time::text, 1, 2)::integer) || '>点' as name
union all
select 2 as obs,
unnest(array['00', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24']) as value
,
unnest(array['0点','1点','2点','3点','4点','5点','6点','7点','8点','9点','10点','11点','12点','13点','14点','15点','16点','17点','18点','19点','20点','21点','22点','23点','24点']) as name) a

order by 1;`

mysql:SELECT * FROM (SELECT 1 AS obs, SUBSTRING(CAST((CURRENT_TIME() - INTERVAL '16' MIN) AS STRING), 1, 2) AS value, (CAST(SUBSTRING(CAST(CURRENT_TIME() AS STRING), 1, 2) AS INT)) || '>点' AS name UNION ALL SELECT 2 AS obs, EXPLODE(ARRAY('00', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24')) AS value, EXPLODE(ARRAY('0点', '1点', '2点', '3点', '4点', '5点', '6点', '7点', '8点', '9点', '10点', '11点', '12点', '13点', '14点', '15点', '16点', '17点', '18点', '19点', '20点', '21点', '22点', '23点', '24点')) AS name) AS a ORDER BY 1;

from sqlglot.

pgsql:
select t1.rc_month, t2.id::varchar||'-'::varchar||t2.code_value overdue_reason,count(distinct loan_id) cnt from sherlock.t_dwp_jxj_bp_collect_record_rucui_trans_base t1 left join sherlock.t_overdue_reason_code t2 on t1.overdue_reason::bigint = t2.id WHERE t1.overdue_reason:: bigint not in (0 , 1 , 2 , 27) and rc_month is not null and ods_day<=to_char(due_date +interval '1 months' , 'YYYYmmdd') group by 1, 2

mysql:
SELECT t1.rc_month, CAST(t2.id AS VARCHAR) || CAST('-' AS VARCHAR) || t2.code_value AS overdue_reason, COUNT(DISTINCT loan_id) AS cnt FROM sherlock.t_dwp_jxj_bp_collect_record_rucui_trans_base AS t1 LEFT JOIN sherlock.t_overdue_reason_code AS t2 ON CAST(t1.overdue_reason AS BIGINT) = t2.id WHERE NOT CAST(t1.overdue_reason AS BIGINT) IN (0, 1, 2, 27) AND NOT rc_month IS NULL AND ods_day <= DATE_FORMAT(due_date + INTERVAL '1' MONTHS, '%Ymmdd') GROUP BY 1, 2;

from sqlglot.

more,
postgres:
select before_first_due_date::date + ((installment_count::int - 1) || ' month')::interval as clear_date from edw_dws.dws_unionloan_zhanqi_loan_df;
mysql:
SELECT CAST(before_first_due_date AS DATE) + CAST(((CAST(installment_count AS INT) - 1) || ' month') AS INTERVAL) AS clear_date FROM edw_dws.dws_unionloan_zhanqi_loan_df;

from sqlglot.