[CONJ-1048] INTERVAL missing from getSQLKeywords Created: 2023-01-31  Updated: 2023-03-01  Resolved: 2023-02-28

Status: Closed
Project: MariaDB Connector/J
Component/s: metadata
Affects Version/s: 3.1.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Diego Dupin
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

From https://stackoverflow.com/questions/75298378/unable-to-persist-an-object-in-mariadb-caused-by-org-mariadb-jdbc-internal-uti

Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'interval, last_updated) values ('5MIN', '2023-02-01 00:56:16')' at line 1
 
Query is : insert into cms_kpi_config (interval, last_updated) values ('5MIN', '2023-02-01 00:56:16.0')
 
at org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:125)
 
at org.mariadb.jdbc.internal.util.ExceptionMapper.throwException(ExceptionMapper.java:69)
 
at org.mariadb.jdbc.MariaDbStatement.executeQueryEpilog(MariaDbStatement.java:242)
 
at org.mariadb.jdbc.MariaDbClientPreparedStatement.executeInternal(MariaDbClientPreparedStatement.java:210)
 
at org.mariadb.jdbc.MariaDbClientPreparedStatement.executeUpdate(MariaDbClientPreparedStatement.java:186)
 
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:384)
 
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
 
... 223 more
 
Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'interval, last_updated) values ('5MIN', '2023-02-01 00:56:16')' at line 1
 
Query is : insert into cms_kpi_config (interval, last_updated) values ('5MIN', '2023-02-01 00:56:16.0')
 
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:939)
 
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQueries(AbstractQueryProtocol.java:775)
 
at org.mariadb.jdbc.MariaDbClientPreparedStatement.executeInternal(MariaDbClientPreparedStatement.java:201)
 
... 226 more

Seems to be missing from https://github.com/mariadb-corporation/mariadb-connector-j/blob/master/src/main/java/org/mariadb/jdbc/DatabaseMetaData.java#L1348

 Comments   
Comment by Diego Dupin [ 2023-02-02 ]

danblack have you any idea when INTERVAL has been defined in SQL standard ?
DatabaseMetadata.getSQLKeywords list the SQL keywords that are NOT in SQL:2003 keywords, and i cannot found when this has been defined.

Comment by Daniel Black [ 2023-02-02 ]

Does this help?

$ git blame sql/sql_yacc.yy | grep INTERV
 
Blaming lines: 100% (19456/19456), done.
 
d168601e83dd (Alexander Barkov                   2019-10-03 14:02:00 +0400   483) %token  <kwd> DATE_ADD_INTERVAL             /* MYSQL-FUNC */
 
d168601e83dd (Alexander Barkov                   2019-10-03 14:02:00 +0400   484) %token  <kwd> DATE_SUB_INTERVAL             /* MYSQL-FUNC */
 
d168601e83dd (Alexander Barkov                   2019-10-03 14:02:00 +0400   546) %token  <kwd> INTERVAL_SYM                  /* SQL-2003-R */

Comment by Diego Dupin [ 2023-02-28 ]

INTERVAL is a reserved word from SQL2003 ( https://ronsavage.github.io/SQL/sql-2003-2.bnf.html#reserved%20word) so doesn't have to be in DatabaseMetadata.getSQLKeywords.

Comment by Daniel Black [ 2023-02-28 ]

So why isn't it escaped in the INSERT sql? Is that a responsibility higher up the stack?

Comment by Diego Dupin [ 2023-03-01 ]

because connector doesn't parse sql command. it would really be a pain in term of performance if connector need to parse all sql commands in order to escape part that connector imagine can be a property.

I imagine that initially, this DatabaseMetadata.getSQLKeywords was designed in order to prevent user to use reserved words, but that was badly designed, since it is not static, but need a connection first. I've seen some ORM use a list of reserved words like this, but checking now, not even hibernate does so. So i imagine there not so much we can do about that.

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