0

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?

Felipe Baytelman
  • 544
  • 3
  • 12

1 Answers1

1

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`
Felipe Baytelman
  • 544
  • 3
  • 12