The JcrSql2QueryParser breaks if it is passed a query containing a set operation (UNION, INTERSECT, EXCEPT) with the following stack trace:

java.lang.ClassCastException: org.modeshape.graph.query.model.Limit cannot be cast to org.modeshape.jcr.query.qom.JcrLimit
	at org.modeshape.jcr.query.qom.JcrSetQuery.limits(JcrSetQuery.java:125)
	at org.modeshape.jcr.query.qom.JcrSetQuery.limits(JcrSetQuery.java:1)
	at org.modeshape.jcr.query.JcrSql2QueryParser.parseSetQuery(JcrSql2QueryParser.java:197)
	at org.modeshape.graph.query.parse.SqlQueryParser.parseQueryCommand(SqlQueryParser.java:514)
	at org.modeshape.graph.query.parse.SqlQueryParser.parseQuery(SqlQueryParser.java:504)
	at org.modeshape.jcr.query.JcrSql2QueryParserTest.parseSetQuery(JcrSql2QueryParserTest.java:618)
	at org.modeshape.jcr.query.JcrSql2QueryParserTest.shouldParseSetQuery(JcrSql2QueryParserTest.java:527)
... (trace after the testcase elided)

The sequence of events here is a little more complicated than the trace indicates, so for clarity's sake –

1. JcrSql2QueryParser.parseSetQuery() fires its superimplementation to setup.
2. SqlQueryParser.parseSetQuery() determines the set operation being used and calls setQuery() to create the SetQuery object.
3. Since JcrSql2QueryParser overrides the setQuery() method, the subimplementation is executed rather than the super.
4. JcrSql2QueryParser.setQuery() creates and returns a new object of type JcrSetQuery using the four-argument constructor (parameters are left, operation, right, and all).
5. The four-arg JcrSetQuery constructor calls its super in SetQuery.
6. The SetQuery constructor initializes the "limits" field to Limit.NONE.
7. Back in JcrSql2QueryParser.parseSetQuery(), the parser tries to cast query.limits() to the type JcrLimit. Since JcrLimit is a subclass of Limit, it throws a ClassCastException.

JcrSetQuery expects its limits field to be specifically the JcrLimit type, but the four-arg SetQuery constructor is instead setting it to an instance of JcrLimit's parent class. This can be fixed pretty easily by using the six-argument JcrSetQuery constructor from JcrSql2QueryParser.setQuery(), which specifies the limits field. Since the value would have been some variant of NONE in all cases (there is another setQuery() method for queries having a limit), JcrLimit.NONE fits perfectly well.

This bug affects 2.8.2.Final at least (I haven't gone back to see how far it extends), and is still present in the 2.9 snapshot. I'm attaching a patch to the 2.x branch that contains this fix and a unit test in JcrSql2QueryParserTest.