[BUG] Valid MySQL query fails on three-part identifiers and parenthesized joins

[sulo1337]

Valid MySQL query fails to parse: three-part identifiers and parenthesized joins

I found two parsing issues with a valid MySQL query generated from the Sakila schema.

Original query

SELECT
  `cu`.`customer_id` AS `ID`,
  CONCAT(`cu`.`first_name`, ' ', `cu`.`last_name`) AS `name`,
  `a`.`address` AS `address`,
  `a`.`postal_code` AS `zip code`,
  `a`.`phone` AS `phone`,
  `sakila`.`city`.`city` AS `city`,
  `sakila`.`country`.`country` AS `country`,
  IF(`cu`.`active`, 'active', '') AS `notes`,
  `cu`.`store_id` AS `SID`
FROM (
  (
    (
      `sakila`.`customer` `cu`
      JOIN `sakila`.`address` `a`
        ON (`cu`.`address_id` = `a`.`address_id`)
    )
    JOIN `sakila`.`city`
      ON (`a`.`city_id` = `sakila`.`city`.`city_id`)
  )
  JOIN `sakila`.`country`
    ON (`sakila`.`city`.`country_id` = `sakila`.`country`.`country_id`)
);

Issue 1: Three-part column reference

This valid MySQL reference fails:

`sakila`.`city`.`city`

Error:

gosqlx: syntax error: Error E2002 at line 1, column 189:
expected FROM, semicolon, or end of statement, got PERIOD

It looks like the parser accepts table.column or alias.column, but not database.table.column.

Minimal repro:

SELECT
  `sakila`.`city`.`city` AS `city`
FROM `sakila`.`city`;

Issue 2: Parenthesized joins

After removing the three-part column references, the query still fails on the parenthesized join structure:

FROM (
  (
    (
      `sakila`.`customer` `cu`
      JOIN `sakila`.`address` `a`
        ON (`cu`.`address_id` = `a`.`address_id`)
    )
    JOIN `sakila`.`city`
      ON (`a`.`city_id` = `sakila`.`city`.`city_id`)
  )
  JOIN `sakila`.`country`
    ON (`sakila`.`city`.`country_id` = `sakila`.`country`.`country_id`)
);

Error:

gosqlx: syntax error: Error E2002 at line 12, column 3:
expected SELECT in derived table, got LPAREN

It seems like FROM ( is being treated only as a derived table, but MySQL also allows parenthesized join expressions.

Minimal repro:

SELECT
  `cu`.`customer_id` AS `ID`
FROM (
  (
    `sakila`.`customer` `cu`
    JOIN `sakila`.`address` `a`
      ON (`cu`.`address_id` = `a`.`address_id`)
  )
);

Expected behavior

Both queries should parse successfully in MySQL mode.

Workaround

This version works around both issues by using aliases and removing the nested join parentheses:

SELECT
  `cu`.`customer_id` AS `ID`,
  CONCAT(`cu`.`first_name`, ' ', `cu`.`last_name`) AS `name`,
  `a`.`address` AS `address`,
  `a`.`postal_code` AS `zip code`,
  `a`.`phone` AS `phone`,
  `ci`.`city` AS `city`,
  `co`.`country` AS `country`,
  IF(`cu`.`active`, 'active', '') AS `notes`,
  `cu`.`store_id` AS `SID`
FROM `sakila`.`customer` AS `cu`
JOIN `sakila`.`address` AS `a`
  ON `cu`.`address_id` = `a`.`address_id`
JOIN `sakila`.`city` AS `ci`
  ON `a`.`city_id` = `ci`.`city_id`
JOIN `sakila`.`country` AS `co`
  ON `ci`.`country_id` = `co`.`country_id`;