You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2014/06/29 01:55:59 UTC
[2/2] git commit: Fix a bug where composite SELECT DISTINCT would
return duplicate rows.
Fix a bug where composite SELECT DISTINCT would return duplicate rows.
Project: http://git-wip-us.apache.org/repos/asf/incubator-optiq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-optiq/commit/2db452ec
Tree: http://git-wip-us.apache.org/repos/asf/incubator-optiq/tree/2db452ec
Diff: http://git-wip-us.apache.org/repos/asf/incubator-optiq/diff/2db452ec
Branch: refs/heads/master
Commit: 2db452ecbe0837d9f8207b5862558c031ac4aa7b
Parents: 71ebeaa
Author: Julian Hyde <ju...@gmail.com>
Authored: Sat Jun 28 16:36:39 2014 -0700
Committer: Julian Hyde <ju...@gmail.com>
Committed: Sat Jun 28 16:47:20 2014 -0700
----------------------------------------------------------------------
.../hydromatic/optiq/rules/java/JavaRules.java | 3 +-
core/src/test/resources/sql/agg.oq | 200 +++++++++++++++++++
2 files changed, 201 insertions(+), 2 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/2db452ec/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java b/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
index 266a7b4..3b17701 100644
--- a/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
+++ b/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
@@ -1180,8 +1180,7 @@ public class JavaRules {
childExp,
BuiltinMethod.DISTINCT.method,
Expressions.<Expression>list()
- .appendIfNotNull(
- keyPhysType.comparer()))));
+ .appendIfNotNull(physType.comparer()))));
} else {
final Expression resultSelector =
builder.append(
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/2db452ec/core/src/test/resources/sql/agg.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/agg.oq b/core/src/test/resources/sql/agg.oq
new file mode 100644
index 0000000..b718949
--- /dev/null
+++ b/core/src/test/resources/sql/agg.oq
@@ -0,0 +1,200 @@
+# outer.oq - Various kinds of outer join
+!use post
+!set outputformat mysql
+
+select * from emp;
++-------+--------+--------+
+| ENAME | DEPTNO | GENDER |
++-------+--------+--------+
+| Jane | 10 | F |
+| Bob | 10 | M |
+| Eric | 20 | M |
+| Susan | 30 | F |
+| Alice | 30 | F |
+| Adam | 50 | M |
+| Eve | 50 | F |
+| Grace | 60 | F |
+| Wilma | | F |
++-------+--------+--------+
+(9 rows)
+
+!ok
+select * from emp join dept on emp.deptno = dept.deptno;
++-------+--------+--------+---------+-------------+
+| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME |
++-------+--------+--------+---------+-------------+
+| Jane | 10 | F | 10 | Sales |
+| Bob | 10 | M | 10 | Sales |
+| Eric | 20 | M | 20 | Marketing |
+| Susan | 30 | F | 30 | Engineering |
+| Alice | 30 | F | 30 | Engineering |
++-------+--------+--------+---------+-------------+
+(5 rows)
+
+!ok
+
+# The following test is disabled, because we cannot handle non-equi-join.
+# Following it are the results from MySQL.
+!if (false) {
+select * from emp join dept on emp.deptno = dept.deptno and emp.gender = 'F';
+ ename | deptno | gender | deptno | dname
+-------+--------+--------+--------+-------------
+ Jane | 10 | F | 10 | Sales
+ Susan | 30 | F | 30 | Engineering
+ Alice | 30 | F | 30 | Engineering
+
+!ok
+!}
+
+select * from emp join dept on emp.deptno = dept.deptno where emp.gender = 'F';
++-------+--------+--------+---------+-------------+
+| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME |
++-------+--------+--------+---------+-------------+
+| Jane | 10 | F | 10 | Sales |
+| Susan | 30 | F | 30 | Engineering |
+| Alice | 30 | F | 30 | Engineering |
++-------+--------+--------+---------+-------------+
+(3 rows)
+
+!ok
+
+select * from (select * from emp where gender ='F') as emp join dept on emp.deptno = dept.deptno;
++-------+--------+--------+---------+-------------+
+| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME |
++-------+--------+--------+---------+-------------+
+| Jane | 10 | F | 10 | Sales |
+| Susan | 30 | F | 30 | Engineering |
+| Alice | 30 | F | 30 | Engineering |
++-------+--------+--------+---------+-------------+
+(3 rows)
+
+!ok
+
+# The following test is disabled, because we cannot handle non-equi-join.
+# Following it are the results from MySQL.
+!if (false) {
+select * from emp left join dept on emp.deptno = dept.deptno and emp.gender = 'F';
+ ename | deptno | gender | deptno | dname
+-------+--------+--------+--------+-------------
+ Jane | 10 | F | 10 | Sales
+ Susan | 30 | F | 30 | Engineering
+ Alice | 30 | F | 30 | Engineering
+ Bob | 10 | M | NULL | NULL
+ Eric | 20 | M | NULL | NULL
+ Adam | 50 | M | NULL | NULL
+ Eve | 50 | F | NULL | NULL
+ Grace | 60 | F | NULL | NULL
+!ok
+!}
+
+select * from emp left join dept on emp.deptno = dept.deptno where emp.gender = 'F';
++-------+--------+--------+---------+-------------+
+| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME |
++-------+--------+--------+---------+-------------+
+| Jane | 10 | F | 10 | Sales |
+| Susan | 30 | F | 30 | Engineering |
+| Alice | 30 | F | 30 | Engineering |
+| Eve | 50 | F | | |
+| Grace | 60 | F | | |
+| Wilma | | F | | |
++-------+--------+--------+---------+-------------+
+(6 rows)
+
+!ok
+
+select * from (select * from emp where gender ='F') as emp left join dept on emp.deptno = dept.deptno;
++-------+--------+--------+---------+-------------+
+| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME |
++-------+--------+--------+---------+-------------+
+| Jane | 10 | F | 10 | Sales |
+| Susan | 30 | F | 30 | Engineering |
+| Alice | 30 | F | 30 | Engineering |
+| Eve | 50 | F | | |
+| Grace | 60 | F | | |
+| Wilma | | F | | |
++-------+--------+--------+---------+-------------+
+(6 rows)
+
+!ok
+
+# The following test is disabled, because we cannot handle non-equi-join.
+# Following it are the results from MySQL.
+!if (false) {
+select * from emp right join dept on emp.deptno = dept.deptno and emp.gender = 'F';
++-------+--------+--------+--------+-------------+
+| ename | deptno | gender | deptno | dname |
++-------+--------+--------+--------+-------------+
+| Jane | 10 | F | 10 | Sales |
+| Susan | 30 | F | 30 | Engineering |
+| Alice | 30 | F | 30 | Engineering |
+| NULL | NULL | NULL | 20 | Marketing |
+| NULL | NULL | NULL | 40 | Empty |
++-------+--------+--------+--------+-------------+
+!ok
+!}
+
+select * from emp right join dept on emp.deptno = dept.deptno where emp.gender = 'F';
++-------+--------+--------+---------+-------------+
+| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME |
++-------+--------+--------+---------+-------------+
+| Jane | 10 | F | 10 | Sales |
+| Susan | 30 | F | 30 | Engineering |
+| Alice | 30 | F | 30 | Engineering |
++-------+--------+--------+---------+-------------+
+(3 rows)
+
+!ok
+
+select * from (select * from emp where gender ='F') as emp right join dept on emp.deptno = dept.deptno;
++-------+--------+--------+---------+-------------+
+| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME |
++-------+--------+--------+---------+-------------+
+| Jane | 10 | F | 10 | Sales |
+| Susan | 30 | F | 30 | Engineering |
+| Alice | 30 | F | 30 | Engineering |
+| | | | 20 | Marketing |
+| | | | 40 | Empty |
++-------+--------+--------+---------+-------------+
+(5 rows)
+
+!ok
+
+!if (false) {
+select * from emp full join dept on emp.deptno = dept.deptno and emp.gender = 'F';
+!ok
+!}
+
+
+select * from emp full join dept on emp.deptno = dept.deptno where emp.gender = 'F';
++-------+--------+--------+---------+-------------+
+| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME |
++-------+--------+--------+---------+-------------+
+| Jane | 10 | F | 10 | Sales |
+| Susan | 30 | F | 30 | Engineering |
+| Alice | 30 | F | 30 | Engineering |
+| Eve | 50 | F | | |
+| Grace | 60 | F | | |
+| Wilma | | F | | |
++-------+--------+--------+---------+-------------+
+(6 rows)
+
+!ok
+
+select * from (select * from emp where gender ='F') as emp full join dept on emp.deptno = dept.deptno;
++-------+--------+--------+---------+-------------+
+| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME |
++-------+--------+--------+---------+-------------+
+| Jane | 10 | F | 10 | Sales |
+| Susan | 30 | F | 30 | Engineering |
+| Alice | 30 | F | 30 | Engineering |
+| Eve | 50 | F | | |
+| Grace | 60 | F | | |
+| Wilma | | F | | |
+| | | | 20 | Marketing |
+| | | | 40 | Empty |
++-------+--------+--------+---------+-------------+
+(8 rows)
+
+!ok
+
+# End outer.oq