[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

[Toms1999]

Before you file an issue

• Make sure you specify the "read" dialect eg. parse_one(sql, read="spark")
• Make sure you specify the "write" dialect eg. ast.sql(dialect="duckdb")
• Check if the issue still exists on main

Fully reproducible code snippet Please include a fully reproducible code snippet or the input sql, dialect, and expected output.

Official Documentation Please include links to official SQL documentation related to your issue.

[Toms1999]

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';

[Toms1999]

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'

[Toms1999]

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);
   
 

[Toms1999]

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;

[Toms1999]

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;

[Toms1999]

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;