Union PreparedStatement ParameterMetaData Parameter value unbound Issue

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Union PreparedStatement ParameterMetaData Parameter value unbound Issue

lewjackman
This post was updated on .
Using a Union in a PreparedStatement seems to cause issues with the
ParameterMetaData.

We believe this is the cause of an issue we are encountering when using the
thin client to execute such a query.

This unit test should illustrate:

package org.apache.phoenix.compile;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.fail;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.phoenix.query.BaseConnectionlessQueryTest;
import org.apache.phoenix.util.PropertiesUtil;
import org.apache.phoenix.util.TestUtil;
import org.junit.BeforeClass;
import org.junit.Test;

public class UnionPreparedStatementFailTest extends BaseConnectionlessQueryTest {
       
    @BeforeClass
    public static void setupClass() throws Exception {
        Properties props = PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(getUrl(), props);
        try {
            conn.setAutoCommit(true);
            conn.createStatement().execute("CREATE TABLE TAB1 (ID VARCHAR NOT NULL CONSTRAINT PK PRIMARY KEY (ID))");
            conn.createStatement().execute("CREATE TABLE TAB2 (ID VARCHAR NOT NULL CONSTRAINT PK PRIMARY KEY (ID))");
        } finally {
            conn.close();
        }
    }
   
    @Test
    public void testNonUnionParameterMetaData() throws Exception {
        String query = "SELECT * FROM TAB1 WHERE (ID = ?) OR (ID = ?)";
        Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
        PreparedStatement statement = conn.prepareStatement(query);    
        try {
            ParameterMetaData pmd = statement.getParameterMetaData();
            assertEquals(2, pmd.getParameterCount());
            pmd.getParameterTypeName(1);
        } catch (SQLException e) {
            fail("getParameterTypeName failure : " + e.getMessage());
        }
    }
   
    @Test
    public void testJoinParameterMetaData() throws Exception {
        String query = "SELECT TAB1.ID FROM TAB1 INNER JOIN TAB2 ON TAB1.ID = TAB2.ID WHERE TAB1.ID = ? and TAB2.ID = ?";
        Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
        PreparedStatement statement = conn.prepareStatement(query);
        try {
            ParameterMetaData pmd = statement.getParameterMetaData();
            assertEquals(2, pmd.getParameterCount());
            pmd.getParameterTypeName(1);
        } catch (SQLException e) {
            fail("getParameterTypeName failure : " + e.getMessage());
        }
    }
   
    /*
     * FAILED TEST :
     * java.lang.AssertionError: getParameterTypeName failure :
     *    ERROR 2004 (INT05): Parameter value unbound. Parameter at index 1 is unbound
     */
    @Test
    public void testUnionParameterMetaData() throws Exception {
        String query = "SELECT * FROM TAB1 WHERE ID = ? UNION ALL SELECT * FROM TAB2 WHERE ID = ?";
        Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
        PreparedStatement statement = conn.prepareStatement(query);
        try {
            ParameterMetaData pmd = statement.getParameterMetaData();
            assertEquals(2, pmd.getParameterCount());
            pmd.getParameterTypeName(1);
        } catch (SQLException e) {
            fail("getParameterTypeName failure : " + e.getMessage());
        }
    }
}







--
Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Union PreparedStatement ParameterMetaData Parameter value unbound Issue

lewjackman
Does this look like an issue for which I should write a Jira?



--
Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Union PreparedStatement ParameterMetaData Parameter value unbound Issue

James Taylor
Yes, JIRA please.

On Wed, Sep 4, 2019 at 1:24 PM lewjackman <[hidden email]> wrote:
Does this look like an issue for which I should write a Jira?



--
Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Union PreparedStatement ParameterMetaData Parameter value unbound Issue

lewjackman