@Test public void SumOfCastTransform() throws Exception { DefaultQueryTransformer transformer = new DefaultQueryTransformer(); String fnConvertSumSql = "select SUM(CAST(LSTG_SITE_ID AS DOUBLE)) from KYLIN_SALES group by LSTG_SITE_ID"; String correctSql = transformer.transform(fnConvertSumSql, "", ""); assertTrue("select sum(LSTG_SITE_ID) from KYLIN_SALES group by LSTG_SITE_ID".equalsIgnoreCase(correctSql)); fnConvertSumSql = "select SUM ( CAST ( LSTG_SITE_ID AS DOUBLE ) ) from KYLIN_SALES group by LSTG_SITE_ID"; correctSql = transformer.transform(fnConvertSumSql, "", ""); assertTrue("select sum(LSTG_SITE_ID) from KYLIN_SALES group by LSTG_SITE_ID".equalsIgnoreCase(correctSql)); fnConvertSumSql = "select SUM ( CAST ( LSTG_SITE_ID AS DOUBLE ) ) from KYLIN_SALES group by LSTG_SITE_ID"; correctSql = transformer.transform(fnConvertSumSql, "", ""); assertTrue("select sum(LSTG_SITE_ID) from KYLIN_SALES group by LSTG_SITE_ID".equalsIgnoreCase(correctSql)); fnConvertSumSql = "select SUM ( CAST(LSTG_SITE_ID AS DOUBLE ) ) from KYLIN_SALES group by LSTG_SITE_ID"; correctSql = transformer.transform(fnConvertSumSql, "", ""); assertTrue("select sum(LSTG_SITE_ID) from KYLIN_SALES group by LSTG_SITE_ID".equalsIgnoreCase(correctSql)); fnConvertSumSql = "select SUM(CAST(LSTG_SITE_ID AS DOUBLE)), SUM(CAST(price AS DOUBLE)) from KYLIN_SALES group by LSTG_SITE_ID"; correctSql = transformer.transform(fnConvertSumSql, "", ""); assertTrue("select sum(LSTG_SITE_ID), sum(price) from KYLIN_SALES group by LSTG_SITE_ID" .equalsIgnoreCase(correctSql)); }
@Override public String transform(String sql, String project, String defaultSchema) { Matcher m; while (true) { m = PIN_SUM_OF_CAST.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " SUM(" + m.group(1).trim() + ")" + sql.substring(m.end(), sql.length()); } while (true) { m = PIN_SUM_OF_FN_CONVERT.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " SUM(" + m.group(1).trim() + ")" + sql.substring(m.end(), sql.length()); } while (true) { m = PTN_HAVING_ESCAPE_FUNCTION.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + m.group(1) + sql.substring(m.end()); } m = PTN_HAVING_COUNT_GREATER_THAN_ZERO.matcher(sql); if (m.find() && PTN_GROUP_BY.matcher(sql).find() == false) { sql = sql.substring(0, m.start()) + " " + sql.substring(m.end()); } while (true) { m = PTN_SUM_1.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " COUNT(1) " + sql.substring(m.end()); } while (true) { m = PTN_MIN_1.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " 1 " + sql.substring(m.end()); } while (true) { m = PTN_MAX_1.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " 1 " + sql.substring(m.end()); } while (true) { m = PTN_NOT_EQ.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " <> " + sql.substring(m.end()); } while (true) { m = PTN_INTERVAL.matcher(sql); if (!m.find()) break; int value = (int) Math.floor(Double.parseDouble(m.group(2))); sql = sql.substring(0, m.start(1)) + "'" + value + "'" + sql.substring(m.end(3)); } return sql; }
DefaultQueryTransformer implements IQueryTransformer { @Override public String transform(String sql, String project, String defaultSchema) { Matcher m; while (true) { m = PIN_SUM_OF_CAST.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " SUM(" + m.group(1).trim() + ")" + sql.substring(m.end(), sql.length()); } while (true) { m = PIN_SUM_OF_FN_CONVERT.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " SUM(" + m.group(1).trim() + ")" + sql.substring(m.end(), sql.length()); } while (true) { m = PTN_HAVING_ESCAPE_FUNCTION.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + m.group(1) + sql.substring(m.end()); } m = PTN_HAVING_COUNT_GREATER_THAN_ZERO.matcher(sql); if (m.find() && PTN_GROUP_BY.matcher(sql).find() == false) { sql = sql.substring(0, m.start()) + " " + sql.substring(m.end()); } while (true) { m = PTN_SUM_1.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " COUNT(1) " + sql.substring(m.end()); } while (true) { m = PTN_MIN_1.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " 1 " + sql.substring(m.end()); } while (true) { m = PTN_MAX_1.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " 1 " + sql.substring(m.end()); } while (true) { m = PTN_NOT_EQ.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " <> " + sql.substring(m.end()); } while (true) { m = PTN_INTERVAL.matcher(sql); if (!m.find()) break; int value = (int) Math.floor(Double.parseDouble(m.group(2))); sql = sql.substring(0, m.start(1)) + "'" + value + "'" + sql.substring(m.end(3)); } return sql; } }
DefaultQueryTransformer implements IQueryTransformer { @Override public String transform(String sql, String project, String defaultSchema) { Matcher m; while (true) { m = PIN_SUM_OF_CAST.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " SUM(" + m.group(1).trim() + ")" + sql.substring(m.end(), sql.length()); } while (true) { m = PIN_SUM_OF_FN_CONVERT.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " SUM(" + m.group(1).trim() + ")" + sql.substring(m.end(), sql.length()); } while (true) { m = PTN_HAVING_ESCAPE_FUNCTION.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + m.group(1) + sql.substring(m.end()); } m = PTN_HAVING_COUNT_GREATER_THAN_ZERO.matcher(sql); if (m.find() && PTN_GROUP_BY.matcher(sql).find() == false) { sql = sql.substring(0, m.start()) + " " + sql.substring(m.end()); } while (true) { m = PTN_SUM_1.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " COUNT(1) " + sql.substring(m.end()); } while (true) { m = PTN_MIN_1.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " 1 " + sql.substring(m.end()); } while (true) { m = PTN_MAX_1.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " 1 " + sql.substring(m.end()); } while (true) { m = PTN_NOT_EQ.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " <> " + sql.substring(m.end()); } while (true) { m = PTN_INTERVAL.matcher(sql); if (!m.find()) break; int value = (int) Math.floor(Double.parseDouble(m.group(2))); sql = sql.substring(0, m.start(1)) + "'" + value + "'" + sql.substring(m.end(3)); } return sql; }  }
DefaultQueryTransformer implements IQueryTransformer { @Override public String transform(String sql, String project, String defaultSchema) { Matcher m; while (true) { m = PIN_SUM_OF_CAST.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " SUM(" + m.group(1).trim() + ")" + sql.substring(m.end(), sql.length()); } while (true) { m = PIN_SUM_OF_FN_CONVERT.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " SUM(" + m.group(1).trim() + ")" + sql.substring(m.end(), sql.length()); } while (true) { m = PTN_HAVING_ESCAPE_FUNCTION.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + m.group(1) + sql.substring(m.end()); } m = PTN_HAVING_COUNT_GREATER_THAN_ZERO.matcher(sql); if (m.find() && PTN_GROUP_BY.matcher(sql).find() == false) { sql = sql.substring(0, m.start()) + " " + sql.substring(m.end()); } while (true) { m = PTN_SUM_1.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " COUNT(1) " + sql.substring(m.end()); } while (true) { m = PTN_MIN_1.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " 1 " + sql.substring(m.end()); } while (true) { m = PTN_MAX_1.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " 1 " + sql.substring(m.end()); } while (true) { m = PTN_NOT_EQ.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " <> " + sql.substring(m.end()); } while (true) { m = PTN_INTERVAL.matcher(sql); if (!m.find()) break; int value = (int) Math.floor(Double.parseDouble(m.group(2))); sql = sql.substring(0, m.start(1)) + "'" + value + "'" + sql.substring(m.end(3)); } return sql; }  @Override String transform(String sql, String project, String defaultSchema); }
DefaultQueryTransformer implements IQueryTransformer { @Override public String transform(String sql, String project, String defaultSchema) { Matcher m; while (true) { m = PIN_SUM_OF_CAST.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " SUM(" + m.group(1).trim() + ")" + sql.substring(m.end(), sql.length()); } while (true) { m = PIN_SUM_OF_FN_CONVERT.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " SUM(" + m.group(1).trim() + ")" + sql.substring(m.end(), sql.length()); } while (true) { m = PTN_HAVING_ESCAPE_FUNCTION.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + m.group(1) + sql.substring(m.end()); } m = PTN_HAVING_COUNT_GREATER_THAN_ZERO.matcher(sql); if (m.find() && PTN_GROUP_BY.matcher(sql).find() == false) { sql = sql.substring(0, m.start()) + " " + sql.substring(m.end()); } while (true) { m = PTN_SUM_1.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " COUNT(1) " + sql.substring(m.end()); } while (true) { m = PTN_MIN_1.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " 1 " + sql.substring(m.end()); } while (true) { m = PTN_MAX_1.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " 1 " + sql.substring(m.end()); } while (true) { m = PTN_NOT_EQ.matcher(sql); if (!m.find()) break; sql = sql.substring(0, m.start()) + " <> " + sql.substring(m.end()); } while (true) { m = PTN_INTERVAL.matcher(sql); if (!m.find()) break; int value = (int) Math.floor(Double.parseDouble(m.group(2))); sql = sql.substring(0, m.start(1)) + "'" + value + "'" + sql.substring(m.end(3)); } return sql; }  @Override String transform(String sql, String project, String defaultSchema);  }