You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "jay wong (JIRA)" <ji...@apache.org> on 2014/02/27 10:52:19 UTC
[jira] [Updated] (PHOENIX-83) select with left join when the where
condition has right tables, the result is error
[ https://issues.apache.org/jira/browse/PHOENIX-83?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
jay wong updated PHOENIX-83:
----------------------------
Description:
I have two table.
select * from test_join1;
| VARCHAR_PK | CHAR_PK | INT_COLUMN | INT_COLUMN_B |
| Aarchar0 | A0 | 50 | 50 |
| Aarchar1 | A1 | 51 | 51 |
select * from test_join2;
| VARCHAR_PK | CHAR_PK | INT_COLUMN | INT_COLUMN_B |
| Barchar1 | B1 | 51 | 51 |
| Barchar2 | B2 | 52 | 52 |
| Barchar3 | B3 | 53 | 53 |
| Barchar4 | B4 | 54 | 54 |
| Barchar5 | B5 | 55 | 55 |
first query:
select test_join1.varchar_pk,sum(test_join2.int_column), min(test_join2.int_column_b) from test_join1 left join test_join2 on test_join1.int_column = test_join2.int_column where test_join1.int_column > 40 and test_join1.int_column < 100 GROUP BY TEST_JOIN1.VARCHAR_PK;
| VARCHAR_PK | SUM(TEST_JOIN2.INT_COLUMN(PROJECTED[1])) | MIN(TEST_JOIN2.INT_COLUMN_B(PROJECTED[2])) |
| Aarchar0 | null | null |
| Aarchar1 | 51 | 51 |
the result is expected。
but when query with
select test_join1.varchar_pk,sum(test_join2.int_column), min(test_join2.int_column_b) from test_join1 left join test_join2 on test_join1.int_column = test_join2.int_column where test_join2.int_column > 50 and test_join1.int_column < 53 GROUP BY TEST_JOIN1.VARCHAR_PK;
| VARCHAR_PK | SUM(TEST_JOIN2.INT_COLUMN(PROJECTED[1])) | MIN(TEST_JOIN2.INT_COLUMN_B(PROJECTED[2])) |
| Aarchar1 | 51 | 51 |
was:
I have two table.
select * from test_join1;
+------------+---------+------------+--------------+
| VARCHAR_PK | CHAR_PK | INT_COLUMN | INT_COLUMN_B |
| Aarchar0 | A0 | 50 | 50 |
| Aarchar1 | A1 | 51 | 51 |
+------------+---------+------------+--------------+
select * from test_join2;
+------------+---------+------------+--------------+
| VARCHAR_PK | CHAR_PK | INT_COLUMN | INT_COLUMN_B |
| Barchar1 | B1 | 51 | 51 |
| Barchar2 | B2 | 52 | 52 |
| Barchar3 | B3 | 53 | 53 |
| Barchar4 | B4 | 54 | 54 |
| Barchar5 | B5 | 55 | 55 |
+------------+---------+------------+--------------+
first query:
select test_join1.varchar_pk,sum(test_join2.int_column), min(test_join2.int_column_b) from test_join1 left join test_join2 on test_join1.int_column = test_join2.int_column where test_join1.int_column > 40 and test_join1.int_column < 100 GROUP BY TEST_JOIN1.VARCHAR_PK;
+------------+------------------------------------------+--------------------------------------------+
| VARCHAR_PK | SUM(TEST_JOIN2.INT_COLUMN(PROJECTED[1])) | MIN(TEST_JOIN2.INT_COLUMN_B(PROJECTED[2])) |
| Aarchar0 | null | null |
| Aarchar1 | 51 | 51 |
+------------+------------------------------------------+--------------------------------------------+
the result is expected。
but when query with
select test_join1.varchar_pk,sum(test_join2.int_column), min(test_join2.int_column_b) from test_join1 left join test_join2 on test_join1.int_column = test_join2.int_column where test_join2.int_column > 50 and test_join1.int_column < 53 GROUP BY TEST_JOIN1.VARCHAR_PK;
+------------+------------------------------------------+--------------------------------------------+
| VARCHAR_PK | SUM(TEST_JOIN2.INT_COLUMN(PROJECTED[1])) | MIN(TEST_JOIN2.INT_COLUMN_B(PROJECTED[2])) |
| Aarchar1 | 51 | 51 |
+------------+------------------------------------------+--------------------------------------------+
> select with left join when the where condition has right tables, the result is error
> ------------------------------------------------------------------------------------
>
> Key: PHOENIX-83
> URL: https://issues.apache.org/jira/browse/PHOENIX-83
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 3.0.0
> Reporter: jay wong
> Labels: patch
> Fix For: 3.0.0
>
>
> I have two table.
> select * from test_join1;
> | VARCHAR_PK | CHAR_PK | INT_COLUMN | INT_COLUMN_B |
> | Aarchar0 | A0 | 50 | 50 |
> | Aarchar1 | A1 | 51 | 51 |
> select * from test_join2;
> | VARCHAR_PK | CHAR_PK | INT_COLUMN | INT_COLUMN_B |
> | Barchar1 | B1 | 51 | 51 |
> | Barchar2 | B2 | 52 | 52 |
> | Barchar3 | B3 | 53 | 53 |
> | Barchar4 | B4 | 54 | 54 |
> | Barchar5 | B5 | 55 | 55 |
> first query:
> select test_join1.varchar_pk,sum(test_join2.int_column), min(test_join2.int_column_b) from test_join1 left join test_join2 on test_join1.int_column = test_join2.int_column where test_join1.int_column > 40 and test_join1.int_column < 100 GROUP BY TEST_JOIN1.VARCHAR_PK;
> | VARCHAR_PK | SUM(TEST_JOIN2.INT_COLUMN(PROJECTED[1])) | MIN(TEST_JOIN2.INT_COLUMN_B(PROJECTED[2])) |
> | Aarchar0 | null | null |
> | Aarchar1 | 51 | 51 |
> the result is expected。
> but when query with
> select test_join1.varchar_pk,sum(test_join2.int_column), min(test_join2.int_column_b) from test_join1 left join test_join2 on test_join1.int_column = test_join2.int_column where test_join2.int_column > 50 and test_join1.int_column < 53 GROUP BY TEST_JOIN1.VARCHAR_PK;
> | VARCHAR_PK | SUM(TEST_JOIN2.INT_COLUMN(PROJECTED[1])) | MIN(TEST_JOIN2.INT_COLUMN_B(PROJECTED[2])) |
> | Aarchar1 | 51 | 51 |
--
This message was sent by Atlassian JIRA
(v6.1.5#6160)