Changeset: ffc2fc8e82ec for monetdb-java URL: https://dev.monetdb.org/hg/monetdb-java/rev/ffc2fc8e82ec Modified Files: ChangeLog src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java tests/JDBC_API_Tester.java Branch: default Log Message:
Fixed an SQL query problem in DatabaseMetaData.getBestRowIdentifier() method when used with MonetDB Jan2022 (or newer) releases. It returned java.sql.SQLException: SELECT: with DISTINCT ORDER BY expressions must appear in select list Added test. diffs (80 lines): diff --git a/ChangeLog b/ChangeLog --- a/ChangeLog +++ b/ChangeLog @@ -5,6 +5,14 @@ - Compiled and released new jar files: monetdb-jdbc-3.2.jre8.jar, monetdb-mcl-1.21.jre8.jar and jdbcclient.jre8.jar +* Wed Jan 19 2022 Martin van Dinther <[email protected]> +- Fixed an SQL query problem in DatabaseMetaData.getBestRowIdentifier() + method when used with MonetDB Jan2022 (or newer) releases. It + returned java.sql.SQLException: + SELECT: with DISTINCT ORDER BY expressions must appear in select list + As of MonetDB Jan2022 (11.43.1) such queries are no longer allowed. + The internally used SQL query has been improved. + * Thu Jan 13 2022 Martin van Dinther <[email protected]> - Extended JdbcClient application with support for following \d commands: \dt \dv \dSt \dSv \df \dp \dSf \dSp \dn \dSn and \ds diff --git a/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java b/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java --- a/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java +++ b/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java @@ -2425,9 +2425,17 @@ public class MonetDatabaseMetaData MonetConnection.closeResultsetStatement(pkey, null); } + // TODO: when there is No PK and there are multiple unique constraints, pick only the unique constraint which has a) the least number of columns and b) the smallest total(size in bytes) + // TODO: when there is No PK and No unique constraints, we potentially should return all columns of the table (else in SQuirreL no header is shown in the "Row IDs" tab) + final StringBuilder query = new StringBuilder(1500); - // Note: DISTINCT is needed to filter out possible duplicate column names from multiple unique constraints - query.append("SELECT DISTINCT cast(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " + + query.append("SELECT "); + if (!hasPK) { + // Note: currently DISTINCT is needed to filter out possible duplicate column names when there are multiple unique constraints !! + // TODO: when no PK and there are multiple unique constraints determine which one to select such that DISTINCT keyword is not needed anymore + query.append("DISTINCT "); + } + query.append("cast(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " + "c.\"name\" AS \"COLUMN_NAME\", " + "cast(").append(MonetDriver.getSQLTypeMap("c.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " + "c.\"type\" AS \"TYPE_NAME\", " + @@ -2441,7 +2449,6 @@ public class MonetDatabaseMetaData "JOIN \"sys\".\"tables\" t ON k.\"table_id\" = t.\"id\" " + "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + "WHERE k.\"type\" = ").append(hasPK ? "0" : "1"); // the primary key (type = 0) or else any unique key (type = 1) - // TODO: when there is no PK and there are multiple unique constraints, pick only the unique constraint which has a) the least number of columns and b) the smallest total(size in bytes) if (catalog != null && !catalog.isEmpty()) { // non-empty catalog selection. @@ -2465,7 +2472,13 @@ public class MonetDatabaseMetaData } } - query.append(" ORDER BY \"SCOPE\", o.\"nr\", \"COLUMN_NAME\""); + // was: query.append(" ORDER BY \"SCOPE\", o.\"nr\", \"COLUMN_NAME\""); + // But as of Jan2022 this ordering returns error: SELECT: with DISTINCT ORDER BY expressions must appear in select list + // so had to remove the o.\"nr\", part when there is No PKey. This means the columns are than ordered on names instead of creation order in their unique constraint definition + query.append(" ORDER BY \"SCOPE\", "); + if (hasPK) + query.append("o.\"nr\", "); + query.append("\"COLUMN_NAME\""); return executeMetaDataQuery(query.toString()); } diff --git a/tests/JDBC_API_Tester.java b/tests/JDBC_API_Tester.java --- a/tests/JDBC_API_Tester.java +++ b/tests/JDBC_API_Tester.java @@ -735,6 +735,12 @@ final public class JDBC_API_Tester { "null sys key_types false null key_types_key_type_id_pkey 2 1 key_type_id null 3 0 null\n" + "null sys key_types false null key_types_key_type_name_unique 2 1 key_type_name null 3 0 null\n"); + compareResultSet(dbmd.getBestRowIdentifier(null, "sys", "function_languages", DatabaseMetaData.bestRowTransaction, true), + "getBestRowIdentifier(null, sys, function_languages, DatabaseMetaData.bestRowTransaction, true)", + "Resultset with 8 columns\n" + + "SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS PSEUDO_COLUMN\n" + + "2 language_id 5 smallint 16 0 0 1\n"); + compareResultSet(dbmd.getTablePrivileges(null, "sys", "table\\_types"), "getTablePrivileges(null, sys, table\\_types)", "Resultset with 7 columns\n" + "TABLE_CAT TABLE_SCHEM TABLE_NAME GRANTOR GRANTEE PRIVILEGE IS_GRANTABLE\n" + _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
