This is an automated email from the ASF dual-hosted git repository.
github-actions[bot] pushed a commit to branch site
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/site by this push:
new e20e10b583 [CALCITE-7727] Comparing UUID <> '' always returns FALSE
e20e10b583 is described below
commit e20e10b583f874172fe91da7c7901ef270cfa774
Author: Mihai Budiu <[email protected]>
AuthorDate: Tue Aug 18 20:19:45 2026 -0700
[CALCITE-7727] Comparing UUID <> '' always returns FALSE
Signed-off-by: Mihai Budiu <[email protected]>
---
.../apache/calcite/jdbc/JavaTypeFactoryImpl.java | 3 +
.../apache/calcite/rel/externalize/RelJson.java | 3 +-
.../org/apache/calcite/runtime/SqlFunctions.java | 66 +++++-
.../apache/calcite/sql/parser/SqlParserUtil.java | 3 +-
.../validate/implicit/AbstractTypeCoercion.java | 21 +-
.../org/apache/calcite/util/BuiltInMethod.java | 2 +-
.../org/apache/calcite/plan/RelWriterTest.java | 4 +-
.../org/apache/calcite/test/TypeCoercionTest.java | 22 ++
core/src/test/resources/sql/misc.iq | 252 ++++++++++++++++++++-
site/_docs/history.md | 18 ++
site/_docs/reference.md | 5 +-
.../org/apache/calcite/test/CalciteAssert.java | 22 +-
12 files changed, 392 insertions(+), 29 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/jdbc/JavaTypeFactoryImpl.java
b/core/src/main/java/org/apache/calcite/jdbc/JavaTypeFactoryImpl.java
index 3f276b41e6..739e8758cb 100644
--- a/core/src/main/java/org/apache/calcite/jdbc/JavaTypeFactoryImpl.java
+++ b/core/src/main/java/org/apache/calcite/jdbc/JavaTypeFactoryImpl.java
@@ -53,6 +53,7 @@
import java.util.HashMap;
import java.util.List;
import java.util.Map;
+import java.util.UUID;
import java.util.stream.Collectors;
import static org.apache.calcite.util.ReflectUtil.isStatic;
@@ -226,6 +227,8 @@ private static Type fieldType(Field field) {
return ByteString.class;
case GEOMETRY:
return Geometry.class;
+ case UUID:
+ return UUID.class;
case SYMBOL:
return Enum.class;
case ANY:
diff --git a/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java
b/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java
index 2409ac6090..2669295a14 100644
--- a/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java
+++ b/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java
@@ -53,6 +53,7 @@
import org.apache.calcite.rex.RexWindowBound;
import org.apache.calcite.rex.RexWindowBounds;
import org.apache.calcite.rex.RexWindowExclusion;
+import org.apache.calcite.runtime.SqlFunctions;
import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlIdentifier;
@@ -874,7 +875,7 @@ public RexNode toRex(RelOptCluster cluster, Object o) {
} else if (sqlTypeName == SqlTypeName.BINARY || sqlTypeName ==
SqlTypeName.VARBINARY) {
literal = ByteString.of((String) literal, 16);
} else if (sqlTypeName == SqlTypeName.UUID) {
- literal = UUID.fromString((String) literal);
+ literal = SqlFunctions.stringToUuid((String) literal);
}
return rexBuilder.makeLiteral(literal, type);
}
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index c1ebafe8ed..c40b8235bc 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -43,6 +43,7 @@
import org.apache.calcite.sql.SqlIntervalQualifier;
import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.fun.SqlLibraryOperators;
+import org.apache.calcite.sql.parser.SqlParserUtil;
import org.apache.calcite.util.NumberUtil;
import org.apache.calcite.util.TimeWithTimeZoneString;
import org.apache.calcite.util.TimestampWithTimeZoneString;
@@ -299,9 +300,70 @@ public static String uuidToString(UUID uuid) {
return uuid.toString();
}
+ /** Converts a string to a UUID: 32 hexadecimal digits. All of the following
give
+ * the UUID {@code 123e4567-e89b-12d3-a456-426655440000}:
+ *
+ * <blockquote><pre>
+ * 123e4567-e89b-12d3-a456-426655440000
+ * 123E4567-E89B-12D3-A456-426655440000
+ * 123e4567e89b12d3a456426655440000
+ * {123e4567-e89b-12d3-a456-426655440000}
+ * {123e4567e89b12d3a456426655440000}
+ * 123e-4567-e89b-12d3-a456-4266-5544-0000
+ * 123e4567-e89b12d3-a4564266-55440000
+ * 123e-4567e89b-12d3a456426655440000
+ * </pre></blockquote>
+ *
+ * <p>and each of the following is an error:
+ *
+ * <blockquote><pre>
+ * 1-2-3-4-5 a group is not four digits wide
+ * 123e456-7e89b-12d3-a456-426655440000 as above, though 36 characters long
+ * 123e4567--e89b-12d3-a456-426655440000 empty group
+ * -123e4567e89b12d3a456426655440000 leading hyphen
+ * 123e4567e89b12d3a456426655440000- trailing hyphen
+ * {123e4567-e89b-12d3-a456-426655440000 unbalanced brace
+ * 123e4567-e89b-12d3-a456-42665544000 31 digits
+ * </pre></blockquote>
+ *
+ * <p>Blanks are never trimmed.
+ */
+ public static UUID stringToUuid(String s) {
+ String body = s;
+ if (body.length() > 1
+ && body.charAt(0) == '{'
+ && body.charAt(body.length() - 1) == '}') {
+ body = body.substring(1, body.length() - 1);
+ }
+ final StringBuilder digits = new StringBuilder(32);
+ for (int i = 0; i < body.length(); i++) {
+ final char c = body.charAt(i);
+ if (c == '-') {
+ // A hyphen separates groups, so it must follow a complete group of
four
+ // digits and cannot be the last character
+ if (digits.length() == 0
+ || digits.length() % 4 != 0
+ || digits.length() == 32
+ || body.charAt(i - 1) == '-') {
+ throw new IllegalArgumentException("Invalid UUID string: " + s);
+ }
+ } else if (SqlParserUtil.isHexDigit(c) && digits.length() < 32) {
+ digits.append(c);
+ } else {
+ throw new IllegalArgumentException("Invalid UUID string: " + s);
+ }
+ }
+ if (digits.length() != 32) {
+ throw new IllegalArgumentException("Invalid UUID string: " + s);
+ }
+ return new UUID(
+ Long.parseUnsignedLong(digits.substring(0, 16), 16),
+ Long.parseUnsignedLong(digits.substring(16), 16));
+ }
+
public static UUID binaryToUuid(ByteString bytes) {
- if (bytes.length() < 16) {
- throw new IllegalArgumentException("Need at least 16 bytes for UUID");
+ if (bytes.length() != 16) {
+ throw new IllegalArgumentException("Need exactly 16 bytes for UUID");
}
ByteBuffer byteBuffer = ByteBuffer.wrap(bytes.getBytes());
long mostSignificantBits = byteBuffer.getLong();
diff --git
a/core/src/main/java/org/apache/calcite/sql/parser/SqlParserUtil.java
b/core/src/main/java/org/apache/calcite/sql/parser/SqlParserUtil.java
index 1ba1933fc7..52f3d064e4 100644
--- a/core/src/main/java/org/apache/calcite/sql/parser/SqlParserUtil.java
+++ b/core/src/main/java/org/apache/calcite/sql/parser/SqlParserUtil.java
@@ -21,6 +21,7 @@
import org.apache.calcite.config.CalciteSystemProperty;
import org.apache.calcite.rel.type.RelDataTypeSystem;
import org.apache.calcite.runtime.CalciteContextException;
+import org.apache.calcite.runtime.SqlFunctions;
import org.apache.calcite.sql.SqlBinaryOperator;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlDateLiteral;
@@ -399,7 +400,7 @@ public static SqlTimestampLiteral
parseTimestampWithLocalTimeZoneLiteral(
}
public static SqlUuidLiteral parseUuidLiteral(String s, SqlParserPos pos) {
- UUID uuid = UUID.fromString(s);
+ UUID uuid = SqlFunctions.stringToUuid(s);
return SqlLiteral.createUuid(uuid, pos);
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java
index 32d39229b5..22f6e16c61 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/AbstractTypeCoercion.java
@@ -291,13 +291,17 @@ protected boolean needToCast(SqlValidatorScope scope,
SqlNode node,
return false;
}
- // No casts to binary except from strings
- if (SqlTypeUtil.isBinary(fromType) && !SqlTypeUtil.isString(toType)) {
+ // No casts from binary except to strings and UUID
+ if (SqlTypeUtil.isBinary(fromType)
+ && !SqlTypeUtil.isString(toType)
+ && toType.getSqlTypeName() != SqlTypeName.UUID) {
return false;
}
- // No casts from binary except to strings
- if (SqlTypeUtil.isBinary(toType) && !SqlTypeUtil.isString(fromType)) {
+ // No casts to binary except from strings and UUID
+ if (SqlTypeUtil.isBinary(toType)
+ && !SqlTypeUtil.isString(fromType)
+ && fromType.getSqlTypeName() != SqlTypeName.UUID) {
return false;
}
@@ -525,14 +529,15 @@ private RelDataType getTightestCommonTypeOrThrow(
return factory.leastRestrictive(ImmutableList.of(type1, type2));
}
+ // CHARACTER or BINARY < UUID -> UUID, similar to CHAR < INT -> INT
if ((SqlTypeUtil.isCharacter(type1) || SqlTypeUtil.isBinary(type1))
- && type2.getSqlTypeName() == SqlTypeName.UUID) {
- return factory.createTypeWithNullability(type1, anyNullable);
+ && typeName2 == SqlTypeName.UUID) {
+ return factory.createTypeWithNullability(type2, anyNullable);
}
if ((SqlTypeUtil.isCharacter(type2) || SqlTypeUtil.isBinary(type2))
- && type1.getSqlTypeName() == SqlTypeName.UUID) {
- return factory.createTypeWithNullability(type2, anyNullable);
+ && typeName1 == SqlTypeName.UUID) {
+ return factory.createTypeWithNullability(type1, anyNullable);
}
// DATETIME < CHARACTER -> DATETIME
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 98b67a02bb..4bbe57908e 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -533,7 +533,7 @@ public enum BuiltInMethod {
IS_JSON_ARRAY(JsonFunctions.class, "isJsonArray", String.class),
IS_JSON_SCALAR(JsonFunctions.class, "isJsonScalar", String.class),
ST_GEOM_FROM_EWKT(SpatialTypeFunctions.class, "ST_GeomFromEWKT",
String.class),
- UUID_FROM_STRING(UUID.class, "fromString", String.class),
+ UUID_FROM_STRING(SqlFunctions.class, "stringToUuid", String.class),
UUID_TO_STRING(SqlFunctions.class, "uuidToString", UUID.class),
UUID_TO_BINARY(SqlFunctions.class, "uuidToBinary", UUID.class),
INT_TO_BINARY(SqlFunctions.class, "intToBinary", Object.class, int.class,
boolean.class),
diff --git a/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
b/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
index 8fd02a8c50..a107afd17f 100644
--- a/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
+++ b/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
@@ -53,6 +53,7 @@
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexProgramBuilder;
import org.apache.calcite.rex.RexWindowBounds;
+import org.apache.calcite.runtime.SqlFunctions;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.sql.SqlExplainFormat;
import org.apache.calcite.sql.SqlExplainLevel;
@@ -99,7 +100,6 @@
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
-import java.util.UUID;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.stream.Stream;
@@ -641,7 +641,7 @@ private static Fixture relFn(Function<RelBuilder, RelNode>
relFn) {
.build();
return b.values(rowType, 0).project(
b.getRexBuilder().makeUuidLiteral(
- UUID.fromString("123e4567-e89b-12d3-a456-426655440000")))
+
SqlFunctions.stringToUuid("123e4567-e89b-12d3-a456-426655440000")))
.build();
};
relFn(relFn)
diff --git a/core/src/test/java/org/apache/calcite/test/TypeCoercionTest.java
b/core/src/test/java/org/apache/calcite/test/TypeCoercionTest.java
index e4f521d0bb..32db4091bf 100644
--- a/core/src/test/java/org/apache/calcite/test/TypeCoercionTest.java
+++ b/core/src/test/java/org/apache/calcite/test/TypeCoercionTest.java
@@ -395,6 +395,26 @@ private static ImmutableList<RelDataType> combine(
f.comparisonCommonType(f.charType, f.varcharType, f.varcharType);
f.comparisonCommonType(f.intType, f.charType, f.intType);
f.comparisonCommonType(f.doubleType, f.charType, f.doubleType);
+ // Test cases for [CALCITE-7727] Comparing UUID <> '' always returns FALSE.
+ final RelDataType char0Type =
f.typeFactory.createSqlType(SqlTypeName.CHAR, 0);
+ final RelDataType char36Type =
f.typeFactory.createSqlType(SqlTypeName.CHAR, 36);
+ final RelDataType char40Type =
f.typeFactory.createSqlType(SqlTypeName.CHAR, 40);
+ final RelDataType binary16Type =
+ f.typeFactory.createSqlType(SqlTypeName.BINARY, 16);
+ final RelDataType binary20Type =
+ f.typeFactory.createSqlType(SqlTypeName.BINARY, 20);
+ f.comparisonCommonType(f.uuidType, char0Type, f.uuidType);
+ f.comparisonCommonType(f.uuidType, f.charType, f.uuidType);
+ f.comparisonCommonType(f.uuidType, char36Type, f.uuidType);
+ f.comparisonCommonType(f.uuidType, char40Type, f.uuidType);
+ f.comparisonCommonType(f.uuidType, f.varchar20Type, f.uuidType);
+ f.comparisonCommonType(f.uuidType, f.varcharType, f.uuidType);
+ f.comparisonCommonType(f.uuidType, f.binaryType, f.uuidType);
+ f.comparisonCommonType(f.uuidType, binary16Type, f.uuidType);
+ f.comparisonCommonType(f.uuidType, binary20Type, f.uuidType);
+ f.comparisonCommonType(f.uuidType, f.varbinaryType, f.uuidType);
+ f.comparisonCommonType(f.uuidType, f.uuidType, f.uuidType);
+
// TIMESTAMP
f.comparisonCommonType(f.timestampType, f.timestampType, f.timestampType);
f.comparisonCommonType(f.dateType, f.timestampType, f.timestampType);
@@ -796,6 +816,7 @@ static class Fixture {
final RelDataType nullableVarchar20Type;
final RelDataType geometryType;
final RelDataType nullableGeometryType;
+ final RelDataType uuidType;
/** Creates a Fixture. */
public static Fixture create(SqlTestFactory testFactory) {
@@ -846,6 +867,7 @@ protected Fixture(RelDataTypeFactory typeFactory,
nullableVarchar20Type =
this.typeFactory.createTypeWithNullability(varchar20Type, true);
geometryType = this.typeFactory.createSqlType(SqlTypeName.GEOMETRY);
nullableGeometryType =
this.typeFactory.createTypeWithNullability(geometryType, true);
+ uuidType = this.typeFactory.createSqlType(SqlTypeName.UUID);
// Initialize category types
diff --git a/core/src/test/resources/sql/misc.iq
b/core/src/test/resources/sql/misc.iq
index 05697b8118..faffcabd41 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -87,8 +87,48 @@ SELECT CAST('123e4567-e89b-12d3-a456-426655440000' AS UUID);
!ok
+# Hyphens are optional separators, so this denotes the same UUID. PostgreSQL
+# accepts the same set of spellings.
SELECT CAST('123e4567e89b12d3a456426655440000' AS UUID);
-java.lang.IllegalArgumentException: Invalid UUID string:
123e4567e89b12d3a456426655440000
++--------------------------------------+
+| EXPR$0 |
++--------------------------------------+
+| 123e4567-e89b-12d3-a456-426655440000 |
++--------------------------------------+
+(1 row)
+
+!ok
+
+SELECT CAST('{123e4567-e89b-12d3-a456-426655440000}' AS UUID);
++--------------------------------------+
+| EXPR$0 |
++--------------------------------------+
+| 123e4567-e89b-12d3-a456-426655440000 |
++--------------------------------------+
+(1 row)
+
+!ok
+
+SELECT CAST('123e-4567-e89b-12d3-a456-4266-5544-0000' AS UUID);
++--------------------------------------+
+| EXPR$0 |
++--------------------------------------+
+| 123e4567-e89b-12d3-a456-426655440000 |
++--------------------------------------+
+(1 row)
+
+!ok
+
+SELECT CAST('1-2-3-4-5' AS UUID);
+java.lang.IllegalArgumentException: Invalid UUID string: 1-2-3-4-5
+!error
+
+SELECT CAST('123e456-7e89b-12d3-a456-426655440000' AS UUID);
+java.lang.IllegalArgumentException: Invalid UUID string:
123e456-7e89b-12d3-a456-426655440000
+!error
+
+SELECT CAST('123e4567--e89b-12d3-a456-426655440000' AS UUID);
+java.lang.IllegalArgumentException: Invalid UUID string:
123e4567--e89b-12d3-a456-426655440000
!error
SELECT CAST(UUID '123e4567-e89b-12d3-a456-426655440000' AS VARCHAR);
@@ -122,7 +162,7 @@ SELECT CAST(x'123e4567e89b12d3a456426655440000' AS UUID);
!ok
SELECT CAST(x'00' AS UUID);
-java.lang.IllegalArgumentException: Need at least 16 bytes for UUID
+java.lang.IllegalArgumentException: Need exactly 16 bytes for UUID
!error
SELECT UUID '123e4567-e89b-12d3-a456-426655440000' =
'123e4567-e89b-12d3-a456-426655440000';
@@ -135,6 +175,214 @@ SELECT UUID '123e4567-e89b-12d3-a456-426655440000' =
'123e4567-e89b-12d3-a456-42
!ok
+# [CALCITE-7727] Comparing UUID <> '' always returns FALSE.
+# Matches PostgreSQL
+SELECT UUID '123e4567-e89b-12d3-a456-426655440000' <> '' AS C;
+java.lang.IllegalArgumentException: Invalid UUID string:
+!error
+
+# Matches PostgreSQL
+SELECT UUID '123e4567-e89b-12d3-a456-426655440000' = '123e4567' AS C;
+java.lang.IllegalArgumentException: Invalid UUID string: 123e4567
+!error
+
+# A trailing blank does not denote a UUID either
+# Matches PostgreSQL
+SELECT UUID '123e4567-e89b-12d3-a456-426655440000'
+ = '123e4567-e89b-12d3-a456-426655440000 ' AS C;
+java.lang.IllegalArgumentException: Invalid UUID string:
123e4567-e89b-12d3-a456-426655440000
+!error
+
+# Matches PostgreSQL
+SELECT CAST('' AS UUID) AS C;
+java.lang.IllegalArgumentException: Invalid UUID string:
+!error
+
+# Matches PostgreSQL
+SELECT CAST(' ' AS UUID) AS C;
+java.lang.IllegalArgumentException: Invalid UUID string:
+!error
+
+# Blanks are not trimmed
+# Matches PostgreSQL
+SELECT CAST(' 123e4567-e89b-12d3-a456-426655440000' AS UUID) AS C;
+java.lang.IllegalArgumentException: Invalid UUID string:
123e4567-e89b-12d3-a456-426655440000
+!error
+
+# The CHAR(40) string has extra spaces, so casting it to UUID fails
+# Matches PostgreSQL, which rejects char(40) the same way
+SELECT UUID '123e4567-e89b-12d3-a456-426655440000'
+ = CAST('123e4567-e89b-12d3-a456-426655440000' AS CHAR(40)) AS C;
+java.lang.IllegalArgumentException: Invalid UUID string:
123e4567-e89b-12d3-a456-426655440000
+!error
+
+# CHAR(36) is exactly the width of the UUID, so there is no padding
+SELECT UUID '123e4567-e89b-12d3-a456-426655440000'
+ = CAST('123e4567-e89b-12d3-a456-426655440000' AS CHAR(36)) AS C;
++------+
+| C |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+# Matches PostgreSQL
+SELECT UUID '123e4567-e89b-12d3-a456-426655440000'
+ = '123E4567-E89B-12D3-A456-426655440000' AS C;
++------+
+| C |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+# IN uses the comparison common type
+SELECT UUID '123e4567-e89b-12d3-a456-426655440000'
+ IN ('123e4567-e89b-12d3-a456-426655440000',
+ '123E4567-E89B-12D3-A456-426655440001') AS C;
++------+
+| C |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+# explain
+SELECT u <> '' FROM (VALUES (CAST(NULL AS UUID))) AS t(u);
+SELECT "T"."U" <> CAST('' AS UUID)
+FROM (VALUES ROW(CAST(NULL AS UUID))) AS "T" ("U")
+!explain-validated-on Calcite
+
+SELECT u = f AS EQ_FULL, u = g AS EQ_UPPER, u = b AS EQ_BINARY
+FROM (VALUES (CAST('123e4567-e89b-12d3-a456-426655440000' AS UUID),
+ '123e4567-e89b-12d3-a456-426655440000',
+ '123E4567-E89B-12D3-A456-426655440000',
+ x'123e4567e89b12d3a456426655440000'),
+ (CAST(NULL AS UUID),
+ '123e4567-e89b-12d3-a456-426655440000',
+ '123E4567-E89B-12D3-A456-426655440000',
+ x'123e4567e89b12d3a456426655440000'))
+ AS t(u, f, g, b);
++---------+----------+-----------+
+| EQ_FULL | EQ_UPPER | EQ_BINARY |
++---------+----------+-----------+
+| true | true | true |
+| | | |
++---------+----------+-----------+
+(2 rows)
+
+!ok
+
+SELECT CAST(u AS VARCHAR) AS C
+FROM (VALUES (CAST('123e4567-e89b-12d3-a456-426655440000' AS UUID)),
+ (CAST(NULL AS UUID))) AS t(u);
++--------------------------------------+
+| C |
++--------------------------------------+
+| 123e4567-e89b-12d3-a456-426655440000 |
+| |
++--------------------------------------+
+(2 rows)
+
+!ok
+
+WITH t(u) AS (VALUES (CAST('123e4567-e89b-12d3-a456-426655440000' AS UUID)),
+ (CAST(NULL AS UUID)))
+SELECT CAST(u AS VARBINARY) AS B, u = u AS SELF FROM t;
++----------------------------------+------+
+| B | SELF |
++----------------------------------+------+
+| 123e4567e89b12d3a456426655440000 | true |
+| | |
++----------------------------------+------+
+(2 rows)
+
+!ok
+
+# UUID columns as grouping and sorting keys
+WITH t(u) AS (VALUES (CAST('123e4567-e89b-12d3-a456-426655440000' AS UUID)),
+ (CAST('123e4567-e89b-12d3-a456-426655440001' AS UUID)),
+ (CAST('123e4567-e89b-12d3-a456-426655440000' AS UUID)),
+ (CAST(NULL AS UUID)))
+SELECT u, COUNT(*) AS C FROM t GROUP BY u ORDER BY u;
++--------------------------------------+---+
+| U | C |
++--------------------------------------+---+
+| 123e4567-e89b-12d3-a456-426655440000 | 2 |
+| 123e4567-e89b-12d3-a456-426655440001 | 1 |
+| | 1 |
++--------------------------------------+---+
+(3 rows)
+
+!ok
+
+# UUID columns as join keys
+WITH t(u) AS (VALUES (CAST('123e4567-e89b-12d3-a456-426655440000' AS UUID)),
+ (CAST('123e4567-e89b-12d3-a456-426655440001' AS UUID)))
+SELECT t1.u FROM t AS t1 JOIN t AS t2 ON t1.u = t2.u ORDER BY 1;
++--------------------------------------+
+| U |
++--------------------------------------+
+| 123e4567-e89b-12d3-a456-426655440000 |
+| 123e4567-e89b-12d3-a456-426655440001 |
++--------------------------------------+
+(2 rows)
+
+!ok
+
+# Binary compared to UUID
+SELECT UUID '123e4567-e89b-12d3-a456-426655440000' =
x'123e4567e89b12d3a456426655440000' AS C;
++------+
+| C |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+SELECT x'123e4567e89b12d3a456426655440000' = UUID
'123e4567-e89b-12d3-a456-426655440000' AS C;
++------+
+| C |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+SELECT UUID '123e4567-e89b-12d3-a456-426655440000' = x'00' AS C;
+java.lang.IllegalArgumentException: Need exactly 16 bytes for UUID
+!error
+
+SELECT UUID '123e4567-e89b-12d3-a456-426655440000'
+ = x'123e4567e89b12d3a456426655440000ff' AS C;
+java.lang.IllegalArgumentException: Need exactly 16 bytes for UUID
+!error
+
+SELECT CAST(x'123e4567e89b12d3a456426655440000ff' AS UUID) AS C;
+java.lang.IllegalArgumentException: Need exactly 16 bytes for UUID
+!error
+
+# Hyphens are optional, so this string denotes the same UUID.
+# Matches PostgreSQL
+SELECT UUID '123e4567-e89b-12d3-a456-426655440000'
+ = '123e4567e89b12d3a456426655440000' AS C;
++------+
+| C |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
SELECT CAST(NULL AS UUID);
+--------+
| EXPR$0 |
diff --git a/site/_docs/history.md b/site/_docs/history.md
index 3aba1fd075..d1e0caae3a 100644
--- a/site/_docs/history.md
+++ b/site/_docs/history.md
@@ -64,6 +64,24 @@ #### Breaking Changes
classes from model files will lead to `SecurityException` unless an appropriate
pattern is set in `calcite.model.classes.allowed` system property.
+* [<a
href="https://issues.apache.org/jira/browse/CALCITE-7727";>CALCITE-7727</a>]
+Comparing a `UUID` with a character or binary value now converts that value to
a
+`UUID`, the same direction as comparing a string with a number or a datetime.
+Previously the `UUID` was converted to the other operand's type. A value that
does not
+denote a `UUID` is now an error rather than a comparison that silently fails.
+
+* [<a
href="https://issues.apache.org/jira/browse/CALCITE-7727";>CALCITE-7727</a>]
+Converting a string to a `UUID` now follows PostgreSQL: 32 hexadecimal digits
of
+either case, optionally enclosed in braces, optionally separated by a hyphen
+after any complete group of four digits. Forms such as
+`123e4567e89b12d3a456426655440000` and `{123e4567-e89b-12d3-a456-426655440000}`
+are now accepted. Malformed strings are now rejected instead of being converted
+to a different `UUID`; `java.util.UUID.fromString`, used previously, does not
+check the width of each group, and turned `1-2-3-4-5` into
+`00000001-0002-0003-0004-000000000005`. Converting a binary to a `UUID` now
+requires exactly 16 bytes; a longer value used to be truncated. Blanks are not
+trimmed.
+
#### New features
{: #new-features-1-43-0}
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index bdf4ada199..c0ce61bef5 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1248,7 +1248,7 @@ ### Scalar types
| TIMESTAMP [ WITHOUT TIME ZONE ] | Date and time | Example: TIMESTAMP
'1969-07-20 20:17:40'
| TIMESTAMP WITH LOCAL TIME ZONE | Date and time with local time zone |
Example: TIMESTAMP WITH LOCAL TIME ZONE '1969-07-20 20:17:40'
| TIMESTAMP WITH TIME ZONE | Date and time with time zone | Example: TIMESTAMP
WITH TIME ZONE '1969-07-20 20:17:40 America/Los Angeles'
-| UUID | An 128-bit UUID | Example: UUID
'123e4567-e89b-12d3-a456-426655440000'
+| UUID | An 128-bit UUID | Example: UUID
'123e4567-e89b-12d3-a456-426655440000'. A string converts to a `UUID` if it
holds 32 hexadecimal digits of either case, optionally enclosed in braces,
optionally separated by a hyphen after any complete group of four digits; a
binary converts if it is exactly 16 bytes. Anything else is an error. Blanks
are not trimmed.
| INTERVAL timeUnit [ TO timeUnit ] | Date time interval | Examples: INTERVAL
'1-5' YEAR TO MONTH, INTERVAL '45' DAY, INTERVAL '1 2:34:56.789' DAY TO SECOND
| GEOMETRY | Geometry | Examples: ST_GeomFromText('POINT (30 10)')
@@ -1746,7 +1746,8 @@ ##### Conversion Contexts and Strategies
* Binary comparison (`=`, `<`, `<=`, `<>`, `>`, `>=`):
if operands are `STRING` and `TIMESTAMP`, promote to `TIMESTAMP`;
make `1 = true` and `0 = false` always evaluate to `TRUE`;
- if there is numeric type operand, find common type for both operands.
+ if there is numeric type operand, find common type for both operands;
+ if operands are `UUID` and `CHARACTER` or `BINARY`, promote to `UUID`.
* `IN` sub-query: compare type of LHS and RHS, and find the common type;
if it is struct type, find wider type for every field;
* `IN` expression list: compare every expression to find the common type;
diff --git a/testkit/src/main/java/org/apache/calcite/test/CalciteAssert.java
b/testkit/src/main/java/org/apache/calcite/test/CalciteAssert.java
index cbc2f6c799..aa27c7b77a 100644
--- a/testkit/src/main/java/org/apache/calcite/test/CalciteAssert.java
+++ b/testkit/src/main/java/org/apache/calcite/test/CalciteAssert.java
@@ -155,6 +155,12 @@
public class CalciteAssert {
private CalciteAssert() {}
+ // Define string constants before DB to prevent recursive
+ // static initializers
+ private static final String TEST_MYSQL_URL =
"jdbc:mysql://localhost/foodmart";
+
+ private static final String TEST_MYSQL_DRIVER = "com.mysql.jdbc.Driver";
+
/**
* Which database to use for tests that require a JDBC data source.
*
@@ -163,10 +169,6 @@ private CalciteAssert() {}
public static final DatabaseInstance DB =
DatabaseInstance.valueOf(CalciteSystemProperty.TEST_DB.value());
- private static String testMysqlUrl = "jdbc:mysql://localhost/foodmart";
-
- private static String testMysqlDriver = "com.mysql.jdbc.Driver";
-
/** Implementation of {@link AssertThat} that does nothing. */
private static final AssertThat DISABLED =
new AssertThat(ConnectionFactories.empty(), ImmutableList.of()) {
@@ -2056,14 +2058,14 @@ public enum DatabaseInstance {
+ "/h2/target/foodmart;user=foodmart;password=foodmart",
"foodmart", "foodmart", "org.h2.Driver", "foodmart"), null, null),
MYSQL(
- new ConnectionSpec(testMysqlUrl, "foodmart",
- "foodmart", testMysqlDriver, "foodmart"), null, null),
+ new ConnectionSpec(TEST_MYSQL_URL, "foodmart",
+ "foodmart", TEST_MYSQL_DRIVER, "foodmart"), null, null),
STARROCKS(
- new ConnectionSpec(testMysqlUrl, "foodmart",
- "foodmart", testMysqlDriver, "foodmart"), null, null),
+ new ConnectionSpec(TEST_MYSQL_URL, "foodmart",
+ "foodmart", TEST_MYSQL_DRIVER, "foodmart"), null, null),
DORIS(
- new ConnectionSpec(testMysqlUrl, "foodmart",
- "foodmart", testMysqlDriver, "foodmart"), null, null),
+ new ConnectionSpec(TEST_MYSQL_URL, "foodmart",
+ "foodmart", TEST_MYSQL_DRIVER, "foodmart"), null, null),
ORACLE(
new ConnectionSpec("jdbc:oracle:thin:@localhost:1521:XE", "foodmart",
"foodmart", "oracle.jdbc.OracleDriver", "FOODMART"), null, null),
