use of herddb.core.DBManager in project herddb by diennea.
the class RawSQLTest method selectWithParameters.
@Test
public void selectWithParameters() throws Exception {
String nodeId = "localhost";
try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
manager.start();
assumeTrue(manager.getPlanner() instanceof JSQLParserPlanner);
CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
manager.waitForTablespace("tblspace1", 10000);
execute(manager, "CREATE TABLE tblspace1.tsql (k1 string primary key,n1 int,s1 string)", Collections.emptyList());
assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey", Integer.valueOf(1234))).getUpdateCount());
try (DataScanner scan = scan(manager, "SELECT ? as foo, k1, n1 FROM tblspace1.tsql", Arrays.asList("test"))) {
List<DataAccessor> all = scan.consume();
assertEquals(1, all.size());
assertEquals(RawString.of("test"), all.get(0).get("foo"));
assertEquals(RawString.of("mykey"), all.get(0).get("k1"));
assertEquals(Integer.valueOf(1234), all.get(0).get("n1"));
}
Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());
try (DataScanner scan = scan(manager, "SELECT ? as foo, ? as bar FROM tblspace1.tsql", Arrays.asList(Long.valueOf(1), timestamp))) {
List<DataAccessor> all = scan.consume();
assertEquals(1, all.size());
assertEquals(Long.valueOf(1), all.get(0).get("foo"));
assertEquals(timestamp, all.get(0).get("bar"));
}
if (manager.isFullSQLSupportEnabled()) {
try (DataScanner scan = scan(manager, "SELECT MAX(?) as foo, MIN(?) as bar FROM tblspace1.tsql", Arrays.asList(Long.valueOf(1), timestamp))) {
List<DataAccessor> all = scan.consume();
assertEquals(1, all.size());
assertEquals(Long.valueOf(1), all.get(0).get("foo"));
assertEquals(timestamp, all.get(0).get("bar"));
}
}
executeUpdate(manager, "DELETE FROM tblspace1.tsql", Collections.emptyList());
try (DataScanner scan = scan(manager, "SELECT ? as foo, ? as bar FROM tblspace1.tsql", Arrays.asList(Long.valueOf(1), timestamp))) {
List<DataAccessor> all = scan.consume();
assertEquals(0, all.size());
}
if (manager.isFullSQLSupportEnabled()) {
try (DataScanner scan = scan(manager, "SELECT MAX(?) as foo, MIN(?) as bar FROM tblspace1.tsql", Arrays.asList(Long.valueOf(1), timestamp))) {
List<DataAccessor> all = scan.consume();
assertEquals(1, all.size());
assertNull(all.get(0).get("foo"));
assertNull(all.get(0).get("bar"));
}
}
}
}
use of herddb.core.DBManager in project herddb by diennea.
the class RawSQLTest method basicTest.
@Test
public void basicTest() throws Exception {
String nodeId = "localhost";
try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
manager.start();
CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
manager.waitForTablespace("tblspace1", 10000);
execute(manager, "CREATE TABLE tblspace1.tsql (k1 string primary key,n1 int,s1 string)", Collections.emptyList());
assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey", Integer.valueOf(1234))).getUpdateCount());
{
GetResult result = manager.get(new GetStatement("tblspace1", "tsql", Bytes.from_string("mykey"), null, false), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
assertTrue(result.found());
assertEquals(result.getRecord().key, Bytes.from_string("mykey"));
Map<String, Object> finalRecord = result.getRecord().toBean(manager.getTableSpaceManager("tblspace1").getTableManager("tsql").getTable());
assertEquals(RawString.of("mykey"), finalRecord.get("k1"));
assertEquals(Integer.valueOf(1234), finalRecord.get("n1"));
}
{
assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=? where k1 = ?", Arrays.asList(Integer.valueOf(999), "mykey")).getUpdateCount());
}
{
assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=? where k1 = ? and n1 = ?", Arrays.asList(Integer.valueOf(100), "mykey", Integer.valueOf(999))).getUpdateCount());
}
{
assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=? where k1 = ? and (n1 = ? or n1 <> ?)", Arrays.asList(Integer.valueOf(999), "mykey", Integer.valueOf(100), Integer.valueOf(1000))).getUpdateCount());
}
{
assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=? where k1 = ? and (n1 <> ?)", Arrays.asList(Integer.valueOf(34), "mykey", Integer.valueOf(15))).getUpdateCount());
}
{
assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=? where k1 = ? and not (n1 <> ?)", Arrays.asList(Integer.valueOf(999), "mykey", Integer.valueOf(34))).getUpdateCount());
}
{
GetResult result = manager.get(new GetStatement("tblspace1", "tsql", Bytes.from_string("mykey"), null, false), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
assertTrue(result.found());
assertEquals(result.getRecord().key, Bytes.from_string("mykey"));
Map<String, Object> finalRecord = result.getRecord().toBean(manager.getTableSpaceManager("tblspace1").getTableManager("tsql").getTable());
assertEquals(RawString.of("mykey"), finalRecord.get("k1"));
assertEquals(Integer.valueOf(999), finalRecord.get("n1"));
}
{
TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.tsql where k1 = ?", Arrays.asList("mykey"), false, true, false, -1);
GetStatement st_get = (GetStatement) translate1.plan.mainStatement;
GetResult result = manager.get(st_get, translate1.context, TransactionContext.NO_TRANSACTION);
assertTrue(result.found());
assertEquals(result.getRecord().key, Bytes.from_string("mykey"));
Map<String, Object> finalRecord = result.getRecord().toBean(manager.getTableSpaceManager("tblspace1").getTableManager("tsql").getTable());
assertEquals(RawString.of("mykey"), finalRecord.get("k1"));
assertEquals(Integer.valueOf(999), finalRecord.get("n1"));
}
{
TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.tsql where k1 = ? and n1=?", Arrays.asList("mykey", 999), false, true, false, -1);
GetStatement st_get_with_condition = (GetStatement) translate1.plan.mainStatement;
GetResult result = manager.get(st_get_with_condition, translate1.context, TransactionContext.NO_TRANSACTION);
assertTrue(result.found());
assertEquals(result.getRecord().key, Bytes.from_string("mykey"));
Map<String, Object> finalRecord = result.getRecord().toBean(manager.getTableSpaceManager("tblspace1").getTableManager("tsql").getTable());
assertEquals(RawString.of("mykey"), finalRecord.get("k1"));
assertEquals(Integer.valueOf(999), finalRecord.get("n1"));
}
{
TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.tsql where k1 = ? and n1=?", Arrays.asList("mykey", 9992), false, true, false, -1);
GetStatement st_get_with_wrong_condition = (GetStatement) translate1.plan.mainStatement;
GetResult result = manager.get(st_get_with_wrong_condition, translate1.context, TransactionContext.NO_TRANSACTION);
assertFalse(result.found());
}
{
assertEquals(0, executeUpdate(manager, "DELETE FROM tblspace1.tsql where k1 = ? and n1 = ?", Arrays.asList("mykey", 123)).getUpdateCount());
}
{
assertEquals(1, executeUpdate(manager, "DELETE FROM tblspace1.tsql where k1 = ?", Arrays.asList("mykey")).getUpdateCount());
}
{
GetResult result = manager.get(new GetStatement("tblspace1", "tsql", Bytes.from_string("mykey"), null, false), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
assertFalse(result.found());
}
{
assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey", Integer.valueOf(1234))).getUpdateCount());
}
{
GetResult result = manager.get(new GetStatement("tblspace1", "tsql", Bytes.from_string("mykey"), null, false), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
assertTrue(result.found());
}
{
assertEquals(1, executeUpdate(manager, "DELETE FROM tblspace1.tsql where k1 = ? and n1=?", Arrays.asList("mykey", 1234)).getUpdateCount());
}
{
GetResult result = manager.get(new GetStatement("tblspace1", "tsql", Bytes.from_string("mykey"), null, false), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
assertFalse(result.found());
}
{
assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values('mykey2',1234)", Collections.emptyList()).getUpdateCount());
}
{
assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=2135 where k1 = 'mykey2'", Collections.emptyList()).getUpdateCount());
}
{
assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=2138,s1='foo' where k1 = 'mykey2' and s1 is null", Collections.emptyList()).getUpdateCount());
}
{
TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.tsql where k1 ='mykey2'", Collections.emptyList(), true, true, false, -1);
ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
assertEquals(1, scan1.consume().size());
}
}
{
TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 FROM tblspace1.tsql where k1 ='mykey2'", Collections.emptyList(), true, true, false, -1);
try (DataScanner scan1 = ((ScanResult) manager.executePlan(translate1.plan, translate1.context, TransactionContext.NO_TRANSACTION)).dataScanner) {
List<DataAccessor> records = scan1.consume();
assertEquals(1, records.size());
System.out.println("records:" + records);
assertEquals(1, records.get(0).getFieldNames().length);
assertEquals(1, records.get(0).toMap().size());
assertEquals("k1", records.get(0).getFieldNames()[0]);
assertEquals(RawString.of("mykey2"), records.get(0).get(0));
}
}
{
TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 theKey FROM tblspace1.tsql where k1 ='mykey2'", Collections.emptyList(), true, true, false, -1);
try (DataScanner scan1 = ((ScanResult) manager.executePlan(translate1.plan, translate1.context, TransactionContext.NO_TRANSACTION)).dataScanner) {
List<DataAccessor> records = scan1.consume();
assertEquals(1, records.size());
assertEquals(1, records.get(0).getFieldNames().length);
assertEquals(1, records.get(0).toMap().size());
assertEquals("thekey", records.get(0).getFieldNames()[0].toLowerCase());
System.out.println("type: " + records.get(0).getClass());
assertEquals(RawString.of("mykey2"), records.get(0).get(0));
}
}
{
TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 as theKey,'one' as theStringConstant,3 LongConstant FROM tblspace1.tsql where k1 ='mykey2'", Collections.emptyList(), true, true, false, -1);
try (DataScanner scan1 = ((ScanResult) manager.executePlan(translate1.plan, translate1.context, TransactionContext.NO_TRANSACTION)).dataScanner) {
List<DataAccessor> records = scan1.consume();
assertEquals(1, records.size());
assertEquals(3, records.get(0).getFieldNames().length);
assertEquals(3, records.get(0).toMap().size());
assertEquals("thekey", records.get(0).getFieldNames()[0].toLowerCase());
assertEquals(RawString.of("mykey2"), records.get(0).get(0));
assertEquals("thestringconstant", records.get(0).getFieldNames()[1].toLowerCase());
assertEquals(RawString.of("one"), records.get(0).get(1));
assertEquals("longconstant", records.get(0).getFieldNames()[2].toLowerCase());
assertEquals(3, ((Number) records.get(0).get(2)).longValue());
}
}
{
TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.tsql where k1 ='mykey2' and s1 is not null", Collections.emptyList(), true, true, false, -1);
ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
assertEquals(1, scan1.consume().size());
}
}
{
assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=2138,s1='bar' where k1 = 'mykey2' and s1 is not null", Collections.emptyList()).getUpdateCount());
}
{
assertEquals(0, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=2138,s1='bar' where k1 = 'mykey2' and s1 is null", Collections.emptyList()).getUpdateCount());
}
{
assertEquals(0, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=2138,s1='bar' where k1 = 'mykey2' and not (s1 is not null)", Collections.emptyList()).getUpdateCount());
}
{
assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=2138,s1='bar' where k1 = 'mykey2' and not (s1 is null)", Collections.emptyList()).getUpdateCount());
}
{
assertEquals(1, executeUpdate(manager, "DELETE FROM tblspace1.tsql where k1 = 'mykey2' and s1 is not null", Collections.emptyList()).getUpdateCount());
assertEquals(0, executeUpdate(manager, "DELETE FROM tblspace1.tsql where k1 = 'mykey2' and s1 is not null", Collections.emptyList()).getUpdateCount());
}
{
GetResult result = manager.get(new GetStatement("tblspace1", "tsql", Bytes.from_string("mykey2"), null, false), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
assertFalse(result.found());
}
{
TransactionResult result = (TransactionResult) execute(manager, "EXECUTE BEGINTRANSACTION 'tblspace1'", Collections.emptyList());
long tx = result.getTransactionId();
execute(manager, "EXECUTE COMMITTRANSACTION 'tblspace1'," + tx, Collections.emptyList());
}
{
TransactionResult result = (TransactionResult) execute(manager, "EXECUTE BEGINTRANSACTION 'tblspace1'", Collections.emptyList());
long tx = result.getTransactionId();
execute(manager, "EXECUTE ROLLBACKTRANSACTION 'tblspace1'," + tx, Collections.emptyList());
}
}
}
use of herddb.core.DBManager in project herddb by diennea.
the class SimpleJoinTest method testExists.
@Test
public void testExists() throws Exception {
String nodeId = "localhost";
Path dataPath = folder.newFolder("data").toPath();
Path logsPath = folder.newFolder("logs").toPath();
Path metadataPath = folder.newFolder("metadata").toPath();
Path tmoDir = folder.newFolder("tmoDir").toPath();
// reproduces an error due to the order or elements returned during the scan
try (DBManager manager = new DBManager(nodeId, new FileMetadataStorageManager(metadataPath), new FileDataStorageManager(dataPath), new FileCommitLogManager(logsPath), tmoDir, null)) {
assumeThat(manager.getPlanner(), instanceOf(CalcitePlanner.class));
manager.start();
manager.waitForTablespace(TableSpace.DEFAULT, 10000);
execute(manager, "CREATE TABLE a (k1 int primary key, v1 int)", Collections.emptyList());
execute(manager, "CREATE TABLE b (k2 int primary key, v2 int)", Collections.emptyList());
execute(manager, "INSERT INTO a (k1,v1) values(1,1)", Collections.emptyList());
execute(manager, "INSERT INTO b (k2,v2) values(1,1)", Collections.emptyList());
{
try (DataScanner scan1 = scan(manager, " select k1 as kk, v1 as vv from a where 1=1 and ( NOT EXISTS (SELECT NULL FROM b WHERE b.v2=a.k1 AND 1=1 AND b.k2 = 2 ))", Collections.emptyList())) {
List<DataAccessor> consume = scan1.consume();
System.out.println("NUM " + consume.size());
for (DataAccessor r : consume) {
System.out.println("RECORD " + r.toMap());
}
assertEquals(1, consume.size());
}
}
{
try (DataScanner scan1 = scan(manager, " select k1 as kk, v1 as vv from a where 1=1 and ( NOT EXISTS (SELECT NULL FROM b WHERE b.v2=a.k1 AND 1=1 AND b.k2 = 1 ))", Collections.emptyList())) {
List<DataAccessor> consume = scan1.consume();
System.out.println("NUM " + consume.size());
for (DataAccessor r : consume) {
System.out.println("RECORD " + r.toMap());
}
assertEquals(0, consume.size());
}
}
// joins during transactions must release transaction resources properly
{
long tx = TestUtils.beginTransaction(manager, TableSpace.DEFAULT);
try (DataScanner scan1 = scan(manager, " select k1 as kk, v1 as vv from a where 1=1 and ( NOT EXISTS (SELECT NULL FROM b WHERE b.v2=a.k1 AND 1=1 AND b.k2 = 2 ))", Collections.emptyList(), new TransactionContext(tx))) {
List<DataAccessor> consume = scan1.consume();
System.out.println("NUM " + consume.size());
for (DataAccessor r : consume) {
System.out.println("RECORD " + r.toMap());
}
assertEquals(1, consume.size());
}
TestUtils.commitTransaction(manager, TableSpace.DEFAULT, tx);
}
{
long tx = TestUtils.beginTransaction(manager, TableSpace.DEFAULT);
try (DataScanner scan1 = scan(manager, " select k1 as kk, v1 as vv from a where 1=1 and ( EXISTS (SELECT 1 FROM b WHERE b.v2=a.k1 AND b.k2 = 1 ))", Collections.emptyList(), new TransactionContext(tx))) {
List<DataAccessor> consume = scan1.consume();
System.out.println("NUM " + consume.size());
for (DataAccessor r : consume) {
System.out.println("RECORD " + r.toMap());
}
assertEquals(1, consume.size());
}
TestUtils.commitTransaction(manager, TableSpace.DEFAULT, tx);
}
}
}
use of herddb.core.DBManager in project herddb by diennea.
the class SimpleJoinTest method testStartTransactionInJoinBranch.
@Test
public void testStartTransactionInJoinBranch() throws Exception {
String nodeId = "localhost";
try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
manager.start();
CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
manager.waitForTablespace("tblspace1", 10000);
execute(manager, "CREATE TABLE tblspace1.table1 (k1 string primary key,n1 int,s1 string)", Collections.emptyList());
execute(manager, "CREATE TABLE tblspace1.table2 (k2 string primary key,n2 int,s2 string)", Collections.emptyList());
execute(manager, "INSERT INTO tblspace1.table1 (k1,n1,s1) values('a',1,'A')", Collections.emptyList());
execute(manager, "INSERT INTO tblspace1.table1 (k1,n1,s1) values('b',2,'B')", Collections.emptyList());
execute(manager, "INSERT INTO tblspace1.table2 (k2,n2,s2) values('c',3,'A')", Collections.emptyList());
execute(manager, "INSERT INTO tblspace1.table2 (k2,n2,s2) values('d',4,'A')", Collections.emptyList());
{
TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM" + " tblspace1.table1 t1" + " JOIN tblspace1.table2 t2" + " ON t1.n1 > 0" + " and t2.n2 >= 1", Collections.emptyList(), true, true, false, -1);
translated.context.setForceRetainReadLock(true);
if (manager.getPlanner() instanceof CalcitePlanner) {
assertThat(translated.plan.originalRoot, instanceOf(NestedLoopJoinOp.class));
NestedLoopJoinOp join = (NestedLoopJoinOp) translated.plan.originalRoot;
assertThat(join.getLeft(), instanceOf(SimpleScanOp.class));
assertThat(join.getRight(), instanceOf(SimpleScanOp.class));
}
// we want that the left branch of the join starts the transactoion
ScanResult scanResult = ((ScanResult) manager.executePlan(translated.plan, translated.context, TransactionContext.AUTOTRANSACTION_TRANSACTION));
List<DataAccessor> tuples = scanResult.dataScanner.consumeAndClose();
assertTrue(scanResult.transactionId > 0);
for (DataAccessor t : tuples) {
System.out.println("t:" + t);
assertEquals(6, t.getFieldNames().length);
assertEquals("k1", t.getFieldNames()[0]);
assertEquals("n1", t.getFieldNames()[1]);
assertEquals("s1", t.getFieldNames()[2]);
assertEquals("k2", t.getFieldNames()[3]);
assertEquals("n2", t.getFieldNames()[4]);
assertEquals("s2", t.getFieldNames()[5]);
}
assertEquals(4, tuples.size());
TestUtils.commitTransaction(manager, "tblspace1", scanResult.transactionId);
}
{
TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM" + " tblspace1.table1 t1" + " JOIN tblspace1.table2 t2" + " ON t1.n1 > 0" + " and t2.n2 >= 1" + " order by t1.n1, t2.n2", Collections.emptyList(), true, true, false, // with order by
-1);
translated.context.setForceRetainReadLock(true);
assertThat(translated.plan.originalRoot, instanceOf(SortOp.class));
if (manager.getPlanner() instanceof CalcitePlanner) {
NestedLoopJoinOp join = (NestedLoopJoinOp) ((SortOp) translated.plan.originalRoot).getInput();
assertThat(join.getLeft(), instanceOf(SimpleScanOp.class));
assertThat(join.getRight(), instanceOf(SimpleScanOp.class));
} else {
JoinOp join = (JoinOp) ((SortOp) translated.plan.originalRoot).getInput();
assertThat(join.getLeft(), instanceOf(SimpleScanOp.class));
assertThat(join.getRight(), instanceOf(SimpleScanOp.class));
}
// we want that the left branch of the join starts the transactoion
ScanResult scanResult = ((ScanResult) manager.executePlan(translated.plan, translated.context, TransactionContext.AUTOTRANSACTION_TRANSACTION));
List<DataAccessor> tuples = scanResult.dataScanner.consumeAndClose();
assertTrue(scanResult.transactionId > 0);
for (DataAccessor t : tuples) {
System.out.println("t:" + t);
assertEquals(6, t.getFieldNames().length);
assertEquals("k1", t.getFieldNames()[0]);
assertEquals("n1", t.getFieldNames()[1]);
assertEquals("s1", t.getFieldNames()[2]);
assertEquals("k2", t.getFieldNames()[3]);
assertEquals("n2", t.getFieldNames()[4]);
assertEquals("s2", t.getFieldNames()[5]);
}
assertEquals(4, tuples.size());
TestUtils.commitTransaction(manager, "tblspace1", scanResult.transactionId);
}
{
TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT t1.s1 FROM" + " tblspace1.table1 t1" + " JOIN tblspace1.table2 t2" + " ON t1.n1 > 0" + " and t2.n2 >= 1" + " order by t1.n1, t2.n2", Collections.emptyList(), true, true, false, // with order by on non selected fields
-1);
translated.context.setForceRetainReadLock(true);
assertThat(translated.plan.originalRoot, instanceOf(ProjectOp.class));
SortOp sort = (SortOp) ((ProjectOp) translated.plan.originalRoot).getInput();
assertThat(((ProjectOp) translated.plan.originalRoot).getProjection(), instanceOf(ProjectOp.ZeroCopyProjection.class));
if (manager.getPlanner() instanceof CalcitePlanner) {
NestedLoopJoinOp join = (NestedLoopJoinOp) sort.getInput();
assertThat(join.getLeft(), instanceOf(SimpleScanOp.class));
assertThat(join.getRight(), instanceOf(SimpleScanOp.class));
} else {
JoinOp join = (JoinOp) sort.getInput();
assertThat(join.getLeft(), instanceOf(SimpleScanOp.class));
assertThat(join.getRight(), instanceOf(SimpleScanOp.class));
}
// we want that the left branch of the join starts the transactoion
ScanResult scanResult = ((ScanResult) manager.executePlan(translated.plan, translated.context, TransactionContext.AUTOTRANSACTION_TRANSACTION));
List<DataAccessor> tuples = scanResult.dataScanner.consumeAndClose();
assertTrue(scanResult.transactionId > 0);
for (DataAccessor t : tuples) {
System.out.println("t:" + t);
assertEquals(1, t.getFieldNames().length);
assertEquals("s1", t.getFieldNames()[0]);
}
assertEquals(4, tuples.size());
TestUtils.commitTransaction(manager, "tblspace1", scanResult.transactionId);
}
}
}
use of herddb.core.DBManager in project herddb by diennea.
the class SimpleJoinTest method testJoinUnsortedKey.
@Test
public void testJoinUnsortedKey() throws Exception {
String nodeId = "localhost";
Path dataPath = folder.newFolder("data").toPath();
Path logsPath = folder.newFolder("logs").toPath();
Path metadataPath = folder.newFolder("metadata").toPath();
Path tmoDir = folder.newFolder("tmoDir").toPath();
// reproduces an error due to the order or elements returned during the scan
try (DBManager manager = new DBManager(nodeId, new FileMetadataStorageManager(metadataPath), new FileDataStorageManager(dataPath), new FileCommitLogManager(logsPath), tmoDir, null)) {
manager.start();
manager.waitForTablespace(TableSpace.DEFAULT, 10000);
execute(manager, "CREATE TABLE customer (customer_id long primary key,contact_email string,contact_person string, creation long, deleted boolean, modification long, name string, vetting boolean)", Collections.emptyList());
execute(manager, "CREATE TABLE license (license_id long primary key,application string, creation long,data string, deleted boolean, modification long,signature string, customer_id long , license_key_id string)", Collections.emptyList());
execute(manager, "INSERT INTO license (license_id,customer_id) values(1,1)", Collections.emptyList());
execute(manager, "INSERT INTO license (license_id,customer_id) values(2,1)", Collections.emptyList());
execute(manager, "INSERT INTO license (license_id,customer_id) values(3,3)", Collections.emptyList());
execute(manager, "INSERT INTO license (license_id,customer_id) values(4,4)", Collections.emptyList());
execute(manager, "INSERT INTO license (license_id,customer_id) values(5,3)", Collections.emptyList());
execute(manager, "INSERT INTO license (license_id,customer_id) values(6,3)", Collections.emptyList());
execute(manager, "INSERT INTO license (license_id,customer_id) values(7,3)", Collections.emptyList());
execute(manager, "INSERT INTO license (license_id,customer_id) values(8,3)", Collections.emptyList());
execute(manager, "INSERT INTO license (license_id,customer_id) values(9,3)", Collections.emptyList());
execute(manager, "INSERT INTO license (license_id,customer_id) values(10,3)", Collections.emptyList());
execute(manager, "INSERT INTO license (license_id,customer_id) values(11,3)", Collections.emptyList());
execute(manager, "INSERT INTO license (license_id,customer_id) values(12,5)", Collections.emptyList());
execute(manager, "INSERT INTO license (license_id,customer_id) values(13,6)", Collections.emptyList());
execute(manager, "INSERT INTO license (license_id,customer_id) values(14,7)", Collections.emptyList());
execute(manager, "INSERT INTO license (license_id,customer_id) values(15,3)", Collections.emptyList());
execute(manager, "INSERT INTO customer (customer_id) values(1)", Collections.emptyList());
execute(manager, "INSERT INTO customer (customer_id) values(2)", Collections.emptyList());
execute(manager, "INSERT INTO customer (customer_id) values(3)", Collections.emptyList());
execute(manager, "INSERT INTO customer (customer_id) values(4)", Collections.emptyList());
execute(manager, "INSERT INTO customer (customer_id) values(5)", Collections.emptyList());
execute(manager, "INSERT INTO customer (customer_id) values(6)", Collections.emptyList());
execute(manager, "INSERT INTO customer (customer_id) values(7)", Collections.emptyList());
{
try (DataScanner scan1 = scan(manager, "SELECT t0.license_id,c.customer_id FROM license t0, customer c WHERE c.customer_id = 3 AND t0.customer_id = 3 AND c.customer_id = t0.customer_id", Collections.emptyList())) {
List<DataAccessor> consume = scan1.consumeAndClose();
System.out.println("NUM " + consume.size());
assertEquals(9, consume.size());
for (DataAccessor r : consume) {
System.out.println("RECORD " + r.toMap());
}
}
}
// joins during transactions must release transaction resources properly
{
long tx = TestUtils.beginTransaction(manager, TableSpace.DEFAULT);
try (DataScanner scan1 = scan(manager, "SELECT t0.license_id,c.customer_id FROM license t0, customer c WHERE c.customer_id = 3 AND t0.customer_id = 3 AND c.customer_id = t0.customer_id", Collections.emptyList(), new TransactionContext(tx))) {
List<DataAccessor> consume = scan1.consume();
System.out.println("NUM " + consume.size());
assertEquals(9, consume.size());
for (DataAccessor r : consume) {
System.out.println("RECORD " + r.toMap());
}
}
TestUtils.commitTransaction(manager, TableSpace.DEFAULT, tx);
}
}
}
Aggregations