(Parser) support UNION

[pma-import]

Support UNION statements in the parser and correctly
split table references and select expressions.

- Original URL: https://sourceforge.net/p/phpmyadmin/feature-requests/262/
- Original author: lem9

[pma-import]

- labels: —> Parsing
- milestone: —> Needs_planning

- Original author: garvinhicking

[pma-import]

• labels: Parsing --> Parsing, GSoC 2015

---

• Original author: madhuracj

[pma-import]

Planned for GSoC 2015. See http://wiki.phpmyadmin.net/pma/GSoC_2015_Ideas_List#Parser.2Fanalyzer_rewrite.2Freplacement

---

• Original author: madhuracj

[pma-import]

• labels: Parsing, GSoC 2015 --> Parsing, gsoc

---

• Original author: madhuracj

[pma-import]

• assigned_to: Marc Delisle

---

• Original author: lem9

[pma-import]

Where exactly do we need this? I mean after correctly splitting the select statements, where do we use/show it?

---

• Original author: nisargjhaveri

[pma-import]

Originally, this was for code highlighting. I just made a quick test and don't see a problem when browsing and sorting on a column coming from an UNION.

I guess you could not find anything either?

---

• Original author: lem9

[pma-import]

Found a problem which looks like a parser problem. Using the attached file, try
select supplier.city, supplier.id from supplier union select customer.city, customer.id from customer
then ask to sort on both columns (with shift+click on the second column).

---

• Original author: lem9
• Attachment: http://sourceforge.net/p/phpmyadmin/feature-requests/_discuss/thread/f2ccb522/ace5/9630/fa8e/attachment/test.sql

[pma-import]

This does not look like a parser problem.

I've created a separate ticket at https://sourceforge.net/p/phpmyadmin/bugs/4807/

---

• Original author: nisargjhaveri

[pma-import]

I accept your fix, but it could be a parser problem because the table name has not been determined.

---

• Original author: lem9

[udan11]

Fixed in udan11/sql-parser@6e33561. It will be soon in the master branch too.

Tested with

select supplier.city, supplier.id from supplier union select customer.city, customer.id from customer

[deligant]

Don't know if the same error is the culprit, but if I do a complex UNION query, phpmyadmin 4.5.4.1deb2ubuntu2 (latest stable of 16.04 LTS) insertes a "limit 0,100" right after the UNION keyword
(and also does not parse correctly the REGEXP token...)

This original query:
(select DivId, ClId, TfId, TfName, TfDefault, TfRegExp, TfClasses, DivViewOrder, ClViewOrder from Divisions inner join Classes on DivTournament=ClTournament and DivAthlete=ClAthlete inner join TargetFaces Tf on DivTournament=TfTournament and concat(trim(DivId),trim(ClId)) REGEXP TfRegExp WHERE DivTournament=654 AND DivAthlete='1' AND (ClDivisionsAllowed='' or find_in_set(DivId, ClDivisionsAllowed))) UNION (select DivId, ClId, TfId, TfName, TfDefault, TfRegExp, TfClasses, DivViewOrder, ClViewOrder from Divisions inner join Classes on DivTournament=ClTournament and DivAthlete=ClAthlete inner join TargetFaces Tf on DivTournament=TfTournament and concat(trim(DivId),trim(ClId)) like TfClasses WHERE DivTournament=654 AND DivAthlete='1' AND (ClDivisionsAllowed='' or find_in_set(DivId, ClDivisionsAllowed))) order by DivViewOrder , ClViewOrder , TfDefault desc , TfRegExp>'' desc , concat(trim(DivId),trim(ClId)) = TfClasses desc , left(TfClasses,1)!='_' and left(TfClasses,1)!='%' desc , left(TfClasses,1)='_' desc , TfClasses desc , TfClasses='%'

results in 86 rows if executed directly in Mysql or in php, but in phpmyadmin I first get this error:
`Static analysis:

2 errors were found during analysis.

Unrecognized keyword. (near "REGEXP" at position 276)
Unexpected token. (near "TfRegExp" at position 283)
SQL query: Documentation

(select DivId, ClId, TfId, TfName, TfDefault, TfRegExp, TfClasses, DivViewOrder, ClViewOrder from Divisions inner join Classes on DivTournament=ClTournament and DivAthlete=ClAthlete inner join TargetFaces Tf on DivTournament=TfTournament and concat(trim(DivId),trim(ClId)) REGEXP TfRegExp WHERE DivTournament=654 AND DivAthlete='1' AND (ClDivisionsAllowed='' or find_in_set(DivId, ClDivisionsAllowed))) LIMIT 0, 100 UNION (

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
(' at line 7`

Please note theat PhpMyadmin added the "LIMIT 0,100" string BEFORE the Union clause.
Amending the query (adding brackets around the regexp part) the error changes and is now

`SQL query: Documentation

(select DivId, ClId, TfId, TfName, TfDefault, TfRegExp, TfClasses, DivViewOrder, ClViewOrder
from Divisions
inner join Classes on DivTournament=ClTournament and DivAthlete=ClAthlete
inner join TargetFaces Tf on DivTournament=TfTournament and (concat(trim(DivId),trim(ClId)) REGEXP TfRegExp)
WHERE DivTournament=654
AND DivAthlete='1'
AND (ClDivisionsAllowed='' or find_in_set(DivId, ClDivisionsAllowed))) LIMIT 0, 100 UNION
(
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
(' at line 7`
Please notice the LIMIT 0,100 added before the UNION clause again!
To force it to work I need to add (!) a limit 0,1000 right before the closing bracket of the first select... wich is not a viable solution... especially because the same query was working perfectly with the version shipped with phpmyadmin in ubuntu 15.10!
I tried to force the limit after the order clause but without success!

[nijel]

Might be same problem, there is also another bug this query exposes in our SQL parser: phpmyadmin/sql-parser#116