manticore-projects/jsqlformatter

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

Closed this issue · 5 comments

csonuryilmaz commented

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)
manticore-projects commented
csonuryilmaz commented

Thanks for the quick workaround. 👍 Formatter works fine with dual patch.

2021-05-17_16-20

After your feedback I remembered dual from Oracle, but I didn't ever used it in MySQL because it allows select without dual. Oracle doesn't allow statements like that when compared to MySQL.

.. I will fix queries without a FROM clause.

It will be good for MySQL developers. 🙏

manticore-projects commented

It will be good for MySQL developers. pray

We have a Big Heart for all the MySQL developers in the world (especially when also H2 supports SELECT without a FROM). Oracle DUAL Pseudo-table should have died a long time ago.

It has been solved and you can test it here interactively

Maybe you also would like to test the new Import Java Code Fragment and Export to Java String/StringBuilder/MessageFormat? Although it is accessible only in our DemoUI as I have no idea yet, how to bring this to Netbeans.

For now, just right click into the Editor in order to get the PopUp Menu. At the bottom of the PopUp Menu, you see the Paste-From- and the Copy-To-Actions.

manticore-projects commented

(Updated Netbeans Plugin](https://github.com/manticore-projects/jsqlformatter/releases/download/0.1.7/externalcodeformatter-1.14.1-SNAPSHOT.nbm)

Screencast on the Import/Export Java String Fragment:

screencast_JSQLFormatterUI_JavaExport.mp4
csonuryilmaz commented

Thanks, I've tested with latest plugin and it just works. 🎉

.. Although it is accessible only in our DemoUI as I have no idea yet, how to bring this to Netbeans.

That's a great feature and seems working good. 🤗

I've been already using a Netbeans plugin for that purpose: Java Text Copy Paste Module

Note: At the time of writing, Netbeans plugin web site was unreachable. 😞 I hope that was a temporary situation.

2021-05-23_21-57

It's originally Netbeans 8 plugin. But although being unmaintained, it works with latest Netbeans releases. For instance, mine is Netbeans 12.3. A maintained and modern plugin will be better for that feature and will be preferred by many developers.

Maybe you can reach its developer and take a look at source code to achieve your feature. here