[
https://issues.apache.org/jira/browse/DRILL-4682?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15404678#comment-15404678
]
ASF GitHub Bot commented on DRILL-4682:
---------------------------------------
Github user jinfengni commented on a diff in the pull request:
https://github.com/apache/drill/pull/549#discussion_r73226886
--- Diff:
exec/java-exec/src/test/java/org/apache/drill/TestSelectWithFullNameIdentifiers.java
---
@@ -0,0 +1,180 @@
+/**
+ * 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.drill;
+
+import org.apache.drill.exec.rpc.user.QueryDataBatch;
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Ignore;
+import org.junit.Test;
+
+import java.util.List;
+
+public class TestSelectWithFullNameIdentifiers extends BaseTestQuery {
+ static final org.slf4j.Logger logger =
org.slf4j.LoggerFactory.getLogger(TestSelectWithFullNameIdentifiers.class);
+
+ // create table in dfs_test
+ @BeforeClass
+ public static void createTableForTest() throws Exception {
+ test("USE dfs_test");
+ test("CREATE TABLE tmp.`department` (department_id,
department_description) as select department_id, department_description from
cp.`department.json`");
+ }
+
+ // delete table from dfs_test
+ @AfterClass
+ public static void dropCreatedTable() throws Exception {
+ test("DROP TABLE IF EXISTS dfs_test.tmp.`department`");
+ }
+
+ @Test
+ public void testSimpleQuery() throws Exception {
+ // Query with using scalar function, WHERE statement, ORDER BY
statement
+ testBuilder()
+ .sqlQuery("SELECT cp.`employee.json`.employee_id,
UPPER(cp.`employee.json`.full_name) full_name " +
+ "FROM cp.`employee.json` " +
+ "WHERE cp.`employee.json`.department_id = 4 " +
+ "ORDER BY cp.`employee.json`.full_name")
+ .unOrdered()
+ .baselineColumns("employee_id", "full_name")
+ .baselineValues(43L, "JUANITA SHARP")
+ .baselineValues(44L, "SANDRA BRUNNER")
+ .build()
+ .run();
+ }
+
+ @Test
+ @Ignore
+ public void testQueryWithStar() throws Exception {
+ // Query with using full schema name for star identifier
+ // TODO: Can be used after resolving "CALCITE-1323: Wrong prefix
number in DelegatingScope.fullyQualify()"
+ testBuilder()
+ .sqlQuery("SELECT cp.`department.json`.* FROM cp.`department.json`
LIMIT 1")
+ .unOrdered()
+ .baselineColumns("department_id", "department_description")
+ .baselineValues(1L, "HQ General Management")
+ .build()
+ .run();
+ }
+
+ @Test
+ @Ignore
+ public void testQueryWithAggregation() throws Exception {
+ // Query with aggregation
+ // TODO: Can be used after resolving "DRILL-3993: Rebase Drill on
Calcite master branch"
+ // Fixed in "CALCITE-881: Allow schema.table.column references in
GROUP BY".
+ testBuilder()
+ .sqlQuery("SELECT cp.`employee.json`.position_title, " +
+ "COUNT(cp.`employee.json`.employee_id) employee_number " +
+ "FROM cp.`employee.json` " +
+ "WHERE cp.`employee.json`.position_title = 'Store Permanent
Stocker' " +
+ "GROUP BY cp.`employee.json`.position_title")
+ .unOrdered()
+ .baselineColumns("position_title", "employee_number")
+ .baselineValues("Store Permanent Stocker", 222L)
+ .build()
+ .run();
+
+ }
+
+ @Test
+ public void testLeftJoin() throws Exception {
+ // Query with left join (with different schema-qualified tables)
+ testBuilder()
+ .sqlQuery("SELECT cp.`employee.json`.employee_id,
cp.`employee.json`.full_name, dfs_test.tmp.`department`.department_id " +
+ "FROM cp.`employee.json` LEFT JOIN dfs_test.tmp.`department` "
+
+ "ON cp.`employee.json`.department_id =
dfs_test.tmp.`department`.department_id " +
+ "WHERE dfs_test.tmp.`department`.department_description = 'HQ
Marketing' " +
+ "ORDER BY cp.`employee.json`.full_name")
+ .unOrdered()
+ .baselineColumns("employee_id", "full_name", "department_id")
+ .baselineValues(36L, "Donna Arnold", 3L)
+ .baselineValues(42L, "Doris Carter", 3L)
+ .baselineValues(41L, "Howard Bechard", 3L)
+ .baselineValues(7L, "Rebecca Kanagaki", 3L)
+ .build()
+ .run();
+ }
+
+ @Test
+ @Ignore
+ public void testNestedQueryInWhereStatement() throws Exception {
--- End diff --
I'm not fully convinced that the current patch is ready to merge, before we
could resolve the case where full-schema qualified names appear in a subquery.
With your patch, looks like we can only allow fully-schema qualified name
in a top-level query, which is kind of restricted use case. It's hard to tell
Drill user that they only can use one special case, but not for other cases.
I would suggest we look into the Calcite in more detail, to understand the
problem more.
> Allow full schema identifier in SELECT clause
> ---------------------------------------------
>
> Key: DRILL-4682
> URL: https://issues.apache.org/jira/browse/DRILL-4682
> Project: Apache Drill
> Issue Type: Improvement
> Components: SQL Parser
> Reporter: Andries Engelbrecht
> Assignee: Vitalii Diravka
>
> Currently Drill requires aliases to identify columns in the SELECT clause
> when working with multiple tables/workspaces.
> Many BI/Analytical and other tools by default will use the full schema
> identifier in the select clause when generating SQL statements for execution
> for generic JDBC or ODBC sources. Not supporting this feature causes issues
> and a slower adoption of utilizing Drill as an execution engine within the
> larger Analytical SQL community.
> Propose to support
> SELECT <storage_plugin>.<workspace>.<table>.<column> FROM
> <storage_plugin>.<workspace>.<table>
> Also see DRILL-3510 for double quote support as per ANSI_QUOTES
> SELECT "<storage_plugin>"."<workspace>"."<table>"."<column>" FROM
> "<storage_plugin>"."<workspace>"."<table>"
> Which is very common generic SQL being generated by most tools when dealing
> with a generic SQL data source.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
