Java + MySQL + Inline User-Variables: Same query works in SQL editor, wrong outcome in JDBD (org.skife.jdbi)

Question

I'm running a query like this:

INSERT INTO `ABC` (
`col1`,
`col2`)
SELECT
`t`.`col1`,
`t`.`col2`
FROM (
SELECT
    `opr`.`col1`,
    @counter := IF(@var_2 = `opr`.`col_2`, @counter + 1, 1) AS `counter`,
    @val_2 := `opr`.`col2`
FROM 
    (...Some select ...) `opr`
) t

Running this query in SequelPro (SQL editor) works perfect. However, when running this using org.skife.jdbi (as an @SqlUpdate), the user-defined variable @counter is always 1, producing the wrong update.

Moreover, I've tried warpping up this update query into a Stored Procedure: - When I call the SP from SequelPro, I get the correct results. - When I call the SP from JDBI, I get wrong results (@counter == 1).

The SQL query Java is executing is correct (I've looked my MySQL log to check what Java is executing), however is failing to evaluate correctly my user-defined variables, wether I call an explicit query or if I call a SP containing my query.

Ideas? Is this a bug of JDBC or org.skife.jdbi?

Can you get expected results if you use it with plain JDBC?

vector
– vector

2015-10-22 20:35:13 +00:00
Commented Oct 22, 2015 at 20:35 — vector
– vector, Commented Oct 22, 2015 at 20:35

Felipe Baytelman · Accepted Answer · 2015-10-22 21:46:44Z

Found the solution:

Given JDBC starts a new/blank client environment, the variables only live in the context of the query environment (Or something like that).

As the variables have never been set before, they are initially undefined.
On the other hand, SQL editors will persist them.

During the execution of the query, the := setter was failing to initialize a variable for the for the first time when using row values, not sure why.

However, if the nested SELECT is preceded with another nested SELECT that initializes the variables, the behavior of the second SELECT (the one using the := setter with row values) is the expected one.

So adding

(SELECT @var_1 := 0, @var_2 := 0) initialize_variables,

before the operational nested SELECT.

This is the structure of my final query, which addresses the issue:

INSERT INTO `table_name` (
    `col_1`, `col_2`)
SELECT
    `query `.`col_1 `,
    `query `.`col_2`
FROM
    (SELECT @var_1 := 0, @counter := 0) `initialize_variables`,
    (
    SELECT
        ...values...,
        @counter := IF(@var_1 = `col_1`, @counter + 1, 1) AS `col_1`,
        @var_1 := `col_2`
    FROM 
        ... some fancy query ...
    ) `query`

Collectives™ on Stack Overflow

Java + MySQL + Inline User-Variables: Same query works in SQL editor, wrong outcome in JDBD (org.skife.jdbi)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related