[CONJ-812] getBestRowIdentifier returns incorrect value as SCOPE Created: 2020-08-04  Updated: 2021-01-29  Resolved: 2020-08-25

Status: Closed
Project: MariaDB Connector/J
Component/s: metadata
Affects Version/s: None
Fix Version/s: 2.7.0

Type: Bug Priority: Major
Reporter: Lawrin Novitsky Assignee: Diego Dupin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to CONJ-851 metadata getBestRowIdentifier incompa... Closed

 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

 Comments   
Comment by Lawrin Novitsky [ 2020-08-09 ]

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.

Comment by Lawrin Novitsky [ 2020-08-09 ]

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.

Comment by Lawrin Novitsky [ 2020-08-10 ]

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

Comment by Diego Dupin [ 2020-08-25 ]

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
Comment by Diego Dupin [ 2020-08-25 ]

corrected with https://github.com/mariadb-corporation/mariadb-connector-j/commit/f3055c7c7b3e4afb04718e0026240d7ae6295d47 and https://github.com/mariadb-corporation/mariadb-connector-j/commit/09541596d6dfad61973ec6cf38ddc14f1dbea43d

Generated at Thu Feb 08 03:18:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.