postgresql-jdbc.git / summary / log / commit / refs
commit 50d6aba9dcb3be0df7ea39abce1b23b6d8367fa8
Author: Dave Cramer <davecramer@gmail.com>
Commit: GitHub <noreply@github.com>
Date: Sun Oct 10 13:44:54 2021 +0000
fix; do not add double quotes to identifiers already double quoted fixes Issue #2223 (#2224)
* fix; do not add double quotes to identifiers already double quoted fixes Issue #2223
* add test case for quoted identifier
* Added tests for mixed case columns in the returning clause
* Add a property QUOTE_RETURNING_IDENTIFIERS which determines if we put double quotes
around identifiers that are provided in the returning array.
There are some ORMs that now quote all identifiers and if we in turn
quote them this will cause an error.
* update docs for quoteReturning
* Remove unnecessary change
* Unnecessary change
README.md | 3 +-
docs/documentation/head/connect.md | 7 ++
.../src/main/java/org/postgresql/PGProperty.java | 11 ++
.../postgresql/core/CachedQueryCreateAction.java | 4 +-
.../src/main/java/org/postgresql/core/Parser.java | 17 ++-
.../java/org/postgresql/core/QueryExecutor.java | 6 +
.../org/postgresql/core/QueryExecutorBase.java | 7 ++
.../org/postgresql/core/v3/QueryExecutorImpl.java | 2 +-
.../org/postgresql/ds/common/BaseDataSource.java | 16 +++
.../test/java/org/postgresql/core/ParserTest.java | 26 ++---
.../org/postgresql/core/ReturningParserTest.java | 2 +-
.../test/jdbc3/CompositeQueryParseTest.java | 26 ++---
.../postgresql/test/jdbc3/SqlCommandParseTest.java | 2 +-
.../org/postgresql/test/jdbc3/TestReturning.java | 123 +++++++++++++++++++++
14 files changed, 217 insertions(+), 35 deletions(-)
diff --git a/README.md b/README.md
index 5cc6e41e..eb8d8030 100644
--- a/README.md
+++ b/README.md
@@ -133,11 +133,12 @@ In addition to the standard connection parameters the driver supports a number o
| reWriteBatchedInserts | Boolean | false | Enable optimization to rewrite and collapse compatible INSERT statements that are batched. |
| escapeSyntaxCallMode | String | select | Specifies how JDBC escape call syntax is transformed into underlying SQL (CALL/SELECT), for invoking procedures or functions (requires server version >= 11), possible values: select, callIfNoReturn, call |
| maxResultBuffer | String | null | Specifies size of result buffer in bytes, which can't be exceeded during reading result set. Can be specified as particular size (i.e. "100", "200M" "2G") or as percent of max heap memory (i.e. "10p", "20pct", "50percent") |
-| gssEncMode | String | allow | Controls the preference for using GSSAPI encryption for the connection, values are disable, allow, prefer, and require |
+| gssEncMode | String | allow | Controls the preference for using GSSAPI encryption for the connection, values are disable, allow, prefer, and require |
| adaptiveFetch | Boolean | false | Specifies if number of rows fetched in ResultSet by each fetch iteration should be dynamic. Number of rows will be calculated by dividing maxResultBuffer size into max row size observed so far. Requires declaring maxResultBuffer and defaultRowFetchSize for first iteration.
| adaptiveFetchMinimum | Integer | 0 | Specifies minimum number of rows, which can be calculated by adaptiveFetch. Number of rows used by adaptiveFetch cannot go below this value.
| adaptiveFetchMaximum | Integer | -1 | Specifies maximum number of rows, which can be calculated by adaptiveFetch. Number of rows used by adaptiveFetch cannot go above this value. Any negative number set as adaptiveFetchMaximum is used by adaptiveFetch as infinity number of rows.
| localSocketAddress | String | null | Hostname or IP address given to explicitly configure the interface that the driver will bind the client side of the TCP/IP connection to when connecting.
+| quoteReturningIdentifiers | Boolean | true | By default we double quote returning identifiers. Some ORM's already quote them. Switch allows them to turn this off
## Contributing
For information on how to contribute to the project see the [Contributing Guidelines](CONTRIBUTING.md)
diff --git a/docs/documentation/head/connect.md b/docs/documentation/head/connect.md
index a252754a..cc47b266 100644
--- a/docs/documentation/head/connect.md
+++ b/docs/documentation/head/connect.md
@@ -575,6 +575,13 @@ Connection conn = DriverManager.getConnection(url);
By default this is set to true, server error details are propagated. This may include sensitive details such as query parameters.
+* **quoteReturningIdentifiers** == boolean
+
+ Quote returning columns.
+ There are some ORM's that quote everything, including returning columns
+ If we quote them, then we end up sending ""colname"" to the backend instead of "colname"
+ which will not be found.
+
<a name="unix sockets"></a>
## Unix sockets
diff --git a/pgjdbc/src/main/java/org/postgresql/PGProperty.java b/pgjdbc/src/main/java/org/postgresql/PGProperty.java
index d0337617..62e21f15 100644
--- a/pgjdbc/src/main/java/org/postgresql/PGProperty.java
+++ b/pgjdbc/src/main/java/org/postgresql/PGProperty.java
@@ -448,6 +448,17 @@ public enum PGProperty {
false,
new String[] {"3"}),
+ /**
+ * Quote returning columns.
+ * There are some ORM's that quote everything, including returning columns
+ * If we quote them, then we end up sending ""colname"" to the backend
+ * which will not be found
+ */
+ QUOTE_RETURNING_IDENTIFIERS(
+ "quoteReturningIdentifiers",
+ "true",
+ "Quote identifiers provided in returning array",
+ false),
/**
* Puts this connection in read-only mode.
*/
diff --git a/pgjdbc/src/main/java/org/postgresql/core/CachedQueryCreateAction.java b/pgjdbc/src/main/java/org/postgresql/core/CachedQueryCreateAction.java
index 4881f0d8..e3de7aaa 100644
--- a/pgjdbc/src/main/java/org/postgresql/core/CachedQueryCreateAction.java
+++ b/pgjdbc/src/main/java/org/postgresql/core/CachedQueryCreateAction.java
@@ -64,7 +64,9 @@ class CachedQueryCreateAction implements LruCache.CreateAction<Object, CachedQue
List<NativeQuery> queries = Parser.parseJdbcSql(parsedSql,
queryExecutor.getStandardConformingStrings(), isParameterized, splitStatements,
- queryExecutor.isReWriteBatchedInsertsEnabled(), returningColumns);
+ queryExecutor.isReWriteBatchedInsertsEnabled(), queryExecutor.getQuoteReturningIdentifiers(),
+ returningColumns
+ );
Query query = queryExecutor.wrap(queries);
return new CachedQuery(key, query, isFunction);
diff --git a/pgjdbc/src/main/java/org/postgresql/core/Parser.java b/pgjdbc/src/main/java/org/postgresql/core/Parser.java
index db7c60bc..285e5350 100644
--- a/pgjdbc/src/main/java/org/postgresql/core/Parser.java
+++ b/pgjdbc/src/main/java/org/postgresql/core/Parser.java
@@ -40,6 +40,7 @@ public class Parser {
* @param withParameters whether to replace ?, ? with $1, $2, etc
* @param splitStatements whether to split statements by semicolon
* @param isBatchedReWriteConfigured whether re-write optimization is enabled
+ * @param quoteReturningIdentifiers whether to quote identifiers returned using returning clause
* @param returningColumnNames for simple insert, update, delete add returning with given column names
* @return list of native queries
* @throws SQLException if unable to add returning clause (invalid column names)
@@ -47,6 +48,7 @@ public class Parser {
public static List<NativeQuery> parseJdbcSql(String query, boolean standardConformingStrings,
boolean withParameters, boolean splitStatements,
boolean isBatchedReWriteConfigured,
+ boolean quoteReturningIdentifiers,
String... returningColumnNames) throws SQLException {
if (!withParameters && !splitStatements
&& returningColumnNames != null && returningColumnNames.length == 0) {
@@ -145,7 +147,7 @@ public class Parser {
}
fragmentStart = i + 1;
if (nativeSql.length() > 0) {
- if (addReturning(nativeSql, currentCommandType, returningColumnNames, isReturningPresent)) {
+ if (addReturning(nativeSql, currentCommandType, returningColumnNames, isReturningPresent, quoteReturningIdentifiers)) {
isReturningPresent = true;
}
@@ -284,7 +286,7 @@ public class Parser {
return nativeQueries != null ? nativeQueries : Collections.<NativeQuery>emptyList();
}
- if (addReturning(nativeSql, currentCommandType, returningColumnNames, isReturningPresent)) {
+ if (addReturning(nativeSql, currentCommandType, returningColumnNames, isReturningPresent, quoteReturningIdentifiers)) {
isReturningPresent = true;
}
@@ -346,7 +348,7 @@ public class Parser {
}
private static boolean addReturning(StringBuilder nativeSql, SqlCommandType currentCommandType,
- String[] returningColumnNames, boolean isReturningPresent) throws SQLException {
+ String[] returningColumnNames, boolean isReturningPresent, boolean quoteReturningIdentifiers) throws SQLException {
if (isReturningPresent || returningColumnNames.length == 0) {
return false;
}
@@ -367,7 +369,14 @@ public class Parser {
if (col > 0) {
nativeSql.append(", ");
}
- Utils.escapeIdentifier(nativeSql, columnName);
+ /*
+ If the client quotes identifiers then doing so again would create an error
+ */
+ if (quoteReturningIdentifiers) {
+ Utils.escapeIdentifier(nativeSql, columnName);
+ } else {
+ nativeSql.append( columnName );
+ }
}
return true;
}
diff --git a/pgjdbc/src/main/java/org/postgresql/core/QueryExecutor.java b/pgjdbc/src/main/java/org/postgresql/core/QueryExecutor.java
index 9bc547d2..87e73098 100644
--- a/pgjdbc/src/main/java/org/postgresql/core/QueryExecutor.java
+++ b/pgjdbc/src/main/java/org/postgresql/core/QueryExecutor.java
@@ -461,6 +461,12 @@ public interface QueryExecutor extends TypeTransferModeRegistry {
*/
boolean getStandardConformingStrings();
+ /**
+ *
+ * @return true if we are going to quote identifier provided in the returning array default is true
+ */
+ boolean getQuoteReturningIdentifiers();
+
/**
* Returns backend timezone in java format.
* @return backend timezone in java format.
diff --git a/pgjdbc/src/main/java/org/postgresql/core/QueryExecutorBase.java b/pgjdbc/src/main/java/org/postgresql/core/QueryExecutorBase.java
index d472fef8..d375365f 100644
--- a/pgjdbc/src/main/java/org/postgresql/core/QueryExecutorBase.java
+++ b/pgjdbc/src/main/java/org/postgresql/core/QueryExecutorBase.java
@@ -47,6 +47,7 @@ public abstract class QueryExecutorBase implements QueryExecutor {
private final boolean reWriteBatchedInserts;
private final boolean columnSanitiserDisabled;
private final EscapeSyntaxCallMode escapeSyntaxCallMode;
+ private final boolean quoteReturningIdentifiers;
private final PreferQueryMode preferQueryMode;
private AutoSave autoSave;
private boolean flushCacheOnDeallocate = true;
@@ -76,6 +77,7 @@ public abstract class QueryExecutorBase implements QueryExecutor {
this.columnSanitiserDisabled = PGProperty.DISABLE_COLUMN_SANITISER.getBoolean(info);
String callMode = PGProperty.ESCAPE_SYNTAX_CALL_MODE.get(info);
this.escapeSyntaxCallMode = EscapeSyntaxCallMode.of(callMode);
+ this.quoteReturningIdentifiers = PGProperty.QUOTE_RETURNING_IDENTIFIERS.getBoolean(info);
String preferMode = PGProperty.PREFER_QUERY_MODE.get(info);
this.preferQueryMode = PreferQueryMode.of(preferMode);
this.autoSave = AutoSave.of(PGProperty.AUTOSAVE.get(info));
@@ -269,6 +271,11 @@ public abstract class QueryExecutorBase implements QueryExecutor {
return standardConformingStrings;
}
+ @Override
+ public boolean getQuoteReturningIdentifiers() {
+ return quoteReturningIdentifiers;
+ }
+
@Override
public synchronized TransactionState getTransactionState() {
return transactionState;
diff --git a/pgjdbc/src/main/java/org/postgresql/core/v3/QueryExecutorImpl.java b/pgjdbc/src/main/java/org/postgresql/core/v3/QueryExecutorImpl.java
index c3a2fcaf..bb45f5aa 100644
--- a/pgjdbc/src/main/java/org/postgresql/core/v3/QueryExecutorImpl.java
+++ b/pgjdbc/src/main/java/org/postgresql/core/v3/QueryExecutorImpl.java
@@ -237,7 +237,7 @@ public class QueryExecutorImpl extends QueryExecutorBase {
public Query createSimpleQuery(String sql) throws SQLException {
List<NativeQuery> queries = Parser.parseJdbcSql(sql,
getStandardConformingStrings(), false, true,
- isReWriteBatchedInsertsEnabled());
+ isReWriteBatchedInsertsEnabled(), getQuoteReturningIdentifiers());
return wrap(queries);
}
diff --git a/pgjdbc/src/main/java/org/postgresql/ds/common/BaseDataSource.java b/pgjdbc/src/main/java/org/postgresql/ds/common/BaseDataSource.java
index cc8ab18f..0b8df593 100644
--- a/pgjdbc/src/main/java/org/postgresql/ds/common/BaseDataSource.java
+++ b/pgjdbc/src/main/java/org/postgresql/ds/common/BaseDataSource.java
@@ -378,6 +378,22 @@ public abstract class BaseDataSource implements CommonDataSource, Referenceable
}
}
+ /**
+ * @return quoteReturningIdentifiers
+ * @see PGProperty#QUOTE_RETURNING_IDENTIFIERS
+ */
+ public boolean getQuoteReturningIdentifiers() {
+ return PGProperty.QUOTE_RETURNING_IDENTIFIERS.getBoolean(properties);
+ }
+
+ /**
+ * @param quoteIdentifiers indicate whether to quote identifiers
+ * @see PGProperty#QUOTE_RETURNING_IDENTIFIERS
+ */
+ public void setQuoteReturningIdentifiers(boolean quoteIdentifiers) {
+ PGProperty.QUOTE_RETURNING_IDENTIFIERS.set(properties, quoteIdentifiers);
+ }
+
/**
* @return receive buffer size
* @see PGProperty#RECEIVE_BUFFER_SIZE
diff --git a/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java b/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java
index edbea927..a302794a 100644
--- a/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java
@@ -168,7 +168,7 @@ public class ParserTest {
"insert test(id, name) select 1, 'value' as RETURNING from test2";
List<NativeQuery> qry =
Parser.parseJdbcSql(
- query, true, true, true, true);
+ query, true, true, true, true, true);
boolean returningKeywordPresent = qry.get(0).command.isReturningKeywordPresent();
Assert.assertFalse("Query does not have returning clause " + query, returningKeywordPresent);
}
@@ -179,7 +179,7 @@ public class ParserTest {
"insert test(id, name) select 1, 'value' from test2 RETURNING id";
List<NativeQuery> qry =
Parser.parseJdbcSql(
- query, true, true, true, true);
+ query, true, true, true, true, true);
boolean returningKeywordPresent = qry.get(0).command.isReturningKeywordPresent();
Assert.assertTrue("Query has a returning clause " + query, returningKeywordPresent);
}
@@ -190,7 +190,7 @@ public class ParserTest {
"with x as (insert into mytab(x) values(1) returning x) insert test(id, name) select 1, 'value' from test2";
List<NativeQuery> qry =
Parser.parseJdbcSql(
- query, true, true, true, true);
+ query, true, true, true, true, true);
boolean returningKeywordPresent = qry.get(0).command.isReturningKeywordPresent();
Assert.assertFalse("There's no top-level <<returning>> clause " + query, returningKeywordPresent);
}
@@ -198,7 +198,7 @@ public class ParserTest {
@Test
public void insertBatchedReWriteOnConflict() throws SQLException {
String query = "insert into test(id, name) values (:id,:name) ON CONFLICT (id) DO NOTHING";
- List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true);
+ List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true, true);
SqlCommand command = qry.get(0).getCommand();
Assert.assertEquals(34, command.getBatchRewriteValuesBraceOpenPosition());
Assert.assertEquals(44, command.getBatchRewriteValuesBraceClosePosition());
@@ -207,7 +207,7 @@ public class ParserTest {
@Test
public void insertBatchedReWriteOnConflictUpdateBind() throws SQLException {
String query = "insert into test(id, name) values (?,?) ON CONFLICT (id) UPDATE SET name=?";
- List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true);
+ List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true, true);
SqlCommand command = qry.get(0).getCommand();
Assert.assertFalse("update set name=? is NOT compatible with insert rewrite", command.isBatchedReWriteCompatible());
}
@@ -215,7 +215,7 @@ public class ParserTest {
@Test
public void insertBatchedReWriteOnConflictUpdateConstant() throws SQLException {
String query = "insert into test(id, name) values (?,?) ON CONFLICT (id) UPDATE SET name='default'";
- List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true);
+ List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true, true);
SqlCommand command = qry.get(0).getCommand();
Assert.assertTrue("update set name='default' is compatible with insert rewrite", command.isBatchedReWriteCompatible());
}
@@ -224,7 +224,7 @@ public class ParserTest {
public void insertMultiInsert() throws SQLException {
String query =
"insert into test(id, name) values (:id,:name),(:id,:name) ON CONFLICT (id) DO NOTHING";
- List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true);
+ List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true, true);
SqlCommand command = qry.get(0).getCommand();
Assert.assertEquals(34, command.getBatchRewriteValuesBraceOpenPosition());
Assert.assertEquals(56, command.getBatchRewriteValuesBraceClosePosition());
@@ -233,13 +233,13 @@ public class ParserTest {
@Test
public void valuesTableParse() throws SQLException {
String query = "insert into values_table (id, name) values (?,?)";
- List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true);
+ List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true, true);
SqlCommand command = qry.get(0).getCommand();
Assert.assertEquals(43,command.getBatchRewriteValuesBraceOpenPosition());
Assert.assertEquals(49,command.getBatchRewriteValuesBraceClosePosition());
query = "insert into table_values (id, name) values (?,?)";
- qry = Parser.parseJdbcSql(query, true, true, true, true);
+ qry = Parser.parseJdbcSql(query, true, true, true, true, true);
command = qry.get(0).getCommand();
Assert.assertEquals(43,command.getBatchRewriteValuesBraceOpenPosition());
Assert.assertEquals(49,command.getBatchRewriteValuesBraceClosePosition());
@@ -249,7 +249,7 @@ public class ParserTest {
public void createTableParseWithOnDeleteClause() throws SQLException {
String[] returningColumns = {"*"};
String query = "create table \"testTable\" (\"id\" INT SERIAL NOT NULL PRIMARY KEY, \"foreignId\" INT REFERENCES \"otherTable\" (\"id\") ON DELETE NO ACTION)";
- List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true, returningColumns);
+ List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true, true, returningColumns);
SqlCommand command = qry.get(0).getCommand();
Assert.assertFalse("No returning keyword should be present", command.isReturningKeywordPresent());
Assert.assertEquals(SqlCommandType.CREATE, command.getType());
@@ -259,7 +259,7 @@ public class ParserTest {
public void createTableParseWithOnUpdateClause() throws SQLException {
String[] returningColumns = {"*"};
String query = "create table \"testTable\" (\"id\" INT SERIAL NOT NULL PRIMARY KEY, \"foreignId\" INT REFERENCES \"otherTable\" (\"id\")) ON UPDATE NO ACTION";
- List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true, returningColumns);
+ List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true, true, returningColumns);
SqlCommand command = qry.get(0).getCommand();
Assert.assertFalse("No returning keyword should be present", command.isReturningKeywordPresent());
Assert.assertEquals(SqlCommandType.CREATE, command.getType());
@@ -269,7 +269,7 @@ public class ParserTest {
public void alterTableParseWithOnDeleteClause() throws SQLException {
String[] returningColumns = {"*"};
String query = "alter table \"testTable\" ADD \"foreignId\" INT REFERENCES \"otherTable\" (\"id\") ON DELETE NO ACTION";
- List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true, returningColumns);
+ List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true, true, returningColumns);
SqlCommand command = qry.get(0).getCommand();
Assert.assertFalse("No returning keyword should be present", command.isReturningKeywordPresent());
Assert.assertEquals(SqlCommandType.ALTER, command.getType());
@@ -279,7 +279,7 @@ public class ParserTest {
public void alterTableParseWithOnUpdateClause() throws SQLException {
String[] returningColumns = {"*"};
String query = "alter table \"testTable\" ADD \"foreignId\" INT REFERENCES \"otherTable\" (\"id\") ON UPDATE RESTRICT";
- List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true, returningColumns);
+ List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true, true, returningColumns);
SqlCommand command = qry.get(0).getCommand();
Assert.assertFalse("No returning keyword should be present", command.isReturningKeywordPresent());
Assert.assertEquals(SqlCommandType.ALTER, command.getType());
diff --git a/pgjdbc/src/test/java/org/postgresql/core/ReturningParserTest.java b/pgjdbc/src/test/java/org/postgresql/core/ReturningParserTest.java
index 4d29d56b..6acd8cd5 100644
--- a/pgjdbc/src/test/java/org/postgresql/core/ReturningParserTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/core/ReturningParserTest.java
@@ -52,7 +52,7 @@ public class ReturningParserTest {
String query =
"insert into\"prep\"(a, " + prefix + columnName + suffix + ")values(1,2)" + prefix
+ returning + suffix;
- List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true);
+ List<NativeQuery> qry = Parser.parseJdbcSql(query, true, true, true, true, true);
boolean returningKeywordPresent = qry.get(0).command.isReturningKeywordPresent();
boolean expectedReturning = this.returning.equalsIgnoreCase("returning")
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc3/CompositeQueryParseTest.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc3/CompositeQueryParseTest.java
index 31ceb7a3..dc187319 100644
--- a/pgjdbc/src/test/java/org/postgresql/test/jdbc3/CompositeQueryParseTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc3/CompositeQueryParseTest.java
@@ -95,16 +95,16 @@ public class CompositeQueryParseTest {
@Test
public void testHasReturning() throws SQLException {
List<NativeQuery> queries = Parser.parseJdbcSql("insert into foo (a,b,c) values (?,?,?) RetuRning a", true, true, false,
- true);
+ true, true);
NativeQuery query = queries.get(0);
assertTrue("The parser should find the word returning", query.command.isReturningKeywordPresent());
- queries = Parser.parseJdbcSql("insert into foo (a,b,c) values (?,?,?)", true, true, false, true);
+ queries = Parser.parseJdbcSql("insert into foo (a,b,c) values (?,?,?)", true, true, false, true, true);
query = queries.get(0);
assertFalse("The parser should not find the word returning", query.command.isReturningKeywordPresent());
queries = Parser.parseJdbcSql("insert into foo (a,b,c) values ('returning',?,?)", true, true, false,
- true);
+ true, true);
query = queries.get(0);
assertFalse("The parser should not find the word returning as it is in quotes ", query.command.isReturningKeywordPresent());
}
@@ -112,7 +112,7 @@ public class CompositeQueryParseTest {
@Test
public void testSelect() throws SQLException {
List<NativeQuery> queries;
- queries = Parser.parseJdbcSql("select 1 as returning from (update table)", true, true, false, true);
+ queries = Parser.parseJdbcSql("select 1 as returning from (update table)", true, true, false, true, true);
NativeQuery query = queries.get(0);
assertEquals("This is a select ", SqlCommandType.SELECT, query.command.getType());
assertTrue("Returning is OK here as it is not an insert command ", query.command.isReturningKeywordPresent());
@@ -121,7 +121,7 @@ public class CompositeQueryParseTest {
@Test
public void testDelete() throws SQLException {
List<NativeQuery> queries = Parser.parseJdbcSql("DeLeTe from foo where a=1", true, true, false,
- true);
+ true, true);
NativeQuery query = queries.get(0);
assertEquals("This is a delete command", SqlCommandType.DELETE, query.command.getType());
}
@@ -130,7 +130,7 @@ public class CompositeQueryParseTest {
public void testMultiQueryWithBind() throws SQLException {
// braces around (42) are required to puzzle the parser
String sql = "INSERT INTO inttable(a) VALUES (?);SELECT (42)";
- List<NativeQuery> queries = Parser.parseJdbcSql(sql, true, true, true,true);
+ List<NativeQuery> queries = Parser.parseJdbcSql(sql, true, true, true,true, true);
NativeQuery query = queries.get(0);
assertEquals("query(0) of " + sql,
"INSERT: INSERT INTO inttable(a) VALUES ($1)",
@@ -143,7 +143,7 @@ public class CompositeQueryParseTest {
@Test
public void testMove() throws SQLException {
- List<NativeQuery> queries = Parser.parseJdbcSql("MoVe NEXT FROM FOO", true, true, false, true);
+ List<NativeQuery> queries = Parser.parseJdbcSql("MoVe NEXT FROM FOO", true, true, false, true, true);
NativeQuery query = queries.get(0);
assertEquals("This is a move command", SqlCommandType.MOVE, query.command.getType());
}
@@ -152,7 +152,7 @@ public class CompositeQueryParseTest {
public void testUpdate() throws SQLException {
List<NativeQuery> queries;
NativeQuery query;
- queries = Parser.parseJdbcSql("update foo set (a=?,b=?,c=?)", true, true, false, true);
+ queries = Parser.parseJdbcSql("update foo set (a=?,b=?,c=?)", true, true, false, true, true);
query = queries.get(0);
assertEquals("This is an UPDATE command", SqlCommandType.UPDATE, query.command.getType());
}
@@ -160,11 +160,11 @@ public class CompositeQueryParseTest {
@Test
public void testInsert() throws SQLException {
List<NativeQuery> queries = Parser.parseJdbcSql("InSeRt into foo (a,b,c) values (?,?,?) returning a", true, true, false,
- true);
+ true, true);
NativeQuery query = queries.get(0);
assertEquals("This is an INSERT command", SqlCommandType.INSERT, query.command.getType());
- queries = Parser.parseJdbcSql("select 1 as insert", true, true, false, true);
+ queries = Parser.parseJdbcSql("select 1 as insert", true, true, false, true, true);
query = queries.get(0);
assertEquals("This is a SELECT command", SqlCommandType.SELECT, query.command.getType());
}
@@ -172,7 +172,7 @@ public class CompositeQueryParseTest {
@Test
public void testWithSelect() throws SQLException {
List<NativeQuery> queries;
- queries = Parser.parseJdbcSql("with update as (update foo set (a=?,b=?,c=?)) select * from update", true, true, false, true);
+ queries = Parser.parseJdbcSql("with update as (update foo set (a=?,b=?,c=?)) select * from update", true, true, false, true, true);
NativeQuery query = queries.get(0);
assertEquals("with ... () select", SqlCommandType.SELECT, query.command.getType());
}
@@ -180,7 +180,7 @@ public class CompositeQueryParseTest {
@Test
public void testWithInsert() throws SQLException {
List<NativeQuery> queries;
- queries = Parser.parseJdbcSql("with update as (update foo set (a=?,b=?,c=?)) insert into table(select) values(1)", true, true, false, true);
+ queries = Parser.parseJdbcSql("with update as (update foo set (a=?,b=?,c=?)) insert into table(select) values(1)", true, true, false, true, true);
NativeQuery query = queries.get(0);
assertEquals("with ... () insert", SqlCommandType.INSERT, query.command.getType());
}
@@ -201,7 +201,7 @@ public class CompositeQueryParseTest {
boolean splitStatements) {
try {
return toString(
- Parser.parseJdbcSql(query, standardConformingStrings, withParameters, splitStatements, false));
+ Parser.parseJdbcSql(query, standardConformingStrings, withParameters, splitStatements, false, true));
} catch (SQLException e) {
throw new IllegalStateException("Parser.parseJdbcSql: " + e.getMessage(), e);
}
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc3/SqlCommandParseTest.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc3/SqlCommandParseTest.java
index ac98e871..0965d91c 100644
--- a/pgjdbc/src/test/java/org/postgresql/test/jdbc3/SqlCommandParseTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc3/SqlCommandParseTest.java
@@ -47,7 +47,7 @@ public class SqlCommandParseTest {
@Test
public void run() throws SQLException {
List<NativeQuery> queries;
- queries = Parser.parseJdbcSql(sql, true, true, false, true);
+ queries = Parser.parseJdbcSql(sql, true, true, false, true, true);
NativeQuery query = queries.get(0);
assertEquals(sql, type, query.command.getType());
}
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc3/TestReturning.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc3/TestReturning.java
new file mode 100644
index 00000000..88514b31
--- /dev/null
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc3/TestReturning.java
@@ -0,0 +1,123 @@
+/*
+ * Copyright (c) 2021, PostgreSQL Global Development Group
+ * See the LICENSE file in the project root for more information.
+ */
+
+package org.postgresql.test.jdbc3;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNotNull;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+
+import org.postgresql.PGProperty;
+import org.postgresql.test.TestUtil;
+import org.postgresql.test.jdbc2.BaseTest4;
+
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.Properties;
+
+@RunWith(Parameterized.class)
+public class TestReturning extends BaseTest4 {
+
+ public enum ColumnsReturned {
+ Id("Id"),
+ id("id"),
+ ID("*"),
+ QUOTED("\"Id\""),
+ NO();
+
+ final String[] columns;
+ ColumnsReturned(String... columns) {
+ this.columns = columns;
+ }
+
+ public int columnsReturned() {
+ if (columns.length == 1 && columns[0].charAt(0) == '*') {
+ return 100500; // does not matter much, the meaning is "every possible column"
+ }
+ return columns.length;
+ }
+
+ public String[] getColumnNames() {
+ if (columnsReturned() == 0) {
+ return new String[]{};
+ }
+
+ return columns;
+ }
+ }
+
+ static String []returningOptions = {"true", "false"};
+
+ @Parameterized.Parameters(name = "returningInQuery = {0}, quoteReturning = {1}")
+ public static Iterable<Object[]> data() {
+ Collection<Object[]> ids = new ArrayList<Object[]>();
+ for (ColumnsReturned columsReturned : ColumnsReturned.values()) {
+ for (String q : returningOptions) {
+ ids.add(new Object[]{columsReturned, q});
+ }
+ }
+ return ids;
+ }
+
+ private final ColumnsReturned columnsReturned;
+ private final String quoteReturning;
+
+ public TestReturning(ColumnsReturned columnsReturned, String quoteReturning) throws Exception {
+ this.columnsReturned = columnsReturned;
+ this.quoteReturning = quoteReturning;
+ }
+
+ protected void updateProperties(Properties props) {
+ PGProperty.QUOTE_RETURNING_IDENTIFIERS.set(props, quoteReturning);
+ }
+
+ @Override
+ public void setUp() throws Exception {
+ super.setUp();
+ TestUtil.createTempTable(con, "genkeys", "\"Id\" serial, b varchar(5), c int");
+ }
+
+ @Override
+ public void tearDown() throws SQLException {
+ TestUtil.dropTable(con, "genkeys");
+ super.tearDown();
+ }
+
+ private void testGeneratedKeys(Connection conn, String sql, String[] columnNames, boolean exceptionExpected) throws SQLException {
+
+ try (PreparedStatement stmt = conn.prepareStatement(sql, columnNames)) {
+ stmt.execute();
+ ResultSet rs = stmt.getGeneratedKeys();
+ assertNotNull(rs);
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ } catch (SQLException e) {
+ if ( !exceptionExpected ) {
+ fail("error getting column names: " + e.getMessage());
+ }
+ }
+ }
+
+ @Test
+ public void testMixedCase() throws SQLException {
+
+ String insertSql = "INSERT INTO genkeys (b,c) VALUES ('hello', 1)";
+
+ testGeneratedKeys(con, insertSql, new String[] {"Id"}, quoteReturning.equals("false") );
+ testGeneratedKeys(con, insertSql, new String[] {"id"}, true);
+ testGeneratedKeys(con, insertSql, new String[] {"ID"}, true);
+ testGeneratedKeys(con, insertSql, new String[] {"\"Id\""}, quoteReturning.equals("true"));
+ testGeneratedKeys(con, insertSql, new String[] {"bad"}, true);
+ }
+}
[parent: 72fb0aafda7d]