[jira] [Commented] (CALCITE-4519) INSERT mysql table operate failure

["C. Alexander Leigh (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4519?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17449923#comment-17449923 ] 

C. Alexander Leigh commented on CALCITE-4519:
---------------------------------------------

Just ran into this and can confirm it is still an issue in 1.28.0

> INSERT mysql table operate failure
> ----------------------------------
>
>                 Key: CALCITE-4519
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4519
>             Project: Calcite
>          Issue Type: Bug
>    Affects Versions: 1.26.0
>         Environment: JDK: 15.0.2
> calcite: 1.26.0
> mysql: 5.7.31
>            Reporter: wang_da_ye
>            Priority: Major
>
> I have a mysql table as follow:
> {code:java}
> | my_test | CREATE TABLE `my_test`(  
> `id` int(11) NOT NULL AUTO_INCREMENT, 
> `name` varchar(200) DEFAULT NULL,  PRIMARY KEY (`id`))
> ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |
> {code}
>  
> my code as follow:
> {code:java}
> Connection connection = Util.getConnect("/model.json");
> Statement statement = connection.createStatement();
> String sql = "INSERT INTO test.my_test(name) VALUES('test...')";
> statement.execute(sql); //a error here
> {code}
>  
> debug calcite code, I found:
> JdbcSchema#getRelDataType() maybe some problem.   this function parse row name, and transform to RelProtoDataType object.get meta data used DatabaseMetaData class.
> then,the result set of the metadata is obtained,as follow code:
> {code:java}
> final ResultSet resultSet = metaData.getColumns(catalogName, schemaName, tableName, null);{code}
>  
> after,in while loop, resultSet get each row meta, like sqlType,nullable.
> my test table "my_test" id is primary key, so this filed is not null, but this filed has a "auto_increment" attribute.
> it means that:
> {code:java}
> INSERT INTO test.my_test(name) VALUES('test...'){code}
> this sql run ok, I can omit the id filed.
> in
> {code:java}
> statement.execute(sql);{code}
> this sentence, trigger validate,and throws a exeception:Column 'id' has no default value and does not allow NULLs
>  the same sql in mysql 5.7.31 run ok, but in calcite get a error,
> JdbcSchema#getRelDataType() there may be a lack of judgment.in this function,add a "IS_AUTOINCREMENT" meta info, may be can deal with that,
> in validate logic,if a filed has "nullable" and "autoincrement" attribute may be can skip the validate.
>  
>  



--
This message was sent by Atlassian Jira
(v8.20.1#820001)