★ wanayoo — archive 1999 https://github.com/JSQLParser/JSqlParser/issues/947Nouvelle recherche | Portail wanayoo
Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

CCJSqlParserUtil.parse error: Encountered unexpected token: "IF" "IF" #947

Open
zmapleshine opened this issue Feb 12, 2020 · 0 comments
Open

Comments

@zmapleshine
Copy link

@zmapleshine zmapleshine commented Feb 12, 2020

Describe the bug
net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "IF" "IF"

To Reproduce
Steps to reproduce the behavior:

  1. Example SQL
    `SELECT
    count(0)
    FROM
    (
    SELECT
    t1., t2., p.project_name projectName,
    g.group_name groupName,
    t2.zone_id zoneId,
    t2.project_id projectId,
    t2.group_id groupId,
    t2.student_id studentId,
    t2.team_id teamId
    FROM
    (
    SELECT

     		IF (
     			@last_project_id = r.project_id
     			AND @last_group_id = r.group_id ,@rank_global :=@rank_global + 1 ,@rank_global := 1
     		) rankGlobal,
     		r.student_project_id rid ,@last_project_id := r.project_id tmp_p_id1,
     		@last_group_id := r.group_id tmp_g_id1
     	FROM
     		(
     			SELECT
     				@rank_global := 0,
     				@last_project_id := NULL ,@last_group_id := NULL
     		) v,
     		(
     			SELECT
     				r.*, SUM(r.score) totalScore,
     				sp.student_no studentNo,
     				GROUP_CONCAT(r.score ORDER BY r.round) scores,
     				CONCAT(
     					r.event_id,
     					'-',
     					r.student_id,
     					'-',
     					r.project_id,
     					'-',
     					r.group_id
     				) student_project_id
     			FROM
     				result r
     			INNER JOIN student_project sp ON (
     				sp.event_id = r.event_id
     				AND sp.student_id = r.student_id
     				AND sp.zone_id = r.zone_id
     			)
     			INNER JOIN student s ON s.id = sp.student_id
     			INNER JOIN project p ON p.id = r.project_id
     			WHERE
     				r.event_id = 12
     			GROUP BY
     				r.project_id,
     				r.group_id,
     				r.student_id,
     				r.event_id
     			ORDER BY
     				r.project_id,
     				r.group_id,
     				CASE
     			WHEN p.score_sort = 1 THEN
     				SUM(r.score)
     			END DESC,
     			CASE
     		WHEN p.score_sort = 0 THEN
     			SUM(r.score)
     		END,
     		s.birthday DESC
     		) r
     	) t1
     INNER JOIN (
     	SELECT
    
     	IF (
     		@last_zone_id = r.zone_id
     		AND @last_project_id = r.project_id
     		AND @last_group_id = r.group_id ,@rank_zone :=@rank_zone + 1 ,@rank_zone := 1
     	) rankZone,
     	r.*, @last_zone_id := r.zone_id tmp_zone_id ,@last_project_id := r.project_id tmp_p_id2,
     	@last_group_id := r.group_id tmp_g_id2
     FROM
     	(
     		SELECT
     			@rank_zone := 0,
     			@last_zone_id := NULL,
     			@last_project_id := NULL ,@last_group_id := NULL
     	) v,
     	(
     		SELECT
     			r.*, SUM(r.score) totalScore,
     			sp.student_no studentNo,
     			GROUP_CONCAT(r.score ORDER BY r.round) scores,
     			z.zone_name zoneName,
     			s.team_id,
     			t.team_name teamName,
     			s.full_name fullName,
     			s.identity_no identityNo,
     			CONCAT(
     				r.event_id,
     				'-',
     				r.student_id,
     				'-',
     				r.project_id,
     				'-',
     				r.group_id
     			) student_project_id
     		FROM
     			result r
     		INNER JOIN student_project sp ON (
     			sp.event_id = r.event_id
     			AND sp.student_id = r.student_id
     			AND sp.zone_id = r.zone_id
     		)
     		INNER JOIN student s ON s.id = sp.student_id
     		INNER JOIN `zone` z ON z.id = r.zone_id
     		INNER JOIN team t ON t.id = s.team_id
     		INNER JOIN project p ON p.id = r.project_id
     		WHERE
     			r.event_id = 12
     		GROUP BY
     			r.project_id,
     			r.group_id,
     			r.student_id,
     			r.event_id
     		ORDER BY
     			r.project_id,
     			r.group_id,
     			r.zone_id,
     			CASE
     		WHEN p.score_sort = 1 THEN
     			SUM(r.score)
     		END DESC,
     		CASE
     	WHEN p.score_sort = 0 THEN
     		SUM(r.score)
     	END,
     	s.birthday DESC
     	) r
     ) t2 ON t2.student_project_id = t1.rid
     INNER JOIN project p ON p.id = t2.project_id
     INNER JOIN oe_group g ON g.id = t2.group_id
     WHERE
     	1 = 1
     AND team_id = 12
     ORDER BY
     	project_id,
     	group_id,
     	rankGlobal,
     	rankZone
    

    ) tmp_count`

  2. Parsing this SQL using JSqlParser with this statements
    Statement statement = CCJSqlParserUtil.parse(sql); TablesNamesFinder tablesNamesFinder = new TablesNamesFinder(); List<String> tableList = tablesNamesFinder.getTableList(statement); System.out.println(tableList.toString());

  3. Exception
    net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "IF" "IF"
    Expected behavior

[result, student_project, student, project, zone, team, oe_group]

System

  • Database Mysql8.0.18
  • Java Version 1.8
  • JSqlParser version 3.1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant
You can’t perform that action at this time.