Mercurial > hg > monetdb-java

comparison src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java @ 619:ffc2fc8e82ec

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Wed, 19 Jan 2022 19:04:03 +0100 (2022-01-19)
parents 6aa38e8c0f2d
children 06d69b82d409
comparison
equal deleted inserted replaced
618:21d0f4a43697 619:ffc2fc8e82ec
2423 // ignore 2423 // ignore
2424 } finally { 2424 } finally {
2425 MonetConnection.closeResultsetStatement(pkey, null); 2425 MonetConnection.closeResultsetStatement(pkey, null);
2426 } 2426 }
2427 2427
2428 // 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)
2429 // 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)
2430
2428 final StringBuilder query = new StringBuilder(1500); 2431 final StringBuilder query = new StringBuilder(1500);
2429 // Note: DISTINCT is needed to filter out possible duplicate column names from multiple unique constraints 2432 query.append("SELECT ");
2430 query.append("SELECT DISTINCT cast(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " + 2433 if (!hasPK) {
2434 // Note: currently DISTINCT is needed to filter out possible duplicate column names when there are multiple unique constraints !!
2435 // TODO: when no PK and there are multiple unique constraints determine which one to select such that DISTINCT keyword is not needed anymore
2436 query.append("DISTINCT ");
2437 }
2438 query.append("cast(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS \"SCOPE\", " +
2431 "c.\"name\" AS \"COLUMN_NAME\", " + 2439 "c.\"name\" AS \"COLUMN_NAME\", " +
2432 "cast(").append(MonetDriver.getSQLTypeMap("c.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " + 2440 "cast(").append(MonetDriver.getSQLTypeMap("c.\"type\"")).append(" AS int) AS \"DATA_TYPE\", " +
2433 "c.\"type\" AS \"TYPE_NAME\", " + 2441 "c.\"type\" AS \"TYPE_NAME\", " +
2434 "c.\"type_digits\" AS \"COLUMN_SIZE\", " + 2442 "c.\"type_digits\" AS \"COLUMN_SIZE\", " +
2435 "cast(0 as int) AS \"BUFFER_LENGTH\", " + 2443 "cast(0 as int) AS \"BUFFER_LENGTH\", " +
2439 "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " + 2447 "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " +
2440 "JOIN \"sys\".\"columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " + 2448 "JOIN \"sys\".\"columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
2441 "JOIN \"sys\".\"tables\" t ON k.\"table_id\" = t.\"id\" " + 2449 "JOIN \"sys\".\"tables\" t ON k.\"table_id\" = t.\"id\" " +
2442 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + 2450 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
2443 "WHERE k.\"type\" = ").append(hasPK ? "0" : "1"); // the primary key (type = 0) or else any unique key (type = 1) 2451 "WHERE k.\"type\" = ").append(hasPK ? "0" : "1"); // the primary key (type = 0) or else any unique key (type = 1)
2444 // 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)
2445 2452
2446 if (catalog != null && !catalog.isEmpty()) { 2453 if (catalog != null && !catalog.isEmpty()) {
2447 // non-empty catalog selection. 2454 // non-empty catalog selection.
2448 // as we do not support catalogs this always results in no rows returned 2455 // as we do not support catalogs this always results in no rows returned
2449 query.append(" AND 1=0"); 2456 query.append(" AND 1=0");
2463 } else { 2470 } else {
2464 query.append(" AND 1=0"); 2471 query.append(" AND 1=0");
2465 } 2472 }
2466 } 2473 }
2467 2474
2468 query.append(" ORDER BY \"SCOPE\", o.\"nr\", \"COLUMN_NAME\""); 2475 // was: query.append(" ORDER BY \"SCOPE\", o.\"nr\", \"COLUMN_NAME\"");
2476 // But as of Jan2022 this ordering returns error: SELECT: with DISTINCT ORDER BY expressions must appear in select list
2477 // 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
2478 query.append(" ORDER BY \"SCOPE\", ");
2479 if (hasPK)
2480 query.append("o.\"nr\", ");
2481 query.append("\"COLUMN_NAME\"");
2469 2482
2470 return executeMetaDataQuery(query.toString()); 2483 return executeMetaDataQuery(query.toString());
2471 } 2484 }
2472 2485
2473 /** 2486 /**