[BUG] JSQLParser Version 4.9,5.3 : SQL Parse ERROR

[Yizhe1208]

Issue Title: Parser Error with function(if(xx,xx,xx)) in all versions 4.9-5.3

Description:
The parser fails to handle SQL statements containing aggregate functions with IF expressions (like sum(if()), max(if())). This occurs in all versions from 4.9 to 5.3.

Steps to Reproduce:

1. Try to parse any of these SQL statements:

SELECT sum(if(column1=2, column1, 0)) FROM tb_a
SELECT max(if(column1=2, column1, 0)) FROM tb_a


Expected Behavior:
The parser should successfully parse these valid SQL expressions containing IF functions within aggregate functions.

Actual Behavior:
Parser throws an exception:

Exception in thread "main" net.sf.jsqlparser.JSQLParserException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "(" "("
at line 1, column 13.

Was expecting one of:

<ST_SEMICOLON>

[manticore-projects]

Greetings, can't reproduce.

Your statements parse fine and you can test it online here.

SQL Text
 ├─Statements: statement.select.PlainSelect
 │  ├─selectItems: statement.select.SelectItem
 │  │  └─expression: expression.Function
 │  │     └─ExpressionList: If(column1 = 2, column1, 0)
 │  └─Table: tb_a
 └─Statements: statement.select.PlainSelect
    ├─selectItems: statement.select.SelectItem
    │  └─expression: expression.Function
    │     └─ExpressionList: If(column1 = 2, column1, 0)
    └─Table: tb_a

[Yizhe1208]

Sorry, the above is a parameter issue. The complete sql is like this: After removing some logic, it can be parsed normally again.

SELECT a.username, day, a.channel_type, cnt AS new_user
			, sum(if(diff = 2, lc_lv, 0)) AS lc_2
			, sum(if(diff = 3, lc_lv, 0)) AS lc_3
			, sum(if(diff = 4, lc_lv, 0)) AS lc_4
			, sum(if(diff = 5, lc_lv, 0)) AS lc_5
			, sum(if(diff = 6, lc_lv, 0)) AS lc_6
			, sum(if(diff = 7, lc_lv, 0)) AS lc_7
		FROM (
			SELECT a.day, a.channel_type, a.username, a.diff, a.cnt
				, lc, CAST(lc / cnt AS decimal(38, 4)) AS lc_lv
			FROM (
				SELECT a.day, a.channel_type, a.username
					, datediff(b.day, a.day) + 1 AS diff
					, cnt, count(DISTINCT b.user_id) AS lc
				FROM (
					SELECT a.day, a.user_id
						, channel_id channel_type
						, adtrace_adgroup_id AS username
					FROM (
						SELECT day, a.user_id, last_login_channel_id AS channel_id, last_adtrace_adgroup_id AS adtrace_adgroup_id
						FROM (
							SELECT day, user_id, yidevice
							FROM (
								SELECT day, user_id, yidevice, row_number() OVER (PARTITION BY day, user_id ORDER BY event_time) AS rk
								FROM dwd_table.event_pj
								WHERE day BETWEEN '2025-05-30' AND '2025-06-06'
									AND event_id = 'device_login'
									AND yidevice IS NOT NULL
									AND yidevice != ''
							) a
							WHERE rk = 1
						) a
							LEFT JOIN (
								SELECT DISTINCT FROM_UNIXTIME(CAST((CAST(last_adtrace_time AS bigint) + 28800000) / 1000 AS bigint), 'yyyy-MM-dd') AS last_adtrace_dt, yidevice, last_login_channel_id
									, last_adtrace_adgroup_id, last_adtrace_creative_id
								FROM dwd_user.yidevice_pj
								WHERE CAST(adtrace_reattributed_times AS int) > 0
									AND datediff(FROM_UNIXTIME(CAST((CAST(last_adtrace_time AS bigint) + 28800000) / 1000 AS bigint), 'yyyy-MM-dd'), create_date) >= 30
									AND FROM_UNIXTIME(CAST((CAST(last_adtrace_time AS bigint) + 28800000) / 1000 AS bigint), 'yyyy-MM-dd') BETWEEN '2025-05-30' AND '2025-06-06'
							) b
							ON a.day = b.last_adtrace_dt
								AND a.yidevice = b.yidevice

					) a
				) a
					LEFT JOIN (
						SELECT day, user_id
						FROM dwd_table.event_pj
						WHERE day BETWEEN '2025-05-30' AND '2025-06-06'
							AND event_id = 'login'
						GROUP BY day, user_id
					) b
					ON a.user_id = b.user_id
					LEFT JOIN (
						SELECT a.day, channel_type, username, count(DISTINCT a.user_id) AS cnt
						FROM (
							SELECT a.day, a.user_id
								, channel_id AS channel_type
								, adtrace_adgroup_id username
							FROM (
								SELECT day, a.user_id, last_login_channel_id AS channel_id, last_adtrace_adgroup_id AS adtrace_adgroup_id
								FROM (
									SELECT day, user_id, yidevice
									FROM (
										SELECT day, user_id, yidevice, row_number() OVER (PARTITION BY day, user_id ORDER BY event_time) AS rk
										FROM dwd_table.event_pj
										WHERE day BETWEEN '2025-05-30' AND '2025-06-06'
											AND event_id = 'device_login'
											AND yidevice IS NOT NULL
											AND yidevice != ''
									) a
									WHERE rk = 1
								) a
									LEFT JOIN (
										SELECT DISTINCT FROM_UNIXTIME(CAST((CAST(last_adtrace_time AS bigint) + 28800000) / 1000 AS bigint), 'yyyy-MM-dd') AS last_adtrace_dt, yidevice, last_login_channel_id
											, last_adtrace_adgroup_id, last_adtrace_creative_id
										FROM dwd_user.yidevice_pj
										WHERE CAST(adtrace_reattributed_times AS int) > 0
											AND datediff(FROM_UNIXTIME(CAST((CAST(last_adtrace_time AS bigint) + 28800000) / 1000 AS bigint), 'yyyy-MM-dd'), create_date) >= 30
											AND FROM_UNIXTIME(CAST((CAST(last_adtrace_time AS bigint) + 28800000) / 1000 AS bigint), 'yyyy-MM-dd') BETWEEN '2025-05-30' AND '2025-06-06'
									) b
									ON a.day = b.last_adtrace_dt
										AND a.yidevice = b.yidevice
							
								
								
							) a
						) a
						GROUP BY a.day, channel_type, username
					) c
					ON a.day = c.day
						AND a.channel_type = c.channel_type
						AND a.username = c.username
				GROUP BY a.day, a.channel_type, a.username, diff, cnt
			) a
			WHERE diff > 1
				AND diff <= 7
		) a
		GROUP BY username, channel_type, day, cnt
		ORDER BY username DESC, channel_type, day DESC  


here is this error info from JSQLFormatter/demo.html:
![Image](https://github.com/user-attachments/assets/56cf73a6-4221-4066-b751-5e2beead768e)


here is this error message from java
```shell
Exception in thread "main" net.sf.jsqlparser.JSQLParserException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "(" "("
    at line 2, column 9.

Was expecting one of:

    <EOF>
    <ST_SEMICOLON>

	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:352)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:115)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:91)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:64)

[Yizhe1208]

Sorry, the above is a parameter issue. The complete sql is like this: After removing some logic, it can be parsed normally again.

SELECT a.username, day, a.channel_type, cnt AS new_user
, sum(if(diff = 2, lc_lv, 0)) AS lc_2
, sum(if(diff = 3, lc_lv, 0)) AS lc_3
, sum(if(diff = 4, lc_lv, 0)) AS lc_4
, sum(if(diff = 5, lc_lv, 0)) AS lc_5
, sum(if(diff = 6, lc_lv, 0)) AS lc_6
, sum(if(diff = 7, lc_lv, 0)) AS lc_7
FROM (
SELECT a.day, a.channel_type, a.username, a.diff, a.cnt
, lc, CAST(lc / cnt AS decimal(38, 4)) AS lc_lv
FROM (
SELECT a.day, a.channel_type, a.username
, datediff(b.day, a.day) + 1 AS diff
, cnt, count(DISTINCT b.user_id) AS lc
FROM (
SELECT a.day, a.user_id
, channel_id channel_type
, adtrace_adgroup_id AS username
FROM (
SELECT day, a.user_id, last_login_channel_id AS channel_id, last_adtrace_adgroup_id AS adtrace_adgroup_id
FROM (
SELECT day, user_id, yidevice
FROM (
SELECT day, user_id, yidevice, row_number() OVER (PARTITION BY day, user_id ORDER BY event_time) AS rk
FROM dwd_table.event_pj
WHERE day BETWEEN '2025-05-30' AND '2025-06-06'
AND event_id = 'device_login'
AND yidevice IS NOT NULL
AND yidevice != ''
) a
WHERE rk = 1
) a
LEFT JOIN (
SELECT DISTINCT FROM_UNIXTIME(CAST((CAST(last_adtrace_time AS bigint) + 28800000) / 1000 AS bigint), 'yyyy-MM-dd') AS last_adtrace_dt, yidevice, last_login_channel_id
, last_adtrace_adgroup_id, last_adtrace_creative_id
FROM dwd_user.yidevice_pj
WHERE CAST(adtrace_reattributed_times AS int) > 0
AND datediff(FROM_UNIXTIME(CAST((CAST(last_adtrace_time AS bigint) + 28800000) / 1000 AS bigint), 'yyyy-MM-dd'), create_date) >= 30
AND FROM_UNIXTIME(CAST((CAST(last_adtrace_time AS bigint) + 28800000) / 1000 AS bigint), 'yyyy-MM-dd') BETWEEN '2025-05-30' AND '2025-06-06'
) b
ON a.day = b.last_adtrace_dt
AND a.yidevice = b.yidevice

  			) a
  		) a
  			LEFT JOIN (
  				SELECT day, user_id
  				FROM dwd_table.event_pj
  				WHERE day BETWEEN '2025-05-30' AND '2025-06-06'
  					AND event_id = 'login'
  				GROUP BY day, user_id
  			) b
  			ON a.user_id = b.user_id
  			LEFT JOIN (
  				SELECT a.day, channel_type, username, count(DISTINCT a.user_id) AS cnt
  				FROM (
  					SELECT a.day, a.user_id
  						, channel_id AS channel_type
  						, adtrace_adgroup_id username
  					FROM (
  						SELECT day, a.user_id, last_login_channel_id AS channel_id, last_adtrace_adgroup_id AS adtrace_adgroup_id
  						FROM (
  							SELECT day, user_id, yidevice
  							FROM (
  								SELECT day, user_id, yidevice, row_number() OVER (PARTITION BY day, user_id ORDER BY event_time) AS rk
  								FROM dwd_table.event_pj
  								WHERE day BETWEEN '2025-05-30' AND '2025-06-06'
  									AND event_id = 'device_login'
  									AND yidevice IS NOT NULL
  									AND yidevice != ''
  							) a
  							WHERE rk = 1
  						) a
  							LEFT JOIN (
  								SELECT DISTINCT FROM_UNIXTIME(CAST((CAST(last_adtrace_time AS bigint) + 28800000) / 1000 AS bigint), 'yyyy-MM-dd') AS last_adtrace_dt, yidevice, last_login_channel_id
  									, last_adtrace_adgroup_id, last_adtrace_creative_id
  								FROM dwd_user.yidevice_pj
  								WHERE CAST(adtrace_reattributed_times AS int) > 0
  									AND datediff(FROM_UNIXTIME(CAST((CAST(last_adtrace_time AS bigint) + 28800000) / 1000 AS bigint), 'yyyy-MM-dd'), create_date) >= 30
  									AND FROM_UNIXTIME(CAST((CAST(last_adtrace_time AS bigint) + 28800000) / 1000 AS bigint), 'yyyy-MM-dd') BETWEEN '2025-05-30' AND '2025-06-06'
  							) b
  							ON a.day = b.last_adtrace_dt
  								AND a.yidevice = b.yidevice
  					
  						
  						
  					) a
  				) a
  				GROUP BY a.day, channel_type, username
  			) c
  			ON a.day = c.day
  				AND a.channel_type = c.channel_type
  				AND a.username = c.username
  		GROUP BY a.day, a.channel_type, a.username, diff, cnt
  	) a
  	WHERE diff > 1
  		AND diff <= 7
  ) a
  GROUP BY username, channel_type, day, cnt
  ORDER BY username DESC, channel_type, day DESC  

here is this error info from JSQLFormatter/demo.html:

here is this error message from java

Exception in thread "main" net.sf.jsqlparser.JSQLParserException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "(" "("
    at line 2, column 9.

Was expecting one of:

    <EOF>
    <ST_SEMICOLON>

	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:352)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:115)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:91)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:64)

if change "sum(if(diff = 2, lc_lv, 0)) AS lc_2 " to "sum(1)" . job can parse success; T.T

how 2 fix this please 0RZ!!

[manticore-projects]

Greetings.

Thank you for this example, it is an interesting one!

Your example depends on allowComplexParsing which however gets disabled when you have too many nested statements (current limit is 10).

As a first quick fix I have increased this artificial limit to 16 and this solves your particular challenge.
Although I will have to make this limit configurable for a full solution

[Yizhe1208]

Greetings.

Thank you for this example, it is an interesting one!

Your example depends on allowComplexParsing which however gets disabled when you have too many nested statements (current limit is 10).

As a first quick fix I have increased this artificial limit to 16 and this solves your particular challenge. Although I will have to make this limit configurable for a full solution

sorry i didnt get.
what can i do now. clone and change which part ?. that i can parse sql like this .

change this param ALLOWED_NESTING_DEPTH？

i see it in code is
public static final int ALLOWED_NESTING_DEPTH = 10;

[manticore-projects]

Your challenge has been addressed and the change has been committed to GitHub.
Either clone/compile from GitHub or use JSQLParser Snapshot from Sonatype.

[Yizhe1208]

Your challenge has been addressed and the change has been committed to GitHub. Either clone/compile from GitHub or use JSQLParser Snapshot from Sonatype.

thanks A LOT! problem has been solved!!!!!! ORZ!!!!!!!