Details

    • Type: Sub-task
    • Status: Closed
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 3.1.0, 4.1.0
    • Labels:
    • old issue number:
      5

      Description

      Add support for derived queries of the form:
      SELECT * FROM ( SELECT company, revenue FROM Company ORDER BY revenue) LIMIT 10

      Adding support for this requires a compile time change as well as a runtime execution change. The first version of the compile-time change could limit aggregation to only be allowed in the inner or the outer query, but not both. In this case, the inner and outer queries can be combined into a single query with the outer select becoming just a remapping of a subset of the projection from the inner select. The second version of the compile-time change could handle aggregation in the inner and outer select by performing client side (this is likely a less common scenario).

      For the runtime execution, change the UngroupedAggregateRegionObserver would be modified to look for a new "TopNLimit" attribute with an int value in the Scan. This would control the maximum number of values for the coprocessor to hold on to as the scan is performed. Then the GroupedAggregatingResultIterator would be modified to handle keeping the topN values received back from all the child iterators.

        Activity

        Hide
        enis Enis Soztutar added a comment -

        Bulk close of all issues that has been resolved in a released version.

        Show
        enis Enis Soztutar added a comment - Bulk close of all issues that has been resolved in a released version.
        Hide
        jaywong jay wong added a comment -

        Maryann Xue
        Could you attach a patch for 3.0.0-release version. thx

        Show
        jaywong jay wong added a comment - Maryann Xue Could you attach a patch for 3.0.0-release version. thx
        Hide
        jaywong jay wong added a comment -

        How Can I get the patch Maryann Xue

        Show
        jaywong jay wong added a comment - How Can I get the patch Maryann Xue
        Hide
        jamestaylor James Taylor added a comment -

        Excellent work, Maryann Xue. I think we have JIRAs opened already for any corner cases not yet supported, but if not, please open any missing ones.

        Show
        jamestaylor James Taylor added a comment - Excellent work, Maryann Xue . I think we have JIRAs opened already for any corner cases not yet supported, but if not, please open any missing ones.
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user maryannxue closed the pull request at:

        https://github.com/apache/incubator-phoenix/pull/31

        Show
        githubbot ASF GitHub Bot added a comment - Github user maryannxue closed the pull request at: https://github.com/apache/incubator-phoenix/pull/31
        Hide
        jamestaylor James Taylor added a comment -

        Nitin Kumar, Damien Carol - FYI we have derived table implemented in our 3.0,4.0, and master branch (including for joins with some restrictions). Getting ready next week to release this as a 3.1/4.1 release (along with our enhanced Pig Integration). Give a spin if you'd like a sneak peek.

        Show
        jamestaylor James Taylor added a comment - Nitin Kumar , Damien Carol - FYI we have derived table implemented in our 3.0,4.0, and master branch (including for joins with some restrictions). Getting ready next week to release this as a 3.1/4.1 release (along with our enhanced Pig Integration). Give a spin if you'd like a sneak peek.
        Hide
        hudson Hudson added a comment -

        SUCCESS: Integrated in Phoenix-3.0 #71 (See https://builds.apache.org/job/Phoenix-3.0/71/)
        PHOENIX-136 Support derived tables (maryannxue: rev a20f2790b00c13ba19f96d989d31a07e0610b404)

        • phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
        • phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
        • phoenix-core/src/main/java/org/apache/phoenix/parse/HintNode.java
        • phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
        • phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
        • phoenix-core/src/main/java/org/apache/phoenix/compile/IndexStatementRewriter.java
        • phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java
        • phoenix-core/src/main/antlr3/PhoenixSQL.g
        • phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java
        • phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java
        • phoenix-core/src/main/java/org/apache/phoenix/compile/LimitCompiler.java
        • phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java
        • phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
        • phoenix-core/src/main/java/org/apache/phoenix/compile/FromCompiler.java
        Show
        hudson Hudson added a comment - SUCCESS: Integrated in Phoenix-3.0 #71 (See https://builds.apache.org/job/Phoenix-3.0/71/ ) PHOENIX-136 Support derived tables (maryannxue: rev a20f2790b00c13ba19f96d989d31a07e0610b404) phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java phoenix-core/src/main/java/org/apache/phoenix/parse/HintNode.java phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java phoenix-core/src/main/java/org/apache/phoenix/compile/IndexStatementRewriter.java phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java phoenix-core/src/main/antlr3/PhoenixSQL.g phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java phoenix-core/src/main/java/org/apache/phoenix/compile/LimitCompiler.java phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java phoenix-core/src/main/java/org/apache/phoenix/compile/FromCompiler.java
        Hide
        hudson Hudson added a comment -

        SUCCESS: Integrated in Phoenix #217 (See https://builds.apache.org/job/Phoenix/217/)
        PHOENIX-136 Support derived tables (maryannxue: rev 43c788779d0d62aa7d7022802d6064490046ee23)

        • phoenix-core/src/main/java/org/apache/phoenix/parse/HintNode.java
        • phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java
        • phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java
        • phoenix-core/src/main/java/org/apache/phoenix/compile/LimitCompiler.java
        • phoenix-core/src/main/antlr3/PhoenixSQL.g
        • phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java
        • phoenix-core/src/main/java/org/apache/phoenix/compile/IndexStatementRewriter.java
        • phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
        • phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
        • phoenix-core/src/main/java/org/apache/phoenix/compile/FromCompiler.java
        • phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
        • phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
        • phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java
        • phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
        Show
        hudson Hudson added a comment - SUCCESS: Integrated in Phoenix #217 (See https://builds.apache.org/job/Phoenix/217/ ) PHOENIX-136 Support derived tables (maryannxue: rev 43c788779d0d62aa7d7022802d6064490046ee23) phoenix-core/src/main/java/org/apache/phoenix/parse/HintNode.java phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java phoenix-core/src/main/java/org/apache/phoenix/compile/LimitCompiler.java phoenix-core/src/main/antlr3/PhoenixSQL.g phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java phoenix-core/src/main/java/org/apache/phoenix/compile/IndexStatementRewriter.java phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java phoenix-core/src/main/java/org/apache/phoenix/compile/FromCompiler.java phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user maryannxue commented on a diff in the pull request:

        https://github.com/apache/incubator-phoenix/pull/31#discussion_r11866024

        — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java —
        @@ -0,0 +1,242 @@
        +/*
        + * Licensed to the Apache Software Foundation (ASF) under one
        + * or more contributor license agreements. See the NOTICE file
        + * distributed with this work for additional information
        + * regarding copyright ownership. The ASF licenses this file
        + * to you under the Apache License, Version 2.0 (the
        + * "License"); you may not use this file except in compliance
        + * with the License. You may obtain a copy of the License at
        + *
        + * http://www.apache.org/licenses/LICENSE-2.0
        + *
        + * Unless required by applicable law or agreed to in writing, software
        + * distributed under the License is distributed on an "AS IS" BASIS,
        + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
        + * See the License for the specific language governing permissions and
        + * limitations under the License.
        + */
        +
        +package org.apache.phoenix.compile;
        +
        +import java.sql.SQLException;
        +import java.sql.SQLFeatureNotSupportedException;
        +import java.util.Arrays;
        +import java.util.HashMap;
        +import java.util.List;
        +import java.util.Map;
        +
        +import org.apache.phoenix.jdbc.PhoenixConnection;
        +import org.apache.phoenix.parse.AliasedNode;
        +import org.apache.phoenix.parse.ColumnParseNode;
        +import org.apache.phoenix.parse.DerivedTableNode;
        +import org.apache.phoenix.parse.HintNode;
        +import org.apache.phoenix.parse.LimitNode;
        +import org.apache.phoenix.parse.OrderByNode;
        +import org.apache.phoenix.parse.ParseNode;
        +import org.apache.phoenix.parse.ParseNodeRewriter;
        +import org.apache.phoenix.parse.SelectStatement;
        +import org.apache.phoenix.parse.TableNode;
        +import org.apache.phoenix.parse.TableWildcardParseNode;
        +import org.apache.phoenix.parse.WildcardParseNode;
        +import org.apache.phoenix.util.SchemaUtil;
        +
        +import com.google.common.collect.Lists;
        +
        +public class SubselectRewriter extends ParseNodeRewriter {
        + private final String tableAlias;
        + private final Map<String, ParseNode> aliasMap;
        +
        + public static SelectStatement applyPostFilters(SelectStatement statement, List<ParseNode> postFilters, String subqueryAlias) throws SQLException {
        + if (postFilters.isEmpty())
        + return statement;
        +
        + // TODO handle this from caller
        + if (statement.getLimit() != null || (statement.isAggregate() && statement.getGroupBy().isEmpty()))
        — End diff –

        This applyPostFilter() method is for subqueries in joins. We can have some conditions in ON clause or WHERE clause to be pushed down into individual join tables, and if the join table is a subquery, we call this method to merge this post-filter into the subquery, as a WHERE condition or a HAVING condition so that in some cases these post-filters can be optimized as pre-filters.
        Those cases that would throw exceptions from here are very rare, basically like:
        1) select ... from A join (select id, b from T limit 10) as B on A.id = B.id where B.b = 'b'
        2) select ... from A join (select count c from T) as B on A.a = B.c where B.c > 10

        However, this post-filter can be easily handled in joins. I will make another check-in later.

        Show
        githubbot ASF GitHub Bot added a comment - Github user maryannxue commented on a diff in the pull request: https://github.com/apache/incubator-phoenix/pull/31#discussion_r11866024 — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java — @@ -0,0 +1,242 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.phoenix.compile; + +import java.sql.SQLException; +import java.sql.SQLFeatureNotSupportedException; +import java.util.Arrays; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.apache.phoenix.jdbc.PhoenixConnection; +import org.apache.phoenix.parse.AliasedNode; +import org.apache.phoenix.parse.ColumnParseNode; +import org.apache.phoenix.parse.DerivedTableNode; +import org.apache.phoenix.parse.HintNode; +import org.apache.phoenix.parse.LimitNode; +import org.apache.phoenix.parse.OrderByNode; +import org.apache.phoenix.parse.ParseNode; +import org.apache.phoenix.parse.ParseNodeRewriter; +import org.apache.phoenix.parse.SelectStatement; +import org.apache.phoenix.parse.TableNode; +import org.apache.phoenix.parse.TableWildcardParseNode; +import org.apache.phoenix.parse.WildcardParseNode; +import org.apache.phoenix.util.SchemaUtil; + +import com.google.common.collect.Lists; + +public class SubselectRewriter extends ParseNodeRewriter { + private final String tableAlias; + private final Map<String, ParseNode> aliasMap; + + public static SelectStatement applyPostFilters(SelectStatement statement, List<ParseNode> postFilters, String subqueryAlias) throws SQLException { + if (postFilters.isEmpty()) + return statement; + + // TODO handle this from caller + if (statement.getLimit() != null || (statement.isAggregate() && statement.getGroupBy().isEmpty())) — End diff – This applyPostFilter() method is for subqueries in joins. We can have some conditions in ON clause or WHERE clause to be pushed down into individual join tables, and if the join table is a subquery, we call this method to merge this post-filter into the subquery, as a WHERE condition or a HAVING condition so that in some cases these post-filters can be optimized as pre-filters. Those cases that would throw exceptions from here are very rare, basically like: 1) select ... from A join (select id, b from T limit 10) as B on A.id = B.id where B.b = 'b' 2) select ... from A join (select count c from T) as B on A.a = B.c where B.c > 10 However, this post-filter can be easily handled in joins. I will make another check-in later.
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user maryannxue commented on a diff in the pull request:

        https://github.com/apache/incubator-phoenix/pull/31#discussion_r11864465

        — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java —
        @@ -290,6 +292,9 @@ protected BasicQueryPlan compileSingleQuery(StatementContext context, SelectStat
        ColumnResolver resolver = context.getResolver();
        TableRef tableRef = context.getCurrentTable();
        PTable table = tableRef.getTable();
        + if (table.getType() == PTableType.SUBQUERY)
        + throw new SQLFeatureNotSupportedException("Complex nested queries not supported.");
        — End diff –

        I will file a JIRA. As to examples, actually all those counter cases in DerivedTableIT throw exception from here.

        Show
        githubbot ASF GitHub Bot added a comment - Github user maryannxue commented on a diff in the pull request: https://github.com/apache/incubator-phoenix/pull/31#discussion_r11864465 — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java — @@ -290,6 +292,9 @@ protected BasicQueryPlan compileSingleQuery(StatementContext context, SelectStat ColumnResolver resolver = context.getResolver(); TableRef tableRef = context.getCurrentTable(); PTable table = tableRef.getTable(); + if (table.getType() == PTableType.SUBQUERY) + throw new SQLFeatureNotSupportedException("Complex nested queries not supported."); — End diff – I will file a JIRA. As to examples, actually all those counter cases in DerivedTableIT throw exception from here.
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user JamesRTaylor commented on a diff in the pull request:

        https://github.com/apache/incubator-phoenix/pull/31#discussion_r11862953

        — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java —
        @@ -0,0 +1,242 @@
        +/*
        + * Licensed to the Apache Software Foundation (ASF) under one
        + * or more contributor license agreements. See the NOTICE file
        + * distributed with this work for additional information
        + * regarding copyright ownership. The ASF licenses this file
        + * to you under the Apache License, Version 2.0 (the
        + * "License"); you may not use this file except in compliance
        + * with the License. You may obtain a copy of the License at
        + *
        + * http://www.apache.org/licenses/LICENSE-2.0
        + *
        + * Unless required by applicable law or agreed to in writing, software
        + * distributed under the License is distributed on an "AS IS" BASIS,
        + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
        + * See the License for the specific language governing permissions and
        + * limitations under the License.
        + */
        +
        +package org.apache.phoenix.compile;
        +
        +import java.sql.SQLException;
        +import java.sql.SQLFeatureNotSupportedException;
        +import java.util.Arrays;
        +import java.util.HashMap;
        +import java.util.List;
        +import java.util.Map;
        +
        +import org.apache.phoenix.jdbc.PhoenixConnection;
        +import org.apache.phoenix.parse.AliasedNode;
        +import org.apache.phoenix.parse.ColumnParseNode;
        +import org.apache.phoenix.parse.DerivedTableNode;
        +import org.apache.phoenix.parse.HintNode;
        +import org.apache.phoenix.parse.LimitNode;
        +import org.apache.phoenix.parse.OrderByNode;
        +import org.apache.phoenix.parse.ParseNode;
        +import org.apache.phoenix.parse.ParseNodeRewriter;
        +import org.apache.phoenix.parse.SelectStatement;
        +import org.apache.phoenix.parse.TableNode;
        +import org.apache.phoenix.parse.TableWildcardParseNode;
        +import org.apache.phoenix.parse.WildcardParseNode;
        +import org.apache.phoenix.util.SchemaUtil;
        +
        +import com.google.common.collect.Lists;
        +
        +public class SubselectRewriter extends ParseNodeRewriter {
        + private final String tableAlias;
        + private final Map<String, ParseNode> aliasMap;
        +
        + public static SelectStatement applyPostFilters(SelectStatement statement, List<ParseNode> postFilters, String subqueryAlias) throws SQLException {
        + if (postFilters.isEmpty())
        + return statement;
        +
        + // TODO handle this from caller
        + if (statement.getLimit() != null || (statement.isAggregate() && statement.getGroupBy().isEmpty()))
        — End diff –

        Do you think it's feasible to do another set of changes for the compiler to handle the cases it can for derived tables and joins?

        Show
        githubbot ASF GitHub Bot added a comment - Github user JamesRTaylor commented on a diff in the pull request: https://github.com/apache/incubator-phoenix/pull/31#discussion_r11862953 — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java — @@ -0,0 +1,242 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.phoenix.compile; + +import java.sql.SQLException; +import java.sql.SQLFeatureNotSupportedException; +import java.util.Arrays; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.apache.phoenix.jdbc.PhoenixConnection; +import org.apache.phoenix.parse.AliasedNode; +import org.apache.phoenix.parse.ColumnParseNode; +import org.apache.phoenix.parse.DerivedTableNode; +import org.apache.phoenix.parse.HintNode; +import org.apache.phoenix.parse.LimitNode; +import org.apache.phoenix.parse.OrderByNode; +import org.apache.phoenix.parse.ParseNode; +import org.apache.phoenix.parse.ParseNodeRewriter; +import org.apache.phoenix.parse.SelectStatement; +import org.apache.phoenix.parse.TableNode; +import org.apache.phoenix.parse.TableWildcardParseNode; +import org.apache.phoenix.parse.WildcardParseNode; +import org.apache.phoenix.util.SchemaUtil; + +import com.google.common.collect.Lists; + +public class SubselectRewriter extends ParseNodeRewriter { + private final String tableAlias; + private final Map<String, ParseNode> aliasMap; + + public static SelectStatement applyPostFilters(SelectStatement statement, List<ParseNode> postFilters, String subqueryAlias) throws SQLException { + if (postFilters.isEmpty()) + return statement; + + // TODO handle this from caller + if (statement.getLimit() != null || (statement.isAggregate() && statement.getGroupBy().isEmpty())) — End diff – Do you think it's feasible to do another set of changes for the compiler to handle the cases it can for derived tables and joins?
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user JamesRTaylor commented on a diff in the pull request:

        https://github.com/apache/incubator-phoenix/pull/31#discussion_r11862579

        — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java —
        @@ -290,6 +292,9 @@ protected BasicQueryPlan compileSingleQuery(StatementContext context, SelectStat
        ColumnResolver resolver = context.getResolver();
        TableRef tableRef = context.getCurrentTable();
        PTable table = tableRef.getTable();
        + if (table.getType() == PTableType.SUBQUERY)
        + throw new SQLFeatureNotSupportedException("Complex nested queries not supported.");
        — End diff –

        Same comment here - can you add a code comment and JIRA for this? An example query that would produce this exception would be helpful too.

        Show
        githubbot ASF GitHub Bot added a comment - Github user JamesRTaylor commented on a diff in the pull request: https://github.com/apache/incubator-phoenix/pull/31#discussion_r11862579 — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java — @@ -290,6 +292,9 @@ protected BasicQueryPlan compileSingleQuery(StatementContext context, SelectStat ColumnResolver resolver = context.getResolver(); TableRef tableRef = context.getCurrentTable(); PTable table = tableRef.getTable(); + if (table.getType() == PTableType.SUBQUERY) + throw new SQLFeatureNotSupportedException("Complex nested queries not supported."); — End diff – Same comment here - can you add a code comment and JIRA for this? An example query that would produce this exception would be helpful too.
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user JamesRTaylor commented on a diff in the pull request:

        https://github.com/apache/incubator-phoenix/pull/31#discussion_r11862513

        — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java —
        @@ -0,0 +1,242 @@
        +/*
        + * Licensed to the Apache Software Foundation (ASF) under one
        + * or more contributor license agreements. See the NOTICE file
        + * distributed with this work for additional information
        + * regarding copyright ownership. The ASF licenses this file
        + * to you under the Apache License, Version 2.0 (the
        + * "License"); you may not use this file except in compliance
        + * with the License. You may obtain a copy of the License at
        + *
        + * http://www.apache.org/licenses/LICENSE-2.0
        + *
        + * Unless required by applicable law or agreed to in writing, software
        + * distributed under the License is distributed on an "AS IS" BASIS,
        + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
        + * See the License for the specific language governing permissions and
        + * limitations under the License.
        + */
        +
        +package org.apache.phoenix.compile;
        +
        +import java.sql.SQLException;
        +import java.sql.SQLFeatureNotSupportedException;
        +import java.util.Arrays;
        +import java.util.HashMap;
        +import java.util.List;
        +import java.util.Map;
        +
        +import org.apache.phoenix.jdbc.PhoenixConnection;
        +import org.apache.phoenix.parse.AliasedNode;
        +import org.apache.phoenix.parse.ColumnParseNode;
        +import org.apache.phoenix.parse.DerivedTableNode;
        +import org.apache.phoenix.parse.HintNode;
        +import org.apache.phoenix.parse.LimitNode;
        +import org.apache.phoenix.parse.OrderByNode;
        +import org.apache.phoenix.parse.ParseNode;
        +import org.apache.phoenix.parse.ParseNodeRewriter;
        +import org.apache.phoenix.parse.SelectStatement;
        +import org.apache.phoenix.parse.TableNode;
        +import org.apache.phoenix.parse.TableWildcardParseNode;
        +import org.apache.phoenix.parse.WildcardParseNode;
        +import org.apache.phoenix.util.SchemaUtil;
        +
        +import com.google.common.collect.Lists;
        +
        +public class SubselectRewriter extends ParseNodeRewriter {
        + private final String tableAlias;
        + private final Map<String, ParseNode> aliasMap;
        +
        + public static SelectStatement applyPostFilters(SelectStatement statement, List<ParseNode> postFilters, String subqueryAlias) throws SQLException {
        + if (postFilters.isEmpty())
        + return statement;
        +
        + // TODO handle this from caller
        + if (statement.getLimit() != null || (statement.isAggregate() && statement.getGroupBy().isEmpty()))
        — End diff –

        Right, I saw that test case. So under what circumstances would statement.getLimit() != null be true? I think it'd be good to capture the cases we're currently not handling in code comments and in JIRAs.

        Show
        githubbot ASF GitHub Bot added a comment - Github user JamesRTaylor commented on a diff in the pull request: https://github.com/apache/incubator-phoenix/pull/31#discussion_r11862513 — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java — @@ -0,0 +1,242 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.phoenix.compile; + +import java.sql.SQLException; +import java.sql.SQLFeatureNotSupportedException; +import java.util.Arrays; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.apache.phoenix.jdbc.PhoenixConnection; +import org.apache.phoenix.parse.AliasedNode; +import org.apache.phoenix.parse.ColumnParseNode; +import org.apache.phoenix.parse.DerivedTableNode; +import org.apache.phoenix.parse.HintNode; +import org.apache.phoenix.parse.LimitNode; +import org.apache.phoenix.parse.OrderByNode; +import org.apache.phoenix.parse.ParseNode; +import org.apache.phoenix.parse.ParseNodeRewriter; +import org.apache.phoenix.parse.SelectStatement; +import org.apache.phoenix.parse.TableNode; +import org.apache.phoenix.parse.TableWildcardParseNode; +import org.apache.phoenix.parse.WildcardParseNode; +import org.apache.phoenix.util.SchemaUtil; + +import com.google.common.collect.Lists; + +public class SubselectRewriter extends ParseNodeRewriter { + private final String tableAlias; + private final Map<String, ParseNode> aliasMap; + + public static SelectStatement applyPostFilters(SelectStatement statement, List<ParseNode> postFilters, String subqueryAlias) throws SQLException { + if (postFilters.isEmpty()) + return statement; + + // TODO handle this from caller + if (statement.getLimit() != null || (statement.isAggregate() && statement.getGroupBy().isEmpty())) — End diff – Right, I saw that test case. So under what circumstances would statement.getLimit() != null be true? I think it'd be good to capture the cases we're currently not handling in code comments and in JIRAs.
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user maryannxue commented on the pull request:

        https://github.com/apache/incubator-phoenix/pull/31#issuecomment-41050145

        Sure! @JamesRTaylor.

        Show
        githubbot ASF GitHub Bot added a comment - Github user maryannxue commented on the pull request: https://github.com/apache/incubator-phoenix/pull/31#issuecomment-41050145 Sure! @JamesRTaylor.
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user maryannxue commented on a diff in the pull request:

        https://github.com/apache/incubator-phoenix/pull/31#discussion_r11857151

        — Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java —
        @@ -0,0 +1,632 @@
        +/*
        + * Licensed to the Apache Software Foundation (ASF) under one
        + * or more contributor license agreements. See the NOTICE file
        + * distributed with this work for additional information
        + * regarding copyright ownership. The ASF licenses this file
        + * to you under the Apache License, Version 2.0 (the
        + * "License"); you may not use this file except in compliance
        + * with the License. You may obtain a copy of the License at
        + *
        + * http://www.apache.org/licenses/LICENSE-2.0
        + *
        + * Unless required by applicable law or agreed to in writing, software
        + * distributed under the License is distributed on an "AS IS" BASIS,
        + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
        + * See the License for the specific language governing permissions and
        + * limitations under the License.
        + */
        +
        +package org.apache.phoenix.end2end;
        +
        +import static org.apache.phoenix.util.TestUtil.A_VALUE;
        +import static org.apache.phoenix.util.TestUtil.B_VALUE;
        +import static org.apache.phoenix.util.TestUtil.C_VALUE;
        +import static org.apache.phoenix.util.TestUtil.E_VALUE;
        +import static org.apache.phoenix.util.TestUtil.ROW1;
        +import static org.apache.phoenix.util.TestUtil.ROW2;
        +import static org.apache.phoenix.util.TestUtil.ROW3;
        +import static org.apache.phoenix.util.TestUtil.ROW4;
        +import static org.apache.phoenix.util.TestUtil.ROW5;
        +import static org.apache.phoenix.util.TestUtil.ROW6;
        +import static org.apache.phoenix.util.TestUtil.ROW7;
        +import static org.apache.phoenix.util.TestUtil.ROW8;
        +import static org.apache.phoenix.util.TestUtil.ROW9;
        +import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
        +import static org.junit.Assert.assertEquals;
        +import static org.junit.Assert.assertFalse;
        +import static org.junit.Assert.assertTrue;
        +import static org.junit.Assert.fail;
        +
        +import java.sql.Connection;
        +import java.sql.DriverManager;
        +import java.sql.PreparedStatement;
        +import java.sql.ResultSet;
        +import java.sql.SQLFeatureNotSupportedException;
        +import java.util.Properties;
        +
        +import org.apache.phoenix.util.PhoenixRuntime;
        +import org.junit.Test;
        +
        +public class DerivedTableIT extends BaseClientManagedTimeIT {
        — End diff –

        Ok, I'll make it parameterized and keep it separate as well.

        Show
        githubbot ASF GitHub Bot added a comment - Github user maryannxue commented on a diff in the pull request: https://github.com/apache/incubator-phoenix/pull/31#discussion_r11857151 — Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java — @@ -0,0 +1,632 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.phoenix.end2end; + +import static org.apache.phoenix.util.TestUtil.A_VALUE; +import static org.apache.phoenix.util.TestUtil.B_VALUE; +import static org.apache.phoenix.util.TestUtil.C_VALUE; +import static org.apache.phoenix.util.TestUtil.E_VALUE; +import static org.apache.phoenix.util.TestUtil.ROW1; +import static org.apache.phoenix.util.TestUtil.ROW2; +import static org.apache.phoenix.util.TestUtil.ROW3; +import static org.apache.phoenix.util.TestUtil.ROW4; +import static org.apache.phoenix.util.TestUtil.ROW5; +import static org.apache.phoenix.util.TestUtil.ROW6; +import static org.apache.phoenix.util.TestUtil.ROW7; +import static org.apache.phoenix.util.TestUtil.ROW8; +import static org.apache.phoenix.util.TestUtil.ROW9; +import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertTrue; +import static org.junit.Assert.fail; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLFeatureNotSupportedException; +import java.util.Properties; + +import org.apache.phoenix.util.PhoenixRuntime; +import org.junit.Test; + +public class DerivedTableIT extends BaseClientManagedTimeIT { — End diff – Ok, I'll make it parameterized and keep it separate as well.
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user maryannxue commented on a diff in the pull request:

        https://github.com/apache/incubator-phoenix/pull/31#discussion_r11857047

        — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java —
        @@ -290,6 +292,9 @@ protected BasicQueryPlan compileSingleQuery(StatementContext context, SelectStat
        ColumnResolver resolver = context.getResolver();
        TableRef tableRef = context.getCurrentTable();
        PTable table = tableRef.getTable();
        + if (table.getType() == PTableType.SUBQUERY)
        + throw new SQLFeatureNotSupportedException("Complex nested queries not supported.");
        — End diff –

        No, this is just subqueries (derived tables) in the from clause that SubselectRewriter is unable to flatten. We don't have parser support for correlated subqueries so far.

        Show
        githubbot ASF GitHub Bot added a comment - Github user maryannxue commented on a diff in the pull request: https://github.com/apache/incubator-phoenix/pull/31#discussion_r11857047 — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java — @@ -290,6 +292,9 @@ protected BasicQueryPlan compileSingleQuery(StatementContext context, SelectStat ColumnResolver resolver = context.getResolver(); TableRef tableRef = context.getCurrentTable(); PTable table = tableRef.getTable(); + if (table.getType() == PTableType.SUBQUERY) + throw new SQLFeatureNotSupportedException("Complex nested queries not supported."); — End diff – No, this is just subqueries (derived tables) in the from clause that SubselectRewriter is unable to flatten. We don't have parser support for correlated subqueries so far.
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user maryannxue commented on a diff in the pull request:

        https://github.com/apache/incubator-phoenix/pull/31#discussion_r11856917

        — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java —
        @@ -0,0 +1,242 @@
        +/*
        + * Licensed to the Apache Software Foundation (ASF) under one
        + * or more contributor license agreements. See the NOTICE file
        + * distributed with this work for additional information
        + * regarding copyright ownership. The ASF licenses this file
        + * to you under the Apache License, Version 2.0 (the
        + * "License"); you may not use this file except in compliance
        + * with the License. You may obtain a copy of the License at
        + *
        + * http://www.apache.org/licenses/LICENSE-2.0
        + *
        + * Unless required by applicable law or agreed to in writing, software
        + * distributed under the License is distributed on an "AS IS" BASIS,
        + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
        + * See the License for the specific language governing permissions and
        + * limitations under the License.
        + */
        +
        +package org.apache.phoenix.compile;
        +
        +import java.sql.SQLException;
        +import java.sql.SQLFeatureNotSupportedException;
        +import java.util.Arrays;
        +import java.util.HashMap;
        +import java.util.List;
        +import java.util.Map;
        +
        +import org.apache.phoenix.jdbc.PhoenixConnection;
        +import org.apache.phoenix.parse.AliasedNode;
        +import org.apache.phoenix.parse.ColumnParseNode;
        +import org.apache.phoenix.parse.DerivedTableNode;
        +import org.apache.phoenix.parse.HintNode;
        +import org.apache.phoenix.parse.LimitNode;
        +import org.apache.phoenix.parse.OrderByNode;
        +import org.apache.phoenix.parse.ParseNode;
        +import org.apache.phoenix.parse.ParseNodeRewriter;
        +import org.apache.phoenix.parse.SelectStatement;
        +import org.apache.phoenix.parse.TableNode;
        +import org.apache.phoenix.parse.TableWildcardParseNode;
        +import org.apache.phoenix.parse.WildcardParseNode;
        +import org.apache.phoenix.util.SchemaUtil;
        +
        +import com.google.common.collect.Lists;
        +
        +public class SubselectRewriter extends ParseNodeRewriter {
        + private final String tableAlias;
        + private final Map<String, ParseNode> aliasMap;
        +
        + public static SelectStatement applyPostFilters(SelectStatement statement, List<ParseNode> postFilters, String subqueryAlias) throws SQLException {
        + if (postFilters.isEmpty())
        + return statement;
        +
        + // TODO handle this from caller
        + if (statement.getLimit() != null || (statement.isAggregate() && statement.getGroupBy().isEmpty()))
        — End diff –

        Yes, limit with literal numbers in both outer and inner queries can be handled. Please see "testDerivedTableWithLimit()".
        Yes, agree. It would just require the compiler to handle derived tables. Right now we only support cases that can be handled by "pre-processing", i.e., rewriting; and let QueryCompiler to throw a SQLFeatureNotSupportedException if the from node is a derived table.

        Show
        githubbot ASF GitHub Bot added a comment - Github user maryannxue commented on a diff in the pull request: https://github.com/apache/incubator-phoenix/pull/31#discussion_r11856917 — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java — @@ -0,0 +1,242 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.phoenix.compile; + +import java.sql.SQLException; +import java.sql.SQLFeatureNotSupportedException; +import java.util.Arrays; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.apache.phoenix.jdbc.PhoenixConnection; +import org.apache.phoenix.parse.AliasedNode; +import org.apache.phoenix.parse.ColumnParseNode; +import org.apache.phoenix.parse.DerivedTableNode; +import org.apache.phoenix.parse.HintNode; +import org.apache.phoenix.parse.LimitNode; +import org.apache.phoenix.parse.OrderByNode; +import org.apache.phoenix.parse.ParseNode; +import org.apache.phoenix.parse.ParseNodeRewriter; +import org.apache.phoenix.parse.SelectStatement; +import org.apache.phoenix.parse.TableNode; +import org.apache.phoenix.parse.TableWildcardParseNode; +import org.apache.phoenix.parse.WildcardParseNode; +import org.apache.phoenix.util.SchemaUtil; + +import com.google.common.collect.Lists; + +public class SubselectRewriter extends ParseNodeRewriter { + private final String tableAlias; + private final Map<String, ParseNode> aliasMap; + + public static SelectStatement applyPostFilters(SelectStatement statement, List<ParseNode> postFilters, String subqueryAlias) throws SQLException { + if (postFilters.isEmpty()) + return statement; + + // TODO handle this from caller + if (statement.getLimit() != null || (statement.isAggregate() && statement.getGroupBy().isEmpty())) — End diff – Yes, limit with literal numbers in both outer and inner queries can be handled. Please see "testDerivedTableWithLimit()". Yes, agree. It would just require the compiler to handle derived tables. Right now we only support cases that can be handled by "pre-processing", i.e., rewriting; and let QueryCompiler to throw a SQLFeatureNotSupportedException if the from node is a derived table.
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user JamesRTaylor commented on the pull request:

        https://github.com/apache/incubator-phoenix/pull/31#issuecomment-41003342

        +1. This is fantastic, @maryannxue! Let's file separate JIRAs for each type of query that's not yet supported. The list is getting smaller!

        Show
        githubbot ASF GitHub Bot added a comment - Github user JamesRTaylor commented on the pull request: https://github.com/apache/incubator-phoenix/pull/31#issuecomment-41003342 +1. This is fantastic, @maryannxue! Let's file separate JIRAs for each type of query that's not yet supported. The list is getting smaller!
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user JamesRTaylor commented on a diff in the pull request:

        https://github.com/apache/incubator-phoenix/pull/31#discussion_r11838107

        — Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java —
        @@ -0,0 +1,632 @@
        +/*
        + * Licensed to the Apache Software Foundation (ASF) under one
        + * or more contributor license agreements. See the NOTICE file
        + * distributed with this work for additional information
        + * regarding copyright ownership. The ASF licenses this file
        + * to you under the Apache License, Version 2.0 (the
        + * "License"); you may not use this file except in compliance
        + * with the License. You may obtain a copy of the License at
        + *
        + * http://www.apache.org/licenses/LICENSE-2.0
        + *
        + * Unless required by applicable law or agreed to in writing, software
        + * distributed under the License is distributed on an "AS IS" BASIS,
        + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
        + * See the License for the specific language governing permissions and
        + * limitations under the License.
        + */
        +
        +package org.apache.phoenix.end2end;
        +
        +import static org.apache.phoenix.util.TestUtil.A_VALUE;
        +import static org.apache.phoenix.util.TestUtil.B_VALUE;
        +import static org.apache.phoenix.util.TestUtil.C_VALUE;
        +import static org.apache.phoenix.util.TestUtil.E_VALUE;
        +import static org.apache.phoenix.util.TestUtil.ROW1;
        +import static org.apache.phoenix.util.TestUtil.ROW2;
        +import static org.apache.phoenix.util.TestUtil.ROW3;
        +import static org.apache.phoenix.util.TestUtil.ROW4;
        +import static org.apache.phoenix.util.TestUtil.ROW5;
        +import static org.apache.phoenix.util.TestUtil.ROW6;
        +import static org.apache.phoenix.util.TestUtil.ROW7;
        +import static org.apache.phoenix.util.TestUtil.ROW8;
        +import static org.apache.phoenix.util.TestUtil.ROW9;
        +import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
        +import static org.junit.Assert.assertEquals;
        +import static org.junit.Assert.assertFalse;
        +import static org.junit.Assert.assertTrue;
        +import static org.junit.Assert.fail;
        +
        +import java.sql.Connection;
        +import java.sql.DriverManager;
        +import java.sql.PreparedStatement;
        +import java.sql.ResultSet;
        +import java.sql.SQLFeatureNotSupportedException;
        +import java.util.Properties;
        +
        +import org.apache.phoenix.util.PhoenixRuntime;
        +import org.junit.Test;
        +
        +public class DerivedTableIT extends BaseClientManagedTimeIT {
        — End diff –

        One easy way would be to add these tests to QueryIT instead, though I like this separation too.

        Show
        githubbot ASF GitHub Bot added a comment - Github user JamesRTaylor commented on a diff in the pull request: https://github.com/apache/incubator-phoenix/pull/31#discussion_r11838107 — Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java — @@ -0,0 +1,632 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.phoenix.end2end; + +import static org.apache.phoenix.util.TestUtil.A_VALUE; +import static org.apache.phoenix.util.TestUtil.B_VALUE; +import static org.apache.phoenix.util.TestUtil.C_VALUE; +import static org.apache.phoenix.util.TestUtil.E_VALUE; +import static org.apache.phoenix.util.TestUtil.ROW1; +import static org.apache.phoenix.util.TestUtil.ROW2; +import static org.apache.phoenix.util.TestUtil.ROW3; +import static org.apache.phoenix.util.TestUtil.ROW4; +import static org.apache.phoenix.util.TestUtil.ROW5; +import static org.apache.phoenix.util.TestUtil.ROW6; +import static org.apache.phoenix.util.TestUtil.ROW7; +import static org.apache.phoenix.util.TestUtil.ROW8; +import static org.apache.phoenix.util.TestUtil.ROW9; +import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertTrue; +import static org.junit.Assert.fail; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLFeatureNotSupportedException; +import java.util.Properties; + +import org.apache.phoenix.util.PhoenixRuntime; +import org.junit.Test; + +public class DerivedTableIT extends BaseClientManagedTimeIT { — End diff – One easy way would be to add these tests to QueryIT instead, though I like this separation too.
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user JamesRTaylor commented on a diff in the pull request:

        https://github.com/apache/incubator-phoenix/pull/31#discussion_r11838095

        — Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java —
        @@ -0,0 +1,632 @@
        +/*
        + * Licensed to the Apache Software Foundation (ASF) under one
        + * or more contributor license agreements. See the NOTICE file
        + * distributed with this work for additional information
        + * regarding copyright ownership. The ASF licenses this file
        + * to you under the Apache License, Version 2.0 (the
        + * "License"); you may not use this file except in compliance
        + * with the License. You may obtain a copy of the License at
        + *
        + * http://www.apache.org/licenses/LICENSE-2.0
        + *
        + * Unless required by applicable law or agreed to in writing, software
        + * distributed under the License is distributed on an "AS IS" BASIS,
        + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
        + * See the License for the specific language governing permissions and
        + * limitations under the License.
        + */
        +
        +package org.apache.phoenix.end2end;
        +
        +import static org.apache.phoenix.util.TestUtil.A_VALUE;
        +import static org.apache.phoenix.util.TestUtil.B_VALUE;
        +import static org.apache.phoenix.util.TestUtil.C_VALUE;
        +import static org.apache.phoenix.util.TestUtil.E_VALUE;
        +import static org.apache.phoenix.util.TestUtil.ROW1;
        +import static org.apache.phoenix.util.TestUtil.ROW2;
        +import static org.apache.phoenix.util.TestUtil.ROW3;
        +import static org.apache.phoenix.util.TestUtil.ROW4;
        +import static org.apache.phoenix.util.TestUtil.ROW5;
        +import static org.apache.phoenix.util.TestUtil.ROW6;
        +import static org.apache.phoenix.util.TestUtil.ROW7;
        +import static org.apache.phoenix.util.TestUtil.ROW8;
        +import static org.apache.phoenix.util.TestUtil.ROW9;
        +import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
        +import static org.junit.Assert.assertEquals;
        +import static org.junit.Assert.assertFalse;
        +import static org.junit.Assert.assertTrue;
        +import static org.junit.Assert.fail;
        +
        +import java.sql.Connection;
        +import java.sql.DriverManager;
        +import java.sql.PreparedStatement;
        +import java.sql.ResultSet;
        +import java.sql.SQLFeatureNotSupportedException;
        +import java.util.Properties;
        +
        +import org.apache.phoenix.util.PhoenixRuntime;
        +import org.junit.Test;
        +
        +public class DerivedTableIT extends BaseClientManagedTimeIT {
        — End diff –

        Might be nice to parameterize this and test when an index may be used. Is that code path tested?

        Show
        githubbot ASF GitHub Bot added a comment - Github user JamesRTaylor commented on a diff in the pull request: https://github.com/apache/incubator-phoenix/pull/31#discussion_r11838095 — Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java — @@ -0,0 +1,632 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.phoenix.end2end; + +import static org.apache.phoenix.util.TestUtil.A_VALUE; +import static org.apache.phoenix.util.TestUtil.B_VALUE; +import static org.apache.phoenix.util.TestUtil.C_VALUE; +import static org.apache.phoenix.util.TestUtil.E_VALUE; +import static org.apache.phoenix.util.TestUtil.ROW1; +import static org.apache.phoenix.util.TestUtil.ROW2; +import static org.apache.phoenix.util.TestUtil.ROW3; +import static org.apache.phoenix.util.TestUtil.ROW4; +import static org.apache.phoenix.util.TestUtil.ROW5; +import static org.apache.phoenix.util.TestUtil.ROW6; +import static org.apache.phoenix.util.TestUtil.ROW7; +import static org.apache.phoenix.util.TestUtil.ROW8; +import static org.apache.phoenix.util.TestUtil.ROW9; +import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertTrue; +import static org.junit.Assert.fail; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLFeatureNotSupportedException; +import java.util.Properties; + +import org.apache.phoenix.util.PhoenixRuntime; +import org.junit.Test; + +public class DerivedTableIT extends BaseClientManagedTimeIT { — End diff – Might be nice to parameterize this and test when an index may be used. Is that code path tested?
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user JamesRTaylor commented on a diff in the pull request:

        https://github.com/apache/incubator-phoenix/pull/31#discussion_r11838044

        — Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java —
        @@ -0,0 +1,632 @@
        +/*
        + * Licensed to the Apache Software Foundation (ASF) under one
        + * or more contributor license agreements. See the NOTICE file
        + * distributed with this work for additional information
        + * regarding copyright ownership. The ASF licenses this file
        + * to you under the Apache License, Version 2.0 (the
        + * "License"); you may not use this file except in compliance
        + * with the License. You may obtain a copy of the License at
        + *
        + * http://www.apache.org/licenses/LICENSE-2.0
        + *
        + * Unless required by applicable law or agreed to in writing, software
        + * distributed under the License is distributed on an "AS IS" BASIS,
        + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
        + * See the License for the specific language governing permissions and
        + * limitations under the License.
        + */
        +
        +package org.apache.phoenix.end2end;
        +
        +import static org.apache.phoenix.util.TestUtil.A_VALUE;
        +import static org.apache.phoenix.util.TestUtil.B_VALUE;
        +import static org.apache.phoenix.util.TestUtil.C_VALUE;
        +import static org.apache.phoenix.util.TestUtil.E_VALUE;
        +import static org.apache.phoenix.util.TestUtil.ROW1;
        +import static org.apache.phoenix.util.TestUtil.ROW2;
        +import static org.apache.phoenix.util.TestUtil.ROW3;
        +import static org.apache.phoenix.util.TestUtil.ROW4;
        +import static org.apache.phoenix.util.TestUtil.ROW5;
        +import static org.apache.phoenix.util.TestUtil.ROW6;
        +import static org.apache.phoenix.util.TestUtil.ROW7;
        +import static org.apache.phoenix.util.TestUtil.ROW8;
        +import static org.apache.phoenix.util.TestUtil.ROW9;
        +import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
        +import static org.junit.Assert.assertEquals;
        +import static org.junit.Assert.assertFalse;
        +import static org.junit.Assert.assertTrue;
        +import static org.junit.Assert.fail;
        +
        +import java.sql.Connection;
        +import java.sql.DriverManager;
        +import java.sql.PreparedStatement;
        +import java.sql.ResultSet;
        +import java.sql.SQLFeatureNotSupportedException;
        +import java.util.Properties;
        +
        +import org.apache.phoenix.util.PhoenixRuntime;
        +import org.junit.Test;
        +
        +public class DerivedTableIT extends BaseClientManagedTimeIT {
        — End diff –

        Nice tests!

        Show
        githubbot ASF GitHub Bot added a comment - Github user JamesRTaylor commented on a diff in the pull request: https://github.com/apache/incubator-phoenix/pull/31#discussion_r11838044 — Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java — @@ -0,0 +1,632 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.phoenix.end2end; + +import static org.apache.phoenix.util.TestUtil.A_VALUE; +import static org.apache.phoenix.util.TestUtil.B_VALUE; +import static org.apache.phoenix.util.TestUtil.C_VALUE; +import static org.apache.phoenix.util.TestUtil.E_VALUE; +import static org.apache.phoenix.util.TestUtil.ROW1; +import static org.apache.phoenix.util.TestUtil.ROW2; +import static org.apache.phoenix.util.TestUtil.ROW3; +import static org.apache.phoenix.util.TestUtil.ROW4; +import static org.apache.phoenix.util.TestUtil.ROW5; +import static org.apache.phoenix.util.TestUtil.ROW6; +import static org.apache.phoenix.util.TestUtil.ROW7; +import static org.apache.phoenix.util.TestUtil.ROW8; +import static org.apache.phoenix.util.TestUtil.ROW9; +import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertTrue; +import static org.junit.Assert.fail; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLFeatureNotSupportedException; +import java.util.Properties; + +import org.apache.phoenix.util.PhoenixRuntime; +import org.junit.Test; + +public class DerivedTableIT extends BaseClientManagedTimeIT { — End diff – Nice tests!
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user JamesRTaylor commented on a diff in the pull request:

        https://github.com/apache/incubator-phoenix/pull/31#discussion_r11837911

        — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java —
        @@ -290,6 +292,9 @@ protected BasicQueryPlan compileSingleQuery(StatementContext context, SelectStat
        ColumnResolver resolver = context.getResolver();
        TableRef tableRef = context.getCurrentTable();
        PTable table = tableRef.getTable();
        + if (table.getType() == PTableType.SUBQUERY)
        + throw new SQLFeatureNotSupportedException("Complex nested queries not supported.");
        — End diff –

        Add a bit more docs, please. Is this a correlated subquery?

        Show
        githubbot ASF GitHub Bot added a comment - Github user JamesRTaylor commented on a diff in the pull request: https://github.com/apache/incubator-phoenix/pull/31#discussion_r11837911 — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java — @@ -290,6 +292,9 @@ protected BasicQueryPlan compileSingleQuery(StatementContext context, SelectStat ColumnResolver resolver = context.getResolver(); TableRef tableRef = context.getCurrentTable(); PTable table = tableRef.getTable(); + if (table.getType() == PTableType.SUBQUERY) + throw new SQLFeatureNotSupportedException("Complex nested queries not supported."); — End diff – Add a bit more docs, please. Is this a correlated subquery?
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user JamesRTaylor commented on a diff in the pull request:

        https://github.com/apache/incubator-phoenix/pull/31#discussion_r11837900

        — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java —
        @@ -0,0 +1,242 @@
        +/*
        + * Licensed to the Apache Software Foundation (ASF) under one
        + * or more contributor license agreements. See the NOTICE file
        + * distributed with this work for additional information
        + * regarding copyright ownership. The ASF licenses this file
        + * to you under the Apache License, Version 2.0 (the
        + * "License"); you may not use this file except in compliance
        + * with the License. You may obtain a copy of the License at
        + *
        + * http://www.apache.org/licenses/LICENSE-2.0
        + *
        + * Unless required by applicable law or agreed to in writing, software
        + * distributed under the License is distributed on an "AS IS" BASIS,
        + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
        + * See the License for the specific language governing permissions and
        + * limitations under the License.
        + */
        +
        +package org.apache.phoenix.compile;
        +
        +import java.sql.SQLException;
        +import java.sql.SQLFeatureNotSupportedException;
        +import java.util.Arrays;
        +import java.util.HashMap;
        +import java.util.List;
        +import java.util.Map;
        +
        +import org.apache.phoenix.jdbc.PhoenixConnection;
        +import org.apache.phoenix.parse.AliasedNode;
        +import org.apache.phoenix.parse.ColumnParseNode;
        +import org.apache.phoenix.parse.DerivedTableNode;
        +import org.apache.phoenix.parse.HintNode;
        +import org.apache.phoenix.parse.LimitNode;
        +import org.apache.phoenix.parse.OrderByNode;
        +import org.apache.phoenix.parse.ParseNode;
        +import org.apache.phoenix.parse.ParseNodeRewriter;
        +import org.apache.phoenix.parse.SelectStatement;
        +import org.apache.phoenix.parse.TableNode;
        +import org.apache.phoenix.parse.TableWildcardParseNode;
        +import org.apache.phoenix.parse.WildcardParseNode;
        +import org.apache.phoenix.util.SchemaUtil;
        +
        +import com.google.common.collect.Lists;
        +
        +public class SubselectRewriter extends ParseNodeRewriter {
        + private final String tableAlias;
        + private final Map<String, ParseNode> aliasMap;
        +
        + public static SelectStatement applyPostFilters(SelectStatement statement, List<ParseNode> postFilters, String subqueryAlias) throws SQLException {
        + if (postFilters.isEmpty())
        + return statement;
        +
        + // TODO handle this from caller
        + if (statement.getLimit() != null || (statement.isAggregate() && statement.getGroupBy().isEmpty()))
        — End diff –

        Let's document these cases we're not currently handling. Is the limit one where the outer limit is less than the inner limit? Can we handle that?

        The second case is an aggregate over a aggregate? We can't handle that, but it wouldn't be too difficult (if we do it client side in-memory).

        Show
        githubbot ASF GitHub Bot added a comment - Github user JamesRTaylor commented on a diff in the pull request: https://github.com/apache/incubator-phoenix/pull/31#discussion_r11837900 — Diff: phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java — @@ -0,0 +1,242 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.phoenix.compile; + +import java.sql.SQLException; +import java.sql.SQLFeatureNotSupportedException; +import java.util.Arrays; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.apache.phoenix.jdbc.PhoenixConnection; +import org.apache.phoenix.parse.AliasedNode; +import org.apache.phoenix.parse.ColumnParseNode; +import org.apache.phoenix.parse.DerivedTableNode; +import org.apache.phoenix.parse.HintNode; +import org.apache.phoenix.parse.LimitNode; +import org.apache.phoenix.parse.OrderByNode; +import org.apache.phoenix.parse.ParseNode; +import org.apache.phoenix.parse.ParseNodeRewriter; +import org.apache.phoenix.parse.SelectStatement; +import org.apache.phoenix.parse.TableNode; +import org.apache.phoenix.parse.TableWildcardParseNode; +import org.apache.phoenix.parse.WildcardParseNode; +import org.apache.phoenix.util.SchemaUtil; + +import com.google.common.collect.Lists; + +public class SubselectRewriter extends ParseNodeRewriter { + private final String tableAlias; + private final Map<String, ParseNode> aliasMap; + + public static SelectStatement applyPostFilters(SelectStatement statement, List<ParseNode> postFilters, String subqueryAlias) throws SQLException { + if (postFilters.isEmpty()) + return statement; + + // TODO handle this from caller + if (statement.getLimit() != null || (statement.isAggregate() && statement.getGroupBy().isEmpty())) — End diff – Let's document these cases we're not currently handling. Is the limit one where the outer limit is less than the inner limit? Can we handle that? The second case is an aggregate over a aggregate? We can't handle that, but it wouldn't be too difficult (if we do it client side in-memory).
        Hide
        githubbot ASF GitHub Bot added a comment -

        GitHub user maryannxue opened a pull request:

        https://github.com/apache/incubator-phoenix/pull/31

        PHOENIX-136 Support derived tables

        You can merge this pull request into a Git repository by running:

        $ git pull https://github.com/maryannxue/incubator-phoenix master

        Alternatively you can review and apply these changes as the patch at:

        https://github.com/apache/incubator-phoenix/pull/31.patch

        To close this pull request, make a commit to your master/trunk branch
        with (at least) the following in the commit message:

        This closes #31


        commit 05b584181fe36102feca765886fcda75b770b2b2
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-02-12T21:41:53Z

        Fix PHOENIX-44 Cannot create index on tables with case-sensitive schema names

        commit 02af57c96476c9d205d07ba96974c49188a282fa
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-02-21T02:54:17Z

        Merge remote-tracking branch 'asf/master'

        commit bd20346da5336d3d240493fdbfbcd5cc03280810
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-02-21T03:08:23Z

        PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries

        commit 9a2ced8936d6cedbc44a4ed2de2c6f72f4f4307f
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-02-21T04:59:29Z

        PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries

        commit 20a97904e48829f8b412bcfb0ac2084259133126
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-02-21T05:53:21Z

        PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries

        commit 7568516fabfb416f5dcbfa89468b839963802a87
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-02-21T22:14:14Z

        Revert "PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries"

        This reverts commit 20a97904e48829f8b412bcfb0ac2084259133126.

        commit 3fb95819959d22deb2c03e895d976e7d933007f2
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-02-21T22:14:56Z

        Revert "PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries"

        This reverts commit 9a2ced8936d6cedbc44a4ed2de2c6f72f4f4307f.

        commit ca0b55d048a6c547a5d6b081dd84dea01011e5c1
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-02-21T22:16:13Z

        Revert "PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries"

        This reverts commit bd20346da5336d3d240493fdbfbcd5cc03280810.

        commit c4df65791a163070362662aad7ce5916faa3c3d7
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-02-21T22:18:13Z

        Merge remote-tracking branch 'asf/master'

        commit 9b5df132480b6bd25c0c33dd818278a5fc1b3f55
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-02-21T22:22:38Z

        PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries

        commit 3e677489c0cb811c1bb3d266581330fe11fdd674
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-02-22T00:17:15Z

        PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries

        commit 776c1a11b286ebd358f9111f3ff1c99c3f7c7883
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-02-27T18:41:58Z

        Merge remote-tracking branch 'asf/master'

        commit 6d26e78096091624502d4382de23f655626bca58
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-02-28T19:36:38Z

        Merge remote-tracking branch 'asf/master'

        commit df561c8d8ab59651d7e797dc0da501cceb4c6030
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-02-28T19:48:06Z

        PHOENIX-85 Refine error handling in HashJoinPlan execution and add warnings

        commit 53631fa509e2a660241c717a4eb0c131b251ff5d
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-02-28T23:42:14Z

        PHOENIX-85 Refine error handling in HashJoinPlan execution and add warnings

        commit 4fb71128f6ac961acbba7db9c3041c53bd18a64a
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-03-01T01:53:26Z

        Merge remote-tracking branch 'asf/master'

        commit 6a800b53abc6b6dac24daff1eae74100d6c73b97
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-03-01T02:10:20Z

        Merge remote-tracking branch 'asf/master'

        commit af32148215ec4cb443ba9d79c705c94196f1ff03
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-03-03T01:26:48Z

        Merge remote-tracking branch 'asf/master'

        commit 012018d2f58cfb35f7574301c68314ead2f31b9d
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-03-03T01:30:49Z

        PHOENIX-89 Statements are not closed using PhoenixConnection.executeStatements()

        commit c4bd34ff11f75d00bb74008a03247889b3b0160a
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-03-31T01:52:33Z

        Merge remote-tracking branch 'asf/master'

        commit 8fce312e63138f23c1befcf3a7dbbb573275eaf1
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-03-31T01:59:00Z

        PHOENIX-71 Support sub-joins

        commit b1d9340e36f750c66b993eb5e8571371f36ca9f9
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-04-01T00:19:57Z

        PHOENIX-71 Support sub-joins

        commit a3c8f7572a5aa3704ec817f5c42d691df2b631f6
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-04-01T00:43:04Z

        PHOENIX-905 Wrong calculation of maxSetBit in ValueBitSet#or(ImmutableBytesWritable)

        commit 2a99a6d5062a37eb654796550347786195b91b89
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-04-01T00:50:22Z

        PHOENIX-905 Wrong calculation of maxSetBit in ValueBitSet#or(ImmutableBytesWritable)

        commit 860a70d2fe08d16bba7a412810feae957089b39b
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-04-01T17:44:32Z

        PHOENIX-905 Wrong calculation of maxSetBit in ValueBitSet#or(ImmutableBytesWritable)

        commit 037779bd1de9409e741bd97a03c55cec072106c8
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-04-10T18:19:06Z

        Merge remote-tracking branch 'asf/master'

        commit 71569be703d185e5fc6102423ab15aaa3361cb77
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-04-10T18:37:14Z

        PHOENIX-927 Support derived tables in joins

        commit 47d9fd827e4f47795ce5f4bfa0c5116ebd6b1e16
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-04-13T20:42:27Z

        PHOENIX-927 Support derived tables in joins

        commit a28a9f454c31e50a0b3cfe974ccf894b8dfd19d2
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-04-22T02:35:29Z

        Merge remote-tracking branch 'asf/master'

        commit ffcc2715f69e3b2c3a63b368656fc02b60561935
        Author: maryannxue <maryannxue@apache.org>
        Date: 2014-04-22T02:47:47Z

        PHOENIX-136 Support derived tables


        Show
        githubbot ASF GitHub Bot added a comment - GitHub user maryannxue opened a pull request: https://github.com/apache/incubator-phoenix/pull/31 PHOENIX-136 Support derived tables You can merge this pull request into a Git repository by running: $ git pull https://github.com/maryannxue/incubator-phoenix master Alternatively you can review and apply these changes as the patch at: https://github.com/apache/incubator-phoenix/pull/31.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #31 commit 05b584181fe36102feca765886fcda75b770b2b2 Author: maryannxue <maryannxue@apache.org> Date: 2014-02-12T21:41:53Z Fix PHOENIX-44 Cannot create index on tables with case-sensitive schema names commit 02af57c96476c9d205d07ba96974c49188a282fa Author: maryannxue <maryannxue@apache.org> Date: 2014-02-21T02:54:17Z Merge remote-tracking branch 'asf/master' commit bd20346da5336d3d240493fdbfbcd5cc03280810 Author: maryannxue <maryannxue@apache.org> Date: 2014-02-21T03:08:23Z PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries commit 9a2ced8936d6cedbc44a4ed2de2c6f72f4f4307f Author: maryannxue <maryannxue@apache.org> Date: 2014-02-21T04:59:29Z PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries commit 20a97904e48829f8b412bcfb0ac2084259133126 Author: maryannxue <maryannxue@apache.org> Date: 2014-02-21T05:53:21Z PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries commit 7568516fabfb416f5dcbfa89468b839963802a87 Author: maryannxue <maryannxue@apache.org> Date: 2014-02-21T22:14:14Z Revert " PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries" This reverts commit 20a97904e48829f8b412bcfb0ac2084259133126. commit 3fb95819959d22deb2c03e895d976e7d933007f2 Author: maryannxue <maryannxue@apache.org> Date: 2014-02-21T22:14:56Z Revert " PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries" This reverts commit 9a2ced8936d6cedbc44a4ed2de2c6f72f4f4307f. commit ca0b55d048a6c547a5d6b081dd84dea01011e5c1 Author: maryannxue <maryannxue@apache.org> Date: 2014-02-21T22:16:13Z Revert " PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries" This reverts commit bd20346da5336d3d240493fdbfbcd5cc03280810. commit c4df65791a163070362662aad7ce5916faa3c3d7 Author: maryannxue <maryannxue@apache.org> Date: 2014-02-21T22:18:13Z Merge remote-tracking branch 'asf/master' commit 9b5df132480b6bd25c0c33dd818278a5fc1b3f55 Author: maryannxue <maryannxue@apache.org> Date: 2014-02-21T22:22:38Z PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries commit 3e677489c0cb811c1bb3d266581330fe11fdd674 Author: maryannxue <maryannxue@apache.org> Date: 2014-02-22T00:17:15Z PHOENIX-68 Add Option "FAVOR_STAR_JOIN" to indicate if star join optimization is preferred for multi inner join queries commit 776c1a11b286ebd358f9111f3ff1c99c3f7c7883 Author: maryannxue <maryannxue@apache.org> Date: 2014-02-27T18:41:58Z Merge remote-tracking branch 'asf/master' commit 6d26e78096091624502d4382de23f655626bca58 Author: maryannxue <maryannxue@apache.org> Date: 2014-02-28T19:36:38Z Merge remote-tracking branch 'asf/master' commit df561c8d8ab59651d7e797dc0da501cceb4c6030 Author: maryannxue <maryannxue@apache.org> Date: 2014-02-28T19:48:06Z PHOENIX-85 Refine error handling in HashJoinPlan execution and add warnings commit 53631fa509e2a660241c717a4eb0c131b251ff5d Author: maryannxue <maryannxue@apache.org> Date: 2014-02-28T23:42:14Z PHOENIX-85 Refine error handling in HashJoinPlan execution and add warnings commit 4fb71128f6ac961acbba7db9c3041c53bd18a64a Author: maryannxue <maryannxue@apache.org> Date: 2014-03-01T01:53:26Z Merge remote-tracking branch 'asf/master' commit 6a800b53abc6b6dac24daff1eae74100d6c73b97 Author: maryannxue <maryannxue@apache.org> Date: 2014-03-01T02:10:20Z Merge remote-tracking branch 'asf/master' commit af32148215ec4cb443ba9d79c705c94196f1ff03 Author: maryannxue <maryannxue@apache.org> Date: 2014-03-03T01:26:48Z Merge remote-tracking branch 'asf/master' commit 012018d2f58cfb35f7574301c68314ead2f31b9d Author: maryannxue <maryannxue@apache.org> Date: 2014-03-03T01:30:49Z PHOENIX-89 Statements are not closed using PhoenixConnection.executeStatements() commit c4bd34ff11f75d00bb74008a03247889b3b0160a Author: maryannxue <maryannxue@apache.org> Date: 2014-03-31T01:52:33Z Merge remote-tracking branch 'asf/master' commit 8fce312e63138f23c1befcf3a7dbbb573275eaf1 Author: maryannxue <maryannxue@apache.org> Date: 2014-03-31T01:59:00Z PHOENIX-71 Support sub-joins commit b1d9340e36f750c66b993eb5e8571371f36ca9f9 Author: maryannxue <maryannxue@apache.org> Date: 2014-04-01T00:19:57Z PHOENIX-71 Support sub-joins commit a3c8f7572a5aa3704ec817f5c42d691df2b631f6 Author: maryannxue <maryannxue@apache.org> Date: 2014-04-01T00:43:04Z PHOENIX-905 Wrong calculation of maxSetBit in ValueBitSet#or(ImmutableBytesWritable) commit 2a99a6d5062a37eb654796550347786195b91b89 Author: maryannxue <maryannxue@apache.org> Date: 2014-04-01T00:50:22Z PHOENIX-905 Wrong calculation of maxSetBit in ValueBitSet#or(ImmutableBytesWritable) commit 860a70d2fe08d16bba7a412810feae957089b39b Author: maryannxue <maryannxue@apache.org> Date: 2014-04-01T17:44:32Z PHOENIX-905 Wrong calculation of maxSetBit in ValueBitSet#or(ImmutableBytesWritable) commit 037779bd1de9409e741bd97a03c55cec072106c8 Author: maryannxue <maryannxue@apache.org> Date: 2014-04-10T18:19:06Z Merge remote-tracking branch 'asf/master' commit 71569be703d185e5fc6102423ab15aaa3361cb77 Author: maryannxue <maryannxue@apache.org> Date: 2014-04-10T18:37:14Z PHOENIX-927 Support derived tables in joins commit 47d9fd827e4f47795ce5f4bfa0c5116ebd6b1e16 Author: maryannxue <maryannxue@apache.org> Date: 2014-04-13T20:42:27Z PHOENIX-927 Support derived tables in joins commit a28a9f454c31e50a0b3cfe974ccf894b8dfd19d2 Author: maryannxue <maryannxue@apache.org> Date: 2014-04-22T02:35:29Z Merge remote-tracking branch 'asf/master' commit ffcc2715f69e3b2c3a63b368656fc02b60561935 Author: maryannxue <maryannxue@apache.org> Date: 2014-04-22T02:47:47Z PHOENIX-136 Support derived tables
        Hide
        maryannxue Maryann Xue added a comment -

        alex kamil We are going to support use case 1 (derived tables as join tables) soon, and nested select as well if the query can be flattened.

        Tony Stevenson Thanks a lot for the comments! Could you please file another bug for the "upsert select" issue with the table definition and other details so that we can reproduce the problem?

        Show
        maryannxue Maryann Xue added a comment - alex kamil We are going to support use case 1 (derived tables as join tables) soon, and nested select as well if the query can be flattened. Tony Stevenson Thanks a lot for the comments! Could you please file another bug for the "upsert select" issue with the table definition and other details so that we can reproduce the problem?
        Hide
        alexdl alex kamil added a comment - - edited

        use case 1 (critical): cc @maryannxue
        SELECT a.col1, b.col2, c.col3 FROM
        (SELECT rk, col1 FROM table1 WHERE col1 LIKE 'foo%' AND col300 IS NULL) AS a
        JOIN (SELECT rk, col2 FROM table2 WHERE col2 LIKE 'bar%') AS b ON a.rk=b.rk
        JOIN (SELECT rk, col3 FROM table3 ) AS c ON a.rk=c.rk;

        nice to have: TPC-H queries with nested SELECT: #2, 7, 9, 11,13,15-18,20-22 (http://www.tpc.org/tpch/spec/tpch2.16.0.pdf )

        thanks!

        Show
        alexdl alex kamil added a comment - - edited use case 1 (critical): cc @maryannxue SELECT a.col1, b.col2, c.col3 FROM (SELECT rk, col1 FROM table1 WHERE col1 LIKE 'foo%' AND col300 IS NULL) AS a JOIN (SELECT rk, col2 FROM table2 WHERE col2 LIKE 'bar%') AS b ON a.rk=b.rk JOIN (SELECT rk, col3 FROM table3 ) AS c ON a.rk=c.rk; nice to have: TPC-H queries with nested SELECT: #2, 7, 9, 11,13,15-18,20-22 ( http://www.tpc.org/tpch/spec/tpch2.16.0.pdf ) thanks!
        Hide
        jamestaylor James Taylor added a comment -

        Can you list your top 5 nested queries you'd like to support, alex kamil? That'll help drive this use case. Maryann Xue is already working on this. BTW, often times a query can be "flattened" manually. Is that not the case for you?

        Show
        jamestaylor James Taylor added a comment - Can you list your top 5 nested queries you'd like to support, alex kamil ? That'll help drive this use case. Maryann Xue is already working on this. BTW, often times a query can be "flattened" manually. Is that not the case for you?
        Hide
        pctony Tony Stevenson added a comment -

        Comment:jhanit:11/26/13 12:00:24 PM:

        Hi,

        I hit a road block with my approach. As per our earlier discussion (how to proceed with join and nested query),
        I was trying to use Upsert select and Join (with master stream) and some how started to achieve my use case posted above(Initially).

        Issue:-
        Upsert select didn't work with join .
        UPSERT INTO ATTRIBUTE(ENTITY,ATTRIBUTE) select EM.entity,EM.EVCOL_NO_OF_PARTS from EVENT_MANAGEMENT EM(EVCOL_NO_OF_PARTS INTEGER,EVCOL_STANDARD_COST FLOAT,EVCOL_INVENTORY INTEGER,EVCOL_TYPE VARCHAR) INNER JOIN TEMPENTITY MA ON EM.EVENTTIME = MA.EVENTTIME AND EM.ENTITY = MA.ENTITY;

        Error: Joins not supported (state=,code=0)

        Where Join worked with select and UPSERT select worked in other cases.To my disappointment when i combine both it didn't work.
        Is there any workaround for it?
        or am I doing anything wrong?

        I will be really happy if 'YES' is an answer for any of the above question.Eagerly waiting for reply

        Thanks,
        Nitin

        Show
        pctony Tony Stevenson added a comment - Comment:jhanit:11/26/13 12:00:24 PM: Hi, I hit a road block with my approach. As per our earlier discussion (how to proceed with join and nested query), I was trying to use Upsert select and Join (with master stream) and some how started to achieve my use case posted above(Initially). Issue:- Upsert select didn't work with join . UPSERT INTO ATTRIBUTE(ENTITY,ATTRIBUTE) select EM.entity,EM.EVCOL_NO_OF_PARTS from EVENT_MANAGEMENT EM(EVCOL_NO_OF_PARTS INTEGER,EVCOL_STANDARD_COST FLOAT,EVCOL_INVENTORY INTEGER,EVCOL_TYPE VARCHAR) INNER JOIN TEMPENTITY MA ON EM.EVENTTIME = MA.EVENTTIME AND EM.ENTITY = MA.ENTITY; Error: Joins not supported (state=,code=0) Where Join worked with select and UPSERT select worked in other cases.To my disappointment when i combine both it didn't work. Is there any workaround for it? or am I doing anything wrong? I will be really happy if 'YES' is an answer for any of the above question.Eagerly waiting for reply Thanks, Nitin
        Hide
        pctony Tony Stevenson added a comment -

        Comment:jamestaylor:11/18/13 05:02:13 PM:

        Are you using the master branch?

        On Nov 18, 2013, at 6:41 AM, Nitin Kumar <notifications@github.com> wrote:

        I am trying with join.
        i was just trying to run the below mentioned query(just to test):
        SELECT item.item_id, item.name, supp.supplier_id, supp.name FROM
        JOIN_ITEM_TABLE item INNER JOIN JOIN_SUPPLIER_TABLE supp ON
        item.supplier_id = supp.supplier_id;

        Error:-Syntax error. Missing "EOF" at line 1, column 87.

        1) Can we run join query directly from command prompt or squirrel?
        Or it is only meant for running these queries from inside the code base?


        Reply to this email directly or view it on
        GitHub<https://github.com/forcedotcom/phoenix/issues/5_issuecomment-28702566>
        .

        Show
        pctony Tony Stevenson added a comment - Comment:jamestaylor:11/18/13 05:02:13 PM: Are you using the master branch? On Nov 18, 2013, at 6:41 AM, Nitin Kumar <notifications@github.com> wrote: I am trying with join. i was just trying to run the below mentioned query(just to test): SELECT item.item_id, item.name, supp.supplier_id, supp.name FROM JOIN_ITEM_TABLE item INNER JOIN JOIN_SUPPLIER_TABLE supp ON item.supplier_id = supp.supplier_id; Error:-Syntax error. Missing "EOF" at line 1, column 87. 1) Can we run join query directly from command prompt or squirrel? Or it is only meant for running these queries from inside the code base? — Reply to this email directly or view it on GitHub< https://github.com/forcedotcom/phoenix/issues/5_issuecomment-28702566 > .
        Hide
        pctony Tony Stevenson added a comment -

        Comment:jhanit:11/18/13 02:41:12 PM:

        I am trying with join.
        i was just trying to run the below mentioned query(just to test):
        SELECT item.item_id, item.name, supp.supplier_id, supp.name FROM JOIN_ITEM_TABLE item INNER JOIN JOIN_SUPPLIER_TABLE supp ON item.supplier_id = supp.supplier_id;

        Error:-Syntax error. Missing "EOF" at line 1, column 87.

        1) Can we run join query directly from command prompt or squirrel?
        Or it is only meant for running these queries from inside the code base?

        Show
        pctony Tony Stevenson added a comment - Comment:jhanit:11/18/13 02:41:12 PM: I am trying with join. i was just trying to run the below mentioned query(just to test): SELECT item.item_id, item.name, supp.supplier_id, supp.name FROM JOIN_ITEM_TABLE item INNER JOIN JOIN_SUPPLIER_TABLE supp ON item.supplier_id = supp.supplier_id; Error:-Syntax error. Missing "EOF" at line 1, column 87. 1) Can we run join query directly from command prompt or squirrel? Or it is only meant for running these queries from inside the code base?
        Hide
        pctony Tony Stevenson added a comment -

        Comment:jhanit:11/18/13 08:14:03 AM:

        mentioned

        Show
        pctony Tony Stevenson added a comment - Comment:jhanit:11/18/13 08:14:03 AM: mentioned
        Hide
        pctony Tony Stevenson added a comment -

        Comment:jamestaylor:11/18/13 08:14:03 AM:

        Thanks, @jhanit for the detail - very helpful. We have support for joins in our master branch. Can you let us know if this together with upsert/select will meet your need in the short term?

        Show
        pctony Tony Stevenson added a comment - Comment:jamestaylor:11/18/13 08:14:03 AM: Thanks, @jhanit for the detail - very helpful. We have support for joins in our master branch. Can you let us know if this together with upsert/select will meet your need in the short term?
        Hide
        pctony Tony Stevenson added a comment -

        Comment:jhanit:11/18/13 08:09:40 AM:

        Hi James,

        Please find the sample nested queries use case along with SQL queries which i have tested with sql.
        My Table(In Phoenix, similar version is in SQL ):-
        CREATE TABLE IF NOT EXISTS EVENT_MANAGEMENT(
        eventId BIGINT NOT NULL,
        eventData.eventTime DATE,
        eventData.eventType VARCHAR,
        eventData.eventDescription VARCHAR,
        eventData.entity VARCHAR ,
        eventData.facility VARCHAR
        CONSTRAINT pk PRIMARY KEY (eventId));

        col1,col2,col3,col4 are dynamic(for SQl i just added while creating table).

        Requirement:-
        Pull all the unique set of (entity,facility) with recent values of all the attributes stored in col1,col2,col3,col4...

        My SQL querry:-

        1st Approach
        =============
        select Ent.entity,Ent.Facility, Att.Col1,Att.Col2,Att.Col3,Att.Col4

        from

        (select distinct entity,facility from event_mgmt

        where entity is not null and facility is not null) Ent

        join

        (select * from

        (select entity, col1, col2 from (

        select entity, col1, col2, ROW_NUMBER() OVER ( partition by entity order by eventTime desc) rn

        from event_mgmt

        where entity is not null) A

        where rn = 1) A,

        (select facility, col3, col4 from (

        select facility, col3, col4, ROW_NUMBER() OVER ( partition by facility order by eventTime desc) rn

        from event_mgmt

        where facility is not null) A

        where rn = 1) B) Att

        on Ent.Entity = Att.Entity and Ent.Facility = Att.Facility

        2nd Approach
        ==========

        Trying to use temp table by converting the above query into more update(which will translate into upsert select). But i was unable to get rid off join some how.

        --get prelim data in temp table

        select distinct entity,facility, cast (null as int) as col1, cast (null as varchar) as col2, cast (null as int) as col3, cast (null as varchar) as col4 into __TempTable

        from event_mgmt

        where entity is not null and facility is not null

        --update the Entity columns col1

        update A

        set Col1 = B.Col1

        from __TempTable A

        join (select entity, col1 from (

        select entity, col1, ROW_NUMBER() OVER ( partition by entity order by eventTime desc) rn

        from event_mgmt

        where entity is not null and col1 is not null) A

        where rn = 1) B

        on A.Entity = B.Entity

        --update the Entity columns col2

        update A

        set Col2 = B.Col2

        from __TempTable A

        join (select entity, col2 from (

        select entity, col2, ROW_NUMBER() OVER ( partition by entity order by eventTime desc) rn

        from event_mgmt

        where entity is not null and col2 is not null) A

        where rn = 1) B

        on A.Entity = B.Entity

        --update the facility columns col3

        update A

        set Col3 = B.Col3

        from __TempTable A

        join (select facility, col3 from (

        select facility, col3, ROW_NUMBER() OVER ( partition by facility order by eventTime desc) rn

        from event_mgmt

        where facility is not null and col3 is not null) A

        where rn = 1) B

        on A.facility = B.facility

        --update the facility columns col4

        update A

        set Col4 = B.Col4

        from __TempTable A

        join (select facility, col4 from (

        select facility, col4, ROW_NUMBER() OVER ( partition by facility order by eventTime desc) rn

        from event_mgmt

        where facility is not null and col4 is not null) A

        where rn = 1) B

        on A.facility = B.facility

        --select results from the temp table

        select * from __TempTable

        --drop the temp table

        drop table __TempTable

        This is use case 1. And i have several like this which i will start working on.

        Is it possible for a workaround for now(I tried but somehow unable to get rid off Join), as this will decide my phoenix usage extensively?

        Any help or timelines to get these features in phoenix will be helpful for me.

        Show
        pctony Tony Stevenson added a comment - Comment:jhanit:11/18/13 08:09:40 AM: Hi James, Please find the sample nested queries use case along with SQL queries which i have tested with sql. My Table(In Phoenix, similar version is in SQL ):- CREATE TABLE IF NOT EXISTS EVENT_MANAGEMENT( eventId BIGINT NOT NULL, eventData.eventTime DATE, eventData.eventType VARCHAR, eventData.eventDescription VARCHAR, eventData.entity VARCHAR , eventData.facility VARCHAR CONSTRAINT pk PRIMARY KEY (eventId)); col1,col2,col3,col4 are dynamic(for SQl i just added while creating table). Requirement:- Pull all the unique set of (entity,facility) with recent values of all the attributes stored in col1,col2,col3,col4... My SQL querry:- 1st Approach ============= select Ent.entity,Ent.Facility, Att.Col1,Att.Col2,Att.Col3,Att.Col4 from (select distinct entity,facility from event_mgmt where entity is not null and facility is not null) Ent join (select * from (select entity, col1, col2 from ( select entity, col1, col2, ROW_NUMBER() OVER ( partition by entity order by eventTime desc) rn from event_mgmt where entity is not null) A where rn = 1) A, (select facility, col3, col4 from ( select facility, col3, col4, ROW_NUMBER() OVER ( partition by facility order by eventTime desc) rn from event_mgmt where facility is not null) A where rn = 1) B) Att on Ent.Entity = Att.Entity and Ent.Facility = Att.Facility 2nd Approach ========== Trying to use temp table by converting the above query into more update(which will translate into upsert select). But i was unable to get rid off join some how. --get prelim data in temp table select distinct entity,facility, cast (null as int) as col1, cast (null as varchar) as col2, cast (null as int) as col3, cast (null as varchar) as col4 into __TempTable from event_mgmt where entity is not null and facility is not null --update the Entity columns col1 update A set Col1 = B.Col1 from __TempTable A join (select entity, col1 from ( select entity, col1, ROW_NUMBER() OVER ( partition by entity order by eventTime desc) rn from event_mgmt where entity is not null and col1 is not null) A where rn = 1) B on A.Entity = B.Entity --update the Entity columns col2 update A set Col2 = B.Col2 from __TempTable A join (select entity, col2 from ( select entity, col2, ROW_NUMBER() OVER ( partition by entity order by eventTime desc) rn from event_mgmt where entity is not null and col2 is not null) A where rn = 1) B on A.Entity = B.Entity --update the facility columns col3 update A set Col3 = B.Col3 from __TempTable A join (select facility, col3 from ( select facility, col3, ROW_NUMBER() OVER ( partition by facility order by eventTime desc) rn from event_mgmt where facility is not null and col3 is not null) A where rn = 1) B on A.facility = B.facility --update the facility columns col4 update A set Col4 = B.Col4 from __TempTable A join (select facility, col4 from ( select facility, col4, ROW_NUMBER() OVER ( partition by facility order by eventTime desc) rn from event_mgmt where facility is not null and col4 is not null) A where rn = 1) B on A.facility = B.facility --select results from the temp table select * from __TempTable --drop the temp table drop table __TempTable This is use case 1. And i have several like this which i will start working on. Is it possible for a workaround for now(I tried but somehow unable to get rid off Join), as this will decide my phoenix usage extensively? Any help or timelines to get these features in phoenix will be helpful for me.
        Hide
        pctony Tony Stevenson added a comment -

        Comment:jhanit:11/12/13 12:10:34 AM:

        mentioned

        Show
        pctony Tony Stevenson added a comment - Comment:jhanit:11/12/13 12:10:34 AM: mentioned
        Hide
        pctony Tony Stevenson added a comment -

        Comment:jamestaylor:11/12/13 12:10:34 AM:

        Thanks for the feedback, @jhanit. Would you be able to provide some sample queries for your nested query use cases, as this would ensure it'll meet your needs? Often times, with the lack of derived tables, a "poor man's" workaround is to perform the inner query using UPSERT SELECT into a temp table, and then referencing the temp table in the outer query. Would this work for you in the interim?

        Show
        pctony Tony Stevenson added a comment - Comment:jamestaylor:11/12/13 12:10:34 AM: Thanks for the feedback, @jhanit. Would you be able to provide some sample queries for your nested query use cases, as this would ensure it'll meet your needs? Often times, with the lack of derived tables, a "poor man's" workaround is to perform the inner query using UPSERT SELECT into a temp table, and then referencing the temp table in the outer query. Would this work for you in the interim?
        Hide
        pctony Tony Stevenson added a comment -

        Comment:jhanit:11/11/13 01:03:07 PM:

        Hi James,

        As and when usage of Phoenix increase, the demand for Nested queries will shoot up.
        Nested queries is an important part of any real time project(if they want to use SQL feature and hence phoenix).

        Can you please consider this in your subsequent releases?
        For my product , i need to use nested queries heavily and hence i turned toward phoenix as i have to write several lines of code if i use Hbase directly. But unfortunately this is not supported yet in Phoenix.

        Regards,
        Nitin

        Show
        pctony Tony Stevenson added a comment - Comment:jhanit:11/11/13 01:03:07 PM: Hi James, As and when usage of Phoenix increase, the demand for Nested queries will shoot up. Nested queries is an important part of any real time project(if they want to use SQL feature and hence phoenix). Can you please consider this in your subsequent releases? For my product , i need to use nested queries heavily and hence i turned toward phoenix as i have to write several lines of code if i use Hbase directly. But unfortunately this is not supported yet in Phoenix. Regards, Nitin
        Hide
        pctony Tony Stevenson added a comment -

        Comment:jamestaylor:06/03/13 08:20:00 PM:

        assigned

        Show
        pctony Tony Stevenson added a comment - Comment:jamestaylor:06/03/13 08:20:00 PM: assigned
        Hide
        pctony Tony Stevenson added a comment -

        Comment:jamestaylor:01/31/13 02:35:31 AM:

        Referenced in commit:
        https://github.com/forcedotcom/phoenix

        Show
        pctony Tony Stevenson added a comment - Comment:jamestaylor:01/31/13 02:35:31 AM: Referenced in commit: https://github.com/forcedotcom/phoenix
        Hide
        pctony Tony Stevenson added a comment -

        Comment:tonyhuang:01/30/13 08:05:48 AM:

        assigned

        Show
        pctony Tony Stevenson added a comment - Comment:tonyhuang:01/30/13 08:05:48 AM: assigned

          People

          • Assignee:
            maryannxue Maryann Xue
            Reporter:
            jamestaylor James Taylor
          • Votes:
            1 Vote for this issue
            Watchers:
            8 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development