Bug #102131 UpdatableResultSet NPE when using derived queries or views
Submitted: 3 Jan 8:02 Modified: 12 Mar 21:36
Reporter: Sri Sudarsan Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:8.0.22 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[3 Jan 8:02] Sri Sudarsan
Description:
When using CONCUR_UPDATABLE to query a view with a derived value , NPE occurs in UpdatableResultSet causing SQLException

this is because when a view with a derived value is used, Resultmetadata field dbname is null for derived columns

thus in UpdatableResultSet.java  checkUpdatability() causes NPE thus causing SQLException

How to repeat:
create table user (id int,name varchar(10));
insert into user values (1,'a');
create table age (id int,age int);
insert into age values (1,20);
create view user_age as select name,ifnull(age,0) age from user inner join age on user.id = age.id;

then run the below standalone code 

package com;

import java.sql.*;
import java.util.logging.Logger;

public class Test {

    public static void main(String[] args) {
        try (Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "user", "pass");
             Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
             ResultSet resultSet = stmt.executeQuery("select * from user_age")
        ) {
            if (resultSet.next())
                System.out.println(resultSet.getString("age"));
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
    }
}

output will be 

java.sql.SQLException
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200)
	at com.Test.main(Test.java:14)

on further debugging found that this was caused becuase of NPE in UpdatableResultSet.java 286 as the otherDbName was null
[4 Jan 6:15] MySQL Verification Team
Hello Sri Sudarsan,

Thank you for the report and test case.

regards,
Umesh
[12 Mar 21:36] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 8.0.24 changelog: 

"Creation of an UpdatableResultSet failed with a NullPointerException when it was generated by querying a view with a derived value."