Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
Description
According to documentation SCOPE field of the resultset may contain DatabaseMetaData.bestRowTemporary,
DatabaseMetaData.bestRowTransaction, DatabaseMetaData.bestRowSession values only. Looking in the [code|
But https://github.com/mariadb-corporation/mariadb-connector-j/blob/a5c414d35813c683fdbbb46e9d2ad6149315c88e/src/main/java/org/mariadb/jdbc/MariaDbDatabaseMetaData.java#L1071] one can see that DatabaseMetaData.bestRowUnknown is always returned in this field, which is valid value for the PSEUDO_COLUMN field, but not for the SCOPE. Btw, comments to the code method say the same. Also bestRowUnknown has the same value as one of bestRowTemporary. So, in fact applications read it not as bestRowUnknown, but bestRowTemporary.
The value for PSEUDO_COLUMN is 1. It corresponds bestRowNotPseudo, but does not look very nice. And maybe the field IS_GENERATED should be considered here.
Also I am not sure if MUL index columns should be included in the resultset. At first look - it should not. Mixing unique and primary indexes columns does look quite correct, but is rather harmless
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
According to [documentation|https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getBestRowIdentifier-java.lang.String-java.lang.String-java.lang.String-int-boolean-] SCOPE field of the resultset may contain DatabaseMetaData.bestRowTemporary,
DatabaseMetaData.bestRowTransaction, DatabaseMetaData.bestRowSession values only. Looking in the [code| But https://github.com/mariadb-corporation/mariadb-connector-j/blob/a5c414d35813c683fdbbb46e9d2ad6149315c88e/src/main/java/org/mariadb/jdbc/MariaDbDatabaseMetaData.java#L1071] one can see that DatabaseMetaData.bestRowUnknown is always returned in this field, which is valid value for the PSEUDO_COLUMN field, but not for the SCOPE. Btw, comments to the code method say the same. I would also imagine that bestRowUnknown has the same value as one of bestRowTemporary, bestRowTransaction or bestRowSession. So, in fact applications read it not as bestRowUnknown. The value for PSEUDO_COLUMN is 1. It probably corresponds to one of bestRowUnknown, bestRowNotPseudo or bestRowPseudo, but does not look very nice. And maybe the field IS_GENERATED should be considered here. Also I am not sure if MUL index columns should be included in the resultset. At first look - it should not. |
According to [documentation|https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getBestRowIdentifier-java.lang.String-java.lang.String-java.lang.String-int-boolean-] SCOPE field of the resultset may contain DatabaseMetaData.bestRowTemporary,
DatabaseMetaData.bestRowTransaction, DatabaseMetaData.bestRowSession values only. Looking in the [code| But https://github.com/mariadb-corporation/mariadb-connector-j/blob/a5c414d35813c683fdbbb46e9d2ad6149315c88e/src/main/java/org/mariadb/jdbc/MariaDbDatabaseMetaData.java#L1071] one can see that DatabaseMetaData.bestRowUnknown is always returned in this field, which is valid value for the PSEUDO_COLUMN field, but not for the SCOPE. Btw, comments to the code method say the same. Also bestRowUnknown has the same value as one of bestRowTemporary. So, in fact applications read it not as bestRowUnknown, but bestRowTemporary. The value for PSEUDO_COLUMN is 1. It corresponds bestRowNotPseudo, but does not look very nice. And maybe the field IS_GENERATED should be considered here. Also I am not sure if MUL index columns should be included in the resultset. At first look - it should not. Mixing unique and primary indexes columns does look quite correct, but is rather harmless |
One more thing, if index is unique, them field may be NULL. and then we cannot say, that it "uniquely identifies a row". Thus, adding AND IS_NULLABLE='NO' to WHERE clause looks reasonable. However, if index is composite, that would be not enough - such index columns should be completely omitted.
Not to start new ticket for each little thing -
getMaxProcedureNameLength (and maybe others similar) return 256, which seems to be number of octets rather than number of characters, which is 64(https://mariadb.com/kb/en/identifier-names/)
For some reason it's 0 for catalog, and 32 for schemas. Since database is catalog in C/J, i'd imagine those have to be 64 and 0, respectively
So to resume :
getBestRowIdentifier:
- DatabaseMetaData.bestRowUnknown must replace DatabaseMetaData.bestRowSession - agree
- Pseudo-code - agree
- about column_key, only 'PRI' columns must be taken in account or if not present, 'UNI' depending of nullable parameter. 'MUL' is wrong there
getIndexInfo
- change value 3 by tableIndexOther
getMaxProcedureNameLength()
- right, 64 is the good value
corrected with https://github.com/mariadb-corporation/mariadb-connector-j/commit/f3055c7c7b3e4afb04718e0026240d7ae6295d47 and https://github.com/mariadb-corporation/mariadb-connector-j/commit/09541596d6dfad61973ec6cf38ddc14f1dbea43d
| issue.field.resolutiondate | 2020-08-25 15:12:13.0 | 2020-08-25 15:12:13.094 |
| Fix Version/s | 2.7.0 [ 24279 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 111960 ] | MariaDB v4 [ 135184 ] |
I'd make it
SQLString sql =
"SELECT "
+ DatabaseMetaData.bestRowSession
+" SCOPE, COLUMN_NAME,"
+ dataTypeClause("COLUMN_TYPE")
+" DATA_TYPE, DATA_TYPE TYPE_NAME,"
" IF(NUMERIC_PRECISION IS NULL, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION) COLUMN_SIZE, 0 BUFFER_LENGTH,"
" NUMERIC_SCALE DECIMAL_DIGITS,"
" if(IS_GENERATED='NEVER'," + DatabaseMetaData.bestRowNotPseudo + "," + DatabaseMetaData.bestRowPseudo + ") PSEUDO_COLUMN"
" FROM INFORMATION_SCHEMA.COLUMNS"
" WHERE COLUMN_KEY IN('PRI', 'UNI')"
" AND "
+catalogCond("TABLE_SCHEMA",catalog)
+" AND TABLE_NAME = "
+escapeQuote(table);
Also, one small thing not really related to this. In the getIndexInfo method in the SQL query there is 3 TYPE, which would be better to write as
"+ DatabaseMetaData.tableIndexOther + " TYPE
or the comment what the "3" is, would suffice.