com.gemstone.gemfire.cache.query.functional.LikePredicateTest Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of gemfire-junit Show documentation
Show all versions of gemfire-junit Show documentation
SnappyData store based off Pivotal GemFireXD
/*
* Copyright (c) 2010-2015 Pivotal Software, Inc. All rights reserved.
*
* Licensed 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. See accompanying
* LICENSE file.
*/
/*
* InOperatorTest.java
*
* Created on March 24, 2005, 5:08 PM
*/
package com.gemstone.gemfire.cache.query.functional;
import junit.framework.*;
import com.gemstone.gemfire.cache.query.*;
import com.gemstone.gemfire.cache.query.data.Portfolio;
import com.gemstone.gemfire.cache.query.internal.ResultsCollectionWrapper;
import com.gemstone.gemfire.cache.query.internal.ResultsBag;
import com.gemstone.gemfire.cache.query.internal.index.IndexManager;
import com.gemstone.gemfire.cache.query.internal.index.IndexManager.TestHook;
import com.gemstone.gemfire.cache.query.internal.types.ObjectTypeImpl;
import com.gemstone.gemfire.cache.query.internal.QueryObserver;
import com.gemstone.gemfire.cache.query.internal.QueryObserverAdapter;
import com.gemstone.gemfire.cache.query.internal.QueryObserverHolder;
import java.util.Collection;
import com.gemstone.gemfire.cache.*;
import io.snappydata.test.dunit.DistributedTestBase;
import java.util.*;
/**
*
* @author asif
*/
public class LikePredicateTest extends TestCase {
public LikePredicateTest(String testName) {
super(testName);
}
public static void main(java.lang.String[] args) {
junit.textui.TestRunner.run(suite());
}
protected void setUp() throws Exception {
CacheUtils.startCache();
}
protected void tearDown() throws Exception {
CacheUtils.closeCache();
}
public static Test suite() {
TestSuite suite = new TestSuite(LikePredicateTest.class);
return suite;
}
public void testLikePercentageTerminated_1_withoutBindParam() throws Exception {
likePercentageTerminated_1(false);
}
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);
}
public void testLikePercentageTerminated_2_withoutParams() throws Exception {
likePercentageTerminated_2(false);
}
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);
}
public void testLikePercentageTerminated_3_withoutBindPrms() throws Exception {
likePercentageTerminated_3(false);
}
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);
}
public void testLikePercentageTerminated_4_withoutBindPrms() throws Exception {
likePercentageTerminated_4(false);
}
public void testLikePercentageTerminated_4_withBindPrms() throws Exception {
likePercentageTerminated_4(true);
}
/**
* Tests a simple % terminated like predicate with an AND condition
* @throws Exception
*/
public 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);
}
public void testLikePercentageTerminated_5_withoutBindPrms() throws Exception {
likePercentageTerminated_5(false);
}
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);
}
public void testEqualityForm_1_withoutBindParams() throws Exception {
equalityForm_1(false);
}
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);
}
public void testEqualityForm_2_withoutBindPrms() throws Exception {
equalityForm_2(false);
}
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);
}
public void testRegexMetaCharWithoutBindPrms() throws Exception {
regexMetaChar(false);
}
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",
};
// 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"));
}
}
}
public void testEnhancedLikeWithoutBindPrms() throws Exception {
enhancedLike(false);
}
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"));
}
}
}
public void testEnhancedLike2WithoutBindPrms() throws Exception {
enhancedLike2(false);
}
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
*/
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
*/
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);
}
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());
}
}
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"));
}
}
}
public void testEnhancedNotLikeWithoutBindPrms() throws Exception {
enhancedNotLike(false,false);
}
public void testEnhancedNotLikeWithBindPrms() throws Exception {
enhancedNotLike(true,false);
}
public void testEnhancedNotLikeWithoutBindPrmsWithIndex() throws Exception {
enhancedNotLike(true,true);
}
public void testEnhancedNotLike2WithoutBindPrms() throws Exception {
enhancedNotLike2(false, false);
}
public void testEnhancedNotLike2WithBindPrms() throws Exception {
enhancedNotLike2(true, false);
}
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
*/
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);
}
public void testLikeWithNewLine() throws Exception {
likeWithNewLine(false);
}
public void testLikeWithNewLineWithIndex() throws Exception {
likeWithNewLine(true);
}
public 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();
Assert.assertEquals(sr.size(), 1);
query = qs.newQuery("select * from /pos a where a like '%bc\n%'");
sr = (SelectResults) query.execute();
Assert.assertEquals(sr.size(), 1);
query = qs.newQuery("select * from /pos a where a like '%bcde%'");
sr = (SelectResults) query.execute();
Assert.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();
Assert.assertEquals(sr.size(), 1);
query = qs.newQuery("select * from /pos a where a like '%de%'");
sr = (SelectResults) query.execute();
Assert.assertEquals(sr.size(), 1);
query = qs.newQuery("select * from /pos a where a like '%\nde%'");
sr = (SelectResults) query.execute();
Assert.assertEquals(sr.size(), 1);
query = qs.newQuery("select * from /pos a where a like '%bc%de%'");
sr = (SelectResults) query.execute();
Assert.assertEquals(sr.size(), 1);
query = qs.newQuery("select * from /pos a where a like '%zyx%'");
sr = (SelectResults) query.execute();
Assert.assertEquals(sr.size(), 0);
query = qs.newQuery("select * from /pos a where a like '%\n%'");
sr = (SelectResults) query.execute();
Assert.assertEquals(sr.size(), 3); // find newline anywhere in string
query = qs.newQuery("select * from /pos a where a like '\n%'");
sr = (SelectResults) query.execute();
Assert.assertEquals(sr.size(), 1);
query = qs.newQuery("select * from /pos a where a like '%\n'");
sr = (SelectResults) query.execute();
Assert.assertEquals(sr.size(), 1);
}
/**
* Test to verify that query executes using index even when the index is being
* removed during the execution
*
* @throws Exception
*/
public void testRemoveIndexDuringQueryinRR() throws Exception {
removeIndexDuringQuery(false);
}
public void testRemoveIndexDuringQueryinPR() throws Exception {
removeIndexDuringQuery(true);
}
public 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
DistributedTestBase.join(LikeQueryIndexTestHook.th, 60 * 1000, null);
// 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);
}
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();
}
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy