MySQL Bugs: #21267: NEP is thrown when using parameter metadata

Bug #21267	NEP is thrown when using parameter metadata
Submitted:	25 Jul 2006 9:46	Modified:	9 Mar 2007 12:51
Reporter:	wu mingxia
Status:	Closed
Category:	Connector/J	Severity:	S2 (Serious)
Version:	5.0.0-beta	OS:	Microsoft Windows (XP)
Assigned to:		Target Version:

Description:
When I use mysql-connector-java-5.0.0-beta-bin.jar to connect mysql database, I write a
sql text like "select id, name from myTable where id = ?", it works ok for myTable has
id,name columns. But if I change the sql "select id, namm from myTable where id =?" where
namm does not exist in myTable. But if I use parameterMetadata.getParameterCount(), it
will return 1.It means there is one parameter metadata available. So I use
parameterMetadata.getParameterType( param ), NEP will be thrown out. 
The error stack is:
java.lang.NullPointerException
	at com.mysql.jdbc.ResultSetMetaData.getField(ResultSetMetaData.java:381)
	at com.mysql.jdbc.ResultSetMetaData.getColumnType(ResultSetMetaData.java:259)
	at
com.mysql.jdbc.MysqlParameterMetadata.getParameterType(MysqlParameterMetadata.java:80)
	at
org.eclipse.birt.report.data.oda.jdbc.ParameterMetaData.getParameterType(ParameterMetaData
.java:134)
	at
org.eclipse.datatools.connectivity.oda.design.ui.designsession.DesignSessionUtil.toDataSet
ParametersDesign(DesignSessionUtil.java:484)

How to repeat:
write a invalid sql text with parameter place holder, the parameter metadata will show
there is parameter, but when get the detail infomation of parameter, NEP.

Suggested fix:
If the sql is not correct, the parameter metadata should be unavailabe and the count
should be zero.

This bug also happens in 3.0 and 3.1 version

Hello and thanks for your bug report. 
Verified as described by reporter:
JDK 1.6
mysql-connector-java 3.1.12, 3.1.13 and 5.0 2006-07-04 snapshot

    public static void main(String args[]) throws Exception {
        Connection testConn = null;
        PreparedStatement ps = null;
        java.sql.ParameterMetaData psMD = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            testConn = DriverManager.getConnection("jdbc:mysql://10.192.192.99:3307/test",
"root", "");
            System.out.println("Connected");
            ps = testConn.prepareStatement(Query);
            ps.setInt(1,1);
            psMD = ps. getParameterMetaData();
            System.out.println("Param cnt: "+psMD.getParameterCount());
            System.out.println("Param Mode: "+psMD.getParameterMode(1));
            System.out.println("Param Type: "+psMD.getParameterType(1)); << NPE here.

init:
deps-jar:
compile:
run:
Connected
Param cnt: 1
Param Mode: 1
Exception in thread "main" java.lang.NullPointerException
        at com.mysql.jdbc.ResultSetMetaData.getField(ResultSetMetaData.java:381)
        at com.mysql.jdbc.ResultSetMetaData.getColumnType(ResultSetMetaData.java:259)
        at
com.mysql.jdbc.MysqlParameterMetadata.getParameterType(MysqlParameterMetadata.java:80)
        at xxx.Main.main(Main.java:55)
Java Result: 1

DDL:
CREATE TABLE `bug21141` (
`Col1` int(11) NOT NULL,
`Col2` varchar(45) default NULL,
`Col3` varchar(45) default NULL,
PRIMARY KEY  (`Col1`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
private static final String Query = "SELECT Col1, Col2,Col4 FROM bug21141 WHERE Col1=?";
Offending part is ... ,Col4 FROM ... which reffers to non-existent column.

This bug is related to the code that handles the fact that not all statements are
supported for server-side prepare, therefore the syntax error that is raised when the
original statement is sent to the server for prepare causes the driver to fall back to
client-side emulation of prepared statements. These variants of prepared statements only
have parameter count metadata available to them. 

I'll put in a check for this corner case, so that you get a better error message.

I just ran into this issue where an incorrect SQL statement leads to a NPE when fetching
the parameter metadata. Since running the same broken query results in a SQLException
wouldn't throwing a SQLException when accessing the broken parameter make more sense?

The issue is that we can't tell from the client side, if the syntax error is because the
statement basically isn't preparable (so we prepare it client-side), or if it's just a
syntax error (which won't show up as a SQLException until we try and execute the statement
we prepared using client-side statements).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20509

Fixed in 5.0.5

A note has been placed in the 5.0.5 changelog.