Postgres查询在客户端正常但在Rails ActiveRecord报错排查

问题详情 #

我有一段Postgres查询在Postgres客户端中运行正常，但在Rails中执行时出现语法错误。

查询代码 #

sql_query = <<-SQL.squish
  SELECT *
       , total_price - taxes - shipping - total_discount AS net_sales
  FROM (SELECT created_at
             , COALESCE(total_orders, 0)        AS total_orders
             , COALESCE(total_price, 0)         AS total_price
             , COALESCE(taxes, 0)               AS taxes
             , COALESCE(shipping, 0)            AS shipping
             , COALESCE(average_order_value, 0) AS average_order_value
             , COALESCE(total_discount, 0)      AS total_discount
        FROM generate_series(timestamp '2022-07-20'
                 , timestamp '2022-07-26'
                 , interval '1 day') AS g(created_at)
                 LEFT JOIN ( -- ③
            SELECT created_at::date
                 , count(*)            AS total_orders
                 , sum(total_price)    AS total_price
                 , sum(taxes)          AS taxes
                 , sum(shipping)       AS shipping
                 , avg(total_price)    AS average_order_value
                 , sum(total_discount) AS total_discount
            FROM orders
            WHERE shop_id = 43
              AND active                    
              AND created_at >= '2022-07-20'
              AND created_at < '2022-07-27'
            GROUP BY 1) o USING (created_at)
       ) sub
  ORDER BY created_at DESC;
SQL

ActiveRecord::Base.connection.execute(sql_query).values

报错信息 #

PG::SyntaxError: ERROR: syntax error at end of input LINE 1: ...P BY 1) o USING (created_at) ) sub ORDER BY created_at DESC; ^

问题原因 #

问题出在.squish方法的使用：

• .squish会将所有多余空白（包括换行）压缩为单个空格，把整个SQL合并成一行；
• 代码中的-- ③是Postgres单行注释，原本只会注释到该行末尾，但.squish去掉换行后，注释后面的子查询代码被并入同一行，导致这部分代码被注释掉，SQL结构缺失，引发语法错误。

解决办法 #

任选以下一种方式修复：

• 替换注释格式：把单行注释-- ③改为多行注释/* ③ */，多行注释不受换行影响，.squish压缩后不会破坏SQL结构；
• 替换字符串处理方法：用.strip替代.squish，.strip仅移除首尾空白，保留内部换行和必要空格，确保单行注释只作用于原行；
• 删除注释：如果注释无保留必要，直接删除-- ③即可。

内容的提问来源于stack exchange，提问作者WagnerMatosUK