Changeset: c6bbd003bd2a for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=c6bbd003bd2a
Added Files:
sql/test/BugTracker-2016/Tests/DISTINCT_with_correlated_scalar_subquery_crashes_mserver.Bug-3920.sql
sql/test/BugTracker-2016/Tests/DISTINCT_with_correlated_scalar_subquery_crashes_mserver.Bug-3920.stable.err
sql/test/BugTracker-2016/Tests/DISTINCT_with_correlated_scalar_subquery_crashes_mserver.Bug-3920.stable.out
sql/test/BugTracker-2016/Tests/create_table_from_complex_query.Bug-3921.sql
Modified Files:
java/ChangeLog
java/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
sql/test/BugTracker-2016/Tests/All
Branch: embedded
Log Message:
merge
diffs (truncated from 843 to 300 lines):
diff --git a/java/ChangeLog b/java/ChangeLog
--- a/java/ChangeLog
+++ b/java/ChangeLog
@@ -1,10 +1,18 @@
# ChangeLog file for java
# This file is updated with Maddlog
+* Thu Feb 11 2016 Martin van Dinther <martin.van.dint...@monetdbsolutions.com>
+- Implemented methods getProcedures() and getProcedureColumns()
+ in DatabaseMetadata.java. They used to return an empty resultset.
+ Now they return the expected Procedures and ProcedureColumns.
+ Also getProcedureColumns() now returns a resultset with all 20 columns
+ instead of 13 columns previously.
+
* Thu Feb 4 2016 Martin van Dinther <martin.van.dint...@monetdbsolutions.com>
- Method getFunctionColumns() in DatabaseMetadata used to throw an
SQLException: getFunctionColumns(String, String, String, String) is
- not implemented This method is now implemented and returns a resultset.
+ not implemented.
+ This method is now implemented and returns a resultset.
* Thu Jan 28 2016 Martin van Dinther <martin.van.dint...@monetdbsolutions.com>
- Method getFunctions() in DatabaseMetadata used to throw an SQLException:
diff --git a/java/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
b/java/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
--- a/java/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
+++ b/java/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
@@ -203,7 +203,7 @@ public class MonetDatabaseMetaData exten
/**
* What is the version of this database product.
*
- * @return a fixed version number, yes it's quick and dirty
+ * @return the mserver5 version number string
* @throws SQLException if a database access error occurs
*/
@Override
@@ -214,8 +214,7 @@ public class MonetDatabaseMetaData exten
}
/**
- * What is the name of this JDBC driver? If we don't know this
- * we are doing something wrong!
+ * What is the name of this JDBC driver?
*
* @return the JDBC driver name
*/
@@ -228,7 +227,7 @@ public class MonetDatabaseMetaData exten
* What is the version string of this JDBC driver? Again, this is
* static.
*
- * @return the JDBC driver name.
+ * @return the JDBC driver version string
*/
@Override
public String getDriverVersion() {
@@ -238,7 +237,7 @@ public class MonetDatabaseMetaData exten
/**
* What is this JDBC driver's major version number?
*
- * @return the JDBC driver major version
+ * @return the JDBC driver major version number
*/
@Override
public int getDriverMajorVersion() {
@@ -248,7 +247,7 @@ public class MonetDatabaseMetaData exten
/**
* What is this JDBC driver's minor version number?
*
- * @return the JDBC driver minor version
+ * @return the JDBC driver minor version number
*/
@Override
public int getDriverMinorVersion() {
@@ -881,13 +880,12 @@ public class MonetDatabaseMetaData exten
/**
* What is the database vendor's preferred term for "procedure"?
- * Traditionally, "function" has been used.
*
* @return the vendor term
*/
@Override
public String getProcedureTerm() {
- return "function";
+ return "procedure";
}
/**
@@ -1538,28 +1536,28 @@ public class MonetDatabaseMetaData exten
/**
* Get a description of stored procedures available in a catalog
- * Currently not applicable and not implemented, returns null
*
* <p>Only procedure descriptions matching the schema and procedure
- * name criteria are returned. They are ordered by PROCEDURE_SCHEM
- * and PROCEDURE_NAME
+ * name criteria are returned. They are ordered by PROCEDURE_SCHEM,
+ * PROCEDURE_NAME and SPECIFIC_NAME.
*
* <p>Each procedure description has the following columns:
* <ol>
* <li><b>PROCEDURE_CAT</b> String => procedure catalog (may be null)
* <li><b>PROCEDURE_SCHEM</b> String => procedure schema (may be null)
* <li><b>PROCEDURE_NAME</b> String => procedure name
- * <li><b>Field 4</b> reserved (make it null)
- * <li><b>Field 5</b> reserved (make it null)
- * <li><b>Field 6</b> reserved (make it null)
+ * <li><b>Field4</b> reserved (make it null)
+ * <li><b>Field5</b> reserved (make it null)
+ * <li><b>Field6</b> reserved (make it null)
* <li><b>REMARKS</b> String => explanatory comment on the procedure
* <li><b>PROCEDURE_TYPE</b> short => kind of procedure
* <ul>
* <li> procedureResultUnknown - May return a result
- * <li> procedureNoResult - Does not return a result
- * <li> procedureReturnsResult - Returns a result
- * </ul>
+ * <li> procedureNoResult - Does not return a result
+ * <li> procedureReturnsResult - Returns a result
+ * </ul>
* </ol>
+ * <li><b>SPECIFIC_NAME</b> String => The name which uniquely
identifies this procedure within its schema.
*
* @param catalog - a catalog name; "" retrieves those without a
* catalog; null means drop catalog name from criteria
@@ -1576,22 +1574,39 @@ public class MonetDatabaseMetaData exten
String procedureNamePattern
) throws SQLException
{
- String query =
- "SELECT cast(null AS varchar(1)) AS \"PROCEDURE_CAT\",
" +
- "cast(null AS varchar(1)) AS
\"PROCEDURE_SCHEM\", " +
- "'' AS \"PROCEDURE_NAME\", cast(null AS
varchar(1)) AS \"Field4\", " +
- "cast(null AS varchar(1)) AS \"Field5\", " +
- "cast(null AS varchar(1)) AS \"Field6\", " +
- "'' AS \"REMARKS\", cast(0 AS smallint) AS
\"PROCEDURE_TYPE\" " +
- "WHERE 1 = 0";
-
- return getStmt().executeQuery(query);
+ StringBuilder query = new StringBuilder(980);
+ query.append("SELECT DISTINCT cast('' as varchar(1)) AS
\"PROCEDURE_CAT\", ") // TODO change "cast('' as varchar(1))" into
"cast(null as char(1))" after Bug 3920 has been fixed.
+ .append("\"schemas\".\"name\" AS \"PROCEDURE_SCHEM\", ")
+ .append("\"functions\".\"name\" AS \"PROCEDURE_NAME\",
")
+ .append("cast(null as char(1)) AS \"Field4\", ")
+ .append("cast(null as char(1)) AS \"Field5\", ")
+ .append("cast(null as char(1)) AS \"Field6\", ")
+ .append("cast(null as char(1)) AS \"REMARKS\", ")
+ .append("CAST(CASE (SELECT COUNT(*) FROM
\"sys\".\"args\" where \"args\".\"func_id\" = \"functions\".\"id\" and
\"args\".\"number\" = 0)")
+ .append(" WHEN 0 THEN
").append(DatabaseMetaData.procedureNoResult)
+ .append(" WHEN 1 THEN
").append(DatabaseMetaData.procedureReturnsResult)
+ .append(" ELSE
").append(DatabaseMetaData.procedureResultUnknown).append(" END AS smallint) AS
\"PROCEDURE_TYPE\", ")
+ .append("CAST(CASE \"functions\".\"language\" WHEN 0
THEN \"functions\".\"mod\" || '.' || \"functions\".\"func\" ELSE
\"schemas\".\"name\" || '.' || \"functions\".\"name\" END AS VARCHAR(1500)) AS
\"SPECIFIC_NAME\" ")
+ .append("FROM \"sys\".\"functions\", \"sys\".\"schemas\" ")
+ .append("WHERE \"functions\".\"schema_id\" = \"schemas\".\"id\"
")
+ // include procedures only (type = 2). Others will be returned
via getFunctions()
+ .append("AND \"functions\".\"type\" = 2");
+
+ if (schemaPattern != null) {
+ query.append(" AND \"schemas\".\"name\"
").append(composeMatchPart(schemaPattern));
+ }
+ if (procedureNamePattern != null) {
+ query.append(" AND \"functions\".\"name\"
").append(composeMatchPart(procedureNamePattern));
+ }
+
+ query.append(" ORDER BY \"PROCEDURE_SCHEM\",
\"PROCEDURE_NAME\", \"SPECIFIC_NAME\"");
+
+ return getStmt().executeQuery(query.toString());
}
/**
* Get a description of a catalog's stored procedure parameters
* and result columns.
- * Currently not applicable and not implemented, returns null
*
* <p>Only descriptions matching the schema, procedure and parameter
* name criteria are returned. They are ordered by PROCEDURE_SCHEM
@@ -1615,16 +1630,31 @@ public class MonetDatabaseMetaData exten
* <li>procedureColumnResult - result column in ResultSet
* </ul>
* <li><b>DATA_TYPE</b> short => SQL type from java.sql.Types
- * <li><b>TYPE_NAME</b> String => Data source specific type name
+ * <li><b>TYPE_NAME</b> String => SQL type name, for a UDT type the
type name is fully qualified
* <li><b>PRECISION</b> int => precision
* <li><b>LENGTH</b> int => length in bytes of data
- * <li><b>SCALE</b> short => scale
+ * <li><b>SCALE</b> short => scale - null is returned for data types
where SCALE is not applicable.
* <li><b>RADIX</b> short => radix
* <li><b>NULLABLE</b> short => can it contain NULL?
* <ul><li>procedureNoNulls - does not allow NULL values
* <li>procedureNullable - allows NULL values
* <li>procedureNullableUnknown - nullability unknown
+ * </ul>
* <li><b>REMARKS</b> String => comment describing parameter/column
+ * <li><b>COLUMN_DEF</b> String => default value for the column, which
should be interpreted as a string when the value is enclosed in single quotes
(may be null)
+ * The string NULL (not enclosed in quotes) - if NULL was
specified as the default value
+ * TRUNCATE (not enclosed in quotes) - if the specified default
value cannot be represented without truncation
+ * NULL - if a default value was not specified
+ * <li><b>SQL_DATA_TYPE</b> int => reserved for future use
+ * <li><b>SQL_DATETIME_SUB</b> int => reserved for future use
+ * <li><b>CHAR_OCTET_LENGTH</b> int => the maximum length of binary and
character based columns. For any other datatype the returned value is a NULL
+ * <li><b>ORDINAL_POSITION</b> int => the ordinal position, starting
from 1, for the input and output parameters for a procedure. A value of 0 is
returned if this row describes the procedure's return value. For result set
columns, it is the ordinal position of the column in the result set starting
from 1. If there are multiple result sets, the column ordinal positions are
implementation defined.
+ * <li><b>IS_NULLABLE</b> String => ISO rules are used to determine the
nullability for a column.
+ * <ul><li>YES --- if the parameter can include NULLs
+ * <li>NO --- if the parameter cannot include NULLs
+ * <li>empty string --- if the nullability for the parameter is unknown
+ * </ul>
+ * <li><b>SPECIFIC_NAME</b> String => the name which uniquely
identifies this procedure within its schema.
* </ol>
* @param catalog not used
* @param schemaPattern not used
@@ -1641,18 +1671,48 @@ public class MonetDatabaseMetaData exten
String procedureNamePattern,
String columnNamePattern
) throws SQLException {
- String query =
- "SELECT cast(null AS varchar(1)) AS \"PROCEDURE_CAT\",
" +
- "cast(null AS varchar(1)) AS
\"PROCEDURE_SCHEM\", " +
- "'' AS \"PROCEDURE_NAME\", '' AS
\"COLUMN_NAME\", " +
- "cast(0 AS smallint) AS \"COLUMN_TYPE\", " +
- "cast(0 AS smallint) AS \"DATA_TYPE\", " +
- "'' AS \"TYPE_NAME\", 0 AS \"PRECISION\", " +
- "0 AS \"LENGTH\", 0 AS \"SCALE\", 0 AS
\"RADIX\", " +
- "cast(0 AS smallint) AS \"NULLABLE\", '' AS
\"REMARKS\" " +
- "WHERE 1 = 0";
-
- return getStmt().executeQuery(query);
+ StringBuilder query = new StringBuilder(2900);
+ query.append("SELECT DISTINCT CAST(null as char(1)) AS
\"PROCEDURE_CAT\", ")
+ .append("\"schemas\".\"name\" AS \"PROCEDURE_SCHEM\", ")
+ .append("\"functions\".\"name\" AS \"PROCEDURE_NAME\",
")
+ .append("\"args\".\"name\" AS \"COLUMN_NAME\", ")
+ .append("CAST(CASE \"args\".\"inout\"")
+ .append(" WHEN 0 THEN (CASE \"args\".\"number\"
WHEN 0 THEN ").append(DatabaseMetaData.procedureColumnReturn).append(" ELSE
").append(DatabaseMetaData.procedureColumnOut).append(" END)")
+ .append(" WHEN 1 THEN
").append(DatabaseMetaData.procedureColumnIn)
+ .append(" ELSE
").append(DatabaseMetaData.procedureColumnUnknown).append(" END AS smallint) AS
\"COLUMN_TYPE\", ")
+
.append("CAST(").append(MonetDriver.getSQLTypeMap("\"args\".\"type\"")).append("
AS int) AS \"DATA_TYPE\", ")
+ .append("\"args\".\"type\" AS \"TYPE_NAME\", ")
+ .append("CASE \"args\".\"type\" WHEN 'tinyint' THEN 3
WHEN 'smallint' THEN 5 WHEN 'int' THEN 10 WHEN 'bigint' THEN 19 WHEN 'hugeint'
THEN 38 WHEN 'oid' THEN 19 WHEN 'wrd' THEN 19 ELSE \"args\".\"type_digits\" END
AS \"PRECISION\", ")
+ .append("CASE \"args\".\"type\" WHEN 'tinyint' THEN 1
WHEN 'smallint' THEN 2 WHEN 'int' THEN 4 WHEN 'bigint' THEN 8 WHEN 'hugeint'
THEN 16 WHEN 'oid' THEN 8 WHEN 'wrd' THEN 8 ELSE \"args\".\"type_digits\" END
AS \"LENGTH\", ")
+ .append("CAST(CASE WHEN \"args\".\"type\" IN
('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric','time','timetz','timestamp','timestamptz','sec_interval')
THEN \"args\".\"type_scale\" ELSE NULL END AS smallint) AS \"SCALE\", ")
+ .append("CAST(CASE WHEN \"args\".\"type\" IN
('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric')
THEN 10 WHEN \"args\".\"type\" IN ('real','float','double') THEN 2 ELSE NULL
END AS smallint) AS \"RADIX\", ")
+
.append("CAST(").append(DatabaseMetaData.procedureNullableUnknown).append(" AS
smallint) AS \"NULLABLE\", ")
+ .append("CAST(null as char(1)) AS \"REMARKS\", ")
+ .append("CAST(null as char(1)) AS \"COLUMN_DEF\", ")
+ .append("CAST(null as int) AS \"SQL_DATA_TYPE\", ")
+ .append("CAST(null as int) AS \"SQL_DATETIME_SUB\", ")
+ .append("CASE WHEN \"args\".\"type\" IN
('char','varchar','binary','varbinary') THEN \"args\".\"type_digits\" ELSE NULL
END AS \"CHAR_OCTET_LENGTH\", ")
+ .append("\"args\".\"number\" AS \"ORDINAL_POSITION\", ")
+ .append("CAST('' as varchar(3)) AS \"IS_NULLABLE\", ")
+ .append("CAST(null as char(1)) AS \"SPECIFIC_NAME\" ")
+ .append("FROM \"sys\".\"args\", \"sys\".\"functions\",
\"sys\".\"schemas\" ")
+ .append("WHERE \"args\".\"func_id\" = \"functions\".\"id\" ")
+ .append("AND \"functions\".\"schema_id\" = \"schemas\".\"id\" ")
+ // include procedures only (type = 2). Others will be returned
via getFunctionColumns()
+ .append("AND \"functions\".\"type\" = 2");
+
+ if (schemaPattern != null) {
+ query.append(" AND \"schemas\".\"name\"
").append(composeMatchPart(schemaPattern));
+ }
+ if (procedureNamePattern != null) {
+ query.append(" AND \"functions\".\"name\"
").append(composeMatchPart(procedureNamePattern));
+ }
+ if (columnNamePattern != null) {
+ query.append(" AND \"args\".\"name\"
").append(composeMatchPart(columnNamePattern));
+ }
+ query.append(" ORDER BY \"PROCEDURE_SCHEM\",
\"PROCEDURE_NAME\", \"ORDINAL_POSITION\"");
+
+ return getStmt().executeQuery(query.toString());
}
//== this is a helper method which does not belong to the interface
@@ -3625,7 +3685,7 @@ public class MonetDatabaseMetaData exten
.append("CASE \"args\".\"type\" WHEN 'tinyint' THEN 1
WHEN 'smallint' THEN 2 WHEN 'int' THEN 4 WHEN 'bigint' THEN 8 WHEN 'hugeint'
THEN 16 WHEN 'oid' THEN 8 WHEN 'wrd' THEN 8 ELSE \"args\".\"type_digits\" END
AS \"LENGTH\", ")
.append("CAST(CASE WHEN \"args\".\"type\" IN
('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric','time','timetz','timestamp','timestamptz','sec_interval')
THEN \"args\".\"type_scale\" ELSE NULL END AS smallint) AS \"SCALE\", ")
.append("CAST(CASE WHEN \"args\".\"type\" IN
('tinyint','smallint','int','bigint','hugeint','oid','wrd','decimal','numeric')
THEN 10 WHEN \"args\".\"type\" IN ('real','float','double') THEN 2 ELSE NULL
END AS smallint) AS \"RADIX\", ")
-
.append("CAST(").append(DatabaseMetaData.functionNullableUnknown).append(" AS
smallint) AS \"IS_NULLABLE\", ")
+
.append("CAST(").append(DatabaseMetaData.functionNullableUnknown).append(" AS
smallint) AS \"NULLABLE\", ")
.append("CAST(null as char(1)) AS \"REMARKS\", ")
.append("CASE WHEN \"args\".\"type\" IN
('char','varchar','binary','varbinary') THEN \"args\".\"type_digits\" ELSE NULL
END AS \"CHAR_OCTET_LENGTH\", ")
.append("\"args\".\"number\" AS \"ORDINAL_POSITION\", ")
@@ -3634,7 +3694,7 @@ public class MonetDatabaseMetaData exten
.append("FROM \"sys\".\"args\", \"sys\".\"functions\",
\"sys\".\"schemas\" ")
.append("WHERE \"args\".\"func_id\" = \"functions\".\"id\" ")
.append("AND \"functions\".\"schema_id\" = \"schemas\".\"id\" ")
- // exclude procedures (type = 2). Those need to be returned via
getProcedures()
+ // exclude procedures (type = 2). Those need to be returned via
getProcedureColumns()
.append("AND \"functions\".\"type\" <> 2");
if (schemaPattern != null) {
@@ -3643,6 +3703,9 @@ public class MonetDatabaseMetaData exten
if (functionNamePattern != null) {
query.append(" AND \"functions\".\"name\"
").append(composeMatchPart(functionNamePattern));
}
+ if (columnNamePattern != null) {
+ query.append(" AND \"args\".\"name\"
").append(composeMatchPart(columnNamePattern));
+ }
query.append(" ORDER BY \"FUNCTION_SCHEM\", \"FUNCTION_NAME\",
\"ORDINAL_POSITION\"");
return getStmt().executeQuery(query.toString());
diff --git a/sql/test/BugTracker-2016/Tests/All
b/sql/test/BugTracker-2016/Tests/All
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
