Error use case when in statement

[Mvpanswer7]

My sql String:
select count(distinct case when split(vir_name,"\\/")[0] in ("G-Ware","RiskWare","Tool","PornWare","Trojan") then apk_name else null end) as black_apk_n from dm_user_apk where split(vir_name,"\\/")[0] in ("G-Ware","RiskWare","Tool","PornWare","Trojan") and time>='20171001' and time <='20180930' Error:
net.sf.jsqlparser.JSQLParserException at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:55) at xmatrix.dsl.ScriptSQLExecListener.replaceTableName(ScriptSQLExec.scala:154) at xmatrix.dsl.SelectAdaptor.parse(SelectAdaptor.scala:55) at xmatrix.dsl.ScriptSQLExecListener.exitSql(ScriptSQLExec.scala:188) at xmatrix.dsl.parser.DSLSQLParser$SqlContext.exitRule(DSLSQLParser.java:240) at org.antlr.v4.runtime.tree.ParseTreeWalker.exitRule(ParseTreeWalker.java:47) at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:30) at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:28) at xmatrix.dsl.ScriptSQLExec$.parse(ScriptSQLExec.scala:70) at xmatrix.rest.services.QueryService$$anonfun$3$$anonfun$apply$1.apply(QueryService.scala:111) at xmatrix.rest.services.QueryService$$anonfun$3$$anonfun$apply$1.apply(QueryService.scala:99) at xmatrix.core.job.SparkJobManager$.run(SparkJobManager.scala:59) at xmatrix.rest.services.QueryService$$anonfun$3.apply(QueryService.scala:99) at xmatrix.rest.services.QueryService$$anonfun$3.apply(QueryService.scala:99) at scala.concurrent.impl.Future$PromiseCompletingRunnable.liftedTree1$1(Future.scala:24) at scala.concurrent.impl.Future$PromiseCompletingRunnable.run(Future.scala:24) at scala.concurrent.impl.ExecutionContextImpl$AdaptedForkJoinTask.exec(ExecutionContextImpl.scala:121) at scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260) at scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339) at scala.concurrent.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979) at scala.concurrent.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107) Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "(" "(" at line 1, column 13.

[Mvpanswer7]

After checked, I think problem is I try to access to array element in sql like this split(vir_name,"\\/")[0] which is the correct way to manage that in JSqlParser?

[wumpz]

Unfortunately at the moment you have no change. Due to "historical" reasons JSqlParser supports quotation using brackets ([column]). This is old SqlServer and MSAccess Style.

There is a discussion to drop this in favor of array handling.

677

But nobody seems to be interested in sharing a though there :(.

[Mvpanswer7]

I think we shoud remove it and support arrays!~

[Mvpanswer7]

Now I am trying to make my own JSqlParser jar to solve this

[matozoid]

@wumpz - in JavaParser (https://github.com/javaparser/javaparser) we make the grammar as flexible as possible, then fix the AST by postprocessing in code. Like: var is the name of a type, except in Java 9+ (or so) where it is a special type. So when the library is configured for 9+, we look through the AST for Types called var and replace it with a VarType.

That's in case you want to support both syntaxes, of course.

[wumpz]

@matozoid As I understand this construct in javaparser it replaces the node type but leaves the children how they are. The problem I see is that there must be a complete different handling here. While now there is a production like

alias -> "[" objname "]" where objname is a simple string literal

it has to be

array -> "[" expression "]" where expression is something complex

additionally in fact the brackets are part of JSqlParser identifier construct and should be removed there and put into a different rule.

I was digging a lot around this problem. And my way to go at the moment to support both ways is going for semantic lookaheads to block the one or the other. But that would be a heavy change.

[matozoid]

I guess it's time to decide if you want to support the most used subset of all SQL dialects (and get an endless stream of issues about features you purposely don't implement) or if you want to support as many dialects as possible :-)

[wumpz]

This class is generated. You have to use Maven to build. Cheers Tobias -------- Ursprüngliche Nachricht --------Von: SJC notifications@github.com Datum: 18.10.18 10:45 (GMT+01:00) An: JSQLParser/JSqlParser JSqlParser@noreply.github.com Cc: Tobias t.warneke@gmx.net, Mention mention@noreply.github.com Betreff: Re: [JSQLParser/JSqlParser] Error use case when in statement (#698) @wumpz It seems your git code miss some Class like CCJSqlParser and some?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread. {"api_version":"1.0","publisher":{"api_key":"05dde50f1d1a384dd78767c55493e4bb","name":"GitHub"},"entity":{"external_key":"github/JSQLParser/JSqlParser","title":"JSQLParser/JSqlParser","subtitle":"GitHub repository","main_image_url":"https://assets-cdn.github.com/images/email/message_cards/header.png","avatar_image_url":"https://assets-cdn.github.com/images/email/message_cards/avatar.png","action":{"name":"Open in GitHub","url":"https://github.com/JSQLParser/JSqlParser"}},"updates":{"snippets":[{"icon":"PERSON","message":"@Mvpanswer7 in #698: @wumpz It seems your git code miss some Class like CCJSqlParser and some?"}],"action":{"name":"View Issue","url":"https://github.com/JSQLParser/JSqlParser/issues/698#issuecomment-430926895"}}} [ { "@context": "http://schema.org", "@type": "EmailMessage", "potentialAction": { "@type": "ViewAction", "target": "https://github.com/JSQLParser/JSqlParser/issues/698#issuecomment-430926895", "url": "https://github.com/JSQLParser/JSqlParser/issues/698#issuecomment-430926895", "name": "View Issue" }, "description": "View this Issue on GitHub", "publisher": { "@type": "Organization", "name": "GitHub", "url": "https://github.com" } }, { "@type": "MessageCard", "@context": "http://schema.org/extensions", "hideOriginalBody": "false", "originator": "AF6C5A86-E920-430C-9C59-A73278B5EFEB", "title": "Re: [JSQLParser/JSqlParser] Error use case when in statement (#698)", "sections": [ { "text": "", "activityTitle": "SJC", "activityImage": "https://assets-cdn.github.com/images/email/message_cards/avatar.png", "activitySubtitle": "@Mvpanswer7", "facts": [

] } ], "potentialAction": [ { "name": "Add a comment", "@type": "ActionCard", "inputs": [ { "isMultiLine": true, "@type": "TextInput", "id": "IssueComment", "isRequired": false } ], "actions": [ { "name": "Comment", "@type": "HttpPOST", "target": "https://api.github.com", "body": "{\n\"commandName\": \"IssueComment\",\n\"repositoryFullName\": \"JSQLParser/JSqlParser\",\n\"issueId\": 698,\n\"IssueComment\": \"{{IssueComment.value}}\"\n}" } ] }, { "name": "Close issue", "@type": "HttpPOST", "target": "https://api.github.com", "body": "{\n\"commandName\": \"IssueClose\",\n\"repositoryFullName\": \"JSQLParser/JSqlParser\",\n\"issueId\": 698\n}" }, { "targets": [ { "os": "default", "uri": "https://github.com/JSQLParser/JSqlParser/issues/698#issuecomment-430926895" } ], "@type": "OpenUri", "name": "View on GitHub" }, { "name": "Unsubscribe", "@type": "HttpPOST", "target": "https://api.github.com", "body": "{\n\"commandName\": \"MuteNotification\",\n\"threadId\": 395045365\n}" } ], "themeColor": "26292E" } ]

[Mvpanswer7]

I wrote an udf function to walk around this issue

[wumpz]

JSqlParser 4.0 parses this.