kylin-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (KYLIN-3594) Select with Catalog fails
Date Sat, 06 Oct 2018 14:59:00 GMT

    [ https://issues.apache.org/jira/browse/KYLIN-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16640744#comment-16640744
] 

ASF GitHub Bot commented on KYLIN-3594:
---------------------------------------

shaofengshi closed pull request #268: KYLIN-3594 Obey JDBC standard
URL: https://github.com/apache/kylin/pull/268
 
 
   

This is a PR merged from a forked repository.
As GitHub hides the original diff on merge, it is displayed below for
the sake of provenance:

As this is a foreign pull request (from a fork), the diff is supplied
below (as it won't show otherwise due to GitHub magic):

diff --git a/kylin-it/src/test/resources/query/sql_snowflake/query07.sql b/kylin-it/src/test/resources/query/sql_snowflake/query07.sql
new file mode 100644
index 0000000000..2705f783d7
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_snowflake/query07.sql
@@ -0,0 +1,23 @@
+SELECT
+
+count(*) as cnt
+
+FROM defaultCatalog.TEST_KYLIN_FACT as TEST_KYLIN_FACT
+INNER JOIN defaultCatalog.TEST_ORDER as TEST_ORDER
+ON TEST_KYLIN_FACT.ORDER_ID = TEST_ORDER.ORDER_ID
+INNER JOIN defaultCatalog.TEST_ACCOUNT as BUYER_ACCOUNT
+ON TEST_ORDER.BUYER_ID = BUYER_ACCOUNT.ACCOUNT_ID
+INNER JOIN defaultCatalog.TEST_ACCOUNT as SELLER_ACCOUNT
+ON TEST_KYLIN_FACT.SELLER_ID = SELLER_ACCOUNT.ACCOUNT_ID
+INNER JOIN defaultCatalog.EDW.TEST_CAL_DT as TEST_CAL_DT
+ON TEST_KYLIN_FACT.CAL_DT = TEST_CAL_DT.CAL_DT
+INNER JOIN defaultCatalog.TEST_CATEGORY_GROUPINGS as TEST_CATEGORY_GROUPINGS
+ON TEST_KYLIN_FACT.LEAF_CATEG_ID = TEST_CATEGORY_GROUPINGS.LEAF_CATEG_ID AND TEST_KYLIN_FACT.LSTG_SITE_ID
= TEST_CATEGORY_GROUPINGS.SITE_ID
+INNER JOIN defaultCatalog.EDW.TEST_SITES as TEST_SITES
+ON TEST_KYLIN_FACT.LSTG_SITE_ID = TEST_SITES.SITE_ID
+INNER JOIN defaultCatalog.EDW.TEST_SELLER_TYPE_DIM as TEST_SELLER_TYPE_DIM
+ON TEST_KYLIN_FACT.SLR_SEGMENT_CD = TEST_SELLER_TYPE_DIM.SELLER_TYPE_CD
+INNER JOIN defaultCatalog.TEST_COUNTRY as BUYER_COUNTRY
+ON BUYER_ACCOUNT.ACCOUNT_COUNTRY = BUYER_COUNTRY.COUNTRY
+INNER JOIN defaultCatalog.TEST_COUNTRY as SELLER_COUNTRY
+ON SELLER_ACCOUNT.ACCOUNT_COUNTRY = SELLER_COUNTRY.COUNTRY
diff --git a/kylin-it/src/test/resources/query/sql_snowflake/query08.sql b/kylin-it/src/test/resources/query/sql_snowflake/query08.sql
new file mode 100644
index 0000000000..454779c0af
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_snowflake/query08.sql
@@ -0,0 +1,14 @@
+SELECT
+
+count(*) as cnt, sum(price) as sum_price, SELLER_COUNTRY.NAME
+
+FROM TEST_KYLIN_FACT as TEST_KYLIN_FACT 
+INNER JOIN defaultCatalog.TEST_ACCOUNT as SELLER_ACCOUNT
+ON TEST_KYLIN_FACT.SELLER_ID = SELLER_ACCOUNT.ACCOUNT_ID
+INNER JOIN defaultCatalog.TEST_CATEGORY_GROUPINGS as TEST_CATEGORY_GROUPINGS
+ON TEST_KYLIN_FACT.LEAF_CATEG_ID = TEST_CATEGORY_GROUPINGS.LEAF_CATEG_ID AND TEST_KYLIN_FACT.LSTG_SITE_ID
= TEST_CATEGORY_GROUPINGS.SITE_ID
+INNER JOIN defaultCatalog.TEST_COUNTRY as SELLER_COUNTRY
+ON SELLER_ACCOUNT.ACCOUNT_COUNTRY = SELLER_COUNTRY.COUNTRY
+
+where SELLER_ACCOUNT.ACCOUNT_SELLER_LEVEL=1
+group by SELLER_COUNTRY.NAME
\ No newline at end of file
diff --git a/query/src/main/java/org/apache/kylin/query/util/QueryUtil.java b/query/src/main/java/org/apache/kylin/query/util/QueryUtil.java
index 4378221a3c..1e7498b511 100644
--- a/query/src/main/java/org/apache/kylin/query/util/QueryUtil.java
+++ b/query/src/main/java/org/apache/kylin/query/util/QueryUtil.java
@@ -38,12 +38,20 @@
 
     protected static final Logger logger = LoggerFactory.getLogger(QueryUtil.class);
 
+    private QueryUtil() {
+        throw new IllegalStateException("Class QueryUtil is an utility class !");
+    }
+
     private static List<IQueryTransformer> queryTransformers;
 
     public interface IQueryTransformer {
         String transform(String sql, String project, String defaultSchema);
     }
 
+    /**
+     * @deprecated Deprecated because of KYLIN-3594
+     */
+    @Deprecated
     public static String massageSql(String sql, String project, int limit, int offset, String
defaultSchema) {
         sql = sql.trim();
         sql = sql.replace("\r", " ").replace("\n", System.getProperty("line.separator"));
@@ -55,7 +63,7 @@ public static String massageSql(String sql, String project, int limit, int
offse
         while (sql.endsWith(";"))
             sql = sql.substring(0, sql.length() - 1);
 
-        String sql1=sql;
+        String sql1 = sql;
         final String suffixPattern = "^.+?\\s(limit\\s\\d+)?\\s(offset\\s\\d+)?\\s*$";
         sql = sql.replaceAll("\\s+", " ");
         Pattern pattern = Pattern.compile(suffixPattern);
@@ -86,6 +94,29 @@ public static String massageSql(String sql, String project, int limit,
int offse
         return sql1;
     }
 
+    /**
+     * add remove catalog step at final
+     */
+    public static String massageSql(String sql, String project, int limit, int offset, String
defaultSchema, String catalog) {
+        String correctedSql = massageSql(sql, project, limit, offset, defaultSchema);
+        correctedSql = removeCatalog(correctedSql, catalog);
+        return correctedSql;
+    }
+
+    /**
+     * Although SQL standard define CATALOG concept, ISV has right not to implement it.
+     * We remove it in before send it to SQL parser.
+     *
+     * @param sql query which maybe has such pattern: [[catalogName.]schemaName.]tableName
+     * @return replace [[catalogName.]schemaName.]tableName with [schemaName.]tableName
+     */
+    static String removeCatalog(String sql, String catalog) {
+        if (catalog == null)
+            return sql;
+        else
+            return sql.replace(catalog + ".", "");
+    }
+
     private static void initQueryTransformers() {
         List<IQueryTransformer> transformers = Lists.newArrayList();
 
diff --git a/query/src/test/java/org/apache/kylin/query/util/QueryUtilTest.java b/query/src/test/java/org/apache/kylin/query/util/QueryUtilTest.java
index 30f4934f38..bf6f8363be 100644
--- a/query/src/test/java/org/apache/kylin/query/util/QueryUtilTest.java
+++ b/query/src/test/java/org/apache/kylin/query/util/QueryUtilTest.java
@@ -27,6 +27,8 @@
 
 public class QueryUtilTest extends LocalFileMetadataTestCase {
 
+    static final String catalog = "CATALOG";
+
     @Before
     public void setUp() throws Exception {
         this.createTestMetadata();
@@ -231,4 +233,34 @@ public void testUnknownErrorResponseMessage() {
         String msg = QueryUtil.makeErrorMsgUserFriendly(new NullPointerException());
         Assert.assertEquals("Unknown error.", msg);
     }
+
+    @Test
+    public void testRemoveCatalog() {
+
+        String[] beforeRemoveSql = new String[] {
+                "select name, count(*) as cnt from schema1.user where bb.dd >2 group by
name",
+                "select name, count(*) as cnt from .default2.user where dd >2 group by
name",
+                "select name, count(*) as cnt from %s.default2.user where dd >2 group
by name",
+                "select name, count(*) as cnt from %s.user.a.cu where dd >2 group by name",
+                "select name, count(*) as cnt from %s.default2.user where dd >2 group
by name",
+                "select name, count() as cnt from %s.test.kylin_sales inner join " + "%s.test.kylin_account
"
+                        + "ON kylin_sales.BUYER_ID=kylin_account.ACCOUNT_ID group by name",
+                "select schema1.table1.col1 from %s.schema1.table1" };
+        String[] afterRemoveSql = new String[] {
+                "select name, count(*) as cnt from schema1.user where bb.dd >2 group by
name",
+                "select name, count(*) as cnt from .default2.user where dd >2 group by
name",
+                "select name, count(*) as cnt from default2.user where dd >2 group by
name",
+                "select name, count(*) as cnt from user.a.cu where dd >2 group by name",
+                "select name, count(*) as cnt from default2.user where dd >2 group by
name",
+                "select name, count() as cnt from test.kylin_sales inner join " + "test.kylin_account
"
+                        + "ON kylin_sales.BUYER_ID=kylin_account.ACCOUNT_ID group by name",
+                "select schema1.table1.col1 from schema1.table1" };
+        Assert.assertEquals(afterRemoveSql.length, beforeRemoveSql.length);
+        for (int i = 0; i < beforeRemoveSql.length; i++) {
+            String before = beforeRemoveSql[i];
+            before = before.replace("%s", catalog);
+            String after = afterRemoveSql[i];
+            Assert.assertEquals(after, QueryUtil.removeCatalog(before, catalog));
+        }
+    }
 }
diff --git a/server-base/src/main/java/org/apache/kylin/rest/service/QueryService.java b/server-base/src/main/java/org/apache/kylin/rest/service/QueryService.java
index 65054c425d..9e6b4afe49 100644
--- a/server-base/src/main/java/org/apache/kylin/rest/service/QueryService.java
+++ b/server-base/src/main/java/org/apache/kylin/rest/service/QueryService.java
@@ -558,9 +558,8 @@ private SQLResponse queryWithSqlMassage(SQLRequest sqlRequest) throws
Exception
                 logger.debug("Return fake response, is exception? " + fakeResponse.getIsException());
                 return fakeResponse;
             }
-
             String correctedSql = QueryUtil.massageSql(sqlRequest.getSql(), sqlRequest.getProject(),
-                    sqlRequest.getLimit(), sqlRequest.getOffset(), conn.getSchema());
+                    sqlRequest.getLimit(), sqlRequest.getOffset(), conn.getSchema(), Constant.FakeCatalogName);
             if (!correctedSql.equals(sqlRequest.getSql())) {
                 logger.info("The corrected query: " + correctedSql);
 
@@ -1087,23 +1086,23 @@ private void setParam(PreparedStatement preparedState, int index,
PrepareSqlRequ
             break;
         case PRIMITIVE_INT:
         case INTEGER:
-            preparedState.setInt(index, isNull ? 0 : Integer.valueOf(param.getValue()));
+            preparedState.setInt(index, isNull ? 0 : Integer.parseInt(param.getValue()));
             break;
         case PRIMITIVE_SHORT:
         case SHORT:
-            preparedState.setShort(index, isNull ? 0 : Short.valueOf(param.getValue()));
+            preparedState.setShort(index, isNull ? 0 : Short.parseShort(param.getValue()));
             break;
         case PRIMITIVE_LONG:
         case LONG:
-            preparedState.setLong(index, isNull ? 0 : Long.valueOf(param.getValue()));
+            preparedState.setLong(index, isNull ? 0 : Long.parseLong(param.getValue()));
             break;
         case PRIMITIVE_FLOAT:
         case FLOAT:
-            preparedState.setFloat(index, isNull ? 0 : Float.valueOf(param.getValue()));
+            preparedState.setFloat(index, isNull ? 0 : Float.parseFloat(param.getValue()));
             break;
         case PRIMITIVE_DOUBLE:
         case DOUBLE:
-            preparedState.setDouble(index, isNull ? 0 : Double.valueOf(param.getValue()));
+            preparedState.setDouble(index, isNull ? 0 : Double.parseDouble(param.getValue()));
             break;
         case PRIMITIVE_BOOLEAN:
         case BOOLEAN:
@@ -1111,7 +1110,7 @@ private void setParam(PreparedStatement preparedState, int index, PrepareSqlRequ
             break;
         case PRIMITIVE_BYTE:
         case BYTE:
-            preparedState.setByte(index, isNull ? 0 : Byte.valueOf(param.getValue()));
+            preparedState.setByte(index, isNull ? 0 : Byte.parseByte(param.getValue()));
             break;
         case JAVA_UTIL_DATE:
         case JAVA_SQL_DATE:


 

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


> Select with Catalog fails
> -------------------------
>
>                 Key: KYLIN-3594
>                 URL: https://issues.apache.org/jira/browse/KYLIN-3594
>             Project: Kylin
>          Issue Type: Bug
>            Reporter: Hosur Narahari
>            Assignee: XiaoXiang Yu
>            Priority: Major
>
> By using DatabaseMetaData if we get catalog using getCatalogs() method, it return value
"defaultCatalog". It returns actual hive schema when we execute getSchemas().
> According to JDBC contract, catalog.schema.table should be valid from clause and many
query layers use that. But kylin fails when we execute that query.
> I've tried to write sample code piece for that below.
>  
>         _DatabaseMetaData db = conn.getMetaData();_
>         _ResultSet catalogSet = db.getCatalogs();_
>         _String catalog = "";_
>         _if(catalogSet.next()) {_
>             _catalog = catalogSet.getString("TABLE_CAT");_
>         _}_
>         _ResultSet schemaSet = db.getSchemas();_
>         _String schema = "";_
>         _if(schemaSet.next()) {_
>             _schema = schemaSet.getString("TABLE_SCHEM");_
>         _}_
>         _StringBuilder sb = new StringBuilder("SELECT * FROM ");_
>         _if(!catalog.isEmpty()) {_
>             _sb.append(catalog + ".");_
>         _}_
>         _if(!schema.isEmpty()) {_
>             _sb.append(schema + ".");_
>         _}_
>         _sb.append("kylin_sales limit 10");_
>         _String query = sb.toString();_
>         _Statement stat = conn.createStatement();_
>         _ResultSet rs = stat.executeQuery(query);_
>         _while(rs.next()) {_
>             _System.out.println(rs.getObject("trans_id"));_
>         _}_
> In short, the above snippet is executing the query,
> _select * from defaultCatalog.DEFAULT.kylin_sales._
>  
> Same thing happens even with different schemas if we have like,
> _select * from defaultCatalog.test.kylin_sales_ also fails.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message