manticore-projects/jsqlformatter

formatter doesn't lower some keywords (in, distinct, union, all, and)

Closed this issue · 5 comments

csonuryilmaz commented

Hi, some keywords aren't being lowered although "options > external formatter > spelling > keywords" is LOWER selected.

Here are examples to reproduce the case.

Example, raw query-1:

SELECT  ccp.product_id, COUNT( DISTINCT ccp.campaign_id )
FROM campaign_constraint_product ccp
LEFT JOIN campaign c ON c.campaign_id = ccp.campaign_id AND c.status = 1
WHERE product_id IN (414732,530729)
GROUP BY ccp.product_id ORDER BY 2 DESC
LIMIT 10;

Formatted:

select  ccp.product_id
        , Count( DISTINCT ccp.campaign_id )
from campaign_constraint_product ccp
  left join campaign c
    on c.campaign_id = ccp.campaign_id
      AND c.status = 1
where product_id IN ( 414732, 530729 )
group by ccp.product_id
order by 2 desc
limit 10
;

Example, raw query-2:

SELECT campaign_id FROM campaign_constraint_product ccp WHERE ccp.product_id = 580696
UNION ALL
SELECT campaign_id FROM campaign_action_product cap WHERE cap.product_id =580696;

Formatted:

select campaign_id
from campaign_constraint_product ccp
where ccp.product_id = 580696
UNION ALL
select campaign_id
from campaign_action_product cap
where cap.product_id = 580696
;
manticore-projects commented
csonuryilmaz commented

👍 Thanks, I will follow your updates.

Currently I'm using formatter in job hours for real business cases while working. So it's under beta testing now. 😊

csonuryilmaz commented

0.1.7 brings some keyword fixes to this issue. 👍

I have two examples which may require additional work to cover for keywords: distinct, null, interval

update sms_queue sq
set sq.gateway_sms_id = NULL
    , sq.date_sent_sms = NULL
    , sq.sms_status_id = 1
    , sq.date_added = Now() - INTERVAL 1 minute
    , sq.date_should_sent = Now() - INTERVAL 1 minute
where sq.sms_id = 2864158
;
select  ccp.product_id
        , Count( DISTINCT ccp.campaign_id )
from campaign_constraint_product ccp
  left join campaign c
    on c.campaign_id = ccp.campaign_id
      and c.status = 1
where product_id in ( 414732, 530729 )
group by ccp.product_id
order by 2 desc
limit 10
;
manticore-projects commented

Thank you so much, I will look into that. I have been heavily invested in working on JSQLParser, which is the foundation of the Formatter. You can expected much improved parsing speed, especially when using nested expression soon.

At the moment, a lot of Expressions like Cast, Interval, Between are only "passed through" because it won't affect the layout of the formatted SQL. But of course it affects the Spelling and the (Ansi) Syntax Highlighting, so I need die hard and complete this part and address ALL Expressions.

  • 👍1