This is an automated email from the ASF dual-hosted git repository.
yuqi1129 pushed a commit to branch branch-1.3
in repository https://gitbox.apache.org/repos/asf/gravitino.git
The following commit(s) were added to refs/heads/branch-1.3 by this push:
new 6daa29da94 [Cherry-pick to branch-1.3] [#14631] fix(jdbc): harden
table DDL and normalize ALTER defaults (#14632) (#14718)
6daa29da94 is described below
commit 6daa29da94d2e25a54efb4515c3dc691cd4f21e3
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Fri Nov 6 17:30:04 2026 +0800
[Cherry-pick to branch-1.3] [#14631] fix(jdbc): harden table DDL and
normalize ALTER defaults (#14632) (#14718)
### What changes were proposed in this pull request?
Cherry-pick 6086b7e4fefa877514f454a492ef5278314b4b47 (#14632) to
`branch-1.3`.
- Validate shared JDBC default-function identifiers and render
literal/nested-function arguments through the dialect converter.
Normalize ALTER defaults consistently with CREATE.
- Preserve PostgreSQL expressions when reading defaults, decode literals
using the connection's string mode, and restrict `ExternalType` writes
to single type declarations.
- Reject PostgreSQL standalone unique-index deletion before any DDL in
the request, including `ifExists` requests.
Resolve the converter conflict by retaining `branch-1.3`'s protected
`CURRENT_TIMESTAMP` constant and the new function-name validation. No
unrelated API visibility change is introduced.
### Why are the changes needed?
Malformed type/function fragments can change the requested DDL. Default
arguments and PostgreSQL expression defaults can be rendered or read
incorrectly, and ALTER NULL defaults differ from CREATE.
Standalone-index deletion can report success without removing the index.
Backports the fix for #14631.
### Does this PR introduce _any_ user-facing change?
Function defaults require plain/qualified identifiers and literal or
nested-function arguments. This shared validation affects PostgreSQL,
MySQL, Doris, StarRocks, OceanBase, and Hologres; ClickHouse overrides
that path. Hologres retains its separate unparsed-expression write path.
Native type/function existence and semantics are still checked by the
backend.
PostgreSQL's type-declaration guards and default-reading changes are
specific to PostgreSQL. Other dialects retain their existing native-type
behavior. PostgreSQL expressions remain available for inspection but
cannot be resubmitted as write expressions. SQL NULL, the string `NULL`,
and omitted defaults remain distinct. Standalone unique indexes must be
removed directly in PostgreSQL.
The index preflight adds a catalog query for each index deletion and can
fail the request before DDL if that lookup fails. No metadata migration
or dependency change is introduced. The change does not make externally
concurrent DDL atomic.
### How was this patch tested?
On the resolved `branch-1.3` checkout, 312 targeted cases passed with
zero failures, errors, or skips:
- 8 ALTER DTO cases (`TestTableUpdatesRequest`) and 37 shared converter
cases (`TestJdbcColumnDefaultValueConverter`).
- 112 PostgreSQL converter, type, table-operation, SQL-generation, and
privilege cases, including real PostgreSQL 13 table tests.
- 154 shared-caller cases: Doris converter/table SQL generation,
MySQL/StarRocks/OceanBase table SQL generation, and Hologres converter
tests.
- `CatalogPostgreSqlIT.testColumnDefaultValueConverter` through the
embedded server and PostgreSQL.
Tests used explicit `--tests` filters with `-PskipDockerTests=false`;
unit-only runs also used `-PskipITs`. Temporarily restoring the shared
converter to the target-base implementation made 10 of its 37 tests
fail; all 37 passed again after restoring the fix. Scoped Spotless and
`git diff --check` passed. Full core, other native JDBC services,
PostGIS, and full REST/Spark/Trino end-to-end suites were not run for
this backport.
---------
Co-authored-by: Qi Yu <[email protected]>
Co-authored-by: yuqi <[email protected]>
---
.../converter/JdbcColumnDefaultValueConverter.java | 35 +-
.../TestJdbcColumnDefaultValueConverter.java | 71 +++
.../PostgreSqlColumnDefaultValueConverter.java | 124 ++++-
.../converter/PostgreSqlTypeConverter.java | 40 +-
.../operation/PostgreSqlTableOperations.java | 54 +-
.../TestPostgreSqlColumnDefaultValueConverter.java | 101 +++-
.../converter/TestPostgreSqlTypeConverter.java | 83 ++-
.../integration/test/CatalogPostgreSqlIT.java | 7 +-
.../operation/TestPostgreSqlTableOperations.java | 575 +++++++++++++++++++++
...TestPostgreSqlTableOperationsSqlGeneration.java | 33 ++
.../gravitino/dto/requests/TableUpdateRequest.java | 18 +-
.../dto/requests/TestTableUpdatesRequest.java | 44 ++
docs/jdbc-postgresql-catalog.md | 38 ++
docs/tables-and-views.md | 12 +
14 files changed, 1193 insertions(+), 42 deletions(-)
diff --git
a/catalogs/catalog-jdbc-common/src/main/java/org/apache/gravitino/catalog/jdbc/converter/JdbcColumnDefaultValueConverter.java
b/catalogs/catalog-jdbc-common/src/main/java/org/apache/gravitino/catalog/jdbc/converter/JdbcColumnDefaultValueConverter.java
index f4eecc197b..9f457a7286 100644
---
a/catalogs/catalog-jdbc-common/src/main/java/org/apache/gravitino/catalog/jdbc/converter/JdbcColumnDefaultValueConverter.java
+++
b/catalogs/catalog-jdbc-common/src/main/java/org/apache/gravitino/catalog/jdbc/converter/JdbcColumnDefaultValueConverter.java
@@ -21,12 +21,14 @@ package org.apache.gravitino.catalog.jdbc.converter;
import static
org.apache.gravitino.catalog.jdbc.utils.JdbcConnectorUtils.escapeSqlLiteral;
import static org.apache.gravitino.rel.Column.DEFAULT_VALUE_NOT_SET;
+import com.google.common.base.Preconditions;
import com.google.common.io.BaseEncoding;
import java.math.BigDecimal;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.time.temporal.ChronoField;
import java.util.Locale;
+import java.util.regex.Pattern;
import org.apache.commons.lang3.StringUtils;
import org.apache.gravitino.rel.expressions.Expression;
import org.apache.gravitino.rel.expressions.FunctionExpression;
@@ -37,7 +39,12 @@ import org.apache.gravitino.rel.types.Types;
public class JdbcColumnDefaultValueConverter {
+ /** The DDL rendering of the current timestamp default value function. */
protected static final String CURRENT_TIMESTAMP = "CURRENT_TIMESTAMP";
+
+ private static final Pattern FUNCTION_NAME =
+
Pattern.compile("[\\p{L}_][\\p{L}\\p{N}_$]*+(?:\\.[\\p{L}_][\\p{L}\\p{N}_$]*+){0,2}");
+
protected static final String NULL = "NULL";
protected static final DateTimeFormatter DATE_TIME_FORMATTER =
new DateTimeFormatterBuilder()
@@ -60,12 +67,32 @@ public class JdbcColumnDefaultValueConverter {
if (defaultValue instanceof FunctionExpression) {
FunctionExpression functionExpression = (FunctionExpression)
defaultValue;
- if
(functionExpression.functionName().equalsIgnoreCase(CURRENT_TIMESTAMP)) {
- // CURRENT_TIMESTAMP is a special case(key word), it should not be
wrapped in parentheses
+ String name = functionExpression.functionName();
+ Preconditions.checkArgument(
+ name != null && FUNCTION_NAME.matcher(name).matches(),
+ "JDBC column default function name must be a plain or qualified
identifier: %s",
+ name);
+ Expression[] arguments = functionExpression.arguments();
+ Preconditions.checkArgument(
+ arguments != null, "JDBC default function arguments must not be
null");
+ if (name.equalsIgnoreCase(CURRENT_TIMESTAMP)) {
+ Preconditions.checkArgument(
+ arguments.length == 0, "CURRENT_TIMESTAMP default does not accept
arguments");
return CURRENT_TIMESTAMP;
- } else {
- return String.format("(%s)", functionExpression);
}
+ String[] sqlArguments = new String[arguments.length];
+ for (int i = 0; i < arguments.length; i++) {
+ Preconditions.checkArgument(
+ arguments[i] instanceof Literal || arguments[i] instanceof
FunctionExpression,
+ "Unsupported JDBC column default function argument at index %s",
+ i);
+ sqlArguments[i] = fromGravitino(arguments[i]);
+ Preconditions.checkArgument(
+ sqlArguments[i] != null,
+ "JDBC column default function argument at index %s must have a
value",
+ i);
+ }
+ return "(" + name + "(" + String.join(", ", sqlArguments) + "))";
}
if (defaultValue instanceof Literal) {
diff --git
a/catalogs/catalog-jdbc-common/src/test/java/org/apache/gravitino/catalog/jdbc/converter/TestJdbcColumnDefaultValueConverter.java
b/catalogs/catalog-jdbc-common/src/test/java/org/apache/gravitino/catalog/jdbc/converter/TestJdbcColumnDefaultValueConverter.java
index a292ff0e6c..9733d67ef6 100644
---
a/catalogs/catalog-jdbc-common/src/test/java/org/apache/gravitino/catalog/jdbc/converter/TestJdbcColumnDefaultValueConverter.java
+++
b/catalogs/catalog-jdbc-common/src/test/java/org/apache/gravitino/catalog/jdbc/converter/TestJdbcColumnDefaultValueConverter.java
@@ -25,6 +25,8 @@ import java.time.LocalDateTime;
import java.time.OffsetDateTime;
import org.apache.gravitino.rel.Column;
import org.apache.gravitino.rel.expressions.Expression;
+import org.apache.gravitino.rel.expressions.FunctionExpression;
+import org.apache.gravitino.rel.expressions.UnparsedExpression;
import org.apache.gravitino.rel.expressions.literals.Literals;
import org.apache.gravitino.rel.types.Types;
import org.junit.jupiter.api.Assertions;
@@ -107,6 +109,75 @@ public class TestJdbcColumnDefaultValueConverter {
Assertions.assertNotNull(exception.getCause());
}
+ @Test
+ void testFunctionArgumentsAreRenderedAsSqlLiterals() {
+ Assertions.assertEquals(
+ "(abs(-3))",
+ converter.fromGravitino(FunctionExpression.of("abs",
Literals.integerLiteral(-3))));
+ Assertions.assertEquals(
+ "(pg_catalog.abs(-3))",
+ converter.fromGravitino(
+ FunctionExpression.of("pg_catalog.abs",
Literals.integerLiteral(-3))));
+ Assertions.assertEquals(
+ "(coalesce(NULL, 'O''Brien'))",
+ converter.fromGravitino(
+ FunctionExpression.of("coalesce", Literals.NULL,
Literals.stringLiteral("O'Brien"))));
+ Assertions.assertEquals(
+ "(date_trunc('day', (now())))",
+ converter.fromGravitino(
+ FunctionExpression.of(
+ "date_trunc", Literals.stringLiteral("day"),
FunctionExpression.of("now"))));
+ }
+
+ @ParameterizedTest
+ @ValueSource(
+ strings = {
+ "abs(1)); CREATE TABLE injected_marker(id int); --",
+ "abs, extra int",
+ "abs/*comment*/",
+ "abs--comment",
+ "abs()",
+ "",
+ "pg_catalog..abs"
+ })
+ void testFunctionNameMustBeAnIdentifier(String name) {
+ Assertions.assertThrows(
+ IllegalArgumentException.class, () ->
converter.fromGravitino(FunctionExpression.of(name)));
+ }
+
+ @Test
+ void testDeeplyQualifiedFunctionNameIsRejected() {
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
converter.fromGravitino(FunctionExpression.of("schema.".repeat(10000) +
"abs")));
+ Assertions.assertThrows(
+ IllegalArgumentException.class, () ->
converter.fromGravitino(FunctionExpression.of(null)));
+ }
+
+ @Test
+ void testFunctionArgumentsMustBeRenderableValues() {
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ converter.fromGravitino(
+ FunctionExpression.of(
+ "abs", UnparsedExpression.of("1); CREATE TABLE
injected_marker(id int); --"))));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () -> converter.fromGravitino(FunctionExpression.of("abs",
DEFAULT_VALUE_NOT_SET)));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () -> converter.fromGravitino(FunctionExpression.of("abs", new
Expression[] {null})));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () -> converter.fromGravitino(FunctionExpression.of("abs",
(Expression[]) null)));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ converter.fromGravitino(
+ FunctionExpression.of("CURRENT_TIMESTAMP",
Literals.integerLiteral(1))));
+ }
+
@Test
public void testFromGravitino() {
Expression expression = DEFAULT_VALUE_NOT_SET;
diff --git
a/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/converter/PostgreSqlColumnDefaultValueConverter.java
b/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/converter/PostgreSqlColumnDefaultValueConverter.java
index e475489895..aec79cff55 100644
---
a/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/converter/PostgreSqlColumnDefaultValueConverter.java
+++
b/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/converter/PostgreSqlColumnDefaultValueConverter.java
@@ -26,6 +26,7 @@ import com.google.common.io.BaseEncoding;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
+import java.util.Locale;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.annotation.Nullable;
@@ -37,18 +38,59 @@ import
org.apache.gravitino.rel.expressions.literals.Literals;
import org.apache.gravitino.rel.types.Decimal;
public class PostgreSqlColumnDefaultValueConverter extends
JdbcColumnDefaultValueConverter {
- public PostgreSqlColumnDefaultValueConverter() {}
-
- // for example:
- // '2021-10-01 00:00:00'::timestamp without time zone
- // ''something'::character varying'
+ private static final String TYPE_MODIFIER =
"(?:\\(\\d++(?:,\\s*\\d++)?\\))?";
+ // Only constant casts that the literal conversion below understands. An
arbitrary suffix would
+ // also match operators, COLLATE clauses, or another cast and silently
change their semantics.
+ private static final String LITERAL_TYPE =
+ "(?:(?:text|bpchar|varchar|character(?:
varying)?|boolean|bool|smallint|int2|integer|int4|bigint|int8|real|float4|double
precision|float8|numeric|decimal|date)"
+ + TYPE_MODIFIER
+ + "|(?:timestamp|time)"
+ + TYPE_MODIFIER
+ + "(?: (?:with|without) time zone)?|timestamptz|timetz)";
// Possessive repetition avoids recursive backtracking for long default
literals.
- private static final Pattern VALUE_WITH_TYPE =
Pattern.compile("'((?:[^']|'')*+)'::\\s*(.+)");
+ private static final Pattern QUOTED_LITERAL =
+ Pattern.compile(
+ "([eE]?)'((?:[^']|'')*+)'(?:\\s*::\\s*" + LITERAL_TYPE + ")?",
Pattern.CASE_INSENSITIVE);
+ // An unquoted constant on a string column, for example from `varchar(20)
DEFAULT (10)` or
+ // `text DEFAULT true`. PostgreSQL shows these as 10 or true, and the
assignment cast stores the
+ // same text. PostgreSQL normalizes other numeric forms, such as 1e3, before
showing them; this
+ // pattern also excludes them so it never guesses their stored text.
+ private static final Pattern STRING_COLUMN_CONSTANT =
+ Pattern.compile("\\d++(?:\\.\\d++)?|true|false");
+ private static final Pattern NULL_LITERAL =
+ Pattern.compile("NULL(?:\\s*::\\s*(.+))?", Pattern.CASE_INSENSITIVE |
Pattern.DOTALL);
- // for example:
- // NULL::character varying
- private static final String NULL_PREFIX = "NULL::";
+ public PostgreSqlColumnDefaultValueConverter() {}
+ /**
+ * Makes backslash escaping explicit for a complete quoted default read with
PostgreSQL's
+ * standard_conforming_strings disabled. PostgreSQL's catalog printer
doubles backslashes in that
+ * mode without adding an E prefix. Other expressions are preserved without
rewriting fragments.
+ *
+ * @param defaultValue the catalog default expression, or null when absent
+ * @return the equivalent explicit escape literal, or the unchanged
expression
+ */
+ @Nullable
+ public static String withExplicitBackslashEscapes(@Nullable String
defaultValue) {
+ return defaultValue != null
+ && defaultValue.startsWith("'")
+ && defaultValue.indexOf('\\') >= 0
+ && QUOTED_LITERAL.matcher(defaultValue).matches()
+ ? "E" + defaultValue
+ : defaultValue;
+ }
+
+ /**
+ * Converts a default deparsed with standard_conforming_strings enabled, or
a literal with
+ * explicit E escaping. Readers using nonstandard strings must first
normalize complete quoted
+ * literals with {@link #withExplicitBackslashEscapes(String)}. Other
expressions remain unparsed.
+ *
+ * @param type the column's JDBC type
+ * @param columnDefaultValue the default expression, or null when absent
+ * @param isExpression whether the column is generated from an expression
+ * @param nullable whether the column is nullable
+ * @return the literal, recognized current-timestamp default, or preserved
expression
+ */
@Override
public Expression toGravitino(
JdbcTypeConverter.JdbcTypeBean type,
@@ -65,12 +107,13 @@ public class PostgreSqlColumnDefaultValueConverter extends
JdbcColumnDefaultValu
return UnparsedExpression.of(columnDefaultValue);
}
+ // Checked before literal parsing so string columns also report the
function, not its name.
+ if (columnDefaultValue.equals(CURRENT_TIMESTAMP)) {
+ return DEFAULT_VALUE_OF_CURRENT_TIMESTAMP;
+ }
try {
return parseLiteral(type, columnDefaultValue);
} catch (Exception e) {
- if (columnDefaultValue.equals(CURRENT_TIMESTAMP)) {
- return DEFAULT_VALUE_OF_CURRENT_TIMESTAMP;
- }
return UnparsedExpression.of(columnDefaultValue);
}
}
@@ -86,13 +129,13 @@ public class PostgreSqlColumnDefaultValueConverter extends
JdbcColumnDefaultValu
return sql;
}
- /**
- * Doubles single quotes only. With {@code standard_conforming_strings} on,
the default since
- * PostgreSQL 9.1, a backslash in a string literal is an ordinary character.
- */
+ /** Uses explicit escape strings for backslashes, independently of the
server string setting. */
@Override
protected String quoteStringLiteral(String value) {
- return "'" + value.replace("'", "''") + "'";
+ String escaped = value.replace("'", "''");
+ return value.indexOf('\\') >= 0
+ ? "E'" + escaped.replace("\\", "\\\\") + "'"
+ : "'" + escaped + "'";
}
/** {@inheritDoc} */
@@ -103,18 +146,42 @@ public class PostgreSqlColumnDefaultValueConverter
extends JdbcColumnDefaultValu
}
private Expression parseLiteral(JdbcTypeConverter.JdbcTypeBean type, String
columnDefaultValue) {
- Matcher matcher = VALUE_WITH_TYPE.matcher(columnDefaultValue);
- if (matcher.find()) {
- // String literals in the catalog are shown with embedded single quotes
doubled.
- columnDefaultValue = matcher.group(1).replace("''", "'");
- }
-
- if (columnDefaultValue.startsWith(NULL_PREFIX)) {
+ String expression = columnDefaultValue;
+ Matcher nullLiteral = NULL_LITERAL.matcher(expression);
+ if (nullLiteral.matches()
+ && (nullLiteral.group(1) == null
+ ||
PostgreSqlTypeConverter.isSingleTypeDeclaration(nullLiteral.group(1)))) {
return Literals.NULL;
}
+ Matcher matcher = QUOTED_LITERAL.matcher(expression);
+ boolean quoted = matcher.matches();
+ if (quoted) {
+ // String literals in the catalog are shown with embedded single quotes
doubled.
+ String content = matcher.group(2).replace("''", "'");
+ if (!matcher.group(1).isEmpty()) {
+ StringBuilder decoded = new StringBuilder();
+ for (int i = 0; i < content.length(); i++) {
+ char character = content.charAt(i);
+ if (character == '\\') {
+ // The renderer and metadata normalization make literal backslash
pairs explicit.
+ // Preserve other escape syntaxes instead of guessing their
decoded value.
+ if (++i >= content.length() || content.charAt(i) != '\\') {
+ return UnparsedExpression.of(columnDefaultValue);
+ }
+ }
+ decoded.append(character);
+ }
+ content = decoded.toString();
+ }
+ columnDefaultValue = content;
+ }
- switch (type.getTypeName().toLowerCase()) {
+ switch (type.getTypeName().toLowerCase(Locale.ROOT)) {
case PostgreSqlTypeConverter.BOOL:
+ if (!"true".equalsIgnoreCase(columnDefaultValue)
+ && !"false".equalsIgnoreCase(columnDefaultValue)) {
+ return UnparsedExpression.of(expression);
+ }
return Literals.booleanLiteral(Boolean.valueOf(columnDefaultValue));
case PostgreSqlTypeConverter.INT_2:
return Literals.shortLiteral(Short.valueOf(columnDefaultValue));
@@ -138,13 +205,18 @@ public class PostgreSqlColumnDefaultValueConverter
extends JdbcColumnDefaultValu
return Literals.timestampLiteral(
LocalDateTime.parse(columnDefaultValue, DATE_TIME_FORMATTER));
case VARCHAR:
+ if (!quoted && !STRING_COLUMN_CONSTANT.matcher(expression).matches()) {
+ return UnparsedExpression.of(expression);
+ }
Integer length = type.getColumnSize();
return length == null || length <= 0 || length == Integer.MAX_VALUE
? Literals.stringLiteral(columnDefaultValue)
: Literals.varcharLiteral(length, columnDefaultValue);
case PostgreSqlTypeConverter.BPCHAR:
case JdbcTypeConverter.TEXT:
- return Literals.stringLiteral(columnDefaultValue);
+ return quoted || STRING_COLUMN_CONSTANT.matcher(expression).matches()
+ ? Literals.stringLiteral(columnDefaultValue)
+ : UnparsedExpression.of(expression);
default:
throw new IllegalArgumentException("Unknown data type for literal: " +
type);
}
diff --git
a/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/converter/PostgreSqlTypeConverter.java
b/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/converter/PostgreSqlTypeConverter.java
index cfd1c3db35..7c8b3f6325 100644
---
a/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/converter/PostgreSqlTypeConverter.java
+++
b/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/converter/PostgreSqlTypeConverter.java
@@ -21,6 +21,8 @@ package org.apache.gravitino.catalog.postgresql.converter;
import com.google.common.annotations.VisibleForTesting;
import com.google.common.base.Preconditions;
import java.util.Optional;
+import java.util.regex.Pattern;
+import javax.annotation.Nullable;
import org.apache.gravitino.catalog.jdbc.converter.JdbcTypeConverter;
import org.apache.gravitino.rel.types.Type;
import org.apache.gravitino.rel.types.Types;
@@ -48,6 +50,31 @@ public class PostgreSqlTypeConverter extends
JdbcTypeConverter {
private static final int MAX_NUMERIC_PRECISION = 1000;
+ // Match a type declaration, not an arbitrary SQL fragment. Quoted
identifiers may contain SQL
+ // punctuation safely; modifier tokens and array suffixes cannot introduce
DDL clauses.
+ private static final String TYPE_IDENTIFIER =
+ "(?:[\\p{L}_][\\p{L}\\p{N}_$]*|\"(?:[^\"]|\"\")++\")";
+ private static final String TYPE_MODIFIER = "(?:[+-]?\\d++|" +
TYPE_IDENTIFIER + ")";
+ private static final String TYPE_MODIFIERS =
+ "(?:\\s*\\(\\s*" + TYPE_MODIFIER + "(?:\\s*,\\s*" + TYPE_MODIFIER +
")*+\\s*\\))?";
+ private static final Pattern EXTERNAL_TYPE_DECLARATION =
+ Pattern.compile(
+ "(?:"
+ + TYPE_IDENTIFIER
+ + "(?:\\s*\\.\\s*"
+ + TYPE_IDENTIFIER
+ + ")?"
+ + TYPE_MODIFIERS
+ +
"|(?:double\\s+precision|(?:character|char|bit)\\s+varying|national\\s+(?:character|char)(?:\\s+varying)?)"
+ + TYPE_MODIFIERS
+ + "|(?:timestamp|time)"
+ + TYPE_MODIFIERS
+ + "\\s+(?:with|without)\\s+time\\s+zone"
+ +
"|interval\\s+(?:year|month|day|hour|minute|second)(?:\\s+to\\s+(?:month|day|hour|minute|second))?"
+ + TYPE_MODIFIERS
+ +
")(?:\\s+ARRAY(?:\\s*\\[\\s*\\d*+\\s*\\])?|(?:\\s*\\[\\s*\\d*+\\s*\\])*+)",
+ Pattern.CASE_INSENSITIVE);
+
@Override
public Type toGravitino(JdbcTypeBean typeBean) {
String typeName = typeBean.getTypeName().toLowerCase();
@@ -177,13 +204,24 @@ public class PostgreSqlTypeConverter extends
JdbcTypeConverter {
} else if (type instanceof Types.ListType) {
return fromGravitinoArrayType((ListType) type);
} else if (type instanceof Types.ExternalType) {
- return ((Types.ExternalType) type).catalogString();
+ String declaration = ((Types.ExternalType) type).catalogString();
+ Preconditions.checkArgument(
+ isSingleTypeDeclaration(declaration),
+ "PostgreSQL external type must be a single type declaration without
DDL clauses: %s",
+ declaration);
+ return declaration;
}
throw new IllegalArgumentException(
String.format(
"Couldn't convert Gravitino type %s to PostgreSQL type",
type.simpleString()));
}
+ static boolean isSingleTypeDeclaration(@Nullable String declaration) {
+ return declaration != null
+ && declaration.indexOf('\0') < 0
+ && EXTERNAL_TYPE_DECLARATION.matcher(declaration.trim()).matches();
+ }
+
// PG doesn't support the multidimensional array internally. The current
implementation does not
// enforce the declared number of dimensions either. Arrays of a particular
element type are all
// considered to be of the same type, regardless of size or number of
dimensions. So, declaring
diff --git
a/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/operation/PostgreSqlTableOperations.java
b/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/operation/PostgreSqlTableOperations.java
index e5ceaff71d..ddd92fa738 100644
---
a/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/operation/PostgreSqlTableOperations.java
+++
b/catalogs/catalog-jdbc-postgresql/src/main/java/org/apache/gravitino/catalog/postgresql/operation/PostgreSqlTableOperations.java
@@ -57,6 +57,7 @@ import
org.apache.gravitino.catalog.jdbc.operation.DatabaseOperation;
import org.apache.gravitino.catalog.jdbc.operation.JdbcTableOperations;
import org.apache.gravitino.catalog.jdbc.operation.RequireDatabaseOperation;
import org.apache.gravitino.catalog.jdbc.utils.JdbcConnectorUtils;
+import
org.apache.gravitino.catalog.postgresql.converter.PostgreSqlColumnDefaultValueConverter;
import org.apache.gravitino.exceptions.ForbiddenException;
import org.apache.gravitino.exceptions.NoSuchColumnException;
import org.apache.gravitino.exceptions.NoSuchSchemaException;
@@ -72,6 +73,7 @@ import
org.apache.gravitino.rel.expressions.transforms.Transform;
import org.apache.gravitino.rel.indexes.Index;
import org.apache.gravitino.rel.types.Type;
import org.apache.gravitino.rel.types.Types;
+import org.postgresql.PGConnection;
/** Table operations for PostgreSQL. */
public class PostgreSqlTableOperations extends JdbcTableOperations
@@ -85,6 +87,16 @@ public class PostgreSqlTableOperations extends
JdbcTableOperations
public static final String COLUMN_COMMENT = "COMMENT ON COLUMN ";
public static final String TABLE_COMMENT = "COMMENT ON TABLE ";
+ private static final String STANDALONE_UNIQUE_INDEX_SQL =
+ "SELECT 1 FROM pg_catalog.pg_index i "
+ + "JOIN pg_catalog.pg_class idx ON idx.oid = i.indexrelid "
+ + "JOIN pg_catalog.pg_class t ON t.oid = i.indrelid "
+ + "JOIN pg_catalog.pg_namespace s ON s.oid = t.relnamespace "
+ + "WHERE s.nspname = ? AND t.relname = ? AND idx.relname = ? AND
i.indisunique "
+ + "AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_constraint c "
+ + "WHERE c.conindid = i.indexrelid AND c.conrelid = i.indrelid "
+ + "AND c.contype IN ('p', 'u'))";
+
private static final String POSTGRESQL_NOT_SUPPORT_NESTED_COLUMN_MSG =
"PostgreSQL does not support nested column names.";
@@ -440,7 +452,9 @@ public class PostgreSqlTableOperations extends
JdbcTableOperations
} else if (change instanceof TableChange.AddIndex) {
alterSql.add(addIndexDefinition(tableName, (TableChange.AddIndex)
change));
} else if (change instanceof TableChange.DeleteIndex) {
- alterSql.add(deleteIndexDefinition(tableName,
(TableChange.DeleteIndex) change));
+ TableChange.DeleteIndex deleteIndex = (TableChange.DeleteIndex) change;
+ rejectStandaloneUniqueIndex(schemaName, tableName,
deleteIndex.getName());
+ alterSql.add(deleteIndexDefinition(tableName, deleteIndex));
} else if (change instanceof TableChange.UpdateColumnAutoIncrement) {
alterSql.add(
updateColumnAutoIncrementDefinition(
@@ -510,6 +524,26 @@ public class PostgreSqlTableOperations extends
JdbcTableOperations
return sqlBuilder.toString();
}
+ private void rejectStandaloneUniqueIndex(String schema, String table, String
index) {
+ // The table-scoped constraint drop cannot remove a standalone index.
Refuse both strict and
+ // IF EXISTS requests rather than silently succeeding or dropping a
schema-scoped index name.
+ // This lookup runs during SQL generation, before borrowing the DDL
execution connection.
+ try (Connection connection = getConnection(schema);
+ PreparedStatement statement =
connection.prepareStatement(STANDALONE_UNIQUE_INDEX_SQL)) {
+ statement.setString(1, schema);
+ statement.setString(2, table);
+ statement.setString(3, index);
+ try (ResultSet result = statement.executeQuery()) {
+ Preconditions.checkArgument(
+ !result.next(),
+ "Index %s is a standalone unique index, not a table constraint;
deleting it through the table index API is not supported",
+ index);
+ }
+ } catch (SQLException e) {
+ throw exceptionMapper.toGravitinoException(e);
+ }
+ }
+
private String updateColumnNullabilityDefinition(
TableChange.UpdateColumnNullability updateColumnNullability, String
tableName) {
if (updateColumnNullability.fieldName().length > 1) {
@@ -813,7 +847,24 @@ public class PostgreSqlTableOperations extends
JdbcTableOperations
}
boolean needsDeclarations = false;
+ Boolean standardConformingStrings = null;
while (columns.next()) {
+ String defaultValue = columns.getString("COLUMN_DEF");
+ String escaped =
+
PostgreSqlColumnDefaultValueConverter.withExplicitBackslashEscapes(defaultValue);
+ if (!Objects.equals(defaultValue, escaped)) {
+ if (standardConformingStrings == null) {
+ // pgJDBC's literal escaper uses the cached
standard_conforming_strings setting.
+ // A single backslash stays unchanged only in standard mode. This
public API also works
+ // with older drivers without the parameter-status API, with no
query or session change.
+ standardConformingStrings =
+
"\\".equals(connection.unwrap(PGConnection.class).escapeLiteral("\\"));
+ }
+ if (!standardConformingStrings) {
+ columns.updateString("COLUMN_DEF", escaped);
+ columns.updateRow();
+ }
+ }
String type = columns.getString("TYPE_NAME");
if ("varchar".equals(type)
|| "bpchar".equals(type)
@@ -821,7 +872,6 @@ public class PostgreSqlTableOperations extends
JdbcTableOperations
|| "_bpchar".equals(type)
|| (getAutoIncrementInfo(columns) && columns.getString("COLUMN_DEF")
!= null)) {
needsDeclarations = true;
- break;
}
}
columns.beforeFirst();
diff --git
a/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/converter/TestPostgreSqlColumnDefaultValueConverter.java
b/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/converter/TestPostgreSqlColumnDefaultValueConverter.java
index f23c6ad07d..c6a9b74d67 100644
---
a/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/converter/TestPostgreSqlColumnDefaultValueConverter.java
+++
b/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/converter/TestPostgreSqlColumnDefaultValueConverter.java
@@ -18,8 +18,11 @@
*/
package org.apache.gravitino.catalog.postgresql.converter;
+import static
org.apache.gravitino.rel.Column.DEFAULT_VALUE_OF_CURRENT_TIMESTAMP;
+
import com.google.common.io.BaseEncoding;
import org.apache.gravitino.catalog.jdbc.converter.JdbcTypeConverter;
+import org.apache.gravitino.rel.expressions.UnparsedExpression;
import org.apache.gravitino.rel.expressions.literals.Literals;
import org.apache.gravitino.rel.types.Types;
import org.junit.jupiter.api.Assertions;
@@ -44,6 +47,100 @@ public class TestPostgreSqlColumnDefaultValueConverter {
converter.fromGravitino(Literals.of("0x" + hex,
Types.BinaryType.get())));
}
+ @Test
+ void testNullDefaultsAllowNativeCastsWithoutSwallowingExpressions() {
+ JdbcTypeConverter.JdbcTypeBean type = new
JdbcTypeConverter.JdbcTypeBean("uuid");
+ for (String sql :
+ new String[] {"NULL", "NULL::uuid", "NULL::integer[]",
"NULL::\"schema\".\"domain\""}) {
+ Assertions.assertEquals(Literals.NULL, converter.toGravitino(type, sql,
false, true));
+ }
+ String expression = "NULL::uuid IS NULL";
+ Assertions.assertEquals(
+ UnparsedExpression.of(expression), converter.toGravitino(type,
expression, false, true));
+ }
+
+ @Test
+ void testBackslashNormalizationDoesNotRewriteExpressions() {
+ String literal = "'a\\\\b'::text";
+ Assertions.assertEquals(
+ "E" + literal,
PostgreSqlColumnDefaultValueConverter.withExplicitBackslashEscapes(literal));
+ for (String expression :
+ new String[] {
+ null,
+ "'plain'::text",
+ "E'a\\\\b'::text",
+ "upper('a\\\\b'::text)",
+ "'a\\\\b'::text || 'tail'::text"
+ }) {
+ Assertions.assertEquals(
+ expression,
+
PostgreSqlColumnDefaultValueConverter.withExplicitBackslashEscapes(expression));
+ }
+ }
+
+ @Test
+ void testExpressionsAreNotMistakenForLiterals() {
+ JdbcTypeConverter.JdbcTypeBean text = new
JdbcTypeConverter.JdbcTypeBean("text");
+ for (String sql :
+ new String[] {
+ "upper('x'::text)", "concat('a'::text, 'b'::text)", "'a'::text ||
'b'::text",
+ "md5(random()::text)", "('x'::text)::character varying(1)",
"NULL::text || 'x'::text"
+ }) {
+ Assertions.assertEquals(
+ UnparsedExpression.of(sql), converter.toGravitino(text, sql, false,
true), sql);
+ }
+ String condition = "(random() > 0.5)";
+ Assertions.assertEquals(
+ UnparsedExpression.of(condition),
+ converter.toGravitino(new JdbcTypeConverter.JdbcTypeBean("bool"),
condition, false, true));
+ Assertions.assertEquals(
+ Literals.stringLiteral("NULL::text"),
+ converter.toGravitino(text, "'NULL::text'::text", false, true));
+ Assertions.assertEquals(Literals.NULL, converter.toGravitino(text,
"NULL::text", false, true));
+ Assertions.assertEquals(
+ Literals.stringLiteral("plain"), converter.toGravitino(text,
"'plain'", false, true));
+ }
+
+ @Test
+ void testStringColumnConstantsAndFunctionsKeepTheirMeaning() {
+ JdbcTypeConverter.JdbcTypeBean varchar = new
JdbcTypeConverter.JdbcTypeBean("varchar");
+ varchar.setColumnSize(20);
+ JdbcTypeConverter.JdbcTypeBean text = new
JdbcTypeConverter.JdbcTypeBean("text");
+ // PostgreSQL shows `varchar(20) DEFAULT (10)` as 10; the stored value is
the text '10'.
+ Assertions.assertEquals(
+ Literals.varcharLiteral(20, "10"), converter.toGravitino(varchar,
"10", false, true));
+ Assertions.assertEquals(
+ Literals.stringLiteral("1.50"), converter.toGravitino(text, "1.50",
false, true));
+ // PostgreSQL shows `text DEFAULT true` as true; the stored value is the
text 'true'.
+ Assertions.assertEquals(
+ Literals.stringLiteral("true"), converter.toGravitino(text, "true",
false, true));
+ Assertions.assertEquals(
+ Literals.varcharLiteral(20, "false"), converter.toGravitino(varchar,
"false", false, true));
+ // Forms that PostgreSQL never shows this way, or whose stored text could
differ, are not
+ // guessed.
+ for (String sql : new String[] {"1e3", ".5", "10 + 1", "TRUE", "TRUE AND
false"}) {
+ Assertions.assertEquals(
+ UnparsedExpression.of(sql), converter.toGravitino(text, sql, false,
true), sql);
+ }
+ Assertions.assertEquals(
+ UnparsedExpression.of("CURRENT_DATE"),
+ converter.toGravitino(varchar, "CURRENT_DATE", false, true));
+ Assertions.assertEquals(
+ DEFAULT_VALUE_OF_CURRENT_TIMESTAMP,
+ converter.toGravitino(varchar, "CURRENT_TIMESTAMP", false, true));
+ Assertions.assertEquals(
+ Literals.varcharLiteral(20, "CURRENT_TIMESTAMP"),
+ converter.toGravitino(varchar, "'CURRENT_TIMESTAMP'::character
varying", false, true));
+ }
+
+ @Test
+ void testOtherEscapeStringSyntaxIsPreservedAsExpression() {
+ JdbcTypeConverter.JdbcTypeBean type = new
JdbcTypeConverter.JdbcTypeBean("text");
+ String sql = "E'line\\n'::text";
+ Assertions.assertEquals(
+ UnparsedExpression.of(sql), converter.toGravitino(type, sql, false,
true));
+ }
+
@Test
void testNulStringDefaultIsRejectedBeforeDdl() {
IllegalArgumentException error =
@@ -53,7 +150,7 @@ public class TestPostgreSqlColumnDefaultValueConverter {
Assertions.assertTrue(error.getMessage().contains("default"));
Assertions.assertTrue(error.getMessage().contains("U+0000"));
Assertions.assertEquals(
- "'before\\u0000after'",
+ "E'before\\\\u0000after'",
converter.fromGravitino(Literals.stringLiteral("before\\u0000after")));
Assertions.assertEquals("NULL", converter.fromGravitino(Literals.NULL));
}
@@ -72,7 +169,7 @@ public class TestPostgreSqlColumnDefaultValueConverter {
@Test
public void testStringLiteralDoublesQuotesOnly() {
Assertions.assertEquals(
- "'O''Brien \\path'",
converter.fromGravitino(Literals.stringLiteral("O'Brien \\path")));
+ "E'O''Brien \\\\path'",
converter.fromGravitino(Literals.stringLiteral("O'Brien \\path")));
}
@Test
diff --git
a/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/converter/TestPostgreSqlTypeConverter.java
b/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/converter/TestPostgreSqlTypeConverter.java
index 0fcdb2e261..1a1e58c03a 100644
---
a/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/converter/TestPostgreSqlTypeConverter.java
+++
b/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/converter/TestPostgreSqlTypeConverter.java
@@ -52,7 +52,88 @@ public class TestPostgreSqlTypeConverter {
private static final PostgreSqlTypeConverter POSTGRE_SQL_TYPE_CONVERTER =
new PostgreSqlTypeConverter();
- private static final String USER_DEFINED_TYPE = "user-defined";
+ private static final String USER_DEFINED_TYPE = "user_defined";
+
+ @Test
+ void testExternalTypeMustBeASingleDeclaration() {
+ for (String declaration :
+ new String[] {
+ "int); CREATE TABLE injected_marker(id int); --",
+ "int, extra int",
+ "int DEFAULT 1",
+ "int NOT NULL",
+ "int CHECK (true)",
+ "int UNIQUE",
+ "int/*comment*/",
+ "int--comment",
+ "numeric(10,2), extra text",
+ "int;SELECT 1",
+ "geometry(Point,4326), extra int",
+ "geometry(Point,4326) DEFAULT NULL",
+ "geometry(Point,abs(4326))",
+ "geometry(Point,4326 /*comment*/)",
+ "int ARRAY NOT NULL",
+ "\"closed\";SELECT 1",
+ "int\u0000",
+ "",
+ " "
+ }) {
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
POSTGRE_SQL_TYPE_CONVERTER.fromGravitino(Types.ExternalType.of(declaration)),
+ declaration);
+ }
+ }
+
+ @Test
+ void testNativeTypeDeclarationsRemainSupported() {
+ for (String declaration :
+ new String[] {
+ "inet",
+ "national character(3)",
+ "national char(3)",
+ "national character varying(3)",
+ "int ARRAY",
+ "int ARRAY[3]",
+ "int ARRAY[]",
+ "geometry(Point,4326)",
+ "public.geometry(PointZ,4326)[]",
+ "geography(LINESTRING,4326)",
+ "numeric",
+ "numeric(1000,0)",
+ "numeric(2,-3)",
+ "numeric(3,5)[]",
+ "pg_catalog.int4",
+ "\"custom schema\".\"custom type\"[]",
+ "\"semi;colon\"",
+ "\"quote\"\"inside\"",
+ "\"user-defined\"",
+ "character varying(12)",
+ "double precision[]",
+ "timestamp(6) without time zone",
+ "time(3) with time zone",
+ "bit varying(8)",
+ "interval day to second(6)",
+ "interval year to month"
+ }) {
+ Assertions.assertEquals(
+ declaration,
+
POSTGRE_SQL_TYPE_CONVERTER.fromGravitino(Types.ExternalType.of(declaration)),
+ declaration);
+ }
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ POSTGRE_SQL_TYPE_CONVERTER.fromGravitino(
+ Types.ListType.nullable(Types.ExternalType.of("int, extra
int"))));
+ }
+
+ @Test
+ void testLongArrayDeclarationDoesNotOverflowTheValidatorStack() {
+ String declaration = "int" + "[]".repeat(10000);
+ Assertions.assertEquals(
+ declaration,
POSTGRE_SQL_TYPE_CONVERTER.fromGravitino(Types.ExternalType.of(declaration)));
+ }
@Test
void testUnboundedVarcharDriverSentinelMapsToString() {
diff --git
a/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/integration/test/CatalogPostgreSqlIT.java
b/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/integration/test/CatalogPostgreSqlIT.java
index dd80fd5b62..bac596faf7 100644
---
a/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/integration/test/CatalogPostgreSqlIT.java
+++
b/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/integration/test/CatalogPostgreSqlIT.java
@@ -1093,12 +1093,11 @@ public class CatalogPostgreSqlIT extends BaseIT {
Assertions.assertEquals(Literals.varcharLiteral(200, "curdate()"),
column.defaultValue());
break;
case "varchar200_col_2":
- Assertions.assertEquals(
- Literals.varcharLiteral(200, "CURRENT_DATE"),
column.defaultValue());
+ // An expression default must not be mistaken for a string literal
of the same text.
+ Assertions.assertEquals(UnparsedExpression.of("CURRENT_DATE"),
column.defaultValue());
break;
case "varchar200_col_3":
- Assertions.assertEquals(
- Literals.varcharLiteral(200, "CURRENT_TIMESTAMP"),
column.defaultValue());
+ Assertions.assertEquals(DEFAULT_VALUE_OF_CURRENT_TIMESTAMP,
column.defaultValue());
break;
case "date_col_1":
Assertions.assertEquals(UnparsedExpression.of("CURRENT_DATE"),
column.defaultValue());
diff --git
a/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/operation/TestPostgreSqlTableOperations.java
b/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/operation/TestPostgreSqlTableOperations.java
index 58c9ec2eff..8c59e49292 100644
---
a/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/operation/TestPostgreSqlTableOperations.java
+++
b/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/operation/TestPostgreSqlTableOperations.java
@@ -21,9 +21,11 @@ package org.apache.gravitino.catalog.postgresql.operation;
import com.google.common.collect.Maps;
import com.google.common.io.BaseEncoding;
import java.sql.Connection;
+import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
+import java.time.LocalTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
@@ -40,7 +42,10 @@ import org.apache.gravitino.catalog.jdbc.config.JdbcConfig;
import org.apache.gravitino.catalog.jdbc.utils.DataSourceUtils;
import org.apache.gravitino.catalog.jdbc.utils.JdbcConnectorUtils;
import
org.apache.gravitino.catalog.postgresql.converter.PostgreSqlColumnDefaultValueConverter;
+import
org.apache.gravitino.catalog.postgresql.converter.PostgreSqlExceptionConverter;
import
org.apache.gravitino.catalog.postgresql.converter.PostgreSqlTypeConverter;
+import org.apache.gravitino.dto.rel.expressions.LiteralDTO;
+import org.apache.gravitino.dto.requests.TableUpdateRequest;
import org.apache.gravitino.dto.util.DTOConverters;
import org.apache.gravitino.exceptions.ForbiddenException;
import org.apache.gravitino.exceptions.GravitinoRuntimeException;
@@ -49,6 +54,7 @@ import org.apache.gravitino.exceptions.NoSuchTableException;
import org.apache.gravitino.exceptions.TableAlreadyExistsException;
import org.apache.gravitino.rel.Column;
import org.apache.gravitino.rel.TableChange;
+import org.apache.gravitino.rel.expressions.FunctionExpression;
import org.apache.gravitino.rel.expressions.UnparsedExpression;
import org.apache.gravitino.rel.expressions.distributions.Distributions;
import org.apache.gravitino.rel.expressions.literals.Literals;
@@ -64,6 +70,9 @@ import org.junit.jupiter.api.Tag;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.ValueSource;
+import org.mockito.AdditionalAnswers;
+import org.mockito.Mockito;
+import org.postgresql.PGConnection;
@Tag("gravitino-docker-test")
public class TestPostgreSqlTableOperations extends TestPostgreSql {
@@ -71,6 +80,572 @@ public class TestPostgreSqlTableOperations extends
TestPostgreSql {
private static Type VARCHAR = Types.VarCharType.of(255);
private static Type INT = Types.IntegerType.get();
+ @ParameterizedTest
+ @ValueSource(strings = {"on", "off"})
+ void testBackslashLiteralRoundTripsUnderReadSetting(String setting) throws
Exception {
+ DataSource source = Mockito.mock(DataSource.class);
+ Mockito.when(source.getConnection())
+ .thenAnswer(
+ invocation -> {
+ Connection c =
+ DriverManager.getConnection(
+ CONTAINER.getJdbcUrl(), CONTAINER.getUsername(),
CONTAINER.getPassword());
+ try (Statement s = c.createStatement()) {
+ s.execute("SET standard_conforming_strings = " + setting);
+ }
+ return c;
+ });
+ PostgreSqlTableOperations ops = new PostgreSqlTableOperations();
+ ops.initialize(
+ source,
+ new PostgreSqlExceptionConverter(),
+ new PostgreSqlTypeConverter(),
+ new PostgreSqlColumnDefaultValueConverter(),
+ Map.of(JdbcConfig.JDBC_DATABASE.getKey(), TEST_DB_NAME));
+ String table = RandomNameUtils.genRandomName("backslash_default_");
+ String[] values = {"a\\b", "a\\\\b", "quote' \\path\nnext", "NULL::text"};
+ for (String value : values) {
+ try {
+ ops.create(
+ TEST_DB_NAME,
+ table,
+ new JdbcColumn[] {
+
JdbcColumn.builder().withName("first").withType(VARCHAR).withNullable(true).build(),
+ JdbcColumn.builder()
+ .withName("s")
+ .withType(Types.StringType.get())
+ .withNullable(true)
+ .withDefaultValue(Literals.stringLiteral(value))
+ .build(),
+ JdbcColumn.builder()
+ .withName("another")
+ .withType(Types.StringType.get())
+ .withNullable(true)
+ .withDefaultValue(Literals.stringLiteral(value))
+ .build()
+ },
+ null,
+ Map.of(),
+ null,
+ Distributions.NONE,
+ Indexes.EMPTY_INDEXES);
+ try (Connection c = source.getConnection();
+ Statement s = c.createStatement()) {
+ s.executeUpdate("INSERT INTO " + TEST_DB_NAME + "." + table + "
DEFAULT VALUES");
+ try (ResultSet row = s.executeQuery("SELECT s FROM " + TEST_DB_NAME
+ "." + table)) {
+ Assertions.assertTrue(row.next());
+ Assertions.assertEquals(value, row.getString(1));
+ }
+ try (ResultSet metadata =
+ c.getMetaData().getColumns(c.getCatalog(), TEST_DB_NAME, table,
"s")) {
+ Assertions.assertTrue(metadata.next());
+ String raw = metadata.getString("COLUMN_DEF");
+ Assertions.assertFalse(raw.startsWith("E'"));
+ }
+ }
+ JdbcTable loaded = ops.load(TEST_DB_NAME, table);
+ Assertions.assertEquals(Literals.stringLiteral(value),
loaded.columns()[1].defaultValue());
+ Assertions.assertEquals(Literals.stringLiteral(value),
loaded.columns()[2].defaultValue());
+ try (Connection c = source.getConnection()) {
+ PGConnection pg =
+ Mockito.mock(
+ PGConnection.class,
AdditionalAnswers.delegatesTo(c.unwrap(PGConnection.class)));
+ Connection wrapped = Mockito.mock(Connection.class,
AdditionalAnswers.delegatesTo(c));
+ Mockito.doReturn(pg).when(wrapped).unwrap(PGConnection.class);
+ try (ResultSet columns = ops.getColumns(wrapped, TEST_DB_NAME,
table)) {
+ Assertions.assertTrue(columns.next());
+ }
+ Mockito.verify(pg, Mockito.times(value.indexOf('\\') >= 0 ? 1 :
0)).escapeLiteral("\\");
+ try (Statement statement = c.createStatement();
+ ResultSet result = statement.executeQuery("SHOW
standard_conforming_strings")) {
+ Assertions.assertTrue(result.next());
+ Assertions.assertEquals(setting, result.getString(1));
+ }
+ }
+ } finally {
+ ops.drop(TEST_DB_NAME, table);
+ }
+ }
+ }
+
+ @Test
+ void testFunctionDefaultsRemainExpressionsAfterCreateAndAlter() throws
SQLException {
+ String table = RandomNameUtils.genRandomName("function_read_");
+ try {
+ TABLE_OPERATIONS.create(
+ TEST_DB_NAME,
+ table,
+ new JdbcColumn[] {
+ JdbcColumn.builder()
+ .withName("s")
+ .withType(Types.StringType.get())
+ .withNullable(true)
+ .withDefaultValue(FunctionExpression.of("upper",
Literals.stringLiteral("x")))
+ .build()
+ },
+ null,
+ Map.of(),
+ null,
+ Distributions.NONE,
+ Indexes.EMPTY_INDEXES);
+ Assertions.assertEquals(
+ UnparsedExpression.of("upper('x'::text)"),
+ TABLE_OPERATIONS.load(TEST_DB_NAME,
table).columns()[0].defaultValue());
+ TABLE_OPERATIONS.alterTable(
+ TEST_DB_NAME,
+ table,
+ TableChange.addColumn(
+ new String[] {"added"},
+ Types.StringType.get(),
+ null,
+ TableChange.ColumnPosition.defaultPos(),
+ true,
+ false,
+ FunctionExpression.of("lower", Literals.stringLiteral("Y"))));
+ Assertions.assertEquals(
+ UnparsedExpression.of("lower('Y'::text)"),
+ TABLE_OPERATIONS.load(TEST_DB_NAME,
table).columns()[1].defaultValue());
+ TABLE_OPERATIONS.alterTable(
+ TEST_DB_NAME,
+ table,
+ TableChange.updateColumnDefaultValue(
+ new String[] {"added"}, FunctionExpression.of("upper",
Literals.stringLiteral("z"))));
+ Assertions.assertEquals(
+ UnparsedExpression.of("upper('z'::text)"),
+ TABLE_OPERATIONS.load(TEST_DB_NAME,
table).columns()[1].defaultValue());
+ try (Connection connection = DATA_SOURCE.getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.executeUpdate("INSERT INTO " + TEST_DB_NAME + "." + table +
" DEFAULT VALUES");
+ try (ResultSet result =
+ statement.executeQuery("SELECT s,added FROM " + TEST_DB_NAME + "."
+ table)) {
+ Assertions.assertTrue(result.next());
+ Assertions.assertEquals("X", result.getString(1));
+ Assertions.assertEquals("Z", result.getString(2));
+ }
+ }
+ } finally {
+ TABLE_OPERATIONS.drop(TEST_DB_NAME, table);
+ }
+ }
+
+ @Test
+ void testStandaloneUniqueIndexDeletionIsExplicitlyRejected() throws
SQLException {
+ String table = RandomNameUtils.genRandomName("standalone_index_");
+ String index = "native\" unique; --";
+ TABLE_OPERATIONS.create(
+ TEST_DB_NAME,
+ table,
+ new JdbcColumn[] {
+
JdbcColumn.builder().withName("id").withType(INT).withNullable(true).build()
+ },
+ "before",
+ Map.of(),
+ null,
+ Distributions.NONE,
+ Indexes.EMPTY_INDEXES);
+ try (Connection connection = DATA_SOURCE.getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.executeUpdate(
+ "CREATE UNIQUE INDEX \""
+ + index.replace("\"", "\"\"")
+ + "\" ON "
+ + TEST_DB_NAME
+ + "."
+ + table
+ + "(id)");
+ for (boolean ifExists : new boolean[] {false, true}) {
+ IllegalArgumentException error =
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ TABLE_OPERATIONS.alterTable(
+ TEST_DB_NAME,
+ table,
+ TableChange.updateComment("must not be applied"),
+ TableChange.deleteIndex(index, ifExists)));
+ Assertions.assertTrue(error.getMessage().contains("standalone"));
+ JdbcTable loaded = TABLE_OPERATIONS.load(TEST_DB_NAME, table);
+ Assertions.assertEquals("before", loaded.comment());
+ Assertions.assertTrue(Arrays.stream(loaded.index()).anyMatch(i ->
index.equals(i.name())));
+ }
+ } finally {
+ TABLE_OPERATIONS.drop(TEST_DB_NAME, table);
+ }
+ }
+
+ @Test
+ void testRestNullDefaultsAreSqlNullOnAlter() throws SQLException {
+ String table = RandomNameUtils.genRandomName("null_default_");
+ TABLE_OPERATIONS.create(
+ TEST_DB_NAME,
+ table,
+ new JdbcColumn[] {
+ JdbcColumn.builder()
+ .withName("s")
+ .withType(VARCHAR)
+ .withNullable(true)
+ .withDefaultValue(Literals.stringLiteral("before"))
+ .build(),
+ JdbcColumn.builder()
+ .withName("i")
+ .withType(INT)
+ .withNullable(true)
+ .withDefaultValue(Literals.integerLiteral(42))
+ .build()
+ },
+ null,
+ Map.of(),
+ null,
+ Distributions.NONE,
+ Indexes.EMPTY_INDEXES);
+ try (Connection connection = DATA_SOURCE.getConnection();
+ Statement statement = connection.createStatement()) {
+ TableUpdateRequest update =
+ new TableUpdateRequest.UpdateTableColumnDefaultValueRequest(
+ new String[] {"s"}, LiteralDTO.NULL);
+ TableUpdateRequest typedNull =
+ new TableUpdateRequest.UpdateTableColumnDefaultValueRequest(
+ new String[] {"i"},
LiteralDTO.builder().withDataType(INT).withValue(null).build());
+ TableUpdateRequest add =
+ new TableUpdateRequest.AddTableColumnRequest(
+ new String[] {"added"}, VARCHAR, null, null, true, false,
LiteralDTO.NULL);
+ TABLE_OPERATIONS.alterTable(
+ TEST_DB_NAME, table, update.tableChange(), typedNull.tableChange(),
add.tableChange());
+ statement.executeUpdate("INSERT INTO " + TEST_DB_NAME + "." + table + "
DEFAULT VALUES");
+ try (ResultSet result =
+ statement.executeQuery("SELECT s,i,added FROM " + TEST_DB_NAME + "."
+ table)) {
+ Assertions.assertTrue(result.next());
+ Assertions.assertNull(result.getObject(1));
+ Assertions.assertNull(result.getObject(2));
+ Assertions.assertNull(result.getObject(3));
+ }
+ for (Column column : TABLE_OPERATIONS.load(TEST_DB_NAME,
table).columns())
+ Assertions.assertEquals(Literals.NULL, column.defaultValue());
+ } finally {
+ TABLE_OPERATIONS.drop(TEST_DB_NAME, table);
+ }
+ }
+
+ @Test
+ void testDdlFragmentsAreRejectedOnCreateAndAlter() {
+ String table = RandomNameUtils.genRandomName("ddl_guard_");
+ JdbcColumn id =
JdbcColumn.builder().withName("id").withType(INT).withNullable(true).build();
+ TABLE_OPERATIONS.create(
+ TEST_DB_NAME,
+ table,
+ new JdbcColumn[] {id},
+ null,
+ Map.of(),
+ null,
+ Distributions.NONE,
+ Indexes.EMPTY_INDEXES);
+ try {
+ for (String fragment :
+ new String[] {
+ "integer, injected_col integer",
+ "integer DEFAULT 42",
+ "integer); CREATE TABLE injected_marker(id int); CREATE TABLE
continuation(id int"
+ }) {
+ JdbcColumn bad =
+ JdbcColumn.builder()
+ .withName("id")
+ .withType(Types.ExternalType.of(fragment))
+ .withNullable(true)
+ .build();
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ TABLE_OPERATIONS.create(
+ TEST_DB_NAME,
+ table + "_bad",
+ new JdbcColumn[] {bad},
+ null,
+ Map.of(),
+ null,
+ Distributions.NONE,
+ Indexes.EMPTY_INDEXES));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ TABLE_OPERATIONS.alterTable(
+ TEST_DB_NAME,
+ table,
+ TableChange.addColumn(new String[] {"bad"},
bad.dataType())));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ TABLE_OPERATIONS.alterTable(
+ TEST_DB_NAME,
+ table,
+ TableChange.updateColumnType(new String[] {"id"},
bad.dataType())));
+ }
+ FunctionExpression badDefault =
+ FunctionExpression.of(
+ "abs(1))); CREATE TABLE injected_marker(id int); CREATE TABLE
continuation(id timestamptz DEFAULT (now");
+ JdbcColumn bad =
+ JdbcColumn.builder()
+ .withName("id")
+ .withType(INT)
+ .withDefaultValue(badDefault)
+ .withNullable(true)
+ .build();
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ TABLE_OPERATIONS.create(
+ TEST_DB_NAME,
+ table + "_bad",
+ new JdbcColumn[] {bad},
+ null,
+ Map.of(),
+ null,
+ Distributions.NONE,
+ Indexes.EMPTY_INDEXES));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ TABLE_OPERATIONS.alterTable(
+ TEST_DB_NAME,
+ table,
+ TableChange.updateColumnDefaultValue(new String[] {"id"},
badDefault)));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ TABLE_OPERATIONS.alterTable(
+ TEST_DB_NAME,
+ table,
+ TableChange.addColumn(
+ new String[] {"bad"},
+ INT,
+ null,
+ TableChange.ColumnPosition.defaultPos(),
+ true,
+ false,
+ badDefault)));
+ List<String> names = TABLE_OPERATIONS.listTables(TEST_DB_NAME);
+ Assertions.assertFalse(names.contains("injected_marker"));
+ Assertions.assertFalse(names.contains("continuation"));
+ Assertions.assertFalse(names.contains(table + "_bad"));
+ Assertions.assertEquals(1, TABLE_OPERATIONS.load(TEST_DB_NAME,
table).columns().length);
+ } finally {
+ TABLE_OPERATIONS.drop(TEST_DB_NAME, table);
+ TABLE_OPERATIONS.drop(TEST_DB_NAME, table + "_bad");
+ TABLE_OPERATIONS.drop(TEST_DB_NAME, "injected_marker");
+ TABLE_OPERATIONS.drop(TEST_DB_NAME, "continuation");
+ }
+ }
+
+ @Test
+ void testQuotedNativeTypesAndModifiersStillExecute() throws SQLException {
+ String table = RandomNameUtils.genRandomName("native_type_");
+ String domain = RandomNameUtils.genRandomName("domain_") + ";quoted";
+ String type = "\"" + TEST_DB_NAME + "\".\"" + domain + "\"";
+ try (Connection connection = DATA_SOURCE.getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.execute("CREATE DOMAIN " + type + " AS integer");
+ try {
+ TABLE_OPERATIONS.create(
+ TEST_DB_NAME,
+ table,
+ new JdbcColumn[] {
+ JdbcColumn.builder()
+ .withName("id")
+ .withType(Types.ExternalType.of(type))
+ .withNullable(true)
+ .build(),
+ JdbcColumn.builder()
+ .withName("n")
+ .withType(Types.ExternalType.of("numeric(39,0) ARRAY"))
+ .withNullable(true)
+ .build(),
+ JdbcColumn.builder()
+ .withName("iv")
+ .withType(Types.ExternalType.of("interval day to second(6)"))
+ .withNullable(true)
+ .build(),
+ JdbcColumn.builder()
+ .withName("national")
+ .withType(Types.ExternalType.of("national character(3)"))
+ .withNullable(true)
+ .build()
+ },
+ null,
+ Map.of(),
+ null,
+ Distributions.NONE,
+ Indexes.EMPTY_INDEXES);
+ statement.executeUpdate(
+ "INSERT INTO "
+ + TEST_DB_NAME
+ + "."
+ + table
+ + " VALUES(7, ARRAY[123456789012345678901234567890123456789],
INTERVAL '1 day', 'abc')");
+ try (ResultSet result =
+ statement.executeQuery("SELECT id, n[1]::text FROM " +
TEST_DB_NAME + "." + table)) {
+ Assertions.assertTrue(result.next());
+ Assertions.assertEquals(7, result.getInt(1));
+ Assertions.assertEquals("123456789012345678901234567890123456789",
result.getString(2));
+ }
+ Assertions.assertEquals(4, TABLE_OPERATIONS.load(TEST_DB_NAME,
table).columns().length);
+ } finally {
+ TABLE_OPERATIONS.drop(TEST_DB_NAME, table);
+ statement.execute("DROP DOMAIN " + type);
+ }
+ }
+ }
+
+ @Test
+ void testFunctionDefaultsWithArgumentsOnCreateAndAlter() throws SQLException
{
+ String table = RandomNameUtils.genRandomName("function_default_");
+ String value = "O'Brien; CREATE TABLE injected_marker(id int); --";
+ JdbcColumn[] columns = {
+ JdbcColumn.builder()
+ .withName("n")
+ .withType(INT)
+ .withNullable(true)
+ .withDefaultValue(FunctionExpression.of("pg_catalog.abs",
Literals.integerLiteral(-3)))
+ .build(),
+ JdbcColumn.builder()
+ .withName("s")
+ .withType(VARCHAR)
+ .withNullable(true)
+ .withDefaultValue(
+ FunctionExpression.of(
+ "concat", Literals.stringLiteral(value),
Literals.stringLiteral("!")))
+ .build(),
+ JdbcColumn.builder()
+ .withName("ts")
+ .withType(Types.TimestampType.withoutTimeZone())
+ .withNullable(true)
+ .withDefaultValue(
+ FunctionExpression.of(
+ "date_trunc", Literals.stringLiteral("day"),
FunctionExpression.of("now")))
+ .build()
+ };
+ TABLE_OPERATIONS.create(
+ TEST_DB_NAME,
+ table,
+ columns,
+ null,
+ Map.of(),
+ null,
+ Distributions.NONE,
+ Indexes.EMPTY_INDEXES);
+ try (Connection connection = DATA_SOURCE.getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.executeUpdate("INSERT INTO " + TEST_DB_NAME + "." + table + "
DEFAULT VALUES");
+ try (ResultSet result =
+ statement.executeQuery("SELECT n,s,ts FROM " + TEST_DB_NAME + "." +
table)) {
+ Assertions.assertTrue(result.next());
+ Assertions.assertEquals(3, result.getInt(1));
+ Assertions.assertEquals(value + "!", result.getString(2));
+ Assertions.assertEquals(
+ LocalTime.MIDNIGHT,
result.getTimestamp(3).toLocalDateTime().toLocalTime());
+ }
+ Assertions.assertInstanceOf(
+ UnparsedExpression.class,
+ TABLE_OPERATIONS.load(TEST_DB_NAME,
table).columns()[1].defaultValue());
+ TABLE_OPERATIONS.alterTable(
+ TEST_DB_NAME,
+ table,
+ TableChange.addColumn(
+ new String[] {"added"},
+ INT,
+ null,
+ TableChange.ColumnPosition.defaultPos(),
+ true,
+ false,
+ FunctionExpression.of("abs", Literals.integerLiteral(-5))));
+ TABLE_OPERATIONS.alterTable(
+ TEST_DB_NAME,
+ table,
+ TableChange.updateColumnDefaultValue(
+ new String[] {"added"}, FunctionExpression.of("abs",
Literals.integerLiteral(-9))));
+ statement.executeUpdate("INSERT INTO " + TEST_DB_NAME + "." + table + "
DEFAULT VALUES");
+ try (ResultSet result =
+ statement.executeQuery(
+ "SELECT added FROM " + TEST_DB_NAME + "." + table + " ORDER BY
added")) {
+ Assertions.assertTrue(result.next());
+ Assertions.assertEquals(5, result.getInt(1));
+ Assertions.assertTrue(result.next());
+ Assertions.assertEquals(9, result.getInt(1));
+ }
+
Assertions.assertFalse(TABLE_OPERATIONS.listTables(TEST_DB_NAME).contains("injected_marker"));
+ } finally {
+ TABLE_OPERATIONS.drop(TEST_DB_NAME, table);
+ }
+ }
+
+ @Test
+ void testSqlTokensInIndexNamesStayIdentifiers() throws SQLException {
+ String table = RandomNameUtils.genRandomName("index_tokens_");
+ String primary = "pk\"; CREATE TABLE marker(id int);--";
+ String unique = "uq\"; CREATE TABLE marker(id int);--";
+ TABLE_OPERATIONS.create(
+ TEST_DB_NAME,
+ table,
+ new JdbcColumn[] {
+
JdbcColumn.builder().withName("id").withType(INT).withNullable(false).build()
+ },
+ null,
+ Map.of(),
+ null,
+ Distributions.NONE,
+ new Index[] {Indexes.primary(primary, new String[][] {{"id"}})});
+ try {
+ Assertions.assertTrue(
+ Arrays.stream(TABLE_OPERATIONS.load(TEST_DB_NAME, table).index())
+ .anyMatch(i -> primary.equals(i.name())));
+ TABLE_OPERATIONS.alterTable(
+ TEST_DB_NAME,
+ table,
+ TableChange.addIndex(IndexType.UNIQUE_KEY, unique, new String[][]
{{"id"}}));
+ Assertions.assertTrue(
+ Arrays.stream(TABLE_OPERATIONS.load(TEST_DB_NAME, table).index())
+ .anyMatch(i -> unique.equals(i.name())));
+ TABLE_OPERATIONS.alterTable(
+ TEST_DB_NAME,
+ table,
+ TableChange.deleteIndex(unique, false),
+ TableChange.deleteIndex(primary, false));
+ Assertions.assertEquals(0, TABLE_OPERATIONS.load(TEST_DB_NAME,
table).index().length);
+
Assertions.assertFalse(TABLE_OPERATIONS.listTables(TEST_DB_NAME).contains("marker"));
+ } finally {
+ TABLE_OPERATIONS.drop(TEST_DB_NAME, table);
+ TABLE_OPERATIONS.drop(TEST_DB_NAME, "marker");
+ }
+ }
+
+ @ParameterizedTest
+ @ValueSource(strings = {"on", "off"})
+ void testStringFunctionArgumentsAreEscapedForAnyStringSetting(String setting)
+ throws SQLException {
+ PostgreSqlColumnDefaultValueConverter converter = new
PostgreSqlColumnDefaultValueConverter();
+ String[] values = {
+ "back\\slash O'Brien; --",
+ "\\'); CREATE TABLE " + TEST_DB_NAME + ".function_argument_marker(id
int); SELECT ('safe"
+ };
+ try (Connection connection = DATA_SOURCE.getConnection();
+ Statement statement = connection.createStatement()) {
+ try {
+ statement.execute("SET standard_conforming_strings = " + setting);
+ for (String value : values) {
+ String expression =
+ converter.fromGravitino(
+ FunctionExpression.of("concat",
Literals.stringLiteral(value)));
+ try (ResultSet result = statement.executeQuery("SELECT " +
expression)) {
+ Assertions.assertTrue(result.next());
+ Assertions.assertEquals(value, result.getString(1));
+ }
+ }
+ Assertions.assertFalse(
+
TABLE_OPERATIONS.listTables(TEST_DB_NAME).contains("function_argument_marker"));
+ } finally {
+ statement.execute("RESET standard_conforming_strings");
+ }
+ }
+ }
+
@ParameterizedTest
@ValueSource(strings = {"4142", "0080ff", ""})
void testBinaryDefaultsPreserveBytesOnCreateAndAlter(String hex) throws
SQLException {
diff --git
a/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/operation/TestPostgreSqlTableOperationsSqlGeneration.java
b/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/operation/TestPostgreSqlTableOperationsSqlGeneration.java
index ef659ec49b..57f34dbcf2 100644
---
a/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/operation/TestPostgreSqlTableOperationsSqlGeneration.java
+++
b/catalogs/catalog-jdbc-postgresql/src/test/java/org/apache/gravitino/catalog/postgresql/operation/TestPostgreSqlTableOperationsSqlGeneration.java
@@ -18,7 +18,16 @@
*/
package org.apache.gravitino.catalog.postgresql.operation;
+import static org.mockito.ArgumentMatchers.anyString;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.when;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
import java.util.Collections;
+import javax.sql.DataSource;
import org.apache.gravitino.catalog.jdbc.JdbcColumn;
import org.apache.gravitino.catalog.jdbc.JdbcTable;
import
org.apache.gravitino.catalog.jdbc.converter.JdbcColumnDefaultValueConverter;
@@ -42,10 +51,22 @@ import org.junit.jupiter.params.provider.ValueSource;
public class TestPostgreSqlTableOperationsSqlGeneration {
private static class TestablePostgreSqlTableOperations extends
PostgreSqlTableOperations {
+ private final ResultSet indexMetadata = mock(ResultSet.class);
+
public TestablePostgreSqlTableOperations() {
super.exceptionMapper = new JdbcExceptionConverter();
super.typeConverter = new PostgreSqlTypeConverter();
super.columnDefaultValueConverter = new
JdbcColumnDefaultValueConverter();
+ super.dataSource = mock(DataSource.class);
+ try {
+ Connection connection = mock(Connection.class);
+ PreparedStatement statement = mock(PreparedStatement.class);
+ when(dataSource.getConnection()).thenReturn(connection);
+ when(connection.prepareStatement(anyString())).thenReturn(statement);
+ when(statement.executeQuery()).thenReturn(indexMetadata);
+ } catch (SQLException e) {
+ throw new AssertionError(e);
+ }
}
private
TestablePostgreSqlTableOperations(PostgreSqlColumnDefaultValueConverter
converter) {
@@ -89,6 +110,18 @@ public class TestPostgreSqlTableOperationsSqlGeneration {
}
}
+ @ParameterizedTest
+ @ValueSource(booleans = {false, true})
+ void testStandaloneUniqueIndexCannotBeSilentlyIgnored(boolean ifExists)
throws SQLException {
+ TestablePostgreSqlTableOperations ops = new
TestablePostgreSqlTableOperations();
+ when(ops.indexMetadata.next()).thenReturn(true);
+ IllegalArgumentException error =
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () -> ops.alterTableSql("table",
TableChange.deleteIndex("native_unique", ifExists)));
+ Assertions.assertTrue(error.getMessage().contains("standalone unique
index"));
+ }
+
@Test
void testQuotedTableAndColumnNamesCannotEscapeIdentifiers() {
TestablePostgreSqlTableOperations ops = new
TestablePostgreSqlTableOperations();
diff --git
a/common/src/main/java/org/apache/gravitino/dto/requests/TableUpdateRequest.java
b/common/src/main/java/org/apache/gravitino/dto/requests/TableUpdateRequest.java
index 300c15c3cb..8c5acda0a4 100644
---
a/common/src/main/java/org/apache/gravitino/dto/requests/TableUpdateRequest.java
+++
b/common/src/main/java/org/apache/gravitino/dto/requests/TableUpdateRequest.java
@@ -36,6 +36,8 @@ import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.ToString;
import org.apache.commons.lang3.StringUtils;
+import org.apache.gravitino.dto.rel.expressions.FunctionArg;
+import org.apache.gravitino.dto.util.DTOConverters;
import org.apache.gravitino.json.JsonUtils;
import org.apache.gravitino.rel.Column;
import org.apache.gravitino.rel.TableChange;
@@ -412,7 +414,15 @@ public interface TableUpdateRequest extends RESTRequest {
@Override
public TableChange tableChange() {
return TableChange.addColumn(
- fieldName, dataType, comment, position, nullable, autoIncrement,
defaultValue);
+ fieldName,
+ dataType,
+ comment,
+ position,
+ nullable,
+ autoIncrement,
+ defaultValue instanceof FunctionArg
+ ? DTOConverters.fromFunctionArg((FunctionArg) defaultValue)
+ : defaultValue);
}
}
@@ -524,7 +534,11 @@ public interface TableUpdateRequest extends RESTRequest {
*/
@Override
public TableChange tableChange() {
- return TableChange.updateColumnDefaultValue(fieldName, newDefaultValue);
+ return TableChange.updateColumnDefaultValue(
+ fieldName,
+ newDefaultValue instanceof FunctionArg
+ ? DTOConverters.fromFunctionArg((FunctionArg) newDefaultValue)
+ : newDefaultValue);
}
}
diff --git
a/common/src/test/java/org/apache/gravitino/dto/requests/TestTableUpdatesRequest.java
b/common/src/test/java/org/apache/gravitino/dto/requests/TestTableUpdatesRequest.java
index 6eeac4d3f9..bbfcb20814 100644
---
a/common/src/test/java/org/apache/gravitino/dto/requests/TestTableUpdatesRequest.java
+++
b/common/src/test/java/org/apache/gravitino/dto/requests/TestTableUpdatesRequest.java
@@ -27,6 +27,7 @@ import org.apache.gravitino.dto.rel.expressions.LiteralDTO;
import org.apache.gravitino.json.JsonUtils;
import org.apache.gravitino.rel.TableChange;
import org.apache.gravitino.rel.TableChange.DeleteIndex;
+import org.apache.gravitino.rel.expressions.literals.Literals;
import org.apache.gravitino.rel.indexes.Index;
import org.apache.gravitino.rel.indexes.Indexes;
import org.apache.gravitino.rel.types.Types;
@@ -34,6 +35,49 @@ import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
public class TestTableUpdatesRequest {
+ @Test
+ void testAlterDefaultDtosNormalizeNullWithoutChangingStringNull() throws
JsonProcessingException {
+ for (String literal :
+ new String[] {
+ "{\"type\":\"literal\",\"dataType\":\"null\",\"value\":\"NULL\"}",
+ "{\"type\":\"literal\",\"dataType\":\"integer\",\"value\":null}"
+ }) {
+ TableUpdateRequest update =
+ JsonUtils.objectMapper()
+ .readValue(
+
"{\"@type\":\"updateColumnDefaultValue\",\"fieldName\":[\"c\"],\"newDefaultValue\":"
+ + literal
+ + "}",
+ TableUpdateRequest.class);
+ update.validate();
+ Assertions.assertSame(
+ Literals.NULL,
+ ((TableChange.UpdateColumnDefaultValue)
update.tableChange()).getNewDefaultValue());
+ TableUpdateRequest add =
+ JsonUtils.objectMapper()
+ .readValue(
+
"{\"@type\":\"addColumn\",\"fieldName\":[\"c\"],\"type\":\"integer\",\"nullable\":true,\"defaultValue\":"
+ + literal
+ + "}",
+ TableUpdateRequest.class);
+ add.validate();
+ Assertions.assertSame(
+ Literals.NULL, ((TableChange.AddColumn)
add.tableChange()).getDefaultValue());
+ }
+ TableUpdateRequest update =
+ new TableUpdateRequest.UpdateTableColumnDefaultValueRequest(
+ new String[] {"c"},
+
LiteralDTO.builder().withDataType(Types.StringType.get()).withValue("NULL").build());
+ Assertions.assertEquals(
+ Literals.stringLiteral("NULL"),
+ ((TableChange.UpdateColumnDefaultValue)
update.tableChange()).getNewDefaultValue());
+ TableUpdateRequest add =
+ new TableUpdateRequest.AddTableColumnRequest(
+ new String[] {"c"}, Types.StringType.get(), null);
+ Assertions.assertSame(
+ DEFAULT_VALUE_NOT_SET, ((TableChange.AddColumn)
add.tableChange()).getDefaultValue());
+ }
+
@Test
public void testTableUpdatesRequest() throws JsonProcessingException {
List<TableUpdateRequest> updates =
diff --git a/docs/jdbc-postgresql-catalog.md b/docs/jdbc-postgresql-catalog.md
index b1ac471bef..f44e649ff4 100644
--- a/docs/jdbc-postgresql-catalog.md
+++ b/docs/jdbc-postgresql-catalog.md
@@ -228,3 +228,41 @@ You can't submit the `RenameTable` operation at the same
time as other operation
PostgreSQL doesn't support the `UpdateColumnPosition` operation, so you can
only use `ColumnPosition.defaultPosition()` when `AddColumn`.
If you update a nullability column to non nullability, there may be
compatibility issues.
:::
+
+### Native type declarations and column defaults
+
+`ExternalType` on CREATE or ALTER accepts a single PostgreSQL type
declaration: an unquoted
+or double-quoted type name, optionally schema-qualified, with numeric or
identifier type
+modifiers (for example `geometry(Point,4326)`) and array suffixes (`[]` or
`ARRAY`). Standard
+multiword types such as `double precision`, `character varying`, `national
character(3)`,
+`timestamp with time zone`, and interval field declarations are also accepted.
Quote type
+names containing spaces or punctuation. Extra column definitions, constraints,
defaults,
+comments, and statements are rejected before DDL execution. Unsupported
declaration syntax
+must be expressed using one of the supported forms; arbitrary SQL fragments
are not accepted.
+The native type read path remains unchanged.
+
+Function defaults accept an unquoted function identifier, optionally
qualified, with literal
+or nested-function arguments. Arguments are rendered as SQL values rather than
Java object
+strings. `CURRENT_TIMESTAMP` remains a no-argument keyword default. Raw SQL
expressions and
+SQL syntax in function names are rejected. String values containing
backslashes use explicit
+PostgreSQL escape literals so that `standard_conforming_strings` does not
change their meaning.
+On load, only complete supported constants are converted to literals;
functions and other
+expressions remain `UnparsedExpression` values. `CURRENT_TIMESTAMP` loads as
the
+`CURRENT_TIMESTAMP` default on any column type. A plain number or
`true`/`false` on a string
+column loads as a string. For example, `varchar(20) DEFAULT 10` loads as `10`,
and
+`text DEFAULT true` loads as `true`. Quoted defaults are decoded using the
reading
+connection's string mode without changing the session or adding a query. An
unparsed default
+is preserved for inspection, but cannot be submitted as a PostgreSQL write
expression.
+See [shared JDBC default
restrictions](./tables-and-views.md#table-column-default-value) for the
+other affected catalogs and the limits of this validation.
+
+ALTER requests normalize explicit NULL default literals in the same way as
CREATE requests.
+A string literal containing `NULL` remains a string, and omitting a default
when adding a
+column still leaves the default unset.
+
+### Deleting unique indexes
+
+The table index API drops PostgreSQL primary-key and unique constraints. A
standalone
+`CREATE UNIQUE INDEX` is visible in table metadata, but deleting it through
this API is
+unsupported. Both strict and `ifExists` deletion requests fail explicitly
before applying
+any changes in the request. Remove standalone indexes directly in PostgreSQL.
diff --git a/docs/tables-and-views.md b/docs/tables-and-views.md
index caf0c89e0d..d723a20810 100644
--- a/docs/tables-and-views.md
+++ b/docs/tables-and-views.md
@@ -133,6 +133,18 @@ A column default can be a
[literal](./expression.md#literal) or an
[expression](./expression.md). The underlying catalog applies it to new rows,
and support depends
on the catalog provider.
+PostgreSQL, MySQL, Doris, StarRocks, OceanBase, and Hologres share
function-default write
+validation: function names must be unquoted identifiers (optionally
qualified), and arguments
+must be literals or nested functions. Column references such as `abs(a)` and
raw SQL expression
+arguments are rejected before DDL. `CURRENT_TIMESTAMP` accepts no arguments.
The source database
+may impose additional restrictions on which functions can be used as defaults.
+
+ClickHouse uses its own function conversion and is outside this shared
validation. Hologres also
+retains its separate `UnparsedExpression` write path. The PostgreSQL
native-type validation and
+default-reading rules are PostgreSQL-specific; they do not change the other
catalogs' native-type
+handling. MySQL, Doris, OceanBase, Hologres, and ClickHouse continue to pass
`ExternalType`
+declarations to the source, while StarRocks does not support `ExternalType`
writes.
+
#### Table Column Auto-increment
An auto-increment column asks the underlying catalog to generate values for
new rows. Support and