You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ja...@apache.org on 2016/09/12 18:45:12 UTC
phoenix git commit: PHOENIX-3046 NOT LIKE with wildcard unexpectedly
returns results (Kevin Liew)
Repository: phoenix
Updated Branches:
refs/heads/master c3cdb2afb -> 47a2b2c3e
PHOENIX-3046 NOT LIKE with wildcard unexpectedly returns results (Kevin Liew)
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/47a2b2c3
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/47a2b2c3
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/47a2b2c3
Branch: refs/heads/master
Commit: 47a2b2c3ec1fb7a5ae3fc42c79a2199ab5108f44
Parents: c3cdb2a
Author: James Taylor <ja...@apache.org>
Authored: Mon Sep 12 09:54:10 2016 -0700
Committer: James Taylor <ja...@apache.org>
Committed: Mon Sep 12 11:46:58 2016 -0700
----------------------------------------------------------------------
.../phoenix/end2end/LikeExpressionIT.java | 100 ++++++++++++++++++-
.../phoenix/compile/ExpressionCompiler.java | 24 ++---
2 files changed, 103 insertions(+), 21 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/47a2b2c3/phoenix-core/src/it/java/org/apache/phoenix/end2end/LikeExpressionIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/LikeExpressionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/LikeExpressionIT.java
index f97e1d7..2fcee45 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/LikeExpressionIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/LikeExpressionIT.java
@@ -96,16 +96,16 @@ public class LikeExpressionIT extends BaseHBaseManagedTimeTableReuseIT {
Connection conn = DriverManager.getConnection(getUrl());
String table = generateRandomString();
String ddl = "CREATE TABLE " + table
- + " (k1 VARCHAR, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1,k2))";
+ + " (k1 VARCHAR, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1,k2))";
conn.createStatement().execute(ddl);
conn.createStatement().execute("UPSERT INTO " + table + " VALUES('aa','bb')");
conn.createStatement().execute("UPSERT INTO " + table + " VALUES('ab','bc')");
conn.createStatement().execute("UPSERT INTO " + table + " VALUES(null,'cc')");
conn.createStatement().execute("UPSERT INTO " + table + " VALUES('dd',null)");
conn.commit();
-
+
ResultSet rs = conn.createStatement().executeQuery(
- "SELECT * FROM " + table + " WHERE k1 LIKE '%'");
+ "SELECT * FROM " + table + " WHERE k1 LIKE '%'");
assertTrue(rs.next());
assertEquals("aa", rs.getString(1));
assertEquals("bb", rs.getString(2));
@@ -116,7 +116,7 @@ public class LikeExpressionIT extends BaseHBaseManagedTimeTableReuseIT {
assertEquals("dd", rs.getString(1));
assertEquals(null, rs.getString(2));
assertFalse(rs.next());
-
+
rs = conn.createStatement().executeQuery("SELECT * FROM " + table + " WHERE k2 LIKE '%'");
assertTrue(rs.next());
assertEquals(null, rs.getString(1));
@@ -128,7 +128,25 @@ public class LikeExpressionIT extends BaseHBaseManagedTimeTableReuseIT {
assertEquals("ab", rs.getString(1));
assertEquals("bc", rs.getString(2));
assertFalse(rs.next());
-
+
+ rs = conn.createStatement().executeQuery("SELECT * FROM " + table + " WHERE k2 LIKE '%%'");
+ assertTrue(rs.next());
+ assertEquals(null, rs.getString(1));
+ assertEquals("cc", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("aa", rs.getString(1));
+ assertEquals("bb", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("ab", rs.getString(1));
+ assertEquals("bc", rs.getString(2));
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("SELECT * FROM " + table + " WHERE k2 NOT LIKE '%'");
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("SELECT * FROM " + table + " WHERE k2 NOT LIKE '%%'");
+ assertFalse(rs.next());
+
conn.close();
}
@@ -205,4 +223,76 @@ public class LikeExpressionIT extends BaseHBaseManagedTimeTableReuseIT {
assertEquals("AA", rs.getString(1));
assertFalse(rs.next());
}
+
+ @Test
+ public void testNull() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String table = generateRandomString();
+ String ddl = "CREATE TABLE " + table
+ + " (pk INTEGER PRIMARY KEY, str VARCHAR)";
+ conn.createStatement().execute(ddl);
+ conn.createStatement().execute("UPSERT INTO " + table + " VALUES(0,'aa')");
+ conn.createStatement().execute("UPSERT INTO " + table + " VALUES(1, null)");
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT str LIKE '%' FROM " + table);
+ assertTrue(rs.next());
+ assertEquals(true, rs.getBoolean(1));
+ assertFalse(rs.wasNull());
+ assertTrue(rs.next());
+ assertEquals(false, rs.getBoolean(1));
+ assertTrue(rs.wasNull());
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery(
+ "SELECT str LIKE '%%' FROM " + table);
+ assertTrue(rs.next());
+ assertEquals(true, rs.getBoolean(1));
+ assertFalse(rs.wasNull());
+ assertTrue(rs.next());
+ assertEquals(false, rs.getBoolean(1));
+ assertTrue(rs.wasNull());
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery(
+ "SELECT str NOT LIKE '%' FROM " + table);
+ assertTrue(rs.next());
+ assertEquals(false, rs.getBoolean(1));
+ assertFalse(rs.wasNull());
+ assertTrue(rs.next());
+ assertEquals(false, rs.getBoolean(1));
+ assertTrue(rs.wasNull());
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery(
+ "SELECT str NOT LIKE '%%' FROM " + table);
+ assertTrue(rs.next());
+ assertEquals(false, rs.getBoolean(1));
+ assertFalse(rs.wasNull());
+ assertTrue(rs.next());
+ assertEquals(false, rs.getBoolean(1));
+ assertTrue(rs.wasNull());
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery(
+ "SELECT NOT (str LIKE '%') FROM " + table);
+ assertTrue(rs.next());
+ assertEquals(false, rs.getBoolean(1));
+ assertFalse(rs.wasNull());
+ assertTrue(rs.next());
+ assertEquals(false, rs.getBoolean(1));
+ assertTrue(rs.wasNull());
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery(
+ "SELECT NOT(str LIKE '%%') FROM " + table);
+ assertTrue(rs.next());
+ assertEquals(false, rs.getBoolean(1));
+ assertFalse(rs.wasNull());
+ assertTrue(rs.next());
+ assertEquals(false, rs.getBoolean(1));
+ assertTrue(rs.wasNull());
+ assertFalse(rs.next());
+ }
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/47a2b2c3/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
index aaab763..c05918b 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
@@ -105,6 +105,7 @@ import org.apache.phoenix.parse.SubqueryParseNode;
import org.apache.phoenix.parse.SubtractParseNode;
import org.apache.phoenix.parse.UDFParseNode;
import org.apache.phoenix.parse.UnsupportedAllParseNodeVisitor;
+import org.apache.phoenix.query.KeyRange;
import org.apache.phoenix.query.QueryServices;
import org.apache.phoenix.query.QueryServicesOptions;
import org.apache.phoenix.schema.ColumnFamilyNotFoundException;
@@ -121,18 +122,7 @@ import org.apache.phoenix.schema.RowKeyValueAccessor;
import org.apache.phoenix.schema.SortOrder;
import org.apache.phoenix.schema.TableRef;
import org.apache.phoenix.schema.TypeMismatchException;
-import org.apache.phoenix.schema.types.PArrayDataType;
-import org.apache.phoenix.schema.types.PBoolean;
-import org.apache.phoenix.schema.types.PChar;
-import org.apache.phoenix.schema.types.PDataType;
-import org.apache.phoenix.schema.types.PDate;
-import org.apache.phoenix.schema.types.PDecimal;
-import org.apache.phoenix.schema.types.PDouble;
-import org.apache.phoenix.schema.types.PLong;
-import org.apache.phoenix.schema.types.PTimestamp;
-import org.apache.phoenix.schema.types.PUnsignedTimestamp;
-import org.apache.phoenix.schema.types.PVarbinary;
-import org.apache.phoenix.schema.types.PhoenixArray;
+import org.apache.phoenix.schema.types.*;
import org.apache.phoenix.util.ExpressionUtil;
import org.apache.phoenix.util.IndexUtil;
import org.apache.phoenix.util.SchemaUtil;
@@ -147,6 +137,7 @@ public class ExpressionCompiler extends UnsupportedAllParseNodeVisitor<Expressio
private int nodeCount;
private int totalNodeCount;
private final boolean resolveViewConstants;
+ private static final Expression NOT_NULL_STRING = LiteralExpression.newConstant(PVarchar.INSTANCE.toObject(KeyRange.IS_NOT_NULL_RANGE.getLowerRange()));
public ExpressionCompiler(StatementContext context) {
this(context,GroupBy.EMPTY_GROUP_BY, false);
@@ -519,11 +510,12 @@ public class ExpressionCompiler extends UnsupportedAllParseNodeVisitor<Expressio
}
}
} else {
- byte[] nullExpressionString = new byte[pattern.length()];
+ byte[] wildcardString = new byte[pattern.length()];
byte[] wildcard = {StringUtil.MULTI_CHAR_LIKE};
- StringUtil.fill(nullExpressionString, 0, pattern.length(), wildcard, 0, 1, false);
- if (pattern.equals(new String (nullExpressionString))) {
- return IsNullExpression.create(lhs, true, context.getTempPtr());
+ StringUtil.fill(wildcardString, 0, pattern.length(), wildcard, 0, 1, false);
+ if (pattern.equals(new String (wildcardString))) {
+ List<Expression> compareChildren = Arrays.asList(lhs, NOT_NULL_STRING);
+ return new ComparisonExpression(compareChildren, node.isNegate() ? CompareOp.LESS : CompareOp.GREATER_OR_EQUAL);
}
}
}