[ https://issues.apache.org/jira/browse/TORQUE-322?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14126268#comment-14126268 ]
Thomas Fox commented on TORQUE-322: ----------------------------------- According to http://en.wikipedia.org/wiki/Set_operations_%28SQL%29, there are three SET operations, namely UNION, INTERSECT and EXCEPT .Each operation can be suffixed by ALL (do not remove duplicate rows) or DISTINCT (do remove duplicate rows). The standard behavior is DISTINCT. Support from the supported databases for the set operators: Oracle: According to http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm, Oracle supports UNION [ALL], INTERSECT and MINUS, where it seems from the description that MINUS is functionally equivalent to EXCEPT. Mysql: According to http://dev.mysql.com/doc/refman/5.7/en/union.html, MySQL supports UNION and UNION ALL. I did not find any support for INTERSECT and EXCEPT. Postgresql: According to http://www.postgresql.org/docs/7.4/static/sql-select.html, all set operations are supported by Postgresql. Derby: According to https://db.apache.org/derby/docs/10.7/ref/rrefsqlj21571.html, all set operations are supported by Derby. Hsqldb: According to http://www.hsqldb.org/doc/guide/ch09.html, hsqldb supports UNION, UNION ALL, MINUS, EXCEPT and INTERSECT. Mssql: According to http://msdn.microsoft.com/de-de/library/ms188055.aspx, Mssql supports UNION [ALL], EXCEPT and INTERSECT. Execution order is also different for databases. E.g. Oracle 10g evaluates all operations from left to right, while in postgresql, the intersect command has higher precedence. Conclusion: all six combinations (three operators and the ALL variant) should be supported. It need not be tracked which operation is supported by which database (this would make the adapter class very fat); however it should be tracked in the tests. It needs to be tracked in the adapter whether MINUS must be used instead of EXCEPT (true for oracle, false for others). There should be some means to control parenthesis (a UNION (b INTERSECT C) vs (a UNION b) INTERSECT c. > Support for SQL Set Operations (UNION, INTERSECT, ...) > ------------------------------------------------------ > > Key: TORQUE-322 > URL: https://issues.apache.org/jira/browse/TORQUE-322 > Project: Torque > Issue Type: New Feature > Affects Versions: 4.0 > Reporter: Michael Wa > Assignee: Thomas Fox > > * It would be nice to support sql constructs like: > SELECT * FROM sales2005 UNION SELECT * FROM sales2006; > * If you want to realize the UNION operator you have to do something like > that: > [...] > Criteria criteriaA = new Criteria(); > criteriaA.where( ... ); > criteriaA.addSelectColumn(COLUMNX); > Query queryA = SqlBuilder.buildQuery(criteriaA); > String sqlA = queryA.toString(); > List<Object> replacementsA = queryA.getPreparedStatementReplacements(); > Criteria criteriaB = new Criteria(); > criteriaB.where( ... ); > criteriaB.addSelectColumn(...); > Query queryB = SqlBuilder.buildQuery(criteriaB); > String sqlB = queryB.toString(); > List<Object> replacementsB = queryB.getPreparedStatementReplacements(); > List<Object> replacements = Lists.newArrayList(); > replacements.addAll(replacementsA); > replacements.addAll(replacementsB); > String sql = COLUMNX + " IN (" + sqlA + " UNION " + sqlB + ")"; > mainCriteria.whereVerbatimSql(sql, replacements.toArray()); > [...] -- This message was sent by Atlassian JIRA (v6.3.4#6332) --------------------------------------------------------------------- To unsubscribe, e-mail: torque-dev-unsubscr...@db.apache.org For additional commands, e-mail: torque-dev-h...@db.apache.org