Hi James,
HBase vesion is 0.96.1.1-cdh5.0.0 and phoenix 4.0.0
Attached please find the test. I get strange results for group by with no match
test as it returns the same as with match.
Best Regards,
Sameer
On Friday, April 25, 2014 6:05 PM, James Taylor <[email protected]> wrote:
Also, what version of Phoenix and HBase are you using?
On Fri, Apr 25, 2014 at 6:04 PM, James Taylor <[email protected]> wrote:
Hi Sameer,
>No, that wouldn't be expected. Can you share a unit test with us that repros
>this?
>Thanks,
>James
>
>
>
>
>On Fri, Apr 25, 2014 at 5:30 PM, Sameer Babu K K <[email protected]> wrote:
>
>Hi,
>>
>>
>>We are trying out phoenix to integrate our SQL Query Builder with HBase.
>>Created a table in HBase using the Phoenix driver and inserted some records
>>to fetch from the Query Builder. Most of the queries executed fine with some
>>changes in the dialect. But I couldn't get the following queries working:
>>
>>
>>
>>select count(*) as count from t1 where t1.tat = 'test'
>>
>>
>>This query returns the count of all rows in table whether there is a match
>>for the filter or not.
>>
>>
>>
>>select count(*) as count, t1.tat from t1 where t1.time1utc <=
>>TO_DATE('2014-04-29 05:02:35.768', 'yyyy-MM-dd HH:mm:ss.SSS') group by t1.tat;
>>
>>
>>This query returns returns proper results when there are rows matching the
>>filter but returns count of all rows in the table when there is no match.
>>
>>I just want to check whether this is a known issue or something wrong with
>>the setup.
>>
>>
>>Best Regards,
>>Sameer
>>
>>
>>
>>
>>
>package com.bps.test;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertTrue;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.UUID;
import org.apache.phoenix.schema.TableNotFoundException;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
public class CountTest {
private static Connection con;
@BeforeClass
public static void setupTables() throws Exception {
Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
con = DriverManager.getConnection("jdbc:phoenix:bpsh02,bpsh03,bpsh04");
Statement stmt = con.createStatement();
try {
stmt.executeUpdate("drop table t1");
} catch (TableNotFoundException ex) {
}
stmt.executeUpdate("create table t1 ("
+ "Id varchar not null primary key, "
+ "time1utc timestamp, "
+ "hostname varchar, "
+ "tat varchar)");
stmt.close();
String upsertSql = "upsert into t1 (Id, time1utc, hostname, tat) values (?, ?, ?, ?)";
PreparedStatement preparedStmt = con.prepareStatement(upsertSql);
preparedStmt.setString(1, UUID.randomUUID().toString());
preparedStmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
preparedStmt.setString(3, "testbox1");
preparedStmt.setString(4, "cleaned");
preparedStmt.addBatch();
preparedStmt.setString(1, UUID.randomUUID().toString());
preparedStmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
preparedStmt.setString(3, "testbox2");
preparedStmt.setString(4, "deleted");
preparedStmt.addBatch();
preparedStmt.setString(1, UUID.randomUUID().toString());
preparedStmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
preparedStmt.setString(3, "testbox1");
preparedStmt.setString(4, "deleted");
preparedStmt.addBatch();
preparedStmt.executeBatch();
con.commit();
preparedStmt.close();
}
@Test
public void countWithFilterWithMatch() throws Exception {
Statement stmt = con.createStatement();
ResultSet rset = stmt
.executeQuery("select count(*) from t1 where tat = 'deleted'");
assertTrue(rset.next());
assertEquals(2, rset.getInt(1));
rset.close();
stmt.close();
}
@Test
public void countWithFilterWithNoMatch() throws Exception {
Statement stmt = con.createStatement();
ResultSet rset = stmt
.executeQuery("select count(*) from t1 where tat = 'test'");
assertTrue(rset.next());
assertEquals(0, rset.getInt(1));
rset.close();
stmt.close();
}
@Test
public void countWithFilterAndGroupByWithMatch() throws Exception {
Statement stmt = con.createStatement();
ResultSet rset = stmt
.executeQuery("select count(*) as count, tat from t1 where time1utc <= TO_DATE('2050-04-21 05:02:35.768', 'yyyy-MM-dd HH:mm:ss.SSS') group by tat order by tat");
assertTrue(rset.next());
assertEquals(1, rset.getInt(1));
assertEquals("cleaned", rset.getString(2));
assertTrue(rset.next());
assertEquals(2, rset.getInt(1));
assertEquals("deleted", rset.getString(2));
rset.close();
stmt.close();
}
@Test
public void countWithFilterAndGroupByWithNoMatch() throws Exception {
Statement stmt = con.createStatement();
ResultSet rset = stmt
.executeQuery("select count(*) as count, tat from t1 where time1utc <= TO_DATE('2012-04-21 05:02:35.768', 'yyyy-MM-dd HH:mm:ss.SSS') group by tat");
assertTrue(rset.next());
assertEquals(0, rset.getInt(1));
rset.close();
stmt.close();
}
@AfterClass
public static void disconnect() throws Exception {
con.close();
}
}
