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

Bug #21267	NEP is thrown when using parameter metadata
Submitted:	25 Jul 2006 7:46	Modified:	9 Mar 2007 11:51
Reporter:	wu mingxia	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.0.0-beta	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

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.toDataSetParametersDesign(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.