Mercurial > hg > monetdb-java

changeset 649:060347aa81ea

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
By fixing methods getPrimaryKeys(), getBestRowIdentifier() and getIndexInfo() for local temporary tables, it also caused it to fail for global temporary tables in schema tmp. Corrected this, such that it now works for local and global temporary tables. Added tests.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Wed, 11 May 2022 17:34:00 +0200 (2022-05-11)
parents 03e0f577db00
children 849f99124e32
files src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java tests/JDBC_API_Tester.java
diffstat 2 files changed, 201 insertions(+), 22 deletions(-) [+]
line wrap: on
line diff
--- a/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java
+++ b/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java
@@ -2409,8 +2409,6 @@ public class MonetDatabaseMetaData
 		final boolean nullable
 	) throws SQLException
 	{
-		final String sysORtmp = (schema != null && "tmp".equals(schema)) ? "\"tmp\"" : "\"sys\"";
-
 		// first find out if the table has a Primary Key. If it does, we should return only those columns
 		boolean hasPK = false;
 		ResultSet pkey = null;
@@ -2423,12 +2421,13 @@ public class MonetDatabaseMetaData
 			// ignore
 		} finally {
 			MonetConnection.closeResultsetStatement(pkey, null);
+			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);
+		final StringBuilder query = new StringBuilder(3000);
 		query.append("SELECT ");
 		if (!hasPK) {
 			// Note: currently DISTINCT is needed to filter out possible duplicate column names when there are multiple unique constraints !!
@@ -2443,10 +2442,10 @@ public class MonetDatabaseMetaData
 			"cast(0 as int) AS \"BUFFER_LENGTH\", " +
 			"cast(c.\"type_scale\" AS smallint) AS \"DECIMAL_DIGITS\", " +
 			"cast(").append(DatabaseMetaData.bestRowNotPseudo).append(" AS smallint) AS \"PSEUDO_COLUMN\" " +
-		"FROM ").append(sysORtmp).append(".\"keys\" k " +
-		"JOIN ").append(sysORtmp).append(".\"objects\" o ON k.\"id\" = o.\"id\" " +
-		"JOIN ").append(sysORtmp).append(".\"_columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
-		"JOIN ").append(sysORtmp).append(".\"_tables\" t ON k.\"table_id\" = t.\"id\" " +
+		"FROM \"sys\".\"keys\" k " +
+		"JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " +
+		"JOIN \"sys\".\"_columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
+		"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)
 
@@ -2474,11 +2473,62 @@ public class MonetDatabaseMetaData
 			}
 		}
 
+		final boolean includetmp = (schema == null)
+					|| (schema != null && ("tmp".equals(schema) || schema.contains("%") || schema.contains("_")));
+		if (includetmp) {
+			// we must also include the primary key or unique constraint of local temporary tables which are stored in tmp.keys, tmp.objects, tmp._tables and tmp._columns
+			query.append(" UNION ALL ");
+			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\", " +
+				"c.\"type_digits\" AS \"COLUMN_SIZE\", " +
+				"cast(0 as int) AS \"BUFFER_LENGTH\", " +
+				"cast(c.\"type_scale\" AS smallint) AS \"DECIMAL_DIGITS\", " +
+				"cast(").append(DatabaseMetaData.bestRowNotPseudo).append(" AS smallint) AS \"PSEUDO_COLUMN\" " +
+			"FROM \"tmp\".\"keys\" k " +
+			"JOIN \"tmp\".\"objects\" o ON k.\"id\" = o.\"id\" " +
+			"JOIN \"tmp\".\"_columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
+			"JOIN \"tmp\".\"_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)
+
+			if (catalog != null && !catalog.isEmpty()) {
+				// non-empty catalog selection.
+				// as we do not support catalogs this always results in no rows returned
+				query.append(" AND 1=0");
+			} else {
+				if (scope == DatabaseMetaData.bestRowSession
+				 || scope == DatabaseMetaData.bestRowTransaction
+				 || scope == DatabaseMetaData.bestRowTemporary) {
+					if (schema != null) {
+						// do not allow wildcard matching with LIKE, as the resultset does not include the schema info
+						query.append(" AND s.\"name\" = ").append(MonetWrapper.sq(schema));
+					}
+					if (table != null) {
+						// do not allow wildcard matching with LIKE, as the resultset does not include the table info
+						query.append(" AND t.\"name\" = ").append(MonetWrapper.sq(table));
+					}
+					if (!nullable) {
+						query.append(" AND c.\"null\" = false");
+					}
+				} else {
+					query.append(" AND 1=0");
+				}
+			}
+		}
+
 		// 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)
+		if (hasPK && !includetmp)
 			query.append("o.\"nr\", ");
 		query.append("\"COLUMN_NAME\"");
 
@@ -2563,17 +2613,16 @@ public class MonetDatabaseMetaData
 		final String table
 	) throws SQLException
 	{
-		final String sysORtmp = (schema != null && "tmp".equals(schema)) ? "\"tmp\"" : "\"sys\"";
-		final StringBuilder query = new StringBuilder(600);
+		final StringBuilder query = new StringBuilder(1200);
 		query.append("SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " +
 			"s.\"name\" AS \"TABLE_SCHEM\", " +
 			"t.\"name\" AS \"TABLE_NAME\", " +
 			"o.\"name\" AS \"COLUMN_NAME\", " +
 			"cast(1 + o.\"nr\" AS smallint) AS \"KEY_SEQ\", " +
-			" k.\"name\" AS \"PK_NAME\" " +
-		"FROM ").append(sysORtmp).append(".\"keys\" k " +
-		"JOIN ").append(sysORtmp).append(".\"objects\" o ON k.\"id\" = o.\"id\" " +
-		"JOIN ").append(sysORtmp).append(".\"_tables\" t ON k.\"table_id\" = t.\"id\" " +
+			"k.\"name\" AS \"PK_NAME\" " +
+		"FROM \"sys\".\"keys\" k " +
+		"JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " +
+		"JOIN \"sys\".\"_tables\" t ON k.\"table_id\" = t.\"id\" " +
 		"JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
 		"WHERE k.\"type\" = 0");	// only primary keys (type = 0)
 
@@ -2590,6 +2639,37 @@ public class MonetDatabaseMetaData
 			}
 		}
 
+		final boolean includetmp = (schema == null)
+					|| (schema != null && ("tmp".equals(schema) || schema.contains("%") || schema.contains("_")));
+		if (includetmp) {
+			// we must also include the keys of local temporary tables which are stored in tmp.keys, tmp.objects and tmp._tables
+			query.append(" UNION ALL ");
+			query.append("SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " +
+				"s.\"name\" AS \"TABLE_SCHEM\", " +
+				"t.\"name\" AS \"TABLE_NAME\", " +
+				"o.\"name\" AS \"COLUMN_NAME\", " +
+				"cast(1 + o.\"nr\" AS smallint) AS \"KEY_SEQ\", " +
+				"k.\"name\" AS \"PK_NAME\" " +
+			"FROM \"tmp\".\"keys\" k " +
+			"JOIN \"tmp\".\"objects\" o ON k.\"id\" = o.\"id\" " +
+			"JOIN \"tmp\".\"_tables\" t ON k.\"table_id\" = t.\"id\" " +
+			"JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
+			"WHERE k.\"type\" = 0");	// only primary keys (type = 0)
+
+			if (catalog != null && !catalog.isEmpty()) {
+				// non-empty catalog selection.
+				// as we do not support catalogs this always results in no rows returned
+				query.append(" AND 1=0");
+			} else {
+				if (schema != null && !schema.equals("%")) {
+					query.append(" AND s.\"name\" ").append(composeMatchPart(schema));
+				}
+				if (table != null && !table.equals("%")) {
+					query.append(" AND t.\"name\" ").append(composeMatchPart(table));
+				}
+			}
+		}
+
 		query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\"");
 
 		return executeMetaDataQuery(query.toString());
@@ -3081,8 +3161,7 @@ public class MonetDatabaseMetaData
 			}
 		}
 
-		final String sysORtmp = (schema != null && "tmp".equals(schema)) ? "\"tmp\"" : "\"sys\"";
-		final StringBuilder query = new StringBuilder(1250);
+		final StringBuilder query = new StringBuilder(2500);
 		query.append(
 		"SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " +
 			"s.\"name\" AS \"TABLE_SCHEM\", " +
@@ -3097,12 +3176,12 @@ public class MonetDatabaseMetaData
 			"cast(").append(table_row_count).append(" AS int) AS \"CARDINALITY\", " +
 			"cast(0 AS int) AS \"PAGES\", " +
 			"cast(null AS char(1)) AS \"FILTER_CONDITION\" " +
-		"FROM ").append(sysORtmp).append(".\"idxs\" i " +
-		"JOIN ").append(sysORtmp).append(".\"_tables\" t ON i.\"table_id\" = t.\"id\" " +
+		"FROM \"sys\".\"idxs\" i " +
+		"JOIN \"sys\".\"_tables\" t ON i.\"table_id\" = t.\"id\" " +
 		"JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
-		"JOIN ").append(sysORtmp).append(".\"objects\" o ON i.\"id\" = o.\"id\" " +
-		"JOIN ").append(sysORtmp).append(".\"_columns\" c ON (t.\"id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
-		"LEFT OUTER JOIN ").append(sysORtmp).append(".\"keys\" k ON (i.\"name\" = k.\"name\" AND i.\"table_id\" = k.\"table_id\" AND k.\"type\" IN (0,1)) ");	// primary (0) and unique keys (1) only
+		"JOIN \"sys\".\"objects\" o ON i.\"id\" = o.\"id\" " +
+		"JOIN \"sys\".\"_columns\" c ON (t.\"id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
+		"LEFT OUTER JOIN \"sys\".\"keys\" k ON (i.\"name\" = k.\"name\" AND i.\"table_id\" = k.\"table_id\" AND k.\"type\" IN (0,1)) ");	// primary (0) and unique keys (1) only
 
 		if (catalog != null && !catalog.isEmpty()) {
 			// non-empty catalog selection.
@@ -3125,6 +3204,54 @@ public class MonetDatabaseMetaData
 			}
 		}
 
+		final boolean includetmp = (schema == null)
+					|| (schema != null && ("tmp".equals(schema) || schema.contains("%") || schema.contains("_")));
+		if (includetmp) {
+			// we must also include the indexes of local temporary tables which are stored in tmp.idxs, tmp._tables, tmp._columns, tmp.objects and tmp.keys
+			query.append(" UNION ALL ");
+			query.append(
+			"SELECT cast(null AS char(1)) AS \"TABLE_CAT\", " +
+				"s.\"name\" AS \"TABLE_SCHEM\", " +
+				"t.\"name\" AS \"TABLE_NAME\", " +
+				"CASE WHEN k.\"name\" IS NULL THEN true ELSE false END AS \"NON_UNIQUE\", " +
+				"cast(null AS char(1)) AS \"INDEX_QUALIFIER\", " +
+				"i.\"name\" AS \"INDEX_NAME\", " +
+				"CASE i.\"type\" WHEN 0 THEN ").append(DatabaseMetaData.tableIndexHashed).append(" ELSE ").append(DatabaseMetaData.tableIndexOther).append(" END AS \"TYPE\", " +
+				"cast(o.\"nr\" +1 AS smallint) AS \"ORDINAL_POSITION\", "+
+				"c.\"name\" AS \"COLUMN_NAME\", " +
+				"cast(null AS char(1)) AS \"ASC_OR_DESC\", " +	// sort sequence currently not supported in keys or indexes in MonetDB
+				"cast(").append(table_row_count).append(" AS int) AS \"CARDINALITY\", " +
+				"cast(0 AS int) AS \"PAGES\", " +
+				"cast(null AS char(1)) AS \"FILTER_CONDITION\" " +
+			"FROM \"tmp\".\"idxs\" i " +
+			"JOIN \"tmp\".\"_tables\" t ON i.\"table_id\" = t.\"id\" " +
+			"JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
+			"JOIN \"tmp\".\"objects\" o ON i.\"id\" = o.\"id\" " +
+			"JOIN \"tmp\".\"_columns\" c ON (t.\"id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
+			"LEFT OUTER JOIN \"tmp\".\"keys\" k ON (i.\"name\" = k.\"name\" AND i.\"table_id\" = k.\"table_id\" AND k.\"type\" IN (0,1)) ");	// primary (0) and unique keys (1) only
+
+			if (catalog != null && !catalog.isEmpty()) {
+				// non-empty catalog selection.
+				// as we do not support catalogs this always results in no rows returned
+				query.append("WHERE 1=0");
+			} else {
+				boolean needWhere = true;
+				if (schema != null && !schema.equals("%")) {
+					query.append("WHERE s.\"name\" ").append(composeMatchPart(schema));
+					needWhere = false;
+				}
+				if (table != null && !table.equals("%")) {
+					query.append(needWhere ? "WHERE" : " AND")
+					.append(" t.\"name\" ").append(composeMatchPart(table));
+					needWhere = false;
+				}
+				if (unique) {
+					query.append(needWhere ? "WHERE" : " AND")
+					.append(" k.\"name\" IS NOT NULL");
+				}
+			}
+		}
+
 		query.append(" ORDER BY \"NON_UNIQUE\", \"TYPE\", \"INDEX_NAME\", \"ORDINAL_POSITION\"");
 
 		return executeMetaDataQuery(query.toString());
--- a/tests/JDBC_API_Tester.java
+++ b/tests/JDBC_API_Tester.java
@@ -698,6 +698,16 @@ final public class JDBC_API_Tester {
 			response = stmt.executeUpdate("CREATE LOCAL TEMP TABLE tmp_pk_uc (id1 INT NOT NULL PRIMARY KEY, name1 VARCHAR(99) UNIQUE)");
 			if (response != Statement.SUCCESS_NO_INFO)
 				sb.append("Creating table ").append(tablename).append(" failed to return -2!! It returned: ").append(response).append("\n");
+
+			tablename = "glbl_nopk_twoucs";
+			response = stmt.executeUpdate("CREATE GLOBAL TEMP TABLE glbl_nopk_twoucs (id2 INT NOT NULL UNIQUE, name2 VARCHAR(99) UNIQUE)");
+			if (response != Statement.SUCCESS_NO_INFO)
+				sb.append("Creating table ").append(tablename).append(" failed to return -2!! It returned: ").append(response).append("\n");
+
+			tablename = "glbl_pk_uc";
+			response = stmt.executeUpdate("CREATE GLOBAL TEMP TABLE glbl_pk_uc (id1 INT NOT NULL PRIMARY KEY, name1 VARCHAR(99) UNIQUE)");
+			if (response != Statement.SUCCESS_NO_INFO)
+				sb.append("Creating table ").append(tablename).append(" failed to return -2!! It returned: ").append(response).append("\n");
 		} catch (SQLException e) {
 			sb.append("failed to create test table ").append(tablename).append(": ").append(e.getMessage()).append("\n");
 		}
@@ -723,9 +733,11 @@ final public class JDBC_API_Tester {
 			"TABLE_SCHEM	TABLE_CATALOG\n" +
 			"sys	null\n");
 
-			compareResultSet(dbmd.getTables(null, "tmp", null, null), "getTables(null, tmp, null, null)",	// schema tmp has 6 tables
+			compareResultSet(dbmd.getTables(null, "tmp", null, null), "getTables(null, tmp, null, null)",	// schema tmp has 6 system tables and 4 temporary test tables
 			"Resultset with 10 columns\n" +
 			"TABLE_CAT	TABLE_SCHEM	TABLE_NAME	TABLE_TYPE	REMARKS	TYPE_CAT	TYPE_SCHEM	TYPE_NAME	SELF_REFERENCING_COL_NAME	REF_GENERATION\n" +
+			"null	tmp	glbl_nopk_twoucs	GLOBAL TEMPORARY TABLE	null	null	null	null	null	null\n" +
+			"null	tmp	glbl_pk_uc	GLOBAL TEMPORARY TABLE	null	null	null	null	null	null\n" +
 			"null	tmp	tmp_nopk_twoucs	LOCAL TEMPORARY TABLE	null	null	null	null	null	null\n" +
 			"null	tmp	tmp_pk_uc	LOCAL TEMPORARY TABLE	null	null	null	null	null	null\n" +
 			"null	tmp	_columns	SYSTEM TABLE	null	null	null	null	null	null\n" +
@@ -756,6 +768,11 @@ final public class JDBC_API_Tester {
 			"TABLE_CAT	TABLE_SCHEM	TABLE_NAME	COLUMN_NAME	KEY_SEQ	PK_NAME\n" +
 			"null	tmp	tmp_pk_uc	id1	1	tmp_pk_uc_id1_pkey\n");
 
+			compareResultSet(dbmd.getPrimaryKeys(null, "tmp", "glbl_pk_uc"), "getPrimaryKeys(null, tmp, glbl_pk_uc)",
+			"Resultset with 6 columns\n" +
+			"TABLE_CAT	TABLE_SCHEM	TABLE_NAME	COLUMN_NAME	KEY_SEQ	PK_NAME\n" +
+			"null	tmp	glbl_pk_uc	id1	1	glbl_pk_uc_id1_pkey\n");
+
 			compareResultSet(dbmd.getExportedKeys(null, "sys", "table\\_types"), "getExportedKeys(null, sys, table\\_types)",
 			"Resultset with 14 columns\n" +
 			"PKTABLE_CAT	PKTABLE_SCHEM	PKTABLE_NAME	PKCOLUMN_NAME	FKTABLE_CAT	FKTABLE_SCHEM	FKTABLE_NAME	FKCOLUMN_NAME	KEY_SEQ	UPDATE_RULE	DELETE_RULE	FK_NAME	PK_NAME	DEFERRABILITY\n");
@@ -780,6 +797,12 @@ final public class JDBC_API_Tester {
 			"null	tmp	tmp_pk_uc	false	null	tmp_pk_uc_id1_pkey	2	1	id1	null	0	0	null\n" +
 			"null	tmp	tmp_pk_uc	false	null	tmp_pk_uc_name1_unique	2	1	name1	null	0	0	null\n");
 
+			compareResultSet(dbmd.getIndexInfo(null, "tmp", "glbl_pk_uc", false, false), "getIndexInfo(null, tmp, glbl_pk_uc, false, false)",
+			"Resultset with 13 columns\n" +
+			"TABLE_CAT	TABLE_SCHEM	TABLE_NAME	NON_UNIQUE	INDEX_QUALIFIER	INDEX_NAME	TYPE	ORDINAL_POSITION	COLUMN_NAME	ASC_OR_DESC	CARDINALITY	PAGES	FILTER_CONDITION\n" +
+			"null	tmp	glbl_pk_uc	false	null	glbl_pk_uc_id1_pkey	2	1	id1	null	0	0	null\n" +
+			"null	tmp	glbl_pk_uc	false	null	glbl_pk_uc_name1_unique	2	1	name1	null	0	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" +
@@ -818,6 +841,25 @@ final public class JDBC_API_Tester {
 			"SCOPE	COLUMN_NAME	DATA_TYPE	TYPE_NAME	COLUMN_SIZE	BUFFER_LENGTH	DECIMAL_DIGITS	PSEUDO_COLUMN\n" +
 			"2	id2	4	int	32	0	0	1\n");
 
+			compareResultSet(dbmd.getBestRowIdentifier(null, "tmp", "glbl_pk_uc", DatabaseMetaData.bestRowTransaction, true),
+						"getBestRowIdentifier(null, tmp, glbl_pk_uc, 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	id1	4	int	32	0	0	1\n");
+
+			compareResultSet(dbmd.getBestRowIdentifier(null, "tmp", "glbl_nopk_twoucs", DatabaseMetaData.bestRowTransaction, true),
+						"getBestRowIdentifier(null, tmp, glbl_nopk_twoucs, 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	id2	4	int	32	0	0	1\n" +
+			"2	name2	12	varchar	99	0	0	1\n");
+
+			compareResultSet(dbmd.getBestRowIdentifier(null, "tmp", "glbl_nopk_twoucs", DatabaseMetaData.bestRowTransaction, false),
+						"getBestRowIdentifier(null, tmp, glbl_nopk_twoucs, DatabaseMetaData.bestRowTransaction, false)",
+			"Resultset with 8 columns\n" +
+			"SCOPE	COLUMN_NAME	DATA_TYPE	TYPE_NAME	COLUMN_SIZE	BUFFER_LENGTH	DECIMAL_DIGITS	PSEUDO_COLUMN\n" +
+			"2	id2	4	int	32	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" +
@@ -863,6 +905,16 @@ final public class JDBC_API_Tester {
 			response = stmt.executeUpdate("DROP TABLE " + tablename);
 			if (response != Statement.SUCCESS_NO_INFO)
 				sb.append("Dropping table ").append(tablename).append(" failed to return -2!! It returned: ").append(response).append("\n");
+
+			tablename = "glbl_nopk_twoucs";
+			response = stmt.executeUpdate("DROP TABLE " + tablename);
+			if (response != Statement.SUCCESS_NO_INFO)
+				sb.append("Dropping table ").append(tablename).append(" failed to return -2!! It returned: ").append(response).append("\n");
+
+			tablename = "glbl_pk_uc";
+			response = stmt.executeUpdate("DROP TABLE " + tablename);
+			if (response != Statement.SUCCESS_NO_INFO)
+				sb.append("Dropping table ").append(tablename).append(" failed to return -2!! It returned: ").append(response).append("\n");
 		} catch (SQLException e) {
 			sb.append("failed to drop test table ").append(tablename).append(": ").append(e.getMessage()).append("\n");
 		}