New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Batch SQLInsert weird behaviour #834

Closed
erosb opened this Issue Jun 30, 2014 · 5 comments

Comments

Projects
None yet
2 participants
@erosb

erosb commented Jun 30, 2014

Hello,

batch insertions fail if different columns are specified for the different rows.
For example if the following code is executed:

QSurvey survey = new QSurvey("survey");
new SQLDeleteClause(conn, SQLTemplates.DEFAULT, survey).execute();
SQLInsertClause insert = new SQLInsertClause(conn, SQLTemplates.DEFAULT, survey);
insert.set(survey.name, "row[0] name");
insert.addBatch();
insert.set(survey.name2, "row[1] name2");
insert.addBatch();
insert.execute();

So in the first inserted row, the name column's value is specified, in the second row the name2 column's value is specified. The expected DB state would be the following after executing the insert:

name name2
"row[0] name" null
null "row[1] name2"

But instead the following happens:

name name2
"row[0] name" null
"row[1] name2" null

Unittest for reproducing the bug:

package com.mysema.query.sql.dml;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.junit.Assert;
import org.junit.Test;

import com.mysema.query.Connections;
import com.mysema.query.Tuple;
import com.mysema.query.sql.SQLQuery;
import com.mysema.query.sql.SQLTemplates;
import com.mysema.query.sql.domain.QSurvey;

public class SQLInsertBatchTest {

    @Test
    public void getSQL() throws ClassNotFoundException, SQLException {
        Connections.initH2();
        QSurvey survey = new QSurvey("survey");
        Connection conn = Connections.getConnection();
        new SQLDeleteClause(conn, SQLTemplates.DEFAULT, survey).execute();
        SQLInsertClause insert = new SQLInsertClause(conn, SQLTemplates.DEFAULT, survey);
        insert.set(survey.name, "row[0] name");
        insert.addBatch();
        insert.set(survey.name2, "row[1] name2");
        insert.addBatch();
        insert.execute();
        List<Tuple> actual = new SQLQuery(conn, SQLTemplates.DEFAULT).from(survey).list(survey.name, survey.name2);
        Assert.assertEquals(2, actual.size());
        Tuple firstRow = actual.get(0);
        Assert.assertEquals("row[0] name", firstRow.get(survey.name));
        Assert.assertEquals(null, firstRow.get(survey.name2));
        Tuple secondRow = actual.get(1);
        Assert.assertEquals(null, secondRow.get(survey.name));
        Assert.assertEquals("row[1] name2", secondRow.get(survey.name2));
    }

}

@erosb erosb changed the title from Batch insert weird behaviour to Batch SQLInsert weird behaviour Jun 30, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jul 1, 2014

Member

A solution could be to group batch statements based on the resulting sql string. That would fix also some other issues.

Member

timowest commented Jul 1, 2014

A solution could be to group batch statements based on the resulting sql string. That would fix also some other issues.

@erosb

This comment has been minimized.

Show comment
Hide comment
@erosb

erosb Jul 1, 2014

My idea was to normalize the batches list. I mean, in the above example, adding a name2=null to the 0th batch entry, and name=null to the 1st batch entry.
What other issues should I take into account?

erosb commented Jul 1, 2014

My idea was to normalize the batches list. I mean, in the above example, adding a name2=null to the 0th batch entry, and name=null to the 1st batch entry.
What other issues should I take into account?

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jul 1, 2014

Member

Normalization is more tricky I think. You need to take also into account that the values might be non-constant expressions.

Member

timowest commented Jul 1, 2014

Normalization is more tricky I think. You need to take also into account that the values might be non-constant expressions.

@timowest timowest added bug and removed bug labels Jul 6, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jul 6, 2014

Member

Related to #829

Member

timowest commented Jul 6, 2014

Related to #829

@timowest timowest added the fixed label Jul 10, 2014

@timowest timowest added this to the 3.4.2 milestone Jul 10, 2014

@timowest timowest modified the milestone: 3.4.2 Jul 19, 2014

@timowest timowest removed the fixed label Jul 19, 2014

@timowest timowest closed this Jul 19, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jul 29, 2014

Member

Released in 3.4.2

Member

timowest commented Jul 29, 2014

Released in 3.4.2

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment