support unicode literal strings prefixed with U&

[lovasoa]

Discussed in #511

^{Originally posted by lozdown July 27, 2024}
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]

Reported upstream in apache/datafusion-sqlparser-rs#1354

[lovasoa]

this is fixed.