use of org.apache.geode.cache.query.Struct in project geode by apache.
the class GroupByTestImpl method testSumWithMultiColumnGroupBy.
@Test
public void testSumWithMultiColumnGroupBy() throws Exception {
Region region = this.createRegion("portfolio", Portfolio.class);
for (int i = 1; i < 200; ++i) {
Portfolio pf = new Portfolio(i);
pf.shortID = (short) ((short) i % 5);
region.put("" + i, pf);
}
Map<String, Integer> expectedData = new HashMap<String, Integer>();
for (Object o : region.values()) {
Portfolio pf = (Portfolio) o;
String key = pf.status + "_" + pf.shortID;
if (expectedData.containsKey(key)) {
int sum = expectedData.get(key).intValue() + pf.ID;
expectedData.put(key, sum);
} else {
expectedData.put(key, pf.ID);
}
}
String queryStr = "select p.status as status, p.shortID as shortID, sum(p.ID) as summ from /portfolio p" + " where p.ID > 0 group by status, shortID ";
QueryService qs = CacheUtils.getQueryService();
Query query = qs.newQuery(queryStr);
CompiledSelect cs = ((DefaultQuery) query).getSelect();
SelectResults sr = (SelectResults) query.execute();
assertTrue(sr.getCollectionType().getElementType().isStructType());
assertEquals(expectedData.size(), sr.size());
Iterator iter = sr.iterator();
while (iter.hasNext()) {
Struct struct = (Struct) iter.next();
StructType structType = struct.getStructType();
ObjectType[] fieldTypes = structType.getFieldTypes();
assertEquals("String", fieldTypes[0].getSimpleClassName());
assertEquals("Short", fieldTypes[1].getSimpleClassName());
assertEquals("Number", fieldTypes[2].getSimpleClassName());
String key = struct.get("status") + "_" + struct.get("shortID");
int sum = ((Integer) struct.get("summ")).intValue();
assertTrue(expectedData.containsKey(key));
assertEquals(expectedData.get(key).intValue(), sum);
}
ObjectType elementType = sr.getCollectionType().getElementType();
assertTrue(elementType.isStructType());
StructType structType = (StructType) elementType;
ObjectType[] fieldTypes = structType.getFieldTypes();
assertEquals("String", fieldTypes[0].getSimpleClassName());
assertEquals("Short", fieldTypes[1].getSimpleClassName());
assertEquals("Number", fieldTypes[2].getSimpleClassName());
}
use of org.apache.geode.cache.query.Struct in project geode by apache.
the class GroupByTestImpl method testAggregateFuncMin.
@Test
public void testAggregateFuncMin() throws Exception {
Region region = this.createRegion("portfolio", Portfolio.class);
for (int i = 1; i < 200; ++i) {
Portfolio pf = new Portfolio(i);
pf.shortID = (short) ((short) i / 5);
region.put("" + i, pf);
}
String queryStr = "select p.status as status, Min(p.ID) as Minn from /portfolio p where p.ID > 0 group by status ";
QueryService qs = CacheUtils.getQueryService();
Query query = qs.newQuery(queryStr);
CompiledSelect cs = ((DefaultQuery) query).getSelect();
SelectResults sr = (SelectResults) query.execute();
assertTrue(sr.getCollectionType().getElementType().isStructType());
assertEquals(2, sr.size());
Iterator iter = sr.iterator();
Region rgn = CacheUtils.getRegion("portfolio");
int activeMinID = Integer.MAX_VALUE;
int inactiveMinID = Integer.MAX_VALUE;
for (Object o : rgn.values()) {
Portfolio pf = (Portfolio) o;
if (pf.status.equals("active")) {
if (pf.ID < activeMinID) {
activeMinID = pf.ID;
}
} else if (pf.status.equals("inactive")) {
if (pf.ID < inactiveMinID) {
inactiveMinID = pf.ID;
}
} else {
fail("unexpected value of status");
}
}
while (iter.hasNext()) {
Struct struct = (Struct) iter.next();
StructType structType = struct.getStructType();
ObjectType[] fieldTypes = structType.getFieldTypes();
assertEquals("String", fieldTypes[0].getSimpleClassName());
assertEquals("Number", fieldTypes[1].getSimpleClassName());
if (struct.get("status").equals("active")) {
assertEquals(activeMinID, ((Integer) struct.get("Minn")).intValue());
} else if (struct.get("status").equals("inactive")) {
assertEquals(inactiveMinID, ((Integer) struct.get("Minn")).intValue());
} else {
fail("unexpected value of status");
}
}
ObjectType elementType = sr.getCollectionType().getElementType();
assertTrue(elementType.isStructType());
StructType structType = (StructType) elementType;
ObjectType[] fieldTypes = structType.getFieldTypes();
assertEquals("String", fieldTypes[0].getSimpleClassName());
assertEquals("Number", fieldTypes[1].getSimpleClassName());
}
use of org.apache.geode.cache.query.Struct in project geode by apache.
the class NonDistinctOrderByTestImplementation method testOrderByWithNullValues.
@Test
public void testOrderByWithNullValues() throws Exception {
// IN ORDER BY NULL values are treated as smallest. E.g For an ascending
// order by field
// its null values are reported first and then the values in ascending
// order.
String[] queries = { // 0 null
"SELECT * FROM /portfolio1 pf1 order by pkid", // 1 same
"SELECT * FROM /portfolio1 pf1 order by pkid asc", // 2 null
"SELECT * FROM /portfolio1 order by pkid desc", // 3 null
"SELECT pkid FROM /portfolio1 pf1 order by pkid", // 4
"SELECT pkid FROM /portfolio1 pf1 where pkid != 'XXXX' order by pkid asc", // 5
"SELECT pkid FROM /portfolio1 pf1 where pkid != 'XXXX' order by pkid desc", // 6
"SELECT ID, pkid FROM /portfolio1 pf1 where ID < 1000 order by pkid", // 7
"SELECT ID, pkid FROM /portfolio1 pf1 where ID > 3 order by pkid", // 8
"SELECT ID, pkid FROM /portfolio1 pf1 where ID < 1000 order by pkid", // 9
"SELECT ID, pkid FROM /portfolio1 pf1 where ID > 0 order by pkid", // 10
"SELECT ID, pkid FROM /portfolio1 pf1 where ID > 0 order by pkid, ID asc", // 11
"SELECT ID, pkid FROM /portfolio1 pf1 where ID > 0 order by pkid, ID desc" };
Object[][] r = new Object[queries.length][2];
QueryService qs;
qs = CacheUtils.getQueryService();
// Create Regions
final int size = 9;
final int numNullValues = 3;
Region r1 = this.createRegion("portfolio1", Portfolio.class);
for (int i = 1; i <= size; i++) {
Portfolio pf = new Portfolio(i);
// Add numNullValues null values.
if (i <= numNullValues) {
pf.pkid = null;
pf.status = "a" + i;
}
r1.put(i + "", pf);
}
Query q = null;
SelectResults results = null;
List list = null;
String str = "";
try {
// Query 0 - null values are first in the order.
str = queries[0];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
r[0][0] = results;
list = results.asList();
for (int i = 1; i <= size; i++) {
Portfolio p = (Portfolio) list.get((i - 1));
if (i <= numNullValues) {
assertNull("Expected null value for pkid, p: " + p, p.pkid);
} else {
assertNotNull("Expected not null value for pkid", p.pkid);
if (!p.pkid.equals("" + i)) {
fail(" Value of pkid is not in expected order.");
}
}
}
// Query 1 - null values are first in the order.
str = queries[1];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= size; i++) {
Portfolio p = (Portfolio) list.get((i - 1));
if (i <= numNullValues) {
assertNull("Expected null value for pkid", p.pkid);
} else {
assertNotNull("Expected not null value for pkid", p.pkid);
if (!p.pkid.equals("" + i)) {
fail(" Value of pkid is not in expected order.");
}
}
}
// Query 2 - null values are last in the order.
str = queries[2];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= size; i++) {
Portfolio p = (Portfolio) list.get((i - 1));
if (i > (size - numNullValues)) {
assertNull("Expected null value for pkid", p.pkid);
} else {
assertNotNull("Expected not null value for pkid", p.pkid);
if (!p.pkid.equals("" + (size - (i - 1)))) {
fail(" Value of pkid is not in expected order.");
}
}
}
// Query 3 - 3 null value with pkid.
str = queries[3];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= list.size(); i++) {
String pkid = (String) list.get((i - 1));
if (i <= numNullValues) {
assertNull("Expected null value for pkid", pkid);
} else {
assertNotNull("Expected not null value for pkid", pkid);
if (!pkid.equals("" + i)) {
fail(" Value of pkid is not in expected order.");
}
}
}
// Query 4 - 3 null value with pkid.
str = queries[4];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= list.size(); i++) {
String pkid = (String) list.get((i - 1));
if (i <= 3) {
assertNull("Expected null value for pkid", pkid);
} else {
assertNotNull("Expected not null value for pkid", pkid);
if (!pkid.equals("" + i)) {
fail(" Value of pkid is not in expected order.");
}
}
}
// Query 5 - 1 distinct null value with pkid at the end.
str = queries[5];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= list.size(); i++) {
String pkid = (String) list.get((i - 1));
if (i > (list.size() - numNullValues)) {
assertNull("Expected null value for pkid", pkid);
} else {
assertNotNull("Expected not null value for pkid", pkid);
if (!pkid.equals("" + (size - (i - 1)))) {
fail(" Value of pkid is not in expected order.");
}
}
}
// Query 6 - ID field values should be in the same order.
str = queries[6];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= size; i++) {
int id = (Integer) ((Struct) list.get((i - 1))).getFieldValues()[0];
// ID should be one of 1, 2, 3 because of distinct
if (i <= numNullValues) {
if (!(id == 1 || id == 2 || id == 3)) {
fail(" Value of ID is not as expected " + id);
}
} else {
if (id != i) {
fail(" Value of ID is not as expected " + id);
}
}
}
// Query 7 - ID field values should be in the same order.
str = queries[7];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= list.size(); i++) {
int id = (Integer) ((Struct) list.get((i - 1))).getFieldValues()[0];
if (id != (numNullValues + i)) {
fail(" Value of ID is not as expected, " + id);
}
}
// Query 8 - ID, pkid field values should be in the same order.
str = queries[8];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= size; i++) {
Struct vals = (Struct) list.get((i - 1));
int id = ((Integer) vals.get("ID")).intValue();
String pkid = (String) vals.get("pkid");
// ID should be one of 1, 2, 3 because of distinct
if (i <= numNullValues) {
if (!(id == 1 || id == 2 || id == 3)) {
fail(" Value of ID is not as expected " + id);
}
assertNull("Expected null value for pkid", pkid);
} else {
if (id != i) {
fail(" Value of ID is not as expected " + id);
}
assertNotNull("Expected not null value for pkid", pkid);
if (!pkid.equals("" + i)) {
fail(" Value of pkid is not in expected order.");
}
}
}
// Query 9 - ID, pkid field values should be in the same order.
str = queries[9];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= list.size(); i++) {
Struct vals = (Struct) list.get((i - 1));
int id = ((Integer) vals.get("ID")).intValue();
String pkid = (String) vals.get("pkid");
if (i <= numNullValues) {
assertNull("Expected null value for pkid, " + pkid, pkid);
if (!(id == 1 || id == 2 || id == 3)) {
fail(" Value of ID is not as expected " + id);
}
} else {
if (!pkid.equals("" + i)) {
fail(" Value of pkid is not as expected, " + pkid);
}
if (id != i) {
fail(" Value of ID is not as expected, " + id);
}
}
}
// Query 10 - ID asc, pkid field values should be in the same order.
str = queries[10];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= list.size(); i++) {
Struct vals = (Struct) list.get((i - 1));
int id = ((Integer) vals.get("ID")).intValue();
String pkid = (String) vals.get("pkid");
if (i <= numNullValues) {
assertNull("Expected null value for pkid, " + pkid, pkid);
if (id != i) {
fail(" Value of ID is not as expected, it is: " + id + " expected :" + i);
}
} else {
if (!pkid.equals("" + i)) {
fail(" Value of pkid is not as expected, " + pkid);
}
if (id != i) {
fail(" Value of ID is not as expected, " + id);
}
}
}
// Query 11 - ID desc, pkid field values should be in the same order.
str = queries[11];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= list.size(); i++) {
Struct vals = (Struct) list.get((i - 1));
int id = ((Integer) vals.get("ID")).intValue();
String pkid = (String) vals.get("pkid");
if (i <= numNullValues) {
assertNull("Expected null value for pkid, " + pkid, pkid);
if (id != (numNullValues - (i - 1))) {
fail(" Value of ID is not as expected " + id);
}
} else {
if (!pkid.equals("" + i)) {
fail(" Value of pkid is not as expected, " + pkid);
}
if (id != i) {
fail(" Value of ID is not as expected, " + id);
}
}
}
} catch (Exception e) {
e.printStackTrace();
fail(q.getQueryString());
}
}
use of org.apache.geode.cache.query.Struct in project geode by apache.
the class OrderByPartitionedJUnitTest method testOrderByWithNullValues.
@Test
public void testOrderByWithNullValues() throws Exception {
// IN ORDER BY NULL values are treated as smallest. E.g For an ascending order by field
// its null values are reported first and then the values in ascending order.
String[] queries = getQueriesForOrderByWithNullValues();
Object[][] r = new Object[queries.length][2];
QueryService qs;
qs = CacheUtils.getQueryService();
// Create Regions
final int size = 9;
final int numNullValues = 3;
Region r1 = createRegion("portfolio1", Portfolio.class);
for (int i = 1; i <= size; i++) {
Portfolio pf = new Portfolio(i);
// Add numNullValues null values.
if (i <= numNullValues) {
pf.pkid = null;
pf.status = "a" + i;
}
r1.put(i + "", pf);
}
Query q = null;
SelectResults results = null;
List list = null;
String str = "";
try {
// Query 0 - null values are first in the order.
str = queries[0];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
r[0][0] = results;
list = results.asList();
for (int i = 1; i <= size; i++) {
Portfolio p = (Portfolio) list.get((i - 1));
if (i <= numNullValues) {
assertNull("Expected null value for pkid, p: " + p, p.pkid);
} else {
assertNotNull("Expected not null value for pkid", p.pkid);
if (!p.pkid.equals("" + i)) {
fail(" Value of pkid is not in expected order.");
}
}
}
// Query 1 - null values are first in the order.
str = queries[1];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= size; i++) {
Portfolio p = (Portfolio) list.get((i - 1));
if (i <= numNullValues) {
assertNull("Expected null value for pkid", p.pkid);
} else {
assertNotNull("Expected not null value for pkid", p.pkid);
if (!p.pkid.equals("" + i)) {
fail(" Value of pkid is not in expected order.");
}
}
}
// Query 2 - null values are last in the order.
str = queries[2];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= size; i++) {
Portfolio p = (Portfolio) list.get((i - 1));
if (i > (size - numNullValues)) {
assertNull("Expected null value for pkid", p.pkid);
} else {
assertNotNull("Expected not null value for pkid", p.pkid);
if (!p.pkid.equals("" + (size - (i - 1)))) {
fail(" Value of pkid is not in expected order.");
}
}
}
// Query 3 - 1 distinct null value with pkid.
str = queries[3];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= list.size(); i++) {
String pkid = (String) list.get((i - 1));
if (i == 1) {
assertNull("Expected null value for pkid", pkid);
} else {
assertNotNull("Expected not null value for pkid", pkid);
if (!pkid.equals("" + (numNullValues + (i - 1)))) {
fail(" Value of pkid is not in expected order.");
}
}
}
// Query 4 - 1 distinct null value with pkid.
str = queries[4];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= list.size(); i++) {
String pkid = (String) list.get((i - 1));
if (i == 1) {
assertNull("Expected null value for pkid", pkid);
} else {
assertNotNull("Expected not null value for pkid", pkid);
if (!pkid.equals("" + (numNullValues + (i - 1)))) {
fail(" Value of pkid is not in expected order.");
}
}
}
// Query 5 - 1 distinct null value with pkid at the end.
str = queries[5];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= list.size(); i++) {
String pkid = (String) list.get((i - 1));
if (i == (list.size())) {
assertNull("Expected null value for pkid", pkid);
} else {
assertNotNull("Expected not null value for pkid", pkid);
if (!pkid.equals("" + (size - (i - 1)))) {
fail(" Value of pkid is not in expected order.");
}
}
}
// Query 6 - ID field values should be in the same order.
str = queries[6];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= size; i++) {
int id = (Integer) ((Struct) list.get((i - 1))).getFieldValues()[0];
// ID should be one of 1, 2, 3 because of distinct
if (i <= numNullValues) {
if (!(id == 1 || id == 2 || id == 3)) {
fail(" Value of ID is not as expected " + id);
}
} else {
if (id != i) {
fail(" Value of ID is not as expected " + id);
}
}
}
// Query 7 - ID, pkid field values should be in the same order.
str = queries[7];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= list.size(); i++) {
Struct vals = (Struct) list.get((i - 1));
int id = ((Integer) vals.get("ID")).intValue();
String pkid = (String) vals.get("pkid");
if (i <= numNullValues) {
assertNull("Expected null value for pkid, " + pkid, pkid);
if (!(id == 1 || id == 2 || id == 3)) {
fail(" Value of ID is not as expected " + id);
}
} else {
if (!pkid.equals("" + i)) {
fail(" Value of pkid is not as expected, " + pkid);
}
if (id != i) {
fail(" Value of ID is not as expected, " + id);
}
}
}
// Query 8 - ID asc, pkid field values should be in the same order.
str = queries[8];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= list.size(); i++) {
Struct vals = (Struct) list.get((i - 1));
int id = ((Integer) vals.get("ID")).intValue();
String pkid = (String) vals.get("pkid");
if (i <= numNullValues) {
assertNull("Expected null value for pkid, " + pkid, pkid);
if (id != i) {
fail(" Value of ID is not as expected, it is: " + id + " expected :" + i);
}
} else {
if (!pkid.equals("" + i)) {
fail(" Value of pkid is not as expected, " + pkid);
}
if (id != i) {
fail(" Value of ID is not as expected, " + id);
}
}
}
// Query 9 - ID desc, pkid field values should be in the same order.
str = queries[9];
q = CacheUtils.getQueryService().newQuery(str);
CacheUtils.getLogger().info("Executing query: " + str);
results = (SelectResults) q.execute();
list = results.asList();
for (int i = 1; i <= list.size(); i++) {
Struct vals = (Struct) list.get((i - 1));
int id = ((Integer) vals.get("ID")).intValue();
String pkid = (String) vals.get("pkid");
if (i <= numNullValues) {
assertNull("Expected null value for pkid, " + pkid, pkid);
if (id != (numNullValues - (i - 1))) {
fail(" Value of ID is not as expected " + id);
}
} else {
if (!pkid.equals("" + i)) {
fail(" Value of pkid is not as expected, " + pkid);
}
if (id != i) {
fail(" Value of ID is not as expected, " + id);
}
}
}
} catch (Exception e) {
e.printStackTrace();
fail(q.getQueryString());
}
}
use of org.apache.geode.cache.query.Struct in project geode by apache.
the class MiscJUnitTest method testMultipleOrderByClauses.
@Test
public void testMultipleOrderByClauses() throws Exception {
Region region = CacheUtils.createRegion("portfolios", Portfolio.class);
region.put("2", new Portfolio(2));
region.put("3", new Portfolio(3));
region.put("4", new Portfolio(4));
region.put("5", new Portfolio(5));
region.put("6", new Portfolio(6));
region.put("7", new Portfolio(7));
QueryService qs = CacheUtils.getQueryService();
String qry = "select distinct status, getID from /portfolios pf where getID < 10 order by status asc, getID desc";
Query q = qs.newQuery(qry);
SelectResults result = (SelectResults) q.execute();
Iterator itr = result.iterator();
int j = 6;
while (itr.hasNext() && j > 0) {
Struct struct = (Struct) itr.next();
assertEquals("active", struct.get("status"));
assertEquals(j, ((Integer) struct.get("getID")).intValue());
j -= 2;
}
j = 7;
while (itr.hasNext()) {
Struct struct = (Struct) itr.next();
assertEquals(j, ((Integer) struct.get("getID")).intValue());
assertEquals("inactive", struct.get("status"));
j -= 2;
}
}
Aggregations