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/calcite Commit: 47a2b2c3ec1fb7a5ae3fc42c79a2199ab5108f44 Parents: c3cdb2a Author: James Taylor <jamestay...@apache.org> Authored: Mon Sep 12 09:54:10 2016 -0700 Committer: James Taylor <jamestay...@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); } } }