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:

1. 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')
2. Parsing this SQL using JSqlParser with this statements
   CCJSqlParserUtil.parse(sql)
3. Exception
   net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "(" "("

System

• JSqlParser version 4.1

Thanks!

[manticore-projects]

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?

[manticore-projects]

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.

[manticore-projects]

Further findings:

1. This happens only with an IN expression as argument of a boolean expression
2. 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.

[manticore-projects]

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:

[manticore-projects]

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.

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:

Thank you for your patience and detailed guidance.
I know little about technology implementation, not deep enough, but Lucky to know more.

Thank you very much!

[manticore-projects]

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

1. 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

2. 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);

[manticore-projects]

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

1. there is a Production NameExpressionList which is defined as SimpleExpression() <K_IN> SimpleExpression()

2. 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.

[manticore-projects]

I think I solved it.