search

asyncer-io / r2dbc-mysql

Reactive Relational Database Connectivity for MySQL. The official successor to mirromutth/r2dbc-mysql(dev.miku:r2dbc-mysql).
https://r2dbc.io
Apache License 2.0
195 stars 21 forks source link

[bug] JSON type breaks when UNION query #91

Closed judgedred closed 1 year ago

judgedred commented 1 year ago

Describe the bug

To Reproduce

package com.infrastructure.db;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.r2dbc.core.DatabaseClient;

import java.util.List;

import static java.util.Objects.requireNonNull;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertIterableEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;

@SpringBootTest
public class R2dbcMysqlIT {

  @Autowired
  private DatabaseClient databaseClient;

  @BeforeEach
  void setUp() {
    var initDb = """
        create schema r2dbc_mysql_test;
        use r2dbc_mysql_test;
        create table phone_call (id int not null, agents json not null);
        insert into phone_call values(1, '[{"id" : "1", "name": "agent_1"}, {"id" : "2", "name": "agent_2"}]');
        insert into phone_call values(2, '[{"id" : "3", "name": "agent_3"}]');
        """;
    databaseClient.sql(initDb)
        .then()
        .block();
  }

  @AfterEach
  void tearDown() {
    var cleanDb = "drop schema r2dbc_mysql_test;";
    databaseClient.sql(cleanDb)
        .then()
        .block();
  }

  @Test
  void noErrorWhenSimpleQuery() {
    var expected = List.of(
        new Agent(1, "agent_1"),
        new Agent(2, "agent_2"),
        new Agent(3, "agent_3"));
    var query = "select * from r2dbc_mysql_test.phone_call;";
    var agentListType = new TypeToken<List<Agent>>(){}.getType();
    List<PhoneCall> phoneCalls = databaseClient.sql(query)
        .map(row -> new PhoneCall(
            requireNonNull(row.get("id", Integer.class)),
            new Gson().fromJson(row.get("agents", String.class), agentListType)))
        .all()
        .collectList()
        .block();
    assertNotNull(phoneCalls);
    assertEquals(2, phoneCalls.size());
    assertIterableEquals(expected, phoneCalls.stream().flatMap(p -> p.agents.stream()).toList());
  }

  @Test
  void errorWhenUnionQuery() {
    var expected = List.of(
        new Agent(1, "agent_1"),
        new Agent(2, "agent_2"),
        new Agent(3, "agent_3"));
    var query = """
        (select * from r2dbc_mysql_test.phone_call
        where id = 1)
        union
        (select * from r2dbc_mysql_test.phone_call
        where id = 2);
        """;
    var agentListType = new TypeToken<List<Agent>>(){}.getType();
    List<PhoneCall> phoneCalls = databaseClient.sql(query)
        .map(row -> new PhoneCall(
            requireNonNull(row.get("id", Integer.class)),
            new Gson().fromJson(row.get("agents", String.class), agentListType)))
        .all()
        .collectList()
        .block();
    assertNotNull(phoneCalls);
    assertEquals(2, phoneCalls.size());
    assertIterableEquals(expected, phoneCalls.stream().flatMap(p -> p.agents.stream()).toList());
  }

  private static class PhoneCall {
    int id;
    List<Agent> agents;

    PhoneCall(int id, List<Agent> agents) {
      this.id = id;
      this.agents = agents;
    }
  }

  private static class Agent {
    int id;
    String name;

    Agent(int id, String name) {
      this.id = id;
      this.name = name;
    }

    @Override
    public boolean equals(Object o) {
      if (this == o) return true;
      if (!(o instanceof Agent agent)) return false;

      if (id != agent.id) return false;
      return name.equals(agent.name);
    }

    @Override
    public int hashCode() {
      int result = id;
      result = 31 * result + name.hashCode();
      return result;
    }
  }
}

Expected behavior Conversion from JSON mysql type to String java type works with UNION queries.

Screenshots asyncer_simple_query asyncer_simple_query_string_codec asyncer_union_query asyncer_union_query_string_codec miku_union_query miku_union_query_string_codec tested versions mysql_version io.asyncer:r2dbc-mysql:1.0.0
dev.miku:r2dbc-mysql:0.8.2.RELEASE

Additional context It seems that the implementation of StringCodec has changed. Conversion works fine for dev.miku:r2dbc-mysql:0.8.2.RELEASE. Also conversion works as well for org.mariadb:r2dbc-mariadb:1.1.4.

jchrys commented 1 year ago

Thanks @judgedred.

Thank you for taking the time to send us your valuable issue report. We appreciate your effort in providing detailed information, which will help us better understand and address the problem.

I will go over this issue.

If you are interested in contributing further by creating a pull request to fix the issue, please feel free to let us know. We would be more than happy to collaborate with you on this matter.

jchrys commented 1 year ago

I have identified the root cause of the bug, and it will be fixed in the next version.

judgedred commented 1 year ago

Thank you!
And thank you guys for taking over this project!

jchrys commented 1 year ago

Hello! @judgedred.

We have just resolved the issue you mentioned, and it has been included in io.asyncer:r2dbc-mysql:1.0.1-SNAPSHOT. If you'd like to verify that the problem has been addressed, please feel free to use our snapshot repository provided below.

Snapshot Repository: https://s01.oss.sonatype.org/content/repositories/snapshots

Thank you for your patience and support!

judgedred commented 1 year ago

Thank you for managing this so quickly!