[
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
