IF(boolean, 0, 1) Function parse ERROR

Describe the bug
parse IF function error when first param(boolean) include logical operation
OK: sum(if(column1 in('value1', 'value2'), 1, 0)) as tcp_logs
ERROR: sum(if(column1 in ('value1', 'value2') and column2 = 'value3', 1, 0))

To Reproduce
Steps to reproduce the behavior:

Example SQL
select
sum(if(column1 in('value1', 'value2'), 1, 0)) as tcp_logs,
sum(if(column1 in ('value1', 'value2') and column2 = 'value3', 1, 0)) as base_tcp_logs
from
table1
where
recv_time >= toDateTime('2021-07-20 00:00:00')
and recv_time < toDateTime('2021-07-21 00:00:00')
Parsing this SQL using JSqlParser with this statements
CCJSqlParserUtil.parse(sql)
Exception
net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "(" "("

System

JSqlParser version 4.1

Thanks!

Greetings,
thank you for the qualified report.

Unfortunately I was not able to reproduce the issue as it parses just fine for me. You can try online and interactively.

Are you using the expression within a more complex statement maybe, with more nesting/brackets?

Nevermind, I can reproduce it, but only with the latest 4.2-Snapshot:

    @Test
    public void testIssue1284() throws JSQLParserException {
      assertSqlCanBeParsedAndDeparsed(
          "SELECT  Sum( If( column1 IN ( 'value1', 'value2' ), 1, 0 ) ) AS tcp_logs\n"
              + "        , Sum( If( column1 IN ( 'value1', 'value2' )\n"
              + "            AND column2 = 'value3', 1, 0 ) ) AS base_tcp_logs\n" + "FROM table1\n"
              + "WHERE recv_time >= Todatetime( '2021-07-20 00:00:00' )\n"
              + "    AND recv_time < Todatetime( '2021-07-21 00:00:00' )\n");
    }

I do suspect, that the XOR expression introduced in 4.1 contributes to that problem.
As a bridge-gap work around you could use the in-official Pre 4.1 snapshot from https://github.com/manticore-projects/jsqlformatter/releases/download/0.1.6/jsqlparser-4.1-SNAPSHOT.jar which is used for the JSQLFormatter.

I will bisect the issue over the weekend and hopefully solve it.

Further findings:

This happens only with an IN expression as argument of a boolean expression
Surrounding the IN expression with extra brackets will also work around:

@Test
    public void testIssue1284() throws JSQLParserException {
      // succeeds
      assertSqlCanBeParsedAndDeparsed(
          "SELECT  Sum( if( (column1 IN ( 'value1', 'value2' )) AND column2 = 'value3', 1, 0 ) ) ", true);
      
      // fails
      assertSqlCanBeParsedAndDeparsed(
          "SELECT  Sum( if( column1 IN ( 'value1', 'value2' ) AND column2 = 'value3', 1, 0 ) ) ", true);
    }

Maybe you want to use the extra brackets temporarily until we have fixed that problem thoroughly.
Thank you again for reporting, it is a good and very interesting bug report.

Sorry for being verbose: the failure is about detecting the Sum() as as Column instead as a Function. Left side shows the success, right side show the fails:

Further simplification:

assertExpressionCanBeParsedAndDeparsed(
          "if( test1 IN ( 'value1', 'value2' ) AND column2 = 'value3', 1, 0 ) ", true);

The IN Expression prevents from detection a Function production. Technically the LOOKAHEAD(Function()) fails and so we move on to the Column() Production.

@wumpz

@wumpz I need some help here, this thing is above my pay grade.

this works exist(1) AND 2=2 and will be parsed into Function with aComplexExpressionList of an AndExpression with a SQLCondition based on the ExistsExpression
this does not work a in (1) AND 2=2 although it is literally the same construct Grammar wise. But somehow the InExpression is not triggered for the SQLCondition and this lets the test for Function fail and we move wrongly to the Column instead

For the testing purpose I have tried already to reduce the InExpression to a minimum functionality like <K_IN> "(" foo=SimpleExpressionList(true) ")" but still no success.

I simply can't understand why SQLCondition is triggered correctly for the ExistsExpression but fails for the InExpression.

        assertExpressionCanBeParsedAndDeparsed(
          "exist(1) AND 2=2", true);
        
        assertExpressionCanBeParsedAndDeparsed(
          "a in (1,2) AND 2=2", true);

Ok, a good night sleep and 2 mugs of coffee later I actually DO understand what is going on:

there is a Production NameExpressionList which is defined as SimpleExpression() <K_IN> SimpleExpression()
NamedExpressionList is tested prior ComplexExpressionList and so when it catches we never reach the ComplexExpressionList

Now, the Problem is, that your particular example MEANS a ComplexExpression but looks to the parser like a NameExpressionList:

expr1=SimpleExpression()
	(tk2=<K_FROM>|tk2=<K_IN>|tk2=<K_PLACING>)
	{
		names.add("");
		expressions.add(expr1);
		names.add(tk2.image);
	}
    (
        expr2=SimpleExpression() { expressions.add(expr2);}
		(
			(tk3=<K_FOR>|tk3=<K_FROM>)
			expr3=SimpleExpression() {names.add(tk3.image); expressions.add(expr3);}
			(
				(tk4=<K_FOR>)
				expr4=SimpleExpression() {names.add(tk4.image); expressions.add(expr4);}
			)?
		)?
    )

Your example triggers only because of the IN expression.
Now, that I understand the root cause we may find a solution eventually.

Separate MySQL Special String Functions accepting Named Argument Sepa…

I think I solved it.

Separate MySQL Special String Functions accepting Named Argument Sepa…

Navigation Menu

Repository navigation

IF(boolean, 0, 1) Function parse ERROR #1284

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

IF(boolean, 0, 1) Function parse ERROR #1284

Description

Activity

manticore-projects commented on Jul 23, 2021

manticore-projects commented on Jul 23, 2021

manticore-projects commented on Jul 23, 2021

manticore-projects commented on Jul 23, 2021

manticore-projects commented on Jul 23, 2021

manticore-projects commented on Jul 23, 2021

manticore-projects commented on Jul 24, 2021

manticore-projects commented on Jul 24, 2021

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions