/*
* 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.
*/
/*
* InOperatorTest.java
*
* Created on March 24, 2005, 5:08 PM
*/
package org.apache.geode.cache.query.functional;
import static org.junit.Assert.*;
import java.util.Collection;
import java.util.List;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.experimental.categories.Category;
import org.apache.geode.cache.AttributesFactory;
import org.apache.geode.cache.Cache;
import org.apache.geode.cache.Region;
import org.apache.geode.cache.RegionAttributes;
import org.apache.geode.cache.RegionShortcut;
import org.apache.geode.cache.query.CacheUtils;
import org.apache.geode.cache.query.Index;
import org.apache.geode.cache.query.IndexType;
import org.apache.geode.cache.query.Query;
import org.apache.geode.cache.query.QueryService;
import org.apache.geode.cache.query.SelectResults;
import org.apache.geode.cache.query.data.Portfolio;
import org.apache.geode.cache.query.data.Position;
import org.apache.geode.cache.query.internal.QueryObserver;
import org.apache.geode.cache.query.internal.QueryObserverAdapter;
import org.apache.geode.cache.query.internal.QueryObserverHolder;
import org.apache.geode.cache.query.internal.ResultsBag;
import org.apache.geode.cache.query.internal.ResultsCollectionWrapper;
import org.apache.geode.cache.query.internal.index.IndexManager;
import org.apache.geode.cache.query.internal.index.IndexManager.TestHook;
import org.apache.geode.cache.query.internal.types.ObjectTypeImpl;
import org.apache.geode.test.dunit.ThreadUtils;
import org.apache.geode.test.junit.categories.IntegrationTest;
@Category(IntegrationTest.class)
public class LikePredicateJUnitTest {
@Before
public void setUp() throws Exception {
CacheUtils.startCache();
}
@After
public void tearDown() throws Exception {
CacheUtils.closeCache();
}
@Test
public void testLikePercentageTerminated_1_withoutBindParam() throws Exception {
likePercentageTerminated_1(false);
}
@Test
public void testLikePercentageTerminated_1_withBindParam() throws Exception {
likePercentageTerminated_1(true);
}
/**
* Tests simple % terminated pattern with atleast one preceding character
*
* @throws Exception
*/
private void likePercentageTerminated_1(boolean useBindParam) throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
char ch = 'd';
String base = "abc";
for (int i = 1; i < 6; ++i) {
Portfolio pf = new Portfolio(i);
pf.status = base + ch;
ch += 1;
region.put(new Integer(i), pf);
}
base = "abd";
ch = 'd';
for (int i = 6; i < 11; ++i) {
Portfolio pf = new Portfolio(i);
pf.status = base + ch;
ch += 1;
region.put(new Integer(i), pf);
}
QueryService qs = cache.getQueryService();
Query q;
SelectResults results;
SelectResults expectedResults;
String predicate = "";
if (useBindParam) {
predicate = "$1";
} else {
predicate = " 'abc%'";
}
q = qs.newQuery("SELECT distinct * FROM /pos ps WHERE ps.status like " + predicate);
if (useBindParam) {
results = (SelectResults) q.execute(new Object[] {"abc%"});
} else {
results = (SelectResults) q.execute();
}
ResultsBag bag = new ResultsBag(null);
for (int i = 1; i < 6; ++i) {
bag.add(region.get(new Integer(i)));
}
expectedResults = new ResultsCollectionWrapper(new ObjectTypeImpl(Object.class), bag.asSet());
SelectResults rs[][] = new SelectResults[][] {{results, expectedResults}};
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
// Create Index
qs.createIndex("status", IndexType.FUNCTIONAL, "ps.status", "/pos ps");
QueryObserver old = QueryObserverHolder.setInstance(new QueryObserverAdapter() {
private boolean indexCalled = false;
public void afterIndexLookup(Collection results) {
indexCalled = true;
}
public void endQuery() {
assertTrue(indexCalled);
}
});
if (useBindParam) {
results = (SelectResults) q.execute(new Object[] {"abc%"});
} else {
results = (SelectResults) q.execute();
}
rs[0][0] = results;
rs[0][1] = expectedResults;
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
QueryObserverHolder.setInstance(old);
}
@Test
public void testLikePercentageTerminated_2_withoutParams() throws Exception {
likePercentageTerminated_2(false);
}
@Test
public void testLikePercentageTerminated_2_withParams() throws Exception {
likePercentageTerminated_2(true);
}
/**
* Tests a pattern which just contains a single % indicating all match
*
* @throws Exception
*/
private void likePercentageTerminated_2(boolean useBindParam) throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
char ch = 'd';
String base = "abc";
for (int i = 1; i < 6; ++i) {
Portfolio pf = new Portfolio(i);
pf.status = base + ch;
ch += 1;
region.put(new Integer(i), pf);
}
base = "abd";
ch = 'd';
for (int i = 6; i < 11; ++i) {
Portfolio pf = new Portfolio(i);
pf.status = base + ch;
ch += 1;
region.put(new Integer(i), pf);
}
QueryService qs = cache.getQueryService();
Query q;
SelectResults results;
SelectResults expectedResults;
String predicate = "";
if (useBindParam) {
predicate = "$1";
} else {
predicate = " '%'";
}
q = qs.newQuery("SELECT distinct * FROM /pos ps WHERE ps.status like " + predicate);
if (useBindParam) {
results = (SelectResults) q.execute(new Object[] {"%"});
} else {
results = (SelectResults) q.execute();
}
ResultsBag bag = new ResultsBag(null);
for (int i = 1; i < 11; ++i) {
bag.add(region.get(new Integer(i)));
}
expectedResults = new ResultsCollectionWrapper(new ObjectTypeImpl(Object.class), bag.asSet());
SelectResults rs[][] = new SelectResults[][] {{results, expectedResults}};
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
// Create Index
qs.createIndex("status", IndexType.FUNCTIONAL, "ps.status", "/pos ps");
QueryObserver old = QueryObserverHolder.setInstance(new QueryObserverAdapter() {
private boolean indexCalled = false;
public void afterIndexLookup(Collection results) {
indexCalled = true;
}
public void endQuery() {
assertTrue(indexCalled);
}
});
if (useBindParam) {
results = (SelectResults) q.execute(new Object[] {"%"});
} else {
results = (SelectResults) q.execute();
}
rs[0][0] = results;
rs[0][1] = expectedResults;
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
QueryObserverHolder.setInstance(old);
}
@Test
public void testLikePercentageTerminated_3_withoutBindPrms() throws Exception {
likePercentageTerminated_3(false);
}
@Test
public void testLikePercentageTerminated_3_withBindPrms() throws Exception {
likePercentageTerminated_3(true);
}
/**
* Tests a simple % terminated like predicate with an OR condition
*
* @throws Exception
*/
private void likePercentageTerminated_3(boolean useBindPrm) throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
char ch = 'd';
String base = "abc";
for (int i = 1; i < 6; ++i) {
Portfolio pf = new Portfolio(i);
pf.status = base + ch;
ch += 1;
region.put(new Integer(i), pf);
}
base = "abd";
ch = 'd';
for (int i = 6; i < 11; ++i) {
Portfolio pf = new Portfolio(i);
pf.status = base + ch;
ch += 1;
region.put(new Integer(i), pf);
}
QueryService qs = cache.getQueryService();
Query q;
SelectResults results;
SelectResults expectedResults;
String predicate = "";
if (useBindPrm) {
predicate = "$1";
} else {
predicate = " 'abc%'";
}
q = qs.newQuery(
"SELECT distinct * FROM /pos ps WHERE ps.status like " + predicate + " OR ps.ID > 6");
if (useBindPrm) {
results = (SelectResults) q.execute(new Object[] {"abc%"});
} else {
results = (SelectResults) q.execute();
}
ResultsBag bag = new ResultsBag(null);
for (int i = 1; i < 11; ++i) {
if (i != 6) {
bag.add(region.get(new Integer(i)));
}
}
expectedResults = new ResultsCollectionWrapper(new ObjectTypeImpl(Object.class), bag.asSet());
SelectResults rs[][] = new SelectResults[][] {{results, expectedResults}};
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
// Create Index
qs.createIndex("status", IndexType.FUNCTIONAL, "ps.status", "/pos ps");
QueryObserver old = QueryObserverHolder.setInstance(new QueryObserverAdapter() {
private boolean indexCalled = false;
public void afterIndexLookup(Collection results) {
indexCalled = true;
}
public void endQuery() {
assertFalse(indexCalled);
}
});
if (useBindPrm) {
results = (SelectResults) q.execute(new Object[] {"abc%"});
} else {
results = (SelectResults) q.execute();
}
rs[0][0] = results;
rs[0][1] = expectedResults;
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
qs.createIndex("id", IndexType.FUNCTIONAL, "ps.ID", "/pos ps");
QueryObserverHolder.setInstance(new QueryObserverAdapter() {
private boolean[] indexCalled = new boolean[] {false, false};
private int i = 0;
public void afterIndexLookup(Collection results) {
indexCalled[i++] = true;
}
public void endQuery() {
for (int i = 0; i < indexCalled.length; ++i) {
assertTrue(indexCalled[i]);
}
}
});
if (useBindPrm) {
results = (SelectResults) q.execute(new Object[] {"abc%"});
} else {
results = (SelectResults) q.execute();
}
rs[0][0] = results;
rs[0][1] = expectedResults;
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
QueryObserverHolder.setInstance(old);
}
@Test
public void testLikePercentageTerminated_4_withoutBindPrms() throws Exception {
likePercentageTerminated_4(false);
}
@Test
public void testLikePercentageTerminated_4_withBindPrms() throws Exception {
likePercentageTerminated_4(true);
}
/**
* Tests a simple % terminated like predicate with an AND condition
*
* @throws Exception
*/
private void likePercentageTerminated_4(boolean useBindPrm) throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
String base = "abc";
String pattern = base;
// so we will get string like abcdcdcdcdcdc
for (int i = 1; i < 200; ++i) {
Portfolio pf = new Portfolio(i);
pattern += "dc";
pf.status = pattern;
region.put(new Integer(i), pf);
}
base = "abd";
pattern = base;
// so we will get string like abddcdcdcd
for (int i = 201; i < 400; ++i) {
Portfolio pf = new Portfolio(i);
pattern += "dc";
pf.status = pattern;
region.put(new Integer(i), pf);
}
QueryService qs = cache.getQueryService();
Query q;
SelectResults results;
SelectResults expectedResults;
String predicate = "";
if (useBindPrm) {
predicate = "$1";
} else {
predicate = " 'abc%'";
}
q = qs.newQuery("SELECT distinct * FROM /pos ps WHERE ps.status like " + predicate
+ " AND ps.ID > 2 AND ps.ID < 150");
if (useBindPrm) {
results = (SelectResults) q.execute(new Object[] {"abc%"});
} else {
results = (SelectResults) q.execute();
}
ResultsBag bag = new ResultsBag(null);
for (int i = 3; i < 150; ++i) {
bag.add(region.get(new Integer(i)));
}
expectedResults = new ResultsCollectionWrapper(new ObjectTypeImpl(Object.class), bag.asSet());
SelectResults rs[][] = new SelectResults[][] {{results, expectedResults}};
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
// Create Index
qs.createIndex("status", IndexType.FUNCTIONAL, "ps.status", "/pos ps");
QueryObserver old = QueryObserverHolder.setInstance(new QueryObserverAdapter() {
private boolean[] indexCalled = new boolean[] {false, false};
private int i = 0;
public void afterIndexLookup(Collection results) {
indexCalled[i++] = true;
}
public void endQuery() {
assertTrue(indexCalled[0]);
assertFalse(indexCalled[1]);
}
});
if (useBindPrm) {
results = (SelectResults) q.execute(new Object[] {"abc%"});
} else {
results = (SelectResults) q.execute();
}
rs[0][0] = results;
rs[0][1] = expectedResults;
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
qs.createIndex("id", IndexType.FUNCTIONAL, "ps.ID", "/pos ps");
QueryObserverHolder.setInstance(new QueryObserverAdapter() {
private boolean[] indexCalled = new boolean[] {false, false};
private int i = 0;
public void afterIndexLookup(Collection results) {
indexCalled[i++] = true;
}
public void endQuery() {
// Only one indexed condition should be called
boolean indexInvoked = false;
for (int i = 0; i < indexCalled.length; ++i) {
indexInvoked = indexInvoked || indexCalled[i];
}
assertTrue(indexInvoked);
}
});
if (useBindPrm) {
results = (SelectResults) q.execute(new Object[] {"abc%"});
} else {
results = (SelectResults) q.execute();
}
rs[0][0] = results;
rs[0][1] = expectedResults;
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
QueryObserverHolder.setInstance(old);
}
@Test
public void testLikePercentageTerminated_5_withoutBindPrms() throws Exception {
likePercentageTerminated_5(false);
}
@Test
public void testLikePercentageTerminated_5_withBindPrms() throws Exception {
likePercentageTerminated_5(true);
}
private void likePercentageTerminated_5(boolean useBindPrm) throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
char ch = 'd';
String base = "abc";
for (int i = 1; i < 6; ++i) {
Portfolio pf = new Portfolio(i);
pf.status = base + ch;
ch += 1;
region.put(new Integer(i), pf);
}
QueryService qs = cache.getQueryService();
Query q;
SelectResults results;
SelectResults expectedResults;
String predicate = "";
if (useBindPrm) {
predicate = "$1";
} else {
predicate = " 'a%c%'";
}
q = qs.newQuery("SELECT distinct * FROM /pos ps WHERE ps.status like " + predicate);
if (useBindPrm) {
results = (SelectResults) q.execute(new Object[] {"a%bc%"});
} else {
results = (SelectResults) q.execute();
}
ResultsBag bag = new ResultsBag(null);
for (int i = 1; i < 6; ++i) {
bag.add(region.get(new Integer(i)));
}
expectedResults = new ResultsCollectionWrapper(new ObjectTypeImpl(Object.class), bag.asSet());
SelectResults rs[][] = new SelectResults[][] {{results, expectedResults}};
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
// Create Index
qs.createIndex("status", IndexType.FUNCTIONAL, "ps.status", "/pos ps");
q = qs.newQuery("SELECT distinct * FROM /pos ps WHERE ps.status like " + predicate);
if (useBindPrm) {
results = (SelectResults) q.execute(new Object[] {"a%bc%"});
} else {
results = (SelectResults) q.execute();
}
rs = new SelectResults[][] {{results, expectedResults}};
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
if (useBindPrm) {
predicate = "$1";
} else {
predicate = "'abc_'";
}
q = qs.newQuery("SELECT distinct * FROM /pos ps WHERE ps.status like " + predicate);
if (useBindPrm) {
results = (SelectResults) q.execute(new Object[] {"abc_"});
} else {
results = (SelectResults) q.execute();
}
rs = new SelectResults[][] {{results, expectedResults}};
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
if (useBindPrm) {
predicate = "$1";
} else {
predicate = "'_bc_'";
}
q = qs.newQuery("SELECT distinct * FROM /pos ps WHERE ps.status like " + predicate);
if (useBindPrm) {
results = (SelectResults) q.execute(new Object[] {"_bc_"});
} else {
results = (SelectResults) q.execute();
}
rs = new SelectResults[][] {{results, expectedResults}};
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
}
@Test
public void testEqualityForm_1_withoutBindParams() throws Exception {
equalityForm_1(false);
}
@Test
public void testEqualityForm_1_withBindParams() throws Exception {
equalityForm_1(true);
}
/**
* Tests simple non % or non _ terminated string which in effect means equality
*
* @throws Exception
*/
private void equalityForm_1(boolean useBindPrms) throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
char ch = 'd';
String base = "abc";
for (int i = 1; i < 6; ++i) {
Portfolio pf = new Portfolio(i);
pf.status = base + ch;
ch += 1;
region.put(new Integer(i), pf);
}
base = "abd";
ch = 'd';
for (int i = 6; i < 11; ++i) {
Portfolio pf = new Portfolio(i);
pf.status = base + ch;
ch += 1;
region.put(new Integer(i), pf);
}
QueryService qs = cache.getQueryService();
Query q;
SelectResults results;
SelectResults expectedResults;
String predicate = "";
if (useBindPrms) {
predicate = "$1";
} else {
predicate = " 'abcd'";
}
q = qs.newQuery("SELECT distinct * FROM /pos ps WHERE ps.status like " + predicate);
if (useBindPrms) {
results = (SelectResults) q.execute(new Object[] {"abcd"});
} else {
results = (SelectResults) q.execute();
}
ResultsBag bag = new ResultsBag(null);
bag.add(region.get(new Integer(1)));
expectedResults = new ResultsCollectionWrapper(new ObjectTypeImpl(Object.class), bag.asSet());
SelectResults rs[][] = new SelectResults[][] {{results, expectedResults}};
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
// Create Index
qs.createIndex("status", IndexType.FUNCTIONAL, "ps.status", "/pos ps");
QueryObserver old = QueryObserverHolder.setInstance(new QueryObserverAdapter() {
private boolean indexCalled = false;
public void afterIndexLookup(Collection results) {
indexCalled = true;
}
public void endQuery() {
assertTrue(indexCalled);
}
});
if (useBindPrms) {
results = (SelectResults) q.execute(new Object[] {"abcd"});
} else {
results = (SelectResults) q.execute();
}
rs[0][0] = results;
rs[0][1] = expectedResults;
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
QueryObserverHolder.setInstance(old);
}
@Test
public void testEqualityForm_2_withoutBindPrms() throws Exception {
equalityForm_2(false);
}
@Test
public void testEqualityForm_2_withBindPrms() throws Exception {
equalityForm_2(true);
}
/**
* Tests simple \% or \ _ terminated string which in effect means equality
*
* @throws Exception
*/
private void equalityForm_2(boolean useBindPrms) throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
String str = "d_";
String base = "abc";
for (int i = 1; i < 6; ++i) {
Portfolio pf = new Portfolio(i);
pf.status = base + str;
region.put(new Integer(i), pf);
}
base = "abc";
str = "d%";
for (int i = 6; i < 11; ++i) {
Portfolio pf = new Portfolio(i);
pf.status = base + str;
region.put(new Integer(i), pf);
}
QueryService qs = cache.getQueryService();
Query q, q1;
SelectResults results;
SelectResults expectedResults;
String predicate = "";
if (useBindPrms) {
predicate = "$1";
} else {
predicate = " 'abcd\\_'";
}
q = qs.newQuery("SELECT distinct * FROM /pos ps WHERE ps.status like " + predicate);
if (useBindPrms) {
results = (SelectResults) q.execute(new Object[] {"abcd\\_"});
} else {
results = (SelectResults) q.execute();
}
ResultsBag bag = new ResultsBag(null);
for (int i = 1; i < 6; ++i) {
bag.add(region.get(new Integer(i)));
}
expectedResults = new ResultsCollectionWrapper(new ObjectTypeImpl(Object.class), bag.asSet());
SelectResults rs[][] = new SelectResults[][] {{results, expectedResults}};
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
predicate = "";
if (useBindPrms) {
predicate = "$1";
} else {
predicate = " 'abcd\\%'";
}
q1 = qs.newQuery("SELECT distinct * FROM /pos ps WHERE ps.status like " + predicate);
if (useBindPrms) {
results = (SelectResults) q1.execute(new Object[] {"abcd\\%"});
} else {
results = (SelectResults) q1.execute();
}
bag = new ResultsBag(null);
for (int i = 6; i < 11; ++i) {
bag.add(region.get(new Integer(i)));
}
SelectResults expectedResults1 =
new ResultsCollectionWrapper(new ObjectTypeImpl(Object.class), bag.asSet());
SelectResults rs1[][] = new SelectResults[][] {{results, expectedResults1}};
CacheUtils.compareResultsOfWithAndWithoutIndex(rs1, this);
// Create Index
qs.createIndex("status", IndexType.FUNCTIONAL, "ps.status", "/pos ps");
QueryObserver old = QueryObserverHolder.setInstance(new QueryObserverAdapter() {
private boolean indexCalled = false;
public void afterIndexLookup(Collection results) {
indexCalled = true;
}
public void endQuery() {
assertTrue(indexCalled);
}
});
if (useBindPrms) {
results = (SelectResults) q.execute(new Object[] {"abcd\\_"});
} else {
results = (SelectResults) q.execute();
}
rs[0][0] = results;
rs[0][1] = expectedResults;
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
if (useBindPrms) {
results = (SelectResults) q1.execute(new Object[] {"abcd\\%"});
} else {
results = (SelectResults) q1.execute();
}
rs1[0][0] = results;
rs1[0][1] = expectedResults1;
CacheUtils.compareResultsOfWithAndWithoutIndex(rs1, this);
QueryObserverHolder.setInstance(old);
}
@Test
public void testRegexMetaCharWithoutBindPrms() throws Exception {
regexMetaChar(false);
}
@Test
public void testRegexMetaCharWithBindPrms() throws Exception {
regexMetaChar(true);
}
/**
* Tests for regular expression meta chars. This has no special meaning with Like.
*
* @throws Exception
*/
private void regexMetaChar(boolean useBindPrms) throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
String[] values = new String[] {"active", "act**ve", "ac+t+ve", "?act?ve", "act)ve^",
"|+act(ve", "act*+|ve", "^+act.ve+^", "act[]ve", "act][ve", "act^[a-z]ve", "act/ve",
"inactive", "acxtxve", "ac(tiv)e", "act()ive", "act{}ive", "act{ive"};
// Add values to region.
for (int i = 0; i < values.length; i++) {
region.put(new Integer(i), values[i]);
}
// Add % and _ with escape char.
region.put(new Integer(values.length + 1), "act%+ive");
region.put(new Integer(values.length + 2), "act_+ive");
QueryService qs = cache.getQueryService();
Query q;
SelectResults results;
for (int i = 0; i < values.length; i++) {
if (!useBindPrms) {
q = qs.newQuery("select p from /pos.values p where p like '" + values[i] + "'");
results = (SelectResults) q.execute();
} else {
q = qs.newQuery("select p from /pos.values p where p like $1");
results = (SelectResults) q.execute(new Object[] {values[i]});
}
List r = results.asList();
if (r.size() != 1 || !r.get(0).equals(values[i])) {
fail("Unexpected result. expected :" + values[i] + " for the like predicate: " + values[i]
+ " found : " + (r.size() == 1 ? r.get(0) : "Result size not equal to 1"));
}
}
// Create Index
qs.createIndex("p", IndexType.FUNCTIONAL, "p", "/pos.values p");
for (int i = 0; i < values.length; i++) {
if (!useBindPrms) {
q = qs.newQuery("select p from /pos.values p where p like '" + values[i] + "'");
results = (SelectResults) q.execute();
} else {
q = qs.newQuery("select p from /pos.values p where p like $1");
results = (SelectResults) q.execute(new Object[] {values[i]});
}
List r = results.asList();
if (r.size() != 1 || !r.get(0).equals(values[i])) {
fail("Unexpected result. expected :" + values[i] + " for the like predicate: " + values[i]
+ " found : " + (r.size() == 1 ? r.get(0) : "Result size not equal to 1"));
}
}
}
@Test
public void testEnhancedLikeWithoutBindPrms() throws Exception {
enhancedLike(false);
}
@Test
public void testEnhancedLikeWithBindPrms() throws Exception {
enhancedLike(true);
}
/**
* Tests with combination of % and _ Supported from 6.6
*
* @throws Exception
*/
private void enhancedLike(boolean useBindPrms) throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
String[] values = new String[] {"active",};
String[] likePredicates = new String[] {"active", "act%%ve", "a%e", "%ctiv%", "%c%iv%",
"%ctive", "%%ti%", "activ_", "_ctive", "ac_ive", "_c_iv_", "_ctiv%", "__tive", "act__e",
"a%iv_", "a_tiv%", "%", "ac%",};
for (int i = 0; i < values.length; i++) {
region.put(new Integer(i), values[i]);
}
QueryService qs = cache.getQueryService();
Query q;
SelectResults results;
for (int i = 0; i < likePredicates.length; i++) {
if (!useBindPrms) {
q = qs.newQuery("select p from /pos.values p where p like '" + likePredicates[i] + "'");
results = (SelectResults) q.execute();
} else {
q = qs.newQuery("select p from /pos.values p where p like $1");
results = (SelectResults) q.execute(new Object[] {likePredicates[i]});
}
List r = results.asList();
if (r.size() != 1 || !r.get(0).equals(values[0])) {
fail("Unexpected result. expected :" + values[0] + " for the like predicate: "
+ likePredicates[i] + " found : "
+ (r.size() == 1 ? r.get(0) : "Result size not equal to 1"));
}
}
// Create Index
qs.createIndex("p", IndexType.FUNCTIONAL, "p", "/pos.values p");
for (int i = 0; i < likePredicates.length; i++) {
if (!useBindPrms) {
q = qs.newQuery("select p from /pos.values p where p like '" + likePredicates[i] + "'");
results = (SelectResults) q.execute();
} else {
q = qs.newQuery("select p from /pos.values p where p like $1");
results = (SelectResults) q.execute(new Object[] {likePredicates[i]});
}
List r = results.asList();
if (r.size() != 1 || !r.get(0).equals(values[0])) {
fail("Unexpected result. expected :" + values[0] + " for the like predicate: "
+ likePredicates[i] + " found : "
+ (r.size() == 1 ? r.get(0) : "Result size not equal to 1"));
}
}
}
@Test
public void testEnhancedLike2WithoutBindPrms() throws Exception {
enhancedLike2(false);
}
@Test
public void testEnhancedLike2WithBindPrms() throws Exception {
enhancedLike2(true);
}
private void enhancedLike2(boolean useBindPrms) throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
String[] values = new String[] {"ac\\tive", "X\\\\X", "Y%Y", "Z\\%Z", "pass\\ive",
"inact\\%+ive", "1inact\\_+ive",};
String[] likePredicates = new String[] {"ac\\\\tive", "ac\\\\%", "ac_tive", "Y\\%Y", "X__X",
"X%X", "Z\\\\\\%Z", "inact\\\\%+ive", "1inact\\\\_+ive",};
String[] result = new String[] {"ac\\tive", "ac\\tive", "ac\\tive", "Y%Y", "X\\\\X", "X\\\\X",
"Z\\%Z", "inact\\%+ive", "1inact\\_+ive",};
for (int i = 0; i < values.length; i++) {
region.put(new Integer(i), values[i]);
}
QueryService qs = cache.getQueryService();
Query q;
SelectResults results;
for (int i = 0; i < likePredicates.length; i++) {
if (!useBindPrms) {
q = qs.newQuery("select p from /pos.values p where p like '" + likePredicates[i] + "'");
results = (SelectResults) q.execute();
} else {
q = qs.newQuery("select p from /pos.values p where p like $1");
results = (SelectResults) q.execute(new Object[] {likePredicates[i]});
}
List r = results.asList();
if (r.size() != 1 || !r.get(0).equals(result[i])) {
fail("Unexpected result. expected :" + result[i] + " for the like predicate: "
+ likePredicates[i] + " found : "
+ (r.size() == 1 ? r.get(0) : "Result size not equal to 1"));
}
}
// Create Index
qs.createIndex("p", IndexType.FUNCTIONAL, "p", "/pos.values p");
for (int i = 0; i < likePredicates.length; i++) {
if (!useBindPrms) {
q = qs.newQuery("select p from /pos.values p where p like '" + likePredicates[i] + "'");
results = (SelectResults) q.execute();
} else {
q = qs.newQuery("select p from /pos.values p where p like $1");
results = (SelectResults) q.execute(new Object[] {likePredicates[i]});
}
List r = results.asList();
if (r.size() != 1 || !r.get(0).equals(result[i])) {
fail("Unexpected result. expected :" + result[i] + " for the like predicate: "
+ likePredicates[i] + " found : "
+ (r.size() == 1 ? r.get(0) : "Result size not equal to 1"));
}
}
}
/**
* Query with index on other fields.
*
* @throws Exception
*/
@Test
public void testLikeWithOtherIndexedField() throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
String base = "abc";
for (int i = 1; i <= 10; i++) {
Portfolio pf = new Portfolio(i);
pf.pkid = "1";
if ((i % 4) == 0) {
pf.status = base;
} else if ((i <= 2)) {
pf.pkid = "2";
}
region.put(new Integer(i), pf);
}
QueryService qs = cache.getQueryService();
Query q;
SelectResults results;
SelectResults expectedResults;
int expectedResultSize = 2;
q = qs.newQuery(" SELECT * FROM /pos ps WHERE ps.status like '%b%'");
results = (SelectResults) q.execute();
if (results.size() != expectedResultSize) {
fail("Unexpected result. expected :" + expectedResultSize + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos ps WHERE ps.status like '%b%' or ps.pkid = '2' ");
results = (SelectResults) q.execute();
if (results.size() != (expectedResultSize * 2)) {
fail("Unexpected result. expected :" + (expectedResultSize * 2) + " found : "
+ results.size());
}
// Query to be compared with indexed results.
q = qs.newQuery(" SELECT * FROM /pos ps WHERE ps.status like '%b%' and ps.pkid = '1' ");
expectedResults = (SelectResults) q.execute();
if (expectedResults.size() != expectedResultSize) {
fail("Unexpected result. expected :" + expectedResultSize + " found : "
+ expectedResults.size());
}
// Create Index
qs.createIndex("pkid", IndexType.FUNCTIONAL, "ps.pkid", "/pos ps");
QueryObserver old = QueryObserverHolder.setInstance(new QueryObserverAdapter() {
private boolean indexCalled = false;
public void afterIndexLookup(Collection results) {
indexCalled = true;
}
public void endQuery() {
assertTrue(indexCalled);
}
});
results = (SelectResults) q.execute();
SelectResults rs[][] = new SelectResults[][] {{results, expectedResults}};
// rs[0][0] = results;
// rs[0][1] = expectedResults;
if (results.size() != expectedResultSize) {
fail("Unexpected result. expected :" + expectedResultSize + " found : " + results.size());
}
// compare results.
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
q = qs.newQuery(" SELECT * FROM /pos ps WHERE ps.status like '%b%' or ps.pkid = '2' ");
results = (SelectResults) q.execute();
if (results.size() != (expectedResultSize * 2)) {
fail("Unexpected result. expected :" + (expectedResultSize * 2) + " found : "
+ results.size());
}
QueryObserverHolder.setInstance(old);
}
/**
* Query with index on other fields.
*
* @throws Exception
*/
@Test
public void testLikeWithOtherIndexedField2() throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
int size = 10;
String base = "abc";
for (int i = 1; i <= size; i++) {
Portfolio pf = new Portfolio(i);
pf.pkid = "1";
if ((i % 4) == 0) {
pf.status = base;
} else if ((i <= 2)) {
pf.pkid = "2";
}
region.put(new Integer(i), pf);
}
QueryService qs = cache.getQueryService();
Query q;
SelectResults results;
SelectResults expectedResults;
int expectedResultSize = 2;
q = qs.newQuery(" SELECT * FROM /pos ps WHERE ps.status like '%b%'");
results = (SelectResults) q.execute();
if (results.size() != expectedResultSize) {
fail("Unexpected result. expected :" + expectedResultSize + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos ps WHERE ps.status like '%b%' or ps.pkid = '2' ");
results = (SelectResults) q.execute();
if (results.size() != (expectedResultSize * 2)) {
fail("Unexpected result. expected :" + (expectedResultSize * 2) + " found : "
+ results.size());
}
q = qs.newQuery(" SELECT * FROM /pos ps WHERE ps.status like '%b%' or ps.ID > 0 ");
results = (SelectResults) q.execute();
if (results.size() != size) {
fail("Unexpected result. expected :" + size + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos ps WHERE ps.status like '%b%' or ps.ID > 4 ");
results = (SelectResults) q.execute();
if (results.size() != 7) {
fail("Unexpected result. expected :" + 7 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos ps WHERE ps.status like '%b%' and ps.ID > 3 ");
results = (SelectResults) q.execute();
if (results.size() != 2) {
fail("Unexpected result. expected :" + 2 + " found : " + results.size());
}
// Query to be compared with indexed results.
q = qs.newQuery(" SELECT * FROM /pos ps WHERE ps.status like '%b%' and ps.pkid = '1' ");
expectedResults = (SelectResults) q.execute();
if (expectedResults.size() != expectedResultSize) {
fail("Unexpected result. expected :" + expectedResultSize + " found : "
+ expectedResults.size());
}
// Create Index
qs.createIndex("pkid", IndexType.FUNCTIONAL, "ps.pkid", "/pos ps");
qs.createIndex("status", IndexType.FUNCTIONAL, "ps.status", "/pos ps");
qs.createIndex("id", IndexType.FUNCTIONAL, "ps.ID", "/pos ps");
QueryObserver old = QueryObserverHolder.setInstance(new QueryObserverAdapter() {
private boolean indexCalled = false;
public void afterIndexLookup(Collection results) {
indexCalled = true;
}
public void endQuery() {
assertTrue(indexCalled);
}
});
results = (SelectResults) q.execute();
SelectResults rs[][] = new SelectResults[][] {{results, expectedResults}};
// rs[0][0] = results;
// rs[0][1] = expectedResults;
if (results.size() != expectedResultSize) {
fail("Unexpected result. expected :" + expectedResultSize + " found : " + results.size());
}
// compare results.
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
q = qs.newQuery(" SELECT * FROM /pos ps WHERE ps.status like '_b_' or ps.pkid = '2' ");
results = (SelectResults) q.execute();
if (results.size() != (expectedResultSize * 2)) {
fail("Unexpected result. expected :" + (expectedResultSize * 2) + " found : "
+ results.size());
}
q = qs.newQuery(" SELECT * FROM /pos ps WHERE ps.status like '%b%' or ps.ID > 0 ");
results = (SelectResults) q.execute();
if (results.size() != size) {
fail("Unexpected result. expected :" + size + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos ps WHERE ps.status like '%b%' or ps.ID > 4 ");
results = (SelectResults) q.execute();
if (results.size() != 7) {
fail("Unexpected result. expected :" + 7 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos ps WHERE ps.status like '%b%' and ps.ID > 3 ");
results = (SelectResults) q.execute();
if (results.size() != 2) {
fail("Unexpected result. expected :" + 2 + " found : " + results.size());
}
QueryObserverHolder.setInstance(old);
}
@Test
public void testMultipleWhereClausesWithIndexes() throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
QueryService qs = cache.getQueryService();
Query q;
String[] queries = new String[] {
" SELECT status, pkid FROM /pos WHERE status like 'inactive' and pkid like '1' ",
" SELECT status, pkid FROM /pos WHERE status like 'active' or pkid like '1' ",
" SELECT status, pkid FROM /pos WHERE status like 'in%' and pkid like '1' ",
" SELECT status FROM /pos WHERE status like 'in%' or pkid like '1'",
" SELECT pkid FROM /pos WHERE status like 'inact%' and pkid like '1%' ",
" SELECT * FROM /pos WHERE status like 'inact%' or pkid like '1%' ",
" SELECT * FROM /pos WHERE status like 'inactiv_' or pkid like '1%' ",
" SELECT status, pkid FROM /pos WHERE status like '_nactive' or pkid like '1%' ",
" SELECT * FROM /pos WHERE status like '_nac%ive' or pkid like '1%' ",
" SELECT * FROM /pos WHERE status like 'in_ctive' or pkid like '1%' ",
" SELECT * FROM /pos WHERE status like 'in_ctive' or pkid like '1_' ",
" SELECT status, pkid FROM /pos WHERE status like '%ctive' and pkid like '1%' ",
" SELECT * FROM /pos WHERE NOT (status like 'active' or pkid like '1') ",
" SELECT * FROM /pos WHERE NOT (status like 'active' or pkid like '1%') ",
" SELECT * FROM /pos WHERE NOT (status like 'acti%' and pkid like '1%') ",
" SELECT * FROM /pos WHERE NOT (status like 'acti%' or pkid like '1%') ",
" SELECT * FROM /pos WHERE NOT (status like 'active' and pkid like '1%') ",
" SELECT * FROM /pos WHERE NOT (status like 'active' or pkid like '1%') ",
" SELECT * FROM /pos WHERE NOT (status like 'active' or pkid like '11' or pkid like '1') ",
" SELECT * FROM /pos WHERE status like '%' and pkid like '1%' ",
" SELECT * FROM /pos WHERE pkid like '_'",
" SELECT * FROM /pos WHERE status like '.*tive' ",
" SELECT * FROM /pos WHERE pkid like '1+' ",
" SELECT * FROM /pos WHERE unicodeṤtring like 'ṤṶẐ' ",
" SELECT * FROM /pos WHERE unicodeṤtring like 'ṤṶ%' ",
" SELECT * FROM /pos p, p.positions.values v WHERE v.secId like 'I%' ",
" SELECT * FROM /pos p, p.positions.values v WHERE v.secId like '%L' ",
" SELECT * FROM /pos p, p.positions.values v WHERE v.secId like 'A%L' ",};
SelectResults[][] sr = new SelectResults[queries.length][2];
for (int i = 0; i < 20; i++) {
Portfolio pf = new Portfolio(i);
region.put(new Integer(i), pf);
}
for (int i = 0; i < queries.length; i++) {
q = qs.newQuery(queries[i]);
sr[i][0] = (SelectResults) q.execute();
}
// Create index.
qs.createIndex("pkidIndex", "pkid", "/pos");
qs.createIndex("statusIndex", "status", "/pos");
qs.createIndex("unicodeṤtringIndex", "unicodeṤtring", "/pos");
qs.createIndex("secIdIndex", "v.secId", "/pos p, p.positions.values v");
for (int i = 0; i < queries.length; i++) {
q = qs.newQuery(queries[i]);
try {
sr[i][1] = (SelectResults) q.execute();
} catch (Exception e) {
fail("Query execution failed for: " + queries[i]);
}
}
// compare results.
CacheUtils.compareResultsOfWithAndWithoutIndex(sr, this);
SelectResults results = null;
q = qs.newQuery("SELECT status FROM /pos WHERE status like 'inactive' and pkid like '1' ");
results = (SelectResults) q.execute();
if (results.size() != 1) {
fail("Unexpected result. expected :" + 1 + " found : " + results.size());
}
q = qs.newQuery(" SELECT pkid FROM /pos WHERE status like 'active' or pkid like '1' ");
results = (SelectResults) q.execute();
if (results.size() != 11) {
fail("Unexpected result. expected :" + 11 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE status like 'in%' and pkid like '1' ");
results = (SelectResults) q.execute();
if (results.size() != 1) {
fail("Unexpected result. expected :" + 1 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE status like 'in%' or pkid like '1' ");
results = (SelectResults) q.execute();
if (results.size() != 10) {
fail("Unexpected result. expected :" + 10 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE status like 'inact%' and pkid like '1%' ");
results = (SelectResults) q.execute();
if (results.size() != 6) {
fail("Unexpected result. expected :" + 6 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE status like 'inact%' or pkid like '1%' ");
results = (SelectResults) q.execute();
if (results.size() != 15) {
fail("Unexpected result. expected :" + 15 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE status like 'inactiv_' or pkid like '1%' ");
results = (SelectResults) q.execute();
if (results.size() != 15) {
fail("Unexpected result. expected :" + 15 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE status like '_nactive' or pkid like '1%' ");
results = (SelectResults) q.execute();
if (results.size() != 15) {
fail("Unexpected result. expected :" + 15 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE status like '_nac%ive' or pkid like '1%' ");
results = (SelectResults) q.execute();
if (results.size() != 15) {
fail("Unexpected result. expected :" + 15 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE status like 'in_ctive' or pkid like '1%' ");
results = (SelectResults) q.execute();
if (results.size() != 15) {
fail("Unexpected result. expected :" + 15 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE status like 'in_ctive' or pkid like '1_' ");
results = (SelectResults) q.execute();
if (results.size() != 15) {
fail("Unexpected result. expected :" + 15 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE status like '%ctive' and pkid like '1%' ");
results = (SelectResults) q.execute();
if (results.size() != 11) {
fail("Unexpected result. expected :" + 11 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE NOT (status like 'active' or pkid like '1') ");
results = (SelectResults) q.execute();
if (results.size() != 9) {
fail("Unexpected result. expected :" + 9 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE NOT (status like 'active' or pkid like '1%') ");
results = (SelectResults) q.execute();
if (results.size() != 4) {
fail("Unexpected result. expected :" + 4 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE NOT (status like 'acti%' and pkid like '1%') ");
results = (SelectResults) q.execute();
if (results.size() != 15) {
fail("Unexpected result. expected :" + 15 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE NOT (status like 'acti%' or pkid like '1%') ");
results = (SelectResults) q.execute();
if (results.size() != 4) {
fail("Unexpected result. expected :" + 4 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE NOT (status like 'active' and pkid like '1%') ");
results = (SelectResults) q.execute();
if (results.size() != 15) {
fail("Unexpected result. expected :" + 15 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE NOT (status like 'active' or pkid like '1%') ");
results = (SelectResults) q.execute();
if (results.size() != 4) {
fail("Unexpected result. expected :" + 4 + " found : " + results.size());
}
q = qs.newQuery(
" SELECT * FROM /pos WHERE NOT (status like 'active' or pkid like '11' or pkid like '1') ");
results = (SelectResults) q.execute();
if (results.size() != 8) {
fail("Unexpected result. expected :" + 8 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE status like '%' and pkid like '1%' ");
results = (SelectResults) q.execute();
if (results.size() != 11) {
fail("Unexpected result. expected :" + 11 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE pkid like '_' ");
results = (SelectResults) q.execute();
if (results.size() != 10) {
fail("Unexpected result. expected :" + 10 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE pkid like '.*tive' ");
results = (SelectResults) q.execute();
if (results.size() != 0) {
fail("Unexpected result. expected :" + 0 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos WHERE pkid like '1+' ");
results = (SelectResults) q.execute();
if (results.size() != 0) {
fail("Unexpected result. expected :" + 0 + " found : " + results.size());
}
}
@Test
public void testLikePredicateOnNullValues() throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
char ch = 'd';
String base = "abc";
for (int i = 1; i < 6; i++) {
Portfolio pf = new Portfolio(i);
pf.pkid = "abc";
if (i % 2 == 0) {
pf.pkid = null;
pf.status = "like";
} else if (i == 3) {
pf.status = null;
}
region.put(new Integer(i), pf);
}
QueryService qs = cache.getQueryService();
Query q;
SelectResults results;
String query[] = new String[] {"SELECT distinct * FROM /pos ps WHERE ps.pkid like '%b%'",
"SELECT * FROM /pos ps WHERE ps.pkid like '%b%' and ps.status like '%ctiv%'",
"SELECT * FROM /pos ps WHERE ps.pkid like '_bc'",
"SELECT pkid FROM /pos ps WHERE ps.pkid like 'abc%'",
"SELECT pkid FROM /pos ps WHERE ps.pkid = 'abc'",
"SELECT pkid FROM /pos ps WHERE ps.pkid like '%b%' and ps.status = 'like'",
"SELECT pkid FROM /pos ps WHERE ps.pkid like '%b%' and ps.status like '%ike'",
"SELECT pkid FROM /pos ps WHERE ps.pkid like '%b%' and ps.pkid like '_bc'",
"SELECT pkid FROM /pos ps WHERE ps.pkid like 'ml%' or ps.status = 'like'",};
// null check
for (int i = 0; i < query.length; i++) {
q = qs.newQuery(query[i]);
results = (SelectResults) q.execute(); // No NPE.
}
// validate results
q = qs.newQuery(query[0]);
results = (SelectResults) q.execute();
assertEquals("Result size is not as expected", 3, results.size());
q = qs.newQuery(query[1]);
results = (SelectResults) q.execute();
assertEquals("Result size is not as expected", 2, results.size());
// Create index.
qs.createIndex("pkid", IndexType.FUNCTIONAL, "ps.pkid", "/pos ps");
qs.createIndex("status", IndexType.FUNCTIONAL, "ps.status", "/pos ps");
for (int i = 0; i < query.length; i++) {
q = qs.newQuery(query[i]);
results = (SelectResults) q.execute(); // No NPE.
}
ResultsBag bag = new ResultsBag(null);
for (int i = 1; i < 6; ++i) {
bag.add(region.get(new Integer(i)));
}
}
/**
* Tests with combination of % and _ and NOT with LIKE Supported from 6.6
*
* @throws Exception
*/
private void enhancedNotLike(boolean useBindPrms, boolean useIndex) throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
String[] values = new String[] {"active", "inactive",};
String[] likePredicates1 = new String[] {"active", "act%%ve", "a%e", "activ_", "ac_ive",
"act__e", "a%iv_", "a_tiv%", "ac%",};
String[] likePredicates2 = new String[] {"%ctiv%", "%c%iv%", "%ctive", "%%ti%", "%",};
String[] likePredicates3 = new String[] {
"___ctive", "___c_iv_", "___ctiv%", "____tive",};
for (int i = 0; i < values.length; i++) {
region.put(new Integer(i), values[i]);
}
QueryService qs = cache.getQueryService();
if (useIndex) {
qs.createIndex("p", IndexType.FUNCTIONAL, "p", "/pos.values p");
}
Query q;
SelectResults results;
for (int i = 0; i < likePredicates1.length; i++) {
if (!useBindPrms) {
q = qs.newQuery("select p from /pos p where NOT (p like '" + likePredicates1[i] + "')");
results = (SelectResults) q.execute();
} else {
q = qs.newQuery("select p from /pos p where NOT (p like $1)");
results = (SelectResults) q.execute(new Object[] {likePredicates1[i]});
}
List r = results.asList();
if (r.size() != 1 || !r.get(0).equals(values[1])) {
fail("Unexprected result. expected :" + values[1] + " for the like predicate1: "
+ likePredicates1[i] + " found : "
+ (r.size() == 1 ? r.get(0) : "Result size not equal to 1"));
}
}
for (int i = 0; i < likePredicates2.length; i++) {
if (!useBindPrms) {
q = qs.newQuery("select p from /pos p where NOT (p like '" + likePredicates2[i] + "')");
results = (SelectResults) q.execute();
} else {
q = qs.newQuery("select p from /pos p where NOT (p like $1)");
results = (SelectResults) q.execute(new Object[] {likePredicates2[i]});
}
List r = results.asList();
if (r.size() != 0) {
fail("Unexprected result. expected nothing for the like predicate2: " + likePredicates2[i]
+ " found : " + (r.size() != 0 ? r.get(0) + " Result size not equal to 0" : ""));
}
}
for (int i = 0; i < likePredicates3.length; i++) {
if (!useBindPrms) {
q = qs.newQuery("select p from /pos p where NOT (p like '" + likePredicates3[i] + "')");
results = (SelectResults) q.execute();
} else {
q = qs.newQuery("select p from /pos p where NOT (p like $1)");
results = (SelectResults) q.execute(new Object[] {likePredicates3[i]});
}
List r = results.asList();
if (r.size() != 1 || !r.get(0).equals(values[0])) {
fail("Unexprected result. expected :" + values[0] + " for the like predicate3: "
+ likePredicates3[i] + " found : "
+ (r.size() == 1 ? r.get(0) : "Result size not equal to 1"));
}
}
}
@Test
public void testEnhancedNotLikeWithoutBindPrms() throws Exception {
enhancedNotLike(false, false);
}
@Test
public void testEnhancedNotLikeWithBindPrms() throws Exception {
enhancedNotLike(true, false);
}
@Test
public void testEnhancedNotLikeWithoutBindPrmsWithIndex() throws Exception {
enhancedNotLike(true, true);
}
@Test
public void testEnhancedNotLike2WithoutBindPrms() throws Exception {
enhancedNotLike2(false, false);
}
@Test
public void testEnhancedNotLike2WithBindPrms() throws Exception {
enhancedNotLike2(true, false);
}
@Test
public void testEnhancedNotLike2WithoutBindPrmsWithIndex() throws Exception {
enhancedNotLike2(false, true);
}
private void enhancedNotLike2(boolean useBindPrms, boolean useIndex) throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
String[] values = new String[] {"ac\\tive", "X\\\\X", "Y%Y", "Z\\%Z", "pass\\ive",
"inact\\%+ive", "1inact\\_+ive",};
String[] likePredicates = new String[] {"ac\\\\tive", "ac\\\\%", "ac_tive", "Y\\%Y", "X__X",
"X%X", "Z\\\\\\%Z", "inact\\\\%+ive", "1inact\\\\_+ive",};
String[] result = new String[] {"ac\\tive", "ac\\tive", "ac\\tive", "Y%Y", "X\\\\X", "X\\\\X",
"Z\\%Z", "inact\\%+ive", "1inact\\_+ive",};
for (int i = 0; i < values.length; i++) {
region.put(new Integer(i), values[i]);
}
QueryService qs = cache.getQueryService();
Query q;
SelectResults results;
if (useIndex) {
qs.createIndex("p", IndexType.FUNCTIONAL, "p", "/pos.values p");
}
for (int i = 0; i < likePredicates.length; i++) {
if (!useBindPrms) {
q = qs
.newQuery("select p from /pos.values p where NOT (p like '" + likePredicates[i] + "')");
results = (SelectResults) q.execute();
} else {
q = qs.newQuery("select p from /pos.values p where NOT (p like $1)");
results = (SelectResults) q.execute(new Object[] {likePredicates[i]});
}
List r = results.asList();
if (r.size() != 6) {
fail("Unexpected result size: " + r.size() + " for query: " + q.getQueryString());
}
}
}
/**
* Query with index on other fields.
*
* @throws Exception
*/
@Test
public void testNotLikeWithOtherIndexedField2() throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
int size = 10;
String base = "abc";
for (int i = 1; i <= size; i++) {
Portfolio pf = new Portfolio(i);
pf.pkid = "1";
if ((i % 4) == 0) {
pf.status = base;
} else if ((i <= 2)) {
pf.pkid = "2";
}
region.put(new Integer(i), pf);
}
QueryService qs = cache.getQueryService();
Query q;
SelectResults results;
SelectResults expectedResults;
int expectedResultSize = 2;
q = qs.newQuery(" SELECT * FROM /pos ps WHERE NOT (ps.status like '%b%')");
results = (SelectResults) q.execute();
if (results.size() != expectedResultSize * 4) {
fail("Unexpected result. expected :" + expectedResultSize * 4 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos ps WHERE NOT (ps.status like '%b%' or ps.pkid = '2')");
results = (SelectResults) q.execute();
if (results.size() != (expectedResultSize * 3)) {
fail("Unexpected result. expected :" + (expectedResultSize * 3) + " found : "
+ results.size());
}
q = qs.newQuery(" SELECT * FROM /pos ps WHERE NOT (ps.status like '%b%' or ps.ID > 0 )");
results = (SelectResults) q.execute();
if (results.size() != 0) {
fail("Unexpected result. expected :" + 0 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos ps WHERE NOT (ps.status like '%b%') or ps.ID > 4 ");
results = (SelectResults) q.execute();
if (results.size() != 9) {
fail("Unexpected result. expected :" + 9 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos ps WHERE NOT (ps.status like '%b%' and ps.ID > 3 )");
results = (SelectResults) q.execute();
if (results.size() != 8) {
fail("Unexpected result. expected :" + 5 + " found : " + results.size());
}
// Query to be compared with indexed results.
q = qs.newQuery(" SELECT * FROM /pos ps WHERE NOT (ps.status like '%b%' and ps.pkid = '1' )");
expectedResults = (SelectResults) q.execute();
if (expectedResults.size() != 8) {
fail("Unexpected result. expected :" + size + " found : " + expectedResults.size());
}
// Create Index
qs.createIndex("pkid", IndexType.FUNCTIONAL, "ps.pkid", "/pos ps");
qs.createIndex("status", IndexType.FUNCTIONAL, "ps.status", "/pos ps");
qs.createIndex("id", IndexType.FUNCTIONAL, "ps.ID", "/pos ps");
results = (SelectResults) q.execute();
SelectResults rs[][] = new SelectResults[][] {{results, expectedResults}};
if (results.size() != expectedResults.size()) {
fail("Unexpected result. expected :" + expectedResults.size() + " found : " + results.size());
}
// compare results.
CacheUtils.compareResultsOfWithAndWithoutIndex(rs, this);
q = qs.newQuery(" SELECT * FROM /pos ps WHERE NOT (ps.status like '%b%' and ps.ID > 3 )");
results = (SelectResults) q.execute();
if (results.size() != 8) {
fail("Unexpected result. expected :" + 5 + " found : " + results.size());
}
// Index will only be used in OR junctions if NOT is used with LIKE.
QueryObserver old = QueryObserverHolder.setInstance(new QueryObserverAdapter() {
private boolean indexCalled = false;
public void afterIndexLookup(Collection results) {
indexCalled = true;
}
public void endQuery() {
assertTrue(indexCalled);
}
});
q = qs.newQuery(" SELECT * FROM /pos ps WHERE NOT (ps.status like '_b_' or ps.pkid = '2')");
results = (SelectResults) q.execute();
if (results.size() != (expectedResultSize * 3)) {
fail("Unexpected result. expected :" + (expectedResultSize * 3) + " found : "
+ results.size());
}
q = qs.newQuery(" SELECT * FROM /pos ps WHERE NOT (ps.status like '%b%' or ps.ID > 0 )");
results = (SelectResults) q.execute();
if (results.size() != 0) {
fail("Unexpected result. expected :" + 0 + " found : " + results.size());
}
q = qs.newQuery(" SELECT * FROM /pos ps WHERE NOT (ps.status like '%b%') or ps.ID > 4");
results = (SelectResults) q.execute();
if (results.size() != 9) {
fail("Unexpected result. expected :" + 9 + " found : " + results.size());
}
QueryObserverHolder.setInstance(old);
}
@Test
public void testLikeWithNewLine() throws Exception {
likeWithNewLine(false);
}
@Test
public void testLikeWithNewLineWithIndex() throws Exception {
likeWithNewLine(true);
}
private void likeWithNewLine(boolean useIndex) throws Exception {
// setup
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
region.put("abc", "abc\ndef");
region.put("opq", "\nopq");
region.put("mns", "opq\n");
QueryService qs = cache.getQueryService();
if (useIndex) {
qs.createIndex("p", IndexType.FUNCTIONAL, "p", "/pos p");
}
Query query = qs.newQuery("select * from /pos a where a like '%bc%'");
SelectResults sr = (SelectResults) query.execute();
assertEquals(sr.size(), 1);
query = qs.newQuery("select * from /pos a where a like '%bc\n%'");
sr = (SelectResults) query.execute();
assertEquals(sr.size(), 1);
query = qs.newQuery("select * from /pos a where a like '%bcde%'");
sr = (SelectResults) query.execute();
assertEquals(sr.size(), 0); // no results as newline is required
// whitespace
query = qs.newQuery("select * from /pos a where a like '%bc\nde%'");
sr = (SelectResults) query.execute();
assertEquals(sr.size(), 1);
query = qs.newQuery("select * from /pos a where a like '%de%'");
sr = (SelectResults) query.execute();
assertEquals(sr.size(), 1);
query = qs.newQuery("select * from /pos a where a like '%\nde%'");
sr = (SelectResults) query.execute();
assertEquals(sr.size(), 1);
query = qs.newQuery("select * from /pos a where a like '%bc%de%'");
sr = (SelectResults) query.execute();
assertEquals(sr.size(), 1);
query = qs.newQuery("select * from /pos a where a like '%zyx%'");
sr = (SelectResults) query.execute();
assertEquals(sr.size(), 0);
query = qs.newQuery("select * from /pos a where a like '%\n%'");
sr = (SelectResults) query.execute();
assertEquals(sr.size(), 3); // find newline anywhere in string
query = qs.newQuery("select * from /pos a where a like '\n%'");
sr = (SelectResults) query.execute();
assertEquals(sr.size(), 1);
query = qs.newQuery("select * from /pos a where a like '%\n'");
sr = (SelectResults) query.execute();
assertEquals(sr.size(), 1);
}
/**
* Test to verify that query executes using index even when the index is being removed during the
* execution
*
* @throws Exception
*/
@Test
public void testRemoveIndexDuringQueryinRR() throws Exception {
removeIndexDuringQuery(false);
}
@Test
public void testRemoveIndexDuringQueryinPR() throws Exception {
removeIndexDuringQuery(true);
}
private void removeIndexDuringQuery(boolean isPr) throws Exception {
String regionName = "exampleRegion";
String name = "/" + regionName;
Cache cache = CacheUtils.getCache();
Region r1 = null;
if (isPr) {
r1 = cache.createRegionFactory(RegionShortcut.PARTITION).create(regionName);
} else {
r1 = cache.createRegionFactory(RegionShortcut.REPLICATE).create(regionName);
}
QueryService qs = cache.getQueryService();
qs.createIndex("status", "status", name);
assertEquals(cache.getQueryService().getIndexes().size(), 1);
QueryObserver old = QueryObserverHolder.setInstance(new QueryObserverAdapter() {
private boolean indexCalled = false;
public void afterIndexLookup(Collection results) {
indexCalled = true;
}
public void endQuery() {
assertTrue(indexCalled);
}
});
// set the test hook
IndexManager.testHook = new LikeQueryIndexTestHook();
for (int i = 0; i < 10; i++) {
r1.put("key-" + i, new Portfolio(i));
}
SelectResults rs[][] = new SelectResults[1][2];
String query = "select distinct * from " + name + " where status like 'act%'";
rs[0][0] = (SelectResults) cache.getQueryService().newQuery(query).execute();
assertEquals(5, rs[0][0].size());
// wait for remove to complete
ThreadUtils.join(LikeQueryIndexTestHook.th, 60 * 1000);
// The index should have been removed by now
assertEquals(0, cache.getQueryService().getIndexes().size());
// remove the test hook
IndexManager.testHook = null;
// create the same index again and execute the query
qs.createIndex("status", "status", name);
rs[0][1] = (SelectResults) cache.getQueryService().newQuery(query).execute();
assertEquals(5, rs[0][1].size());
CacheUtils.compareResultsOfWithAndWithoutIndex(rs);
QueryObserverHolder.setInstance(old);
}
@Test
public void testQueryExecutionMultipleTimes() throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
Region region2 = cache.createRegion("pos2", regionAttributes);
// Create Index
Index i1 =
cache.getQueryService().createIndex("status", IndexType.FUNCTIONAL, "ps.status", "/pos ps");
Index i2 = cache.getQueryService().createIndex("description", IndexType.FUNCTIONAL,
"ps.description", "/pos ps");
Index i3 = cache.getQueryService().createIndex("description2", IndexType.FUNCTIONAL,
"ps2.description", "/pos2 ps2");
for (int i = 0; i < 10; i++) {
Portfolio p = new Portfolio(i);
region.put("key-" + i, p);
region2.put("key-" + i, p);
}
executeQueryMultipleTimes("SELECT distinct * FROM /pos ps WHERE ps.status like 'in%ve'",
false);
assertEquals(2, i1.getStatistics().getTotalUses());
assertEquals(0, i2.getStatistics().getTotalUses());
assertEquals(0, i3.getStatistics().getTotalUses());
executeQueryMultipleTimes(
"SELECT distinct * FROM /pos ps WHERE ps.status like 'in%ve' or ps.description like 'X%X'",
false);
assertEquals(4, i1.getStatistics().getTotalUses());
assertEquals(2, i2.getStatistics().getTotalUses());
assertEquals(0, i3.getStatistics().getTotalUses());
executeQueryMultipleTimes(
"SELECT distinct * FROM /pos ps WHERE ps.ID >= 0 and ps.status like 'in%ve'", false);
assertEquals(6, i1.getStatistics().getTotalUses());
assertEquals(2, i2.getStatistics().getTotalUses());
assertEquals(0, i3.getStatistics().getTotalUses());
executeQueryMultipleTimes(
"SELECT distinct * FROM /pos ps1, /pos2 ps2 WHERE ps1.status like 'in%ve' or ps2.description like 'X%X'",
false);
assertEquals(8, i1.getStatistics().getTotalUses());
assertEquals(2, i2.getStatistics().getTotalUses());
assertEquals(2, i3.getStatistics().getTotalUses());
}
@Test
public void testQueryExecutionMultipleTimesWithBindParams() throws Exception {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
Region region2 = cache.createRegion("pos2", regionAttributes);
// Create Index
Index i1 =
cache.getQueryService().createIndex("status", IndexType.FUNCTIONAL, "ps.status", "/pos ps");
Index i2 = cache.getQueryService().createIndex("description", IndexType.FUNCTIONAL,
"ps.description", "/pos ps");
Index i3 = cache.getQueryService().createIndex("description2", IndexType.FUNCTIONAL,
"ps2.description", "/pos2 ps2");
for (int i = 0; i < 10; i++) {
Portfolio p = new Portfolio(i);
region.put("key-" + i, p);
region2.put("key-" + i, p);
}
executeQueryMultipleTimes("SELECT distinct * FROM /pos ps WHERE ps.status like $1", true);
assertEquals(2, i1.getStatistics().getTotalUses());
assertEquals(0, i2.getStatistics().getTotalUses());
assertEquals(0, i3.getStatistics().getTotalUses());
executeQueryMultipleTimes(
"SELECT distinct * FROM /pos ps WHERE ps.status like $1 or ps.description like $2", true);
assertEquals(4, i1.getStatistics().getTotalUses());
assertEquals(2, i2.getStatistics().getTotalUses());
assertEquals(0, i3.getStatistics().getTotalUses());
executeQueryMultipleTimes(
"SELECT distinct * FROM /pos ps WHERE ps.ID >= 0 and ps.status like $1", true);
assertEquals(6, i1.getStatistics().getTotalUses());
assertEquals(2, i2.getStatistics().getTotalUses());
assertEquals(0, i3.getStatistics().getTotalUses());
executeQueryMultipleTimes(
"SELECT distinct * FROM /pos ps1, /pos2 ps2 WHERE ps1.status like $1 or ps2.description like $2",
true);
assertEquals(8, i1.getStatistics().getTotalUses());
assertEquals(2, i2.getStatistics().getTotalUses());
assertEquals(2, i3.getStatistics().getTotalUses());
}
private void executeQueryMultipleTimes(String queryStr, boolean useBindParam) throws Exception {
QueryService qs = CacheUtils.getCache().getQueryService();
Region region = CacheUtils.getCache().getRegion("pos");
Query q = qs.newQuery(queryStr);
SelectResults results;
SelectResults expectedResults;
if (useBindParam) {
results = (SelectResults) q.execute(new Object[] {"in%ve", "X%X"});
} else {
results = (SelectResults) q.execute();
}
// Execute the query again to verify if it is still using
// the index
if (useBindParam) {
results = (SelectResults) q.execute(new Object[] {"in%ve", "X%X"});
} else {
results = (SelectResults) q.execute();
}
}
@Test
public void testInvalidQuery() throws Exception {
executeInvalidQueryWithLike(false);
}
@Test
public void testInvalidQueryWithBindParams() throws Exception {
executeInvalidQueryWithLike(true);
}
private void executeInvalidQueryWithLike(boolean useBindParam) {
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
for (int i = 0; i < 10; i++) {
region.put("key-" + i, new Portfolio(i));
}
region.put("key-" + 11, new PortfolioModifiedStatus(11));
QueryService qs = cache.getQueryService();
Query[] q = new Query[4];
SelectResults[][] results = new SelectResults[2][2];
String predicate = "";
String predicate2 = "";
if (useBindParam) {
predicate = "$1";
predicate2 = "$1";
} else {
predicate = " '%nactive'";
predicate2 = " 'inactive'";
}
boolean exceptionThrown = false;
q[0] = qs.newQuery("SELECT distinct * FROM /pos ps WHERE ps.status like " + predicate);
q[1] = qs.newQuery("SELECT distinct * FROM /pos ps WHERE ps.status = " + predicate2);
q[2] =
qs.newQuery("SELECT distinct * FROM /pos ps WHERE NOT (ps.status like " + predicate + ")");
q[3] =
qs.newQuery("SELECT distinct * FROM /pos ps WHERE NOT (ps.status = " + predicate2 + ")");
try {
if (useBindParam) {
results[0][0] = (SelectResults) q[0].execute(new Object[] {"%nactive"});
results[0][1] = (SelectResults) q[1].execute(new Object[] {"inactive"});
results[1][0] = (SelectResults) q[2].execute(new Object[] {"%nactive"});
results[1][1] = (SelectResults) q[3].execute(new Object[] {"inactive"});
} else {
results[0][0] = (SelectResults) q[0].execute();
results[0][1] = (SelectResults) q[1].execute();
results[1][0] = (SelectResults) q[2].execute();
results[1][1] = (SelectResults) q[3].execute();
}
} catch (Exception e) {
exceptionThrown = true;
fail("Query execution failed " + e);
}
assertTrue(results[0][0].size() > 0);
assertTrue(CacheUtils.compareResultsOfWithAndWithoutIndex(results));
assertFalse("Query exception should not have been thrown", exceptionThrown);
try {
qs.createIndex("IDindex", "ps.ID", "/pos ps");
} catch (Exception e) {
fail("Index creation failed");
}
exceptionThrown = false;
try {
if (useBindParam) {
results[0][0] = (SelectResults) q[0].execute(new Object[] {"%nactive"});
results[0][1] = (SelectResults) q[1].execute(new Object[] {"inactive"});
results[1][0] = (SelectResults) q[2].execute(new Object[] {"%nactive"});
results[1][1] = (SelectResults) q[3].execute(new Object[] {"inactive"});
} else {
results[0][0] = (SelectResults) q[0].execute();
results[0][1] = (SelectResults) q[1].execute();
results[1][0] = (SelectResults) q[2].execute();
results[1][1] = (SelectResults) q[3].execute();
}
} catch (Exception e) {
exceptionThrown = true;
fail("Query execution failed " + e);
}
assertTrue(results[0][0].size() > 0);
assertTrue(CacheUtils.compareResultsOfWithAndWithoutIndex(results));
assertFalse("Query exception should not have been thrown", exceptionThrown);
}
@Test
public void testUndefined() throws Exception {
String[] queries = {"select * from /pos where status.toUpperCase() like '%ACT%'",
"select * from /pos where status.toUpperCase() like 'ACT%'",
"select * from /pos where status.toUpperCase() like '%IVE'",
"select * from /pos where status.toUpperCase() like 'ACT_VE'",
"select * from /pos where status like '%CT%'",
"select * from /pos where status like 'ACT%'",
"select * from /pos where status like 'ACT_VE'",
"select * from /pos where position1.secId like '%B%'",
"select * from /pos where position1.secId like 'IB%'",
"select * from /pos where position1.secId like 'I_M'",
"select * from /pos p, p.positions.values pos where pos.secId like '%B%'",
"select * from /pos p, p.positions.values pos where pos.secId like 'IB%'",
"select * from /pos p, p.positions.values pos where pos.secId like 'I_M'",};
Cache cache = CacheUtils.getCache();
AttributesFactory attributesFactory = new AttributesFactory();
RegionAttributes regionAttributes = attributesFactory.create();
Region region = cache.createRegion("pos", regionAttributes);
for (int i = 0; i < 10; i++) {
Portfolio p = new Portfolio(i);
if (i % 2 == 0) {
p.status = null;
p.position1 = null;
p.positions = null;
} else {
p.position1 = new Position("IBM", 0);
p.status = "ACTIVE";
}
region.put("key-" + i, p);
}
SelectResults[][] res = new SelectResults[queries.length][2];
QueryService qs = CacheUtils.getCache().getQueryService();
SelectResults results = null;
for (int i = 0; i < queries.length; i++) {
Query q = qs.newQuery(queries[i]);
try {
results = (SelectResults) q.execute();
res[i][0] = results;
} catch (Exception e) {
fail("Query execution failed for query " + queries[i] + " with exception: " + e);
}
if (i < 10) {
assertEquals("Query " + queries[i] + " should return 5 results and not " + results.size(),
5, results.size());
} else {
assertEquals("Query " + queries[i] + " should return 1 results and not " + results.size(),
1, results.size());
}
}
try {
qs.createIndex("status", "status", region.getFullPath());
qs.createIndex("position1secId", "pos1.secId", region.getFullPath());
qs.createIndex("secId", "pos.secId", region.getFullPath() + " pos, pos.secId");
} catch (Exception e) {
fail("Index creation failed");
}
for (int i = 0; i < queries.length; i++) {
Query q = qs.newQuery(queries[i]);
try {
results = (SelectResults) q.execute();
res[i][1] = results;
} catch (Exception e) {
fail("Query execution failed for query " + queries[i] + " with exception: " + e);
}
if (i < 10) {
assertEquals("Query " + queries[i] + " should return 5 results and not " + results.size(),
5, results.size());
} else {
assertEquals("Query " + queries[i] + " should return 1 results and not " + results.size(),
1, results.size());
}
}
CacheUtils.compareResultsOfWithAndWithoutIndex(res);
}
private static class LikeQueryIndexTestHook implements TestHook {
public static Thread th;
@Override
public void hook(int spot) throws RuntimeException {
if (spot == 12) {
Runnable r = new Runnable() {
@Override
public void run() {
Cache cache = CacheUtils.getCache();
cache.getLogger().fine("Removing Index in LikeQueryIndexTestHook");
QueryService qs = cache.getQueryService();
qs.removeIndex(qs.getIndex(cache.getRegion("exampleRegion"), "status"));
}
};
th = new Thread(r);
th.start();
}
}
}
private class PortfolioModifiedStatus {
public PortfolioModifiedStatus(int id) {
String[] status = {"inactive", "active"};
}
}
}