Giter Club home page Giter Club logo

jsqlformatter's People

Contributors

manticore-projects avatar

Stargazers

avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar

Watchers

avatar avatar

Forkers

lybigdata sinlang marlboro029

jsqlformatter's Issues

missing "limit" keyword after format

I tried to format below SQL query.

2021-05-15_13-15

Formatter beautified the query without any error or exception.

2021-05-15_13-16

But formatted SQL didn't contain LIMIT 200000 part of the string.

In order to reproduce case easily, I'm pasting raw SQL query as a text below:

SELECT p.* 
FROM product p 
LEFT JOIN product_description pd ON (p.product_id = pd.product_id) 
LEFT JOIN product_type_google_category ptgc ON (p.product_type_id = ptgc.product_type_id) 
LEFT JOIN product_google_custom_label pgcl ON (p.product_id = pgcl.product_id) 
WHERE p.status=1 AND pd.language_id = 2 
AND p.product_id IN (SELECT product_id FROM cj_googleshopping_products) 
 ORDER BY date_available DESC, p.purchased DESC LIMIT 200000

Note: Forgot to mention that I'm using MySQL and MySQL supports the LIMIT clause to select a limited number of records. See here for other keywords used by various databases to provide the same functionality.

group_concat section in select remains unformatted

Hi, below is a working unformatted MySQL query written in two lines:

SELECT GROUP_CONCAT(DISTINCT TRIM(Concat(m.title, ' ', m.firstname, ' ', m.lastname)) ORDER BY p2m.manufacturer_type_id ASC SEPARATOR ' ') as manufacturer_name
FROM product_to_manufacturer p2m LEFT JOIN manufacturer m ON m.manufacturer_id = p2m.manufacturer_id WHERE p2m.product_id = 574768;

When I format the query, group_concat section in select remains unformatted with all its keywords:

select GROUP_CONCAT(DISTINCT TRIM(Concat(m.title, ' ', m.firstname, ' ', m.lastname)) ORDER BY p2m.manufacturer_type_id ASC SEPARATOR ' ') as manufacturer_name
from product_to_manufacturer p2m
  left join manufacturer m
    on m.manufacturer_id = p2m.manufacturer_id
where p2m.product_id = 574768
;

Here is my formatter settings:
2021-05-15_21-55

Here is an example formatted query from an online formatter service: 😉
2021-05-15_22-01

ParseException: Encountered unexpected token:<EOF>

When I add (create) a new .sql file in Netbeans,

2021-05-15_13-08

.. it makes a file with default comment:

2021-05-15_13-08_1

IMHO it also triggers auto formatting on new file which generates below exception:

  • net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: at line 9, column 3

Full exception here 👉 parseException.txt

How to be compatible with sqls containing keywords

It's actually not a problem but a question.
I notice that on the Online DEMO, the following sql is parsable.

SELECT operator.c1 AS c1, apply.c2 as c2 from t_test1 operator left join t_test2 apply on operator.c3=apply.c3

But when I try it on original JSQLParser, the upper SQL is not parsable, complaining about syntax error.
I would like to ask if you have ever done some optimization on JSQLParser.
Thank you.

BadLocationException: Wrong (offset+length)

Hi, I've installed .nbm from here. 0.1.6 I've added a .sql file with a query:

select * from customer c where date_added >= '2021-04-26 00:00' order by date_added desc limit 3;

2021-05-01_17-03

When I try to format, I'm getting "BadLocationException: Wrong (offset+length)" exception without formatting query.

Screenshot_2021-05-01_17-06-20

Product Version: Apache NetBeans IDE 12.3
Java: 11.0.11; Java HotSpot(TM) 64-Bit Server VM 11.0.11+9-LTS-194
Runtime: Java(TM) SE Runtime Environment 11.0.11+9-LTS-194
System: Linux version 4.19.0-0.bpo.9-amd64 running on amd64; UTF-8; en_US (nb)

"Encountered unexpected token" when SQL has comment

Hi,

select customer_id from customer c where c.email='[email protected]'; --7313088

Query is formatted but plugin throws net.sf.jsqlparser.parser.ParseException exception because of comment section and splits it from SQL query.

select customer_id
from customer c
where c.email = '[email protected]'
;

-- failed to format start
 --7313088
-- failed to format end

Exception:

net.sf.jsqlparser.parser.ParseException: Encountered unexpected token:<EOF>
    at line 1, column 10.

Was expecting one of:

    "("
    "CALL"
    "COMMENT"
    "COMMIT"
    "DECLARE"
    "DELETE"
    "DESCRIBE"
    "DROP"
    "EXEC"
    "EXECUTE"
    "EXPLAIN"
    "GRANT"
    "INSERT"
    "MERGE"
    "SET"
    "SHOW"
    "TRUNCATE"
    "UPDATE"
    "UPSERT"
    "USE"
    "VALUES"
    "WITH"
    <K_SELECT>

	at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:28839)
	at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:28678)
	at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:269)
	at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:81)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:181)
Caused: net.sf.jsqlparser.JSQLParserException: Encountered unexpected token:<EOF>
    at line 1, column 10.

Was expecting one of:

    "("
    "CALL"
    "COMMENT"
    "COMMIT"
    "DECLARE"
    "DELETE"
    "DESCRIBE"
    "DROP"
    "EXEC"
    "EXECUTE"
    "EXPLAIN"
    "GRANT"
    "INSERT"
    "MERGE"
    "SET"
    "SHOW"
    "TRUNCATE"
    "UPDATE"
    "UPSERT"
    "USE"
    "VALUES"
    "WITH"
    <K_SELECT>

	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:183)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:60)
[catch] at com.manticore.jsqlformatter.JSQLFormatter.format(JSQLFormatter.java:966)
	at de.funfried.netbeans.plugins.external.formatter.sql.jsqlformatter.JSQLFormatterWrapper.format(JSQLFormatterWrapper.java:48)
	at de.funfried.netbeans.plugins.external.formatter.sql.jsqlformatter.JSQLFormatterJob.format(JSQLFormatterJob.java:66)
	at de.funfried.netbeans.plugins.external.formatter.sql.jsqlformatter.JSQLFormatterService.format(JSQLFormatterService.java:64)
	at de.funfried.netbeans.plugins.external.formatter.FormatterServiceDelegate.format(FormatterServiceDelegate.java:94)
	at de.funfried.netbeans.plugins.external.formatter.ui.editor.ExternalFormatterReformatTaskFactory$2.reformat(ExternalFormatterReformatTaskFactory.java:93)
	at org.netbeans.modules.editor.indent.TaskHandler$MimeItem.runTask(TaskHandler.java:550)
	at org.netbeans.modules.editor.indent.TaskHandler.runTasks(TaskHandler.java:309)
	at org.netbeans.modules.editor.indent.IndentImpl.reformat(IndentImpl.java:349)
	at org.netbeans.modules.editor.indent.api.Reformat.reformat(Reformat.java:129)
	at org.netbeans.editor.ActionFactory$FormatAction$1$1.run(ActionFactory.java:1854)
	at org.netbeans.editor.GuardedDocument.runAtomicAsUser(GuardedDocument.java:333)
	at org.netbeans.editor.ActionFactory$FormatAction$1.run(ActionFactory.java:1833)
	at org.netbeans.modules.progress.ui.RunOffEDTImpl$1.run(RunOffEDTImpl.java:146)
	at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:1418)
	at org.netbeans.modules.openide.util.GlobalLookup.execute(GlobalLookup.java:45)
	at org.openide.util.lookup.Lookups.executeWith(Lookups.java:278)
	at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:2033)

IMHO, ending comment section may be ignored and left as is after formatting.

I think there is a general problem with SQL comments. When I reformat file, (for example, adding more new SQL queries and format) every formatting action causes failed to format start section to duplicate like this;

select customer_id
from customer c
where c.email = '[email protected]'
;

-- failed to format start


-- failed to format start


-- failed to format start


-- failed to format start


-- failed to format start


-- 7313088
-- failed to format end
-- failed to format end
-- failed to format end
-- failed to format end
-- failed to format end

Comment sections should be ignored without touching them.

NullPointerException with a "select ... case when" query

Hi, below is a working MySQL query from my workspace:

-- failed to format start
select case when (
select ccp.campaign_id
from campaign_constraint_product ccp
  inner join campaign_free_shipping_products_visibility cfspv
    on cfspv.campaign_id = ccp.campaign_id
where ccp.product_id = 530729
  AND cfspv.status = 1
UNION
select cap.campaign_id
from campaign_action_product cap
  inner join campaign_free_shipping_products_visibility cfspv
    on cfspv.campaign_id = cap.campaign_id
where cap.product_id = 530729
  AND cfspv.status = 1
) is not null then 1 else 0 end as is_free_shipping
;
-- failed to format end

When I try to format query, JSQLFormatter throws NullPointerException from appendFromItem() function.

Exception details here:

java.lang.NullPointerException
	at com.manticore.jsqlformatter.JSQLFormatter.appendFromItem(JSQLFormatter.java:2653)
	at com.manticore.jsqlformatter.JSQLFormatter.appendSelectBody(JSQLFormatter.java:1669)
	at com.manticore.jsqlformatter.JSQLFormatter.appendSelect(JSQLFormatter.java:1609)
[catch] at com.manticore.jsqlformatter.JSQLFormatter.format(JSQLFormatter.java:956)
	at de.funfried.netbeans.plugins.external.formatter.sql.jsqlformatter.JSQLFormatterWrapper.format(JSQLFormatterWrapper.java:45)
	at de.funfried.netbeans.plugins.external.formatter.sql.jsqlformatter.JSQLFormatterJob.format(JSQLFormatterJob.java:79)
	at de.funfried.netbeans.plugins.external.formatter.sql.jsqlformatter.JSQLFormatterService.format(JSQLFormatterService.java:60)
	at de.funfried.netbeans.plugins.external.formatter.FormatterServiceDelegate.format(FormatterServiceDelegate.java:94)
	at de.funfried.netbeans.plugins.external.formatter.ui.editor.ExternalFormatterReformatTaskFactory$2.reformat(ExternalFormatterReformatTaskFactory.java:95)
	at org.netbeans.modules.editor.indent.TaskHandler$MimeItem.runTask(TaskHandler.java:550)
	at org.netbeans.modules.editor.indent.TaskHandler.runTasks(TaskHandler.java:309)
	at org.netbeans.modules.editor.indent.IndentImpl.reformat(IndentImpl.java:349)
	at org.netbeans.modules.editor.indent.api.Reformat.reformat(Reformat.java:129)
	at org.netbeans.editor.ActionFactory$FormatAction$1$1.run(ActionFactory.java:1854)
	at org.netbeans.editor.GuardedDocument.runAtomicAsUser(GuardedDocument.java:333)
	at org.netbeans.editor.ActionFactory$FormatAction$1.run(ActionFactory.java:1833)
	at org.netbeans.modules.progress.ui.RunOffEDTImpl$1.run(RunOffEDTImpl.java:146)
	at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:1418)
	at org.netbeans.modules.openide.util.GlobalLookup.execute(GlobalLookup.java:45)
	at org.openide.util.lookup.Lookups.executeWith(Lookups.java:278)
	at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:2033)

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

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
;

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.