postgres unicode escapes in string literals cause a SQL parse error in sqlpage

[lozdown]

I have a simple Postgresql database application for managing attendance records of club members at events, with table for members, table for events and a many-to-many join table linking events and members.

This query works correctly in pgadmin4.

with 
attendances as (
 select distinct
 	em."members_Contact ID" contactid,
 	(bool_or((CASE WHEN e.eventdate ='2024-01-17' THEN true END))) "2024-01-17"
 from events_members em inner join events e on em.events_id=e.id 
 group by em."members_Contact ID"
)
select m."First Name",m."Last Name",
	case when "2024-01-17"  then U&'\2713'  else 'x' end as "2024-01-17" 

	from members m left join attendances a on m."Contact ID"=a.contactid ;

Giving example result table
"F" "A" "✓"
"A" "A" "✓"
"C" "A" "x"
"J" "F" "x"

(nb "✓" indicates attended, "x" indicates absent)

However the same query used in SQLPage throws an error associated with the escaped unicode string U&'\2173'

Error in query number 1:

Failed to execute SQL statement:
WITH attendances AS (SELECT DISTINCT em."members_Contact ID" AS contactid, (bool_or((CASE WHEN e.eventdate = '2024-01-17' THEN true END))) AS "2024-01-17" FROM events_members AS em JOIN events AS e ON em.events_id = e.id GROUP BY em."members_Contact ID") SELECT m."First Name", m."Last Name", CASE WHEN "2024-01-17" THEN U & '\2713' ELSE 'x' END AS "2024-01-17" FROM members AS m LEFT JOIN attendances AS a ON m."Contact ID" = a.contactid;
⬆️
line 1, character 322

Backtrace

error returned from database: column "u" does not exist

column "u" does not exist

The SQLPage definition is

select
'table' as 'component', true as 'small',
TRUE as search
;

with
attendances as (
select distinct
em."members_Contact ID" contactid,
(bool_or((CASE WHEN e.eventdate ='2024-01-17' THEN true END))) "2024-01-17"
from events_members em inner join events e on em.events_id=e.id
group by em."members_Contact ID"
)
select m."First Name",m."Last Name",
case when "2024-01-17" then &'\2713' else 'x' end as "2024-01-17"

from members m left join attendances a on m."Contact ID"=a.contactid
;

If I change the U&'2713' to '2713' the query executes (although clearly does not display tick mark that I want)

[lovasoa]

This looks like a bug in our sql parser! I'll report it upstream and look into fixing it.

In the meantime, maybe you can simply include the Unicode character directly in your query? SQLPage parses all your files as utf8.

[lovasoa]

I created an issue to track the problem in this repo: #512

[lovasoa]

I opened a pull request upstream with a fix: apache/datafusion-sqlparser-rs#1355

[lovasoa]

The fix has now been merged, and is present in sqlpage v0.28, to be released soon.