/*
* 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.query.QueryConstants.MILLIS_IN_DAY;
import static org.apache.phoenix.util.TestUtil.BTABLE_NAME;
import static org.apache.phoenix.util.TestUtil.PTSDB2_NAME;
import static org.apache.phoenix.util.TestUtil.PTSDB_NAME;
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.assertNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.Format;
import java.util.Properties;
import org.apache.phoenix.exception.SQLExceptionCode;
import org.apache.phoenix.schema.ConstraintViolationException;
import org.apache.phoenix.util.DateUtil;
import org.apache.phoenix.util.PropertiesUtil;
import org.junit.Test;
public class VariableLengthPKIT extends ParallelStatsDisabledIT {
private static final String DS1 = "1970-01-01 00:58:00";
private static final Date D1 = toDate(DS1);
private static Date toDate(String dateString) {
return DateUtil.parseDate(dateString);
}
protected static void initGroupByRowKeyColumns(String pTSDBtableName) throws Exception {
ensureTableCreated(getUrl(),pTSDBtableName, PTSDB_NAME, null, null, null);
// Insert all rows at ts
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
PreparedStatement stmt = conn.prepareStatement(
"upsert into " + pTSDBtableName+
" (" +
" INST, " +
" HOST," +
" \"DATE\")" +
"VALUES (?, ?, CURRENT_DATE())");
stmt.setString(1, "ab");
stmt.setString(2, "a");
stmt.execute();
stmt.setString(1, "ac");
stmt.setString(2, "b");
stmt.execute();
stmt.setString(1, "ad");
stmt.setString(2, "a");
stmt.execute();
conn.commit();
conn.close();
}
private static void initVarcharKeyTableValues(byte[][] splits, String varcharKeyTestTableName) throws Exception {
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
String ddl = "create table " +varcharKeyTestTableName+
" (pk varchar not null primary key)";
createTestTable(getUrl(), ddl, splits, null);
PreparedStatement stmt = conn.prepareStatement(
"upsert into " + varcharKeyTestTableName+
"(pk) " +
"VALUES (?)");
stmt.setString(1, " def");
stmt.execute();
stmt.setString(1, "jkl ");
stmt.execute();
stmt.setString(1, " ghi ");
stmt.execute();
conn.commit();
conn.close();
}
private static void initPTSDBTableValues(byte[][] splits, String pTSDBtableName) throws Exception {
ensureTableCreated(getUrl(),pTSDBtableName, PTSDB_NAME, splits, null, null);
// Insert all rows at ts
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
conn.setAutoCommit(true);
PreparedStatement stmt = conn.prepareStatement(
"upsert into " + pTSDBtableName +
" (" +
" INST, " +
" HOST," +
" \"DATE\"," +
" VAL)" +
"VALUES (?, ?, ?, ?)");
stmt.setString(1, "abc");
stmt.setString(2, "abc-def-ghi");
stmt.setDate(3, new Date(System.currentTimeMillis()));
stmt.setBigDecimal(4, new BigDecimal(.5));
stmt.execute();
conn.close();
}
private static void initBTableValues(byte[][] splits, String bTableName) throws Exception {
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
ensureTableCreated(getUrl(),bTableName, BTABLE_NAME, splits, null, null);
PreparedStatement stmt = conn.prepareStatement(
"upsert into " + bTableName+
" (" +
" A_STRING, " +
" A_ID," +
" B_STRING," +
" A_INTEGER," +
" B_INTEGER," +
" C_INTEGER," +
" D_STRING," +
" E_STRING)" +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
stmt.setString(1, "abc");
stmt.setString(2, "111");
stmt.setString(3, "x");
stmt.setInt(4, 1);
stmt.setInt(5, 10);
stmt.setInt(6, 1000);
stmt.setString(7, null);
stmt.setString(8, "0123456789");
stmt.execute();
stmt.setString(1, "abcd");
stmt.setString(2, "222");
stmt.setString(3, "xy");
stmt.setInt(4, 2);
stmt.setNull(5, Types.INTEGER);
stmt.setNull(6, Types.INTEGER);
stmt.execute();
stmt.setString(3, "xyz");
stmt.setInt(4, 3);
stmt.setInt(5, 10);
stmt.setInt(6, 1000);
stmt.setString(7, "efg");
stmt.execute();
stmt.setString(3, "xyzz");
stmt.setInt(4, 4);
stmt.setInt(5, 40);
stmt.setNull(6, Types.INTEGER);
stmt.setString(7, null);
stmt.execute();
conn.commit();
conn.close();
}
@Test
public void testSingleColumnScanKey() throws Exception {
String bTableName = generateUniqueName();
String query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM "+bTableName+" WHERE A_STRING=?";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null,bTableName);
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, "abc");
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("abc", rs.getString(1));
assertEquals("1", rs.getString(2));
assertEquals("x", rs.getString(3));
assertEquals(1, rs.getInt(4));
assertEquals(10, rs.getInt(5));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testSingleColumnGroupBy() throws Exception {
String pTSDBTableName = generateUniqueName();
String query = "SELECT INST FROM "+pTSDBTableName+" GROUP BY INST";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initPTSDBTableValues(null, pTSDBTableName);
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("abc", rs.getString(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testNonfirstColumnGroupBy() throws Exception {
String pTSDBTableName = generateUniqueName();
String query = "SELECT HOST FROM "+pTSDBTableName+" WHERE INST='abc' GROUP BY HOST";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initPTSDBTableValues(null, pTSDBTableName);
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("abc-def-ghi", rs.getString(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testGroupByRowKeyColumns() throws Exception {
String pTSDBTableName = generateUniqueName();
String query = "SELECT SUBSTR(INST,1,1),HOST FROM "+pTSDBTableName+" GROUP BY SUBSTR(INST,1,1),HOST";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initGroupByRowKeyColumns(pTSDBTableName);
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("a", rs.getString(1));
assertEquals("a", rs.getString(2));
assertTrue(rs.next());
assertEquals("a", rs.getString(1));
assertEquals("b", rs.getString(2));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testSkipScan() throws Exception {
String pTSDBTableName = generateUniqueName();
String query = "SELECT HOST FROM "+pTSDBTableName+" WHERE INST='abc' AND \"DATE\">=TO_DATE('1970-01-01 00:00:00') AND \"DATE\" <TO_DATE('2171-01-01 00:00:00')";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initPTSDBTableValues(null, pTSDBTableName);
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("abc-def-ghi", rs.getString(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testSkipMax() throws Exception {
String pTSDBTableName = generateUniqueName();
String query = "SELECT MAX(INST),MAX(\"DATE\") FROM "+pTSDBTableName+" WHERE INST='abc' AND \"DATE\">=TO_DATE('1970-01-01 00:00:00') AND \"DATE\" <TO_DATE('2171-01-01 00:00:00')";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initPTSDBTableValues(null, pTSDBTableName);
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("abc", rs.getString(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testSkipMaxWithLimit() throws Exception {
String pTSDBTableName = generateUniqueName();
String query = "SELECT MAX(INST),MAX(\"DATE\") FROM "+pTSDBTableName+" WHERE INST='abc' AND \"DATE\">=TO_DATE('1970-01-01 00:00:00') AND \"DATE\" <TO_DATE('2171-01-01 00:00:00') LIMIT 2";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initPTSDBTableValues(null, pTSDBTableName);
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("abc", rs.getString(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testSingleColumnKeyFilter() throws Exception {
String bTableName = generateUniqueName();
// Requires not null column to be projected, since the only one projected in the query is
// nullable and will cause the no key value to be returned if it is the only one projected.
String query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM "+bTableName+" WHERE B_STRING=?";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null, bTableName);
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, "xy");
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("abcd", rs.getString(1));
assertEquals("2", rs.getString(2));
assertEquals("xy", rs.getString(3));
assertEquals(2, rs.getInt(4));
assertEquals(0, rs.getInt(5));
assertTrue(rs.wasNull());
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testMultiColumnEqScanKey() throws Exception {
String bTableName = generateUniqueName();
String query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM "+bTableName+" WHERE A_STRING=? AND A_ID=? AND B_STRING=?";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null, bTableName);
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, "abcd");
statement.setString(2, "222");
statement.setString(3, "xy");
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("abcd", rs.getString(1));
assertEquals("2", rs.getString(2));
assertEquals("xy", rs.getString(3));
assertEquals(2, rs.getInt(4));
assertEquals(0, rs.getInt(5));
assertTrue(rs.wasNull());
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testMultiColumnGTScanKey() throws Exception {
String bTableName = generateUniqueName();
String query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM "+bTableName+" WHERE A_STRING=? AND A_ID=? AND B_STRING>?";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null, bTableName);
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, "abcd");
statement.setString(2, "222");
statement.setString(3, "xy");
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("abcd", rs.getString(1));
assertEquals("2", rs.getString(2));
assertEquals("xyz", rs.getString(3));
assertEquals(3, rs.getInt(4));
assertEquals(10, rs.getInt(5));
assertTrue(rs.next());
assertEquals("abcd", rs.getString(1));
assertEquals("2", rs.getString(2));
assertEquals("xyzz", rs.getString(3));
assertEquals(4, rs.getInt(4));
assertEquals(40, rs.getInt(5));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testMultiColumnGTKeyFilter() throws Exception {
String bTableName = generateUniqueName();
String query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM "+bTableName+" WHERE A_STRING>? AND A_INTEGER>=?";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null, bTableName);
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, "abc");
statement.setInt(2, 4);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("abcd", rs.getString(1));
assertEquals("2", rs.getString(2));
assertEquals("xyzz", rs.getString(3));
assertEquals(4, rs.getInt(4));
assertEquals(40, rs.getInt(5));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testNullValueEqualityScan() throws Exception {
String pTSDBTableName = generateUniqueName();
ensureTableCreated(getUrl(),pTSDBTableName, PTSDB_NAME, null, null, null);
// Insert all rows at ts
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
conn.setAutoCommit(true);
PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDBTableName+" VALUES ('', '', ?, 0.5)");
stmt.setDate(1, D1);
stmt.execute();
conn.close();
// Comparisons against null are always false.
String query = "SELECT HOST,\"DATE\" FROM "+pTSDBTableName+" WHERE HOST='' AND INST=''";
url = getUrl();
conn = DriverManager.getConnection(url, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testVarLengthPKColScan() throws Exception {
String pTSDBTableName = generateUniqueName();
ensureTableCreated(getUrl(),pTSDBTableName, PTSDB_NAME, null, null, null);
String url = getUrl(); // Insert at timestamp 0
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
conn.setAutoCommit(true);
PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDBTableName+" VALUES (?, 'y', ?, 0.5)");
stmt.setString(1, "x");
stmt.setDate(2, D1);
stmt.execute();
stmt.setString(1, "xy");
stmt.execute();
conn.close();
String query = "SELECT HOST,\"DATE\" FROM "+pTSDBTableName+" WHERE INST='x' AND HOST='y'";
url = getUrl();
conn = DriverManager.getConnection(url, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(D1, rs.getDate(2));
} finally {
conn.close();
}
}
@Test
public void testEscapedQuoteScan() throws Exception {
String pTSDBTableName = generateUniqueName();
ensureTableCreated(getUrl(), pTSDBTableName, PTSDB_NAME, null, null, null);
String url = getUrl(); // Insert at timestamp 0
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
conn.setAutoCommit(true);
PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDBTableName+" VALUES (?, 'y', ?, 0.5)");
stmt.setString(1, "x'y");
stmt.setDate(2, D1);
stmt.execute();
stmt.setString(1, "x");
stmt.execute();
conn.close();
String query1 = "SELECT INST,\"DATE\" FROM "+pTSDBTableName+" WHERE INST='x''y'";
String query2 = "SELECT INST,\"DATE\" FROM "+pTSDBTableName+" WHERE INST='x\\\'y'";
url = getUrl();
conn = DriverManager.getConnection(url, props);
try {
PreparedStatement statement = conn.prepareStatement(query1);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("x'y", rs.getString(1));
assertEquals(D1, rs.getDate(2));
assertFalse(rs.next());
statement = conn.prepareStatement(query2);
rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("x'y", rs.getString(1));
assertEquals(D1, rs.getDate(2));
assertFalse(rs.next());
} finally {
conn.close();
}
}
private static void initPTSDBTableValues1(String pTSDBTableName) throws Exception {
ensureTableCreated(getUrl(),pTSDBTableName, PTSDB_NAME, null, null, null);
String url = getUrl(); // Insert at timestamp 0
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
conn.setAutoCommit(true);
PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDBTableName+" VALUES ('x', 'y', ?, 0.5)");
stmt.setDate(1, D1);
stmt.execute();
conn.close();
}
@Test
public void testToStringOnDate() throws Exception {
String pTSDBTableName = generateUniqueName();
initPTSDBTableValues1(pTSDBTableName);
String query = "SELECT HOST,\"DATE\" FROM "+pTSDBTableName+" WHERE INST='x' AND HOST='y'";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(DateUtil.DEFAULT_DATE_FORMATTER.format(D1), rs.getString(2));
assertFalse(rs.next());
} finally {
conn.close();
}
}
private static void initPTSDBTableValues2(String pTSDB2TableName, Date d) throws Exception {
ensureTableCreated(getUrl(),pTSDB2TableName, PTSDB2_NAME, null, null, null);
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
conn.setAutoCommit(true);
PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDB2TableName+"(inst,\"DATE\",val2) VALUES (?, ?, ?)");
stmt.setString(1, "a");
stmt.setDate(2, d);
stmt.setDouble(3, 101.3);
stmt.execute();
stmt.setString(1, "a");
stmt.setDate(2, new Date(d.getTime() + 1 * MILLIS_IN_DAY));
stmt.setDouble(3, 99.7);
stmt.execute();
stmt.setString(1, "a");
stmt.setDate(2, new Date(d.getTime() - 1 * MILLIS_IN_DAY));
stmt.setDouble(3, 105.3);
stmt.execute();
stmt.setString(1, "b");
stmt.setDate(2, d);
stmt.setDouble(3, 88.5);
stmt.execute();
stmt.setString(1, "b");
stmt.setDate(2, new Date(d.getTime() + 1 * MILLIS_IN_DAY));
stmt.setDouble(3, 89.7);
stmt.execute();
stmt.setString(1, "b");
stmt.setDate(2, new Date(d.getTime() - 1 * MILLIS_IN_DAY));
stmt.setDouble(3, 94.9);
stmt.execute();
conn.close();
}
@Test
public void testRoundOnDate() throws Exception {
String pTSDB2TableName = generateUniqueName();
Date date = new Date(System.currentTimeMillis());
initPTSDBTableValues2(pTSDB2TableName, date);
String query = "SELECT MAX(val2)"
+ " FROM "+pTSDB2TableName
+ " WHERE inst='a'"
+ " GROUP BY ROUND(\"DATE\",'day',1)"
+ " ORDER BY MAX(val2)"; // disambiguate row order
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(99.7, rs.getDouble(1), 1e-6);
assertTrue(rs.next());
assertEquals(101.3, rs.getDouble(1), 1e-6);
assertTrue(rs.next());
assertEquals(105.3, rs.getDouble(1), 1e-6);
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testOrderBy() throws Exception {
String pTSDB2TableName = generateUniqueName();
Date date = new Date(System.currentTimeMillis());
initPTSDBTableValues2(pTSDB2TableName, date);
String query = "SELECT inst,MAX(val2),MIN(val2)"
+ " FROM "+pTSDB2TableName
+ " GROUP BY inst,ROUND(\"DATE\",'day',1)"
+ " ORDER BY inst,ROUND(\"DATE\",'day',1)"
;
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("a", rs.getString(1));
assertEquals(105.3, rs.getDouble(2), 1e-6);
assertEquals(105.3, rs.getDouble(3), 1e-6);
assertTrue(rs.next());
assertEquals("a", rs.getString(1));
assertEquals(101.3, rs.getDouble(2), 1e-6);
assertEquals(101.3, rs.getDouble(3), 1e-6);
assertTrue(rs.next());
assertEquals("a", rs.getString(1));
assertEquals(99.7, rs.getDouble(2), 1e-6);
assertEquals(99.7, rs.getDouble(3), 1e-6);
assertTrue(rs.next());
assertEquals("b", rs.getString(1));
assertEquals(94.9, rs.getDouble(2), 1e-6);
assertEquals(94.9, rs.getDouble(3), 1e-6);
assertTrue(rs.next());
assertEquals("b", rs.getString(1));
assertEquals(88.5, rs.getDouble(2), 1e-6);
assertEquals(88.5, rs.getDouble(3), 1e-6);
assertTrue(rs.next());
assertEquals("b", rs.getString(1));
assertEquals(89.7, rs.getDouble(2), 1e-6);
assertEquals(89.7, rs.getDouble(3), 1e-6);
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testSelectCount() throws Exception {
String pTSDB2TableName = generateUniqueName();
Date date = new Date(System.currentTimeMillis());
initPTSDBTableValues2(pTSDB2TableName, date);
String query = "SELECT COUNT(*)"
+ " FROM "+pTSDB2TableName
+ " WHERE inst='a'";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(3, rs.getInt(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testBatchUpsert() throws Exception {
String pTSDB2TableName = generateUniqueName();
Date d = new Date(System.currentTimeMillis());
ensureTableCreated(getUrl(),pTSDB2TableName, PTSDB2_NAME, null, null, null);
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
String query = "SELECT SUM(val1),SUM(val2),SUM(val3) FROM "+pTSDB2TableName;
String sql1 = "UPSERT INTO "+pTSDB2TableName+"(inst,\"DATE\",val1) VALUES (?, ?, ?)";
String sql2 = "UPSERT INTO "+pTSDB2TableName+"(inst,\"DATE\",val2) VALUES (?, ?, ?)";
String sql3 = "UPSERT INTO "+pTSDB2TableName+"(inst,\"DATE\",val3) VALUES (?, ?, ?)";
Connection conn = DriverManager.getConnection(getUrl(), props);
conn.setAutoCommit(false);
// conn.setAutoCommit(true);
{
// verify precondition: SUM(val{1,2,3}) are null
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertNull(rs.getBigDecimal(1));
assertNull(rs.getBigDecimal(2));
assertNull(rs.getBigDecimal(3));
assertFalse(rs.next());
statement.close();
}
{
PreparedStatement s = conn.prepareStatement(sql1);
s.setString(1, "a");
s.setDate(2, d);
s.setInt(3, 1);
assertEquals(1, s.executeUpdate());
s.close();
}
{
PreparedStatement s = conn.prepareStatement(sql2);
s.setString(1, "b");
s.setDate(2, d);
s.setInt(3, 1);
assertEquals(1, s.executeUpdate());
s.close();
}
{
PreparedStatement s = conn.prepareStatement(sql3);
s.setString(1, "c");
s.setDate(2, d);
s.setInt(3, 1);
assertEquals(1, s.executeUpdate());
s.close();
}
{
PreparedStatement s = conn.prepareStatement(sql1);
s.setString(1, "a");
s.setDate(2, d);
s.setInt(3, 5);
assertEquals(1, s.executeUpdate());
s.close();
}
{
PreparedStatement s = conn.prepareStatement(sql1);
s.setString(1, "b");
s.setDate(2, d);
s.setInt(3, 5);
assertEquals(1, s.executeUpdate());
s.close();
}
{
PreparedStatement s = conn.prepareStatement(sql1);
s.setString(1, "c");
s.setDate(2, d);
s.setInt(3, 5);
assertEquals(1, s.executeUpdate());
s.close();
}
conn.commit();
conn.close();
// Query at a time after the upsert to confirm they took place
conn = DriverManager.getConnection(getUrl(), props);
{
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(15, rs.getDouble(1), 1e-6);
assertEquals(1, rs.getDouble(2), 1e-6);
assertEquals(1, rs.getDouble(3), 1e-6);
assertFalse(rs.next());
statement.close();
}
}
@Test
public void testSelectStar() throws Exception {
String pTSDBTableName = generateUniqueName();
initPTSDBTableValues1(pTSDBTableName);
String query = "SELECT * FROM "+pTSDBTableName+" WHERE INST='x' AND HOST='y'";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("x",rs.getString("inst"));
assertEquals("y",rs.getString("host"));
assertEquals(D1, rs.getDate("DATE"));
assertEquals(BigDecimal.valueOf(0.5), rs.getBigDecimal("val"));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testToCharOnDate() throws Exception {
String pTSDBTableName = generateUniqueName();
initPTSDBTableValues1(pTSDBTableName);
String query = "SELECT HOST,TO_CHAR(\"DATE\") FROM "+pTSDBTableName+" WHERE INST='x' AND HOST='y'";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(DateUtil.DEFAULT_DATE_FORMATTER.format(D1), rs.getString(2));
} finally {
conn.close();
}
}
@Test
public void testToCharWithFormatOnDate() throws Exception {
String pTSDBTableName = generateUniqueName();
initPTSDBTableValues1(pTSDBTableName);
String format = "HH:mm:ss";
Format dateFormatter = DateUtil.getDateFormatter(format);
String query = "SELECT HOST,TO_CHAR(\"DATE\",'" + format + "') FROM "+pTSDBTableName+" WHERE INST='x' AND HOST='y'";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(dateFormatter.format(D1), rs.getString(2));
} finally {
conn.close();
}
}
@Test
public void testToDateWithFormatOnDate() throws Exception {
String pTSDBTableName = generateUniqueName();
initPTSDBTableValues1(pTSDBTableName);
String format = "yyyy-MM-dd HH:mm:ss.S";
Format dateFormatter = DateUtil.getDateFormatter(format);
String query = "SELECT HOST,TO_CHAR(\"DATE\",'" + format + "') FROM "+pTSDBTableName+" WHERE INST='x' AND HOST='y' and \"DATE\"=TO_DATE(?,'" + format + "')";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, dateFormatter.format(D1));
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(dateFormatter.format(D1), rs.getString(2));
} finally {
conn.close();
}
}
@Test
public void testMissingPKColumn() throws Exception {
String pTSDBTableName = generateUniqueName();
ensureTableCreated(getUrl(),pTSDBTableName, PTSDB_NAME, null, null, null);
String url = getUrl(); // Insert at timestamp 0
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
conn.setAutoCommit(true);
Statement stmt = conn.createStatement();
try {
stmt.execute("upsert into "+pTSDBTableName+"(INST,HOST,VAL) VALUES ('abc', 'abc-def-ghi', 0.5)");
fail();
} catch (SQLException e) {
assertEquals(SQLExceptionCode.CONSTRAINT_VIOLATION.getErrorCode(), e.getErrorCode());
} finally {
conn.close();
}
}
@Test
public void testNoKVColumn() throws Exception {
String pTSDBTableName = generateUniqueName();
ensureTableCreated(getUrl(),pTSDBTableName, BTABLE_NAME, null, null, null);
String url = getUrl(); // Insert at timestamp 0
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
conn.setAutoCommit(true);
PreparedStatement stmt = conn.prepareStatement(
"upsert into "+pTSDBTableName+" VALUES (?, ?, ?, ?, ?)");
stmt.setString(1, "abc");
stmt.setString(2, "123");
stmt.setString(3, "x");
stmt.setInt(4, 1);
stmt.setString(5, "ab");
// Succeeds since we have an empty KV
stmt.execute();
}
@Test
public void testTooShortKVColumn() throws Exception {
String pTSDBTableName = generateUniqueName();
ensureTableCreated(getUrl(),pTSDBTableName, BTABLE_NAME, null, null, null);
String url = getUrl(); // Insert at timestamp 0
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
conn.setAutoCommit(true);
// Insert all rows at ts
PreparedStatement stmt = conn.prepareStatement(
"upsert into " + pTSDBTableName +
" (" +
" A_STRING, " +
" A_ID," +
" B_STRING," +
" A_INTEGER," +
" C_STRING," +
" E_STRING)" +
"VALUES (?, ?, ?, ?, ?, ?)");
stmt.setString(1, "abc");
stmt.setString(2, "123");
stmt.setString(3, "x");
stmt.setInt(4, 1);
stmt.setString(5, "ab");
stmt.setString(6, "01234");
try {
stmt.execute();
} catch (ConstraintViolationException e) {
fail("Constraint voilation Exception should not be thrown, the characters have to be padded");
} finally {
conn.close();
}
}
@Test
public void testTooShortPKColumn() throws Exception {
String pTSDBTableName = generateUniqueName();
ensureTableCreated(getUrl(),pTSDBTableName, BTABLE_NAME, null, null, null);
String url = getUrl(); // Insert at timestamp 0
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
conn.setAutoCommit(true);
// Insert all rows at ts
PreparedStatement stmt = conn.prepareStatement(
"upsert into " + pTSDBTableName+
" (" +
" A_STRING, " +
" A_ID," +
" B_STRING," +
" A_INTEGER," +
" C_STRING," +
" E_STRING)" +
"VALUES (?, ?, ?, ?, ?, ?)");
stmt.setString(1, "abc");
stmt.setString(2, "12");
stmt.setString(3, "x");
stmt.setInt(4, 1);
stmt.setString(5, "ab");
stmt.setString(6, "0123456789");
try {
stmt.execute();
} catch (ConstraintViolationException e) {
fail("Constraint voilation Exception should not be thrown, the characters have to be padded");
} finally {
conn.close();
}
}
@Test
public void testTooLongPKColumn() throws Exception {
String bTableName = generateUniqueName();
ensureTableCreated(getUrl(),bTableName, BTABLE_NAME, null, null, null);
String url = getUrl(); // Insert at timestamp 0
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
conn.setAutoCommit(true);
// Insert all rows at ts
PreparedStatement stmt = conn.prepareStatement(
"upsert into " + bTableName+
"(" +
" A_STRING, " +
" A_ID," +
" B_STRING," +
" A_INTEGER," +
" C_STRING," +
" E_STRING)" +
"VALUES (?, ?, ?, ?, ?, ?)");
stmt.setString(1, "abc");
stmt.setString(2, "123");
stmt.setString(3, "x");
stmt.setInt(4, 1);
stmt.setString(5, "abc");
stmt.setString(6, "0123456789");
try {
stmt.execute();
fail();
} catch (SQLException e) {
assertEquals(SQLExceptionCode.DATA_EXCEEDS_MAX_CAPACITY.getErrorCode(),e.getErrorCode());
} finally {
conn.close();
}
}
@Test
public void testTooLongKVColumn() throws Exception {
String bTableName = generateUniqueName();
ensureTableCreated(getUrl(),bTableName, BTABLE_NAME, null, null, null);
String url = getUrl(); // Insert at timestamp 0
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
conn.setAutoCommit(true);
// Insert all rows at ts
PreparedStatement stmt = conn.prepareStatement(
"upsert into " + bTableName+
"(" +
" A_STRING, " +
" A_ID," +
" B_STRING," +
" A_INTEGER," +
" C_STRING," +
" D_STRING," +
" E_STRING)" +
"VALUES (?, ?, ?, ?, ?, ?, ?)");
stmt.setString(1, "abc");
stmt.setString(2, "123");
stmt.setString(3, "x");
stmt.setInt(4, 1);
stmt.setString(5, "ab");
stmt.setString(6,"abcd");
stmt.setString(7, "0123456789");
try {
stmt.execute();
fail();
} catch (SQLException e) {
assertEquals(SQLExceptionCode.DATA_EXCEEDS_MAX_CAPACITY.getErrorCode(),e.getErrorCode());
} finally {
conn.close();
}
}
@Test
public void testMultiFixedLengthNull() throws Exception {
String bTableName = generateUniqueName();
String query = "SELECT B_INTEGER,C_INTEGER,COUNT(1) FROM "+bTableName+" GROUP BY B_INTEGER,C_INTEGER";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null, bTableName);
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(0, rs.getInt(1));
assertTrue(rs.wasNull());
assertEquals(0, rs.getInt(2));
assertTrue(rs.wasNull());
assertEquals(1, rs.getLong(3));
assertTrue(rs.next());
assertEquals(10, rs.getInt(1));
assertEquals(1000, rs.getInt(2));
assertEquals(2, rs.getLong(3));
assertTrue(rs.next());
assertEquals(40, rs.getInt(1));
assertEquals(0, rs.getInt(2));
assertTrue(rs.wasNull());
assertEquals(1, rs.getLong(3));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testSingleFixedLengthNull() throws Exception {
String bTableName = generateUniqueName();
String query = "SELECT C_INTEGER,COUNT(1) FROM "+bTableName+" GROUP BY C_INTEGER";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null,bTableName);
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(0, rs.getInt(1));
assertTrue(rs.wasNull());
assertEquals(2, rs.getLong(2));
assertTrue(rs.next());
assertEquals(1000, rs.getInt(1));
assertEquals(2, rs.getLong(2));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testMultiMixedTypeGroupBy() throws Exception {
String bTableName = generateUniqueName();
String query = "SELECT A_ID, E_STRING, D_STRING, C_INTEGER, COUNT(1) FROM "+bTableName+" GROUP BY A_ID, E_STRING, D_STRING, C_INTEGER";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null,bTableName);
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("111", rs.getString(1));
assertEquals("0123456789", rs.getString(2));
assertEquals(null, rs.getString(3));
assertEquals(1000, rs.getInt(4));
assertEquals(1, rs.getInt(5));
assertTrue(rs.next());
assertEquals("222", rs.getString(1));
assertEquals("0123456789", rs.getString(2));
assertEquals(null, rs.getString(3));
assertEquals(0, rs.getInt(4));
assertTrue(rs.wasNull());
assertEquals(2, rs.getInt(5));
assertTrue(rs.next());
assertEquals("222", rs.getString(1));
assertEquals("0123456789", rs.getString(2));
assertEquals("efg", rs.getString(3));
assertEquals(1000, rs.getInt(4));
assertEquals(1, rs.getInt(5));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testSubstrFunction() throws Exception {
String bTableName = generateUniqueName();
String varcharKeyTestTable = generateUniqueName();
String query[] = {
"SELECT substr('ABC',-1,1) FROM "+bTableName+" LIMIT 1",
"SELECT substr('ABC',-4,1) FROM "+bTableName+" LIMIT 1",
"SELECT substr('ABC',2,4) FROM "+bTableName+" LIMIT 1",
"SELECT substr('ABC',1,1) FROM "+bTableName+" LIMIT 1",
"SELECT substr('ABC',0,1) FROM "+bTableName+" LIMIT 1",
// Test for multibyte characters support.
"SELECT substr('ĎďĒ',0,1) FROM "+bTableName+" LIMIT 1",
"SELECT substr('ĎďĒ',0,2) FROM "+bTableName+" LIMIT 1",
"SELECT substr('ĎďĒ',1,1) FROM "+bTableName+" LIMIT 1",
"SELECT substr('ĎďĒ',1,2) FROM "+bTableName+" LIMIT 1",
"SELECT substr('ĎďĒ',2,1) FROM "+bTableName+" LIMIT 1",
"SELECT substr('ĎďĒ',2,2) FROM "+bTableName+" LIMIT 1",
"SELECT substr('ĎďĒ',-1,1) FROM "+bTableName+" LIMIT 1",
"SELECT substr('Ďďɚʍ',2,4) FROM "+bTableName+" LIMIT 1",
"SELECT pk FROM "+varcharKeyTestTable+" WHERE substr(pk, 0, 3)='jkl'",
};
String result[] = {
"C",
null,
"BC",
"A",
"A",
"Ď",
"Ďď",
"Ď",
"Ďď",
"ď",
"ďĒ",
"Ē",
"ďɚʍ",
"jkl ",
};
assertEquals(query.length,result.length);
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null,bTableName);
initVarcharKeyTableValues(null,varcharKeyTestTable);
for (int i = 0; i < query.length; i++) {
PreparedStatement statement = conn.prepareStatement(query[i]);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(result[i], rs.getString(1));
assertFalse(rs.next());
}
} finally {
conn.close();
}
}
@Test
public void testRegexReplaceFunction() throws Exception {
String bTableName = generateUniqueName();
// NOTE: we need to double escape the "\\" here because conn.prepareStatement would
// also try to evaluate the escaping. As a result, to represent what normally would be
// a "\d" in this test, it would become "\\\\d".
String query[] = {
"SELECT regexp_replace('', '') FROM "+bTableName+" LIMIT 1",
"SELECT regexp_replace('', 'abc', 'def') FROM "+bTableName+" LIMIT 1",
"SELECT regexp_replace('123abcABC', '[a-z]+') FROM "+bTableName+" LIMIT 1",
"SELECT regexp_replace('123-abc-ABC', '-[a-zA-Z-]+') FROM "+bTableName+" LIMIT 1",
"SELECT regexp_replace('abcABC123', '\\\\d+', '') FROM "+bTableName+" LIMIT 1",
"SELECT regexp_replace('abcABC123', '\\\\D+', '') FROM "+bTableName+" LIMIT 1",
"SELECT regexp_replace('abc', 'abc', 'def') FROM "+bTableName+" LIMIT 1",
"SELECT regexp_replace('abc123ABC', '\\\\d+', 'def') FROM "+bTableName+" LIMIT 1",
"SELECT regexp_replace('abc123ABC', '[0-9]+', '#') FROM "+bTableName+" LIMIT 1",
"SELECT CASE WHEN regexp_replace('abcABC123', '[a-zA-Z]+') = '123' THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
"SELECT A_STRING FROM "+bTableName+" WHERE A_ID = regexp_replace('abcABC111', '[a-zA-Z]+') LIMIT 1", // 111
// Test for multibyte characters support.
"SELECT regexp_replace('Ďď Ēĕ ĜĞ ϗϘϛϢ', '[a-zA-Z]+') FROM "+bTableName+" LIMIT 1",
"SELECT regexp_replace('Ďď Ēĕ ĜĞ ϗϘϛϢ', '[Ď-ě]+', '#') FROM "+bTableName+" LIMIT 1",
"SELECT regexp_replace('Ďď Ēĕ ĜĞ ϗϘϛϢ', '.+', 'replacement') FROM "+bTableName+" LIMIT 1",
"SELECT regexp_replace('Ďď Ēĕ ĜĞ ϗϘϛϢ', 'Ďď', 'DD') FROM "+bTableName+" LIMIT 1",
};
String result[] = {
null,
null,
"123ABC",
"123",
"abcABC",
"123",
"def",
"abcdefABC",
"abc#ABC",
"1",
"abc", // the first column
"Ďď Ēĕ ĜĞ ϗϘϛϢ",
"# # ĜĞ ϗϘϛϢ",
"replacement",
"DD Ēĕ ĜĞ ϗϘϛϢ",
};
assertEquals(query.length,result.length);
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null,bTableName);
for (int i = 0; i < query.length; i++) {
PreparedStatement statement = conn.prepareStatement(query[i]);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(result[i], rs.getString(1));
assertFalse(rs.next());
}
} finally {
conn.close();
}
}
@Test
public void testRegexpSubstrFunction() throws Exception {
String bTableName = generateUniqueName();
String query[] = {
"SELECT regexp_substr('', '', 0) FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('', '', 1) FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('', 'abc', 0) FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('abc', '', 0) FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('123', '123', 3) FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('123', '123', -4) FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('123ABC', '[a-z]+', 0) FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('123ABC', '[0-9]+', 4) FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('123ABCabc', '\\\\d+', 0) FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('123ABCabc', '\\\\D+', 0) FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('123ABCabc', '\\\\D+', 4) FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('123ABCabc', '\\\\D+', 7) FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('na11-app5-26-sjl', '[^-]+', 0) FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('na11-app5-26-sjl', '[^-]+') FROM "+bTableName+" LIMIT 1",
// Test for multibyte characters support.
"SELECT regexp_substr('ĎďĒĕĜĞ', '.+') FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('ĎďĒĕĜĞ', '.+', 3) FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('ĎďĒĕĜĞ', '[a-zA-Z]+', 0) FROM "+bTableName+" LIMIT 1",
"SELECT regexp_substr('ĎďĒĕĜĞ', '[Ď-ě]+', 3) FROM "+bTableName+" LIMIT 1",
};
String result[] = {
null,
null,
null,
null,
null,
null,
null,
null,
"123",
"ABCabc",
"ABCabc",
"abc",
"na11",
"na11",
"ĎďĒĕĜĞ",
"ĒĕĜĞ",
null,
"Ēĕ",
};
assertEquals(query.length,result.length);
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null,bTableName);
for (int i = 0; i < query.length; i++) {
PreparedStatement statement = conn.prepareStatement(query[i]);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(result[i], rs.getString(1));
assertFalse(rs.next());
}
} finally {
conn.close();
}
}
@Test
public void testRegexpSubstrFunction2() throws Exception {
String tTableName = generateUniqueName();
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
String ddl = "create table " + tTableName + " (k INTEGER NOT NULL PRIMARY KEY, name VARCHAR)";
conn.createStatement().execute(ddl);
conn.close();
String dml = "upsert into " + tTableName + " values(?,?)";
conn = DriverManager.getConnection(url, props);
PreparedStatement stmt = conn.prepareStatement(dml);
String[] values = new String[] {"satax","jruls","hrjcu","yqtrv","jjcvw"};
for (int i = 0; i < values.length; i++) {
stmt.setInt(1, i+1);
stmt.setString(2, values[i]);
stmt.execute();
}
conn.commit();
conn.close();
// This matches what Oracle returns for regexp_substr, even through
// it seems oke for "satax", it should return null.
String query = "select regexp_substr(name,'[^s]+',1) from " + tTableName + " limit 5";
conn = DriverManager.getConnection(url, props);
ResultSet rs = conn.createStatement().executeQuery(query);
int count = 0;
String[] results = new String[] {"atax","jrul","hrjcu","yqtrv","jjcvw"};
while (rs.next()) {
assertEquals(results[count],rs.getString(1));
count++;
}
}
@Test
public void testLikeConstant() throws Exception {
String bTableName = generateUniqueName();
String query[] = {
"SELECT CASE WHEN 'ABC' LIKE '' THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
"SELECT CASE WHEN 'ABC' LIKE 'A_' THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
"SELECT CASE WHEN 'ABC' LIKE 'A__' THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
"SELECT CASE WHEN 'AB_C' LIKE 'AB\\_C' THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
"SELECT CASE WHEN 'ABC%DE' LIKE 'ABC\\%D%' THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
};
String result[] = {
"2",
"2",
"1",
"1",
"1",
};
assertEquals(query.length,result.length);
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null,bTableName);
for (int i = 0; i < query.length; i++) {
PreparedStatement statement = conn.prepareStatement(query[i]);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(query[i],result[i], rs.getString(1));
assertFalse(rs.next());
}
} finally {
conn.close();
}
}
@Test
public void testInListConstant() throws Exception {
String bTableName = generateUniqueName();
String query[] = {
"SELECT CASE WHEN 'a' IN (null,'a') THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
"SELECT CASE WHEN NOT 'a' IN (null,'b') THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
"SELECT CASE WHEN 'a' IN (null,'b') THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
"SELECT CASE WHEN NOT 'a' IN ('c','b') THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
"SELECT CASE WHEN 1 IN ('foo',2,1) THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
"SELECT CASE WHEN NOT null IN ('c','b') THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
"SELECT CASE WHEN NOT null IN (null,'c','b') THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
"SELECT CASE WHEN null IN (null,'c','b') THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
"SELECT CASE WHEN 'a' IN (null,1) THEN '1' ELSE '2' END FROM "+bTableName+" LIMIT 1",
};
String result[] = {
"1",
"1",
"2",
"1",
"1",
"2",
"2",
"2",
"2"
};
assertEquals(query.length,result.length);
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null,bTableName);
for (int i = 0; i < query.length; i++) {
PreparedStatement statement = conn.prepareStatement(query[i]);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(query[i],result[i], rs.getString(1));
assertFalse(rs.next());
}
} finally {
conn.close();
}
}
@Test
public void testLikeOnColumn() throws Exception {
String pTSDBTableName = generateUniqueName();
ensureTableCreated(getUrl(),pTSDBTableName, PTSDB_NAME, null, null, null);
// Insert all rows at ts
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDBTableName+" VALUES (?, ?, ?, 0.5)");
stmt.setDate(3, D1);
stmt.setString(1, "a");
stmt.setString(2, "a");
stmt.execute();
stmt.setString(1, "x");
stmt.setString(2, "a");
stmt.execute();
stmt.setString(1, "xy");
stmt.setString(2, "b");
stmt.execute();
stmt.setString(1, "xyz");
stmt.setString(2, "c");
stmt.execute();
stmt.setString(1, "xyza");
stmt.setString(2, "d");
stmt.execute();
stmt.setString(1, "xyzab");
stmt.setString(2, "e");
stmt.execute();
stmt.setString(1, "z");
stmt.setString(2, "e");
stmt.execute();
conn.commit();
conn.close();
url = getUrl();
conn = DriverManager.getConnection(url, props);
PreparedStatement statement;
ResultSet rs;
try {
// Test 1
statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE INST LIKE 'x%'");
rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("x", rs.getString(1));
assertTrue(rs.next());
assertEquals("xy", rs.getString(1));
assertTrue(rs.next());
assertEquals("xyz", rs.getString(1));
assertTrue(rs.next());
assertEquals("xyza", rs.getString(1));
assertTrue(rs.next());
assertEquals("xyzab", rs.getString(1));
assertFalse(rs.next());
// Test 2
statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE INST LIKE 'xy_a%'");
rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("xyza", rs.getString(1));
assertTrue(rs.next());
assertEquals("xyzab", rs.getString(1));
assertFalse(rs.next());
// Test 3
statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE INST NOT LIKE 'xy_a%'");
rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("a", rs.getString(1));
assertTrue(rs.next());
assertEquals("x", rs.getString(1));
assertTrue(rs.next());
assertEquals("xy", rs.getString(1));
assertTrue(rs.next());
assertEquals("xyz", rs.getString(1));
assertTrue(rs.next());
assertEquals("z", rs.getString(1));
assertFalse(rs.next());
// Test 4
statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE 'xzabc' LIKE 'xy_a%'");
rs = statement.executeQuery();
assertFalse(rs.next());
// Test 5
statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE 'abcdef' LIKE '%bCd%'");
rs = statement.executeQuery();
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testILikeOnColumn() throws Exception {
String pTSDBTableName = generateUniqueName();
ensureTableCreated(getUrl(),pTSDBTableName, PTSDB_NAME, null, null, null);
// Insert all rows at ts
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDBTableName+"(INST, HOST, \"DATE\", VAL, PATTERN VARCHAR) VALUES (?, ?, ?, 0.5, 'x_Z%')");
stmt.setDate(3, D1);
stmt.setString(1, "a");
stmt.setString(2, "a");
stmt.execute();
stmt.setString(1, "x");
stmt.setString(2, "a");
stmt.execute();
stmt.setString(1, "xy");
stmt.setString(2, "b");
stmt.execute();
stmt.setString(1, "xyz");
stmt.setString(2, "c");
stmt.execute();
stmt.setString(1, "xyza");
stmt.setString(2, "d");
stmt.execute();
stmt.setString(1, "xyzab");
stmt.setString(2, "e");
stmt.execute();
stmt.setString(1, "z");
stmt.setString(2, "e");
stmt.execute();
conn.commit();
conn.close();
url = getUrl();
conn = DriverManager.getConnection(url, props);
PreparedStatement statement;
ResultSet rs;
try {
// Test 1
statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE INST ILIKE 'x%'");
rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("x", rs.getString(1));
assertTrue(rs.next());
assertEquals("xy", rs.getString(1));
assertTrue(rs.next());
assertEquals("xyz", rs.getString(1));
assertTrue(rs.next());
assertEquals("xyza", rs.getString(1));
assertTrue(rs.next());
assertEquals("xyzab", rs.getString(1));
assertFalse(rs.next());
// Test 2
statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE INST ILIKE 'xy_a%'");
rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("xyza", rs.getString(1));
assertTrue(rs.next());
assertEquals("xyzab", rs.getString(1));
assertFalse(rs.next());
// Test 3
statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE INST NOT ILIKE 'xy_a%'");
rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("a", rs.getString(1));
assertTrue(rs.next());
assertEquals("x", rs.getString(1));
assertTrue(rs.next());
assertEquals("xy", rs.getString(1));
assertTrue(rs.next());
assertEquals("xyz", rs.getString(1));
assertTrue(rs.next());
assertEquals("z", rs.getString(1));
assertFalse(rs.next());
// Test 4
statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE 'xzabc' ILIKE 'xy_a%'");
rs = statement.executeQuery();
assertFalse(rs.next());
// Test 5
statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+" WHERE 'abcdef' ILIKE '%bCd%'");
rs = statement.executeQuery();
assertTrue(rs.next());
// Test 5
statement = conn.prepareStatement("SELECT INST FROM "+pTSDBTableName+"(PATTERN VARCHAR) WHERE INST ILIKE PATTERN");
rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("xyz", rs.getString(1));
assertTrue(rs.next());
assertEquals("xyza", rs.getString(1));
assertTrue(rs.next());
assertEquals("xyzab", rs.getString(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testIsNullInPK() throws Exception {
String pTSDBTableName = generateUniqueName();
ensureTableCreated(getUrl(),pTSDBTableName, PTSDB_NAME, null, null, null);
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
conn.setAutoCommit(true);
PreparedStatement stmt = conn.prepareStatement("upsert into "+pTSDBTableName+" VALUES ('', '', ?, 0.5)");
stmt.setDate(1, D1);
stmt.execute();
conn.close();
String query = "SELECT HOST,INST,\"DATE\" FROM "+pTSDBTableName+" WHERE HOST IS NULL AND INST IS NULL AND \"DATE\"=?";
url = getUrl();
conn = DriverManager.getConnection(url, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
statement.setDate(1, D1);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertNull(rs.getString(1));
assertNull(rs.getString(2));
assertEquals(D1, rs.getDate(3));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testLengthFunction() throws Exception {
String bTableName = generateUniqueName();
String query[] = {
"SELECT length('') FROM "+bTableName+" LIMIT 1",
"SELECT length(' ') FROM "+bTableName+" LIMIT 1",
"SELECT length('1') FROM "+bTableName+" LIMIT 1",
"SELECT length('1234') FROM "+bTableName+" LIMIT 1",
"SELECT length('ɚɦɰɸ') FROM "+bTableName+" LIMIT 1",
"SELECT length('ǢǛǟƈ') FROM "+bTableName+" LIMIT 1",
"SELECT length('This is a test!') FROM "+bTableName+" LIMIT 1",
"SELECT A_STRING FROM "+bTableName+" WHERE length(A_STRING)=3",
};
String result[] = {
null,
"1",
"1",
"4",
"4",
"4",
"15",
"abc",
};
assertEquals(query.length,result.length);
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null,bTableName);
for (int i = 0; i < query.length; i++) {
PreparedStatement statement = conn.prepareStatement(query[i]);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(query[i],result[i], rs.getString(1));
assertFalse(rs.next());
}
} finally {
conn.close();
}
}
@Test
public void testUpperFunction() throws Exception {
String bTableName = generateUniqueName();
String query[] = {
"SELECT upper('abc') FROM "+bTableName+" LIMIT 1",
"SELECT upper('Abc') FROM "+bTableName+" LIMIT 1",
"SELECT upper('ABC') FROM "+bTableName+" LIMIT 1",
"SELECT upper('ĎďĒ') FROM "+bTableName+" LIMIT 1",
"SELECT upper('ß') FROM "+bTableName+" LIMIT 1",
};
String result[] = {
"ABC",
"ABC",
"ABC",
"ĎĎĒ",
"SS",
};
assertEquals(query.length, result.length);
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null,bTableName);
for (int i = 0; i < query.length; i++) {
PreparedStatement statement = conn.prepareStatement(query[i]);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(query[i],result[i], rs.getString(1));
assertFalse(rs.next());
}
} finally {
conn.close();
}
}
@Test
public void testLowerFunction() throws Exception {
String bTableName = generateUniqueName();
String query[] = {
"SELECT lower('abc') FROM "+bTableName+" LIMIT 1",
"SELECT lower('Abc') FROM "+bTableName+" LIMIT 1",
"SELECT lower('ABC') FROM "+bTableName+" LIMIT 1",
"SELECT lower('ĎďĒ') FROM "+bTableName+" LIMIT 1",
"SELECT lower('ß') FROM "+bTableName+" LIMIT 1",
"SELECT lower('SS') FROM "+bTableName+" LIMIT 1",
};
String result[] = {
"abc",
"abc",
"abc",
"ďďē",
"ß",
"ss",
};
assertEquals(query.length, result.length);
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null,bTableName);
for (int i = 0; i < query.length; i++) {
PreparedStatement statement = conn.prepareStatement(query[i]);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(query[i],result[i], rs.getString(1));
assertFalse(rs.next());
}
} finally {
conn.close();
}
}
@Test
public void testRTrimFunction() throws Exception {
String bTableName = generateUniqueName();
String varcharKeyTestTable = generateUniqueName();
String query[] = {
"SELECT rtrim('') FROM "+bTableName+" LIMIT 1",
"SELECT rtrim(' ') FROM "+bTableName+" LIMIT 1",
"SELECT rtrim(' ') FROM "+bTableName+" LIMIT 1",
"SELECT rtrim('abc') FROM "+bTableName+" LIMIT 1",
"SELECT rtrim('abc ') FROM "+bTableName+" LIMIT 1",
"SELECT rtrim('abc def') FROM "+bTableName+" LIMIT 1",
"SELECT rtrim('abc def ') FROM "+bTableName+" LIMIT 1",
"SELECT rtrim('ĎďĒ ') FROM "+bTableName+" LIMIT 1",
"SELECT pk FROM "+varcharKeyTestTable+" WHERE rtrim(pk)='jkl' LIMIT 1",
};
String result[] = {
null,
null,
null,
"abc",
"abc",
"abc def",
"abc def",
"ĎďĒ",
"jkl ",
};
assertEquals(query.length, result.length);
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null,bTableName);
initVarcharKeyTableValues(null,varcharKeyTestTable);
for (int i = 0; i < query.length; i++) {
PreparedStatement statement = conn.prepareStatement(query[i]);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(query[i],result[i], rs.getString(1));
assertFalse(rs.next());
}
} finally {
conn.close();
}
}
@Test
public void testLTrimFunction() throws Exception {
String bTableName = generateUniqueName();
String varcharKeyTestTable = generateUniqueName();
String query[] = {
"SELECT ltrim('') FROM "+bTableName+" LIMIT 1",
"SELECT ltrim(' ') FROM "+bTableName+" LIMIT 1",
"SELECT ltrim(' ') FROM "+bTableName+" LIMIT 1",
"SELECT ltrim('abc') FROM "+bTableName+" LIMIT 1",
"SELECT ltrim(' abc') FROM "+bTableName+" LIMIT 1",
"SELECT ltrim('abc def') FROM "+bTableName+" LIMIT 1",
"SELECT ltrim(' abc def') FROM "+bTableName+" LIMIT 1",
"SELECT ltrim(' ĎďĒ') FROM "+bTableName+" LIMIT 1",
"SELECT pk FROM "+varcharKeyTestTable+" WHERE ltrim(pk)='def' LIMIT 1",
};
String result[] = {
null,
null,
null,
"abc",
"abc",
"abc def",
"abc def",
"ĎďĒ",
" def",
};
assertEquals(query.length, result.length);
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null,bTableName);
initVarcharKeyTableValues(null,varcharKeyTestTable);
for (int i = 0; i < query.length; i++) {
PreparedStatement statement = conn.prepareStatement(query[i]);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(query[i],result[i], rs.getString(1));
assertFalse(rs.next());
}
} finally {
conn.close();
}
}
@Test
public void testSubstrFunctionOnRowKeyInWhere() throws Exception {
String substrTestTableName = generateUniqueName();
String url = getUrl();
Connection conn = DriverManager.getConnection(url);
conn.createStatement().execute("CREATE TABLE "+substrTestTableName+" (s1 varchar not null, s2 varchar not null constraint pk primary key(s1,s2))");
conn.close();
url = getUrl();
conn = DriverManager.getConnection(url);
conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abc','a')");
conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd','b')");
conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abce','c')");
conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcde','d')");
conn.commit();
conn.close();
url = getUrl();
conn = DriverManager.getConnection(url);
ResultSet rs = conn.createStatement().executeQuery("SELECT s1 from "+substrTestTableName+" where substr(s1,1,4) = 'abcd'");
assertTrue(rs.next());
assertEquals("abcd",rs.getString(1));
assertTrue(rs.next());
assertEquals("abcde",rs.getString(1));
assertFalse(rs.next());
}
@Test
public void testRTrimFunctionOnRowKeyInWhere() throws Exception {
String substrTestTableName = generateUniqueName();
String url = getUrl();
Connection conn = DriverManager.getConnection(url);
conn.createStatement().execute("CREATE TABLE "+substrTestTableName+" (s1 varchar not null, s2 varchar not null constraint pk primary key(s1,s2))");
conn.close();
url = getUrl();
conn = DriverManager.getConnection(url);
conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abc','a')");
conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd','b')");
conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd ','c')");
conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd ','c')");
conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd a','c')"); // Need TRAVERSE_AND_LEAVE for cases like this
conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcde','d')");
conn.commit();
conn.close();
url = getUrl();
conn = DriverManager.getConnection(url);
ResultSet rs = conn.createStatement().executeQuery("SELECT s1 from "+substrTestTableName+" where rtrim(s1) = 'abcd'");
assertTrue(rs.next());
assertEquals("abcd",rs.getString(1));
assertTrue(rs.next());
assertEquals("abcd ",rs.getString(1));
assertTrue(rs.next());
assertEquals("abcd ",rs.getString(1));
assertFalse(rs.next());
}
@Test
public void testLikeFunctionOnRowKeyInWhere() throws Exception {
String substrTestTableName = generateUniqueName();
String url = getUrl();
Connection conn = DriverManager.getConnection(url);
conn.createStatement().execute("CREATE TABLE "+substrTestTableName+" (s1 varchar not null, s2 varchar not null constraint pk primary key(s1,s2))");
conn.close();
url = getUrl();
conn = DriverManager.getConnection(url);
conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abc','a')");
conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd','b')");
conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd-','c')");
conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abcd-1','c')");
conn.createStatement().execute("UPSERT INTO "+substrTestTableName+" VALUES('abce','d')");
conn.commit();
conn.close();
url = getUrl();
conn = DriverManager.getConnection(url);
ResultSet rs = conn.createStatement().executeQuery("SELECT s1 from "+substrTestTableName+" where s1 like 'abcd%1'");
assertTrue(rs.next());
assertEquals("abcd-1",rs.getString(1));
assertFalse(rs.next());
}
@Test
public void testTrimFunction() throws Exception {
String bTableName = generateUniqueName();
String varcharKeyTestTable = generateUniqueName();
String query[] = {
"SELECT trim('') FROM "+bTableName+" LIMIT 1",
"SELECT trim(' ') FROM "+bTableName+" LIMIT 1",
"SELECT trim(' ') FROM "+bTableName+" LIMIT 1",
"SELECT trim('abc') FROM "+bTableName+" LIMIT 1",
"SELECT trim(' abc') FROM "+bTableName+" LIMIT 1",
"SELECT trim('abc ') FROM "+bTableName+" LIMIT 1",
"SELECT trim('abc def') FROM "+bTableName+" LIMIT 1",
"SELECT trim(' abc def') FROM "+bTableName+" LIMIT 1",
"SELECT trim('abc def ') FROM "+bTableName+" LIMIT 1",
"SELECT trim(' abc def ') FROM "+bTableName+" LIMIT 1",
"SELECT trim(' ĎďĒ ') FROM "+bTableName+" LIMIT 1",
"SELECT pk FROM "+varcharKeyTestTable+" WHERE trim(pk)='ghi'",
};
String result[] = {
null,
null,
null,
"abc",
"abc",
"abc",
"abc def",
"abc def",
"abc def",
"abc def",
"ĎďĒ",
" ghi ",
};
assertEquals(query.length, result.length);
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initBTableValues(null,bTableName);
initVarcharKeyTableValues(null,varcharKeyTestTable);
for (int i = 0; i < query.length; i++) {
PreparedStatement statement = conn.prepareStatement(query[i]);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(query[i],result[i], rs.getString(1));
assertFalse(rs.next());
}
} finally {
conn.close();
}
}
}
