001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.test;
032:
033: import java.sql.Connection;
034: import java.sql.PreparedStatement;
035: import java.sql.ResultSet;
036: import java.sql.Statement;
037:
038: import junit.framework.TestCase;
039: import junit.framework.TestResult;
040:
041: /**
042: * HSQLDB TestLikePredicate Junit test case. <p>
043: *
044: * @author boucherb@users
045: * @version 1.7.2
046: * @since 1.7.2
047: */
048: public class TestLikePredicateOptimizations extends TestBase {
049:
050: public TestLikePredicateOptimizations(String name) {
051: super (name);
052: }
053:
054: /* Implements the TestLikePredicate test */
055: public void test() throws Exception {
056:
057: Connection conn = newConnection();
058: Statement stmt = conn.createStatement();
059: PreparedStatement pstmt;
060: ResultSet rs;
061: String sql;
062: int expectedCount;
063: int actualCount;
064:
065: stmt.execute("drop table test if exists");
066:
067: sql = "create table test(name varchar(255))";
068:
069: stmt.execute(sql);
070:
071: sql = "insert into test values(?)";
072: pstmt = conn.prepareStatement(sql);
073:
074: for (int i = 0; i < 10000; i++) {
075: pstmt.setString(1, "name" + i);
076: pstmt.addBatch();
077: }
078:
079: pstmt.executeBatch();
080:
081: sql = "select count(*) from test where name = null";
082: rs = stmt.executeQuery(sql);
083:
084: rs.next();
085:
086: expectedCount = rs.getInt(1);
087: sql = "select count(*) from test where name like null";
088: pstmt = conn.prepareStatement(sql);
089: rs = pstmt.executeQuery();
090:
091: rs.next();
092:
093: actualCount = rs.getInt(1);
094:
095: assertEquals("\"" + sql + "\"", expectedCount, actualCount);
096:
097: // --
098: sql = "select count(*) from test where name = ''";
099: rs = stmt.executeQuery(sql);
100:
101: rs.next();
102:
103: expectedCount = rs.getInt(1);
104: sql = "select count(*) from test where name like ''";
105: pstmt = conn.prepareStatement(sql);
106: rs = pstmt.executeQuery();
107:
108: rs.next();
109:
110: actualCount = rs.getInt(1);
111:
112: assertEquals("\"" + sql + "\"", expectedCount, actualCount);
113:
114: // --
115: sql = "select count(*) from test where name is not null";
116: rs = stmt.executeQuery(sql);
117:
118: rs.next();
119:
120: expectedCount = rs.getInt(1);
121: sql = "select count(*) from test where name like '%'";
122: pstmt = conn.prepareStatement(sql);
123: rs = pstmt.executeQuery();
124:
125: rs.next();
126:
127: actualCount = rs.getInt(1);
128:
129: assertEquals("\"" + sql + "\"", expectedCount, actualCount);
130:
131: // --
132: sql = "select count(*) from test where left(name, 6) = 'name44'";
133: rs = stmt.executeQuery(sql);
134:
135: rs.next();
136:
137: expectedCount = rs.getInt(1);
138: sql = "select count(*) from test where name like 'name44%'";
139: pstmt = conn.prepareStatement(sql);
140: rs = pstmt.executeQuery();
141:
142: rs.next();
143:
144: actualCount = rs.getInt(1);
145:
146: assertEquals("\"" + sql + "\"", expectedCount, actualCount);
147:
148: // --
149: sql = "select count(*) from test where left(name,5) = 'name4' and right(name,1) = 5";
150: rs = stmt.executeQuery(sql);
151:
152: rs.next();
153:
154: expectedCount = rs.getInt(1);
155: sql = "select count(*) from test where name like 'name4%5'";
156: pstmt = conn.prepareStatement(sql);
157: rs = pstmt.executeQuery();
158:
159: rs.next();
160:
161: actualCount = rs.getInt(1);
162:
163: assertEquals("\"" + sql + "\"", expectedCount, actualCount);
164:
165: // --
166: stmt.execute("drop table test1 if exists");
167:
168: sql = "CREATE TABLE test1 (col VARCHAR(30))";
169: pstmt = conn.prepareStatement(sql);
170:
171: pstmt.execute();
172:
173: sql = "INSERT INTO test1 (col) VALUES ('one')";
174: pstmt = conn.prepareStatement(sql);
175:
176: pstmt.execute();
177:
178: sql = "SELECT * FROM test1 WHERE ( col LIKE ? )";
179: pstmt = conn.prepareStatement(sql);
180:
181: pstmt.setString(1, "one");
182:
183: rs = pstmt.executeQuery();
184:
185: rs.next();
186:
187: String presult = rs.getString("COL");
188:
189: sql = "SELECT * FROM test1 WHERE ( col LIKE 'one' )";
190: pstmt = conn.prepareStatement(sql);
191: rs = pstmt.executeQuery();
192:
193: rs.next();
194:
195: String result = rs.getString("COL");
196:
197: assertEquals("\"" + sql + "\"", result, presult);
198: }
199:
200: /* Runs TestLikePredicate test from the command line*/
201: public static void main(String[] args) throws Exception {
202:
203: TestResult result;
204: TestCase test;
205: java.util.Enumeration failures;
206: int count;
207:
208: result = new TestResult();
209: test = new TestLikePredicateOptimizations("test");
210:
211: test.run(result);
212:
213: count = result.failureCount();
214:
215: System.out
216: .println("TestLikePredicateOptimizations failure count: "
217: + count);
218:
219: failures = result.failures();
220:
221: while (failures.hasMoreElements()) {
222: System.out.println(failures.nextElement());
223: }
224: }
225: }
|