Skip to content
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

Incorrect parsing of complex types (Arrays and Tuples) #103

Closed
Aivean opened this issue Jun 1, 2017 · 9 comments · Fixed by #769
Closed

Incorrect parsing of complex types (Arrays and Tuples) #103

Aivean opened this issue Jun 1, 2017 · 9 comments · Fixed by #769
Assignees
Milestone

Comments

@Aivean
Copy link

Aivean commented Jun 1, 2017

Here are some cases that I've found (rs is a ResultSet instance):

  1. Tuple:

    select ('1', 2) as a;
    

    can only be extracted as String:

    rs.getMetaData.getColumnTypeName(1) = "Tuple(String, UInt8)"
    rs.getObject(1) = "('1',2)"
    
  2. Nested arrays:

    select [[1,2], [3,4]] as a;
    

    cannot be extracted as Array:

    rs.getMetaData.getColumnTypeName(1) = "Array(Array(UInt8))"
    rs.getObject(1) // Method threw 'java.lang.RuntimeException' exception.
                               // Parse exception: ByteFragment{[[[1,2],[3,4]]], start=0, len=13}
    rs.getArray(1) // Method threw 'java.lang.IllegalStateException' exception.
    
  3. Tuples nested in Array:

    select [('a',1), ('b', 2)] as a;
    

    strange parsing behavior when parsed as Array:

    rs.getMetaData.getColumnTypeName(1) = Array(Tuple(String, UInt8))
    rs.getArray(1)={ru.yandex.clickhouse.ClickHouseArray}:
           array = {java.lang.String[4]@3644} 
           0 = "('a'"
           1 = "1)"
           2 = "('b'"
           3 = "2)"
    
@serebrserg
Copy link
Contributor

Thanks.
Currently the driver has just a basic arrays support. All of cases you listed are expected.
I'll check what can be done on that.

@serebrserg serebrserg self-assigned this Jun 2, 2017
@serebrserg
Copy link
Contributor

Nested arrays are quite straightforward as I see. It just needs careful implementation.
Tuples on other hand are not clear for me now. Either they should be represented as arrays of Objects, or for some fixed lengths as Objects with typed fields.

@Aivean
Copy link
Author

Aivean commented Jun 5, 2017

As Tuple is not a part of standard JDBC, I think it's reasonable to return tuple as Object, internally backed by ClickhouseTuple, which has interface similar to ResultSet (get column type, get typed column value by index).

@vladsaraykin
Copy link

vladsaraykin commented Nov 19, 2019

@serebrserg Hello, Is this issue actual?
Now I get result of parse 0 - '(\'2018-01-31 22:02:24\''; 1 - '2018-01-31 22:02:24\''
Result query in http [('2018-01-31 22:02:24',1 - 2018-01-31 22:02:24')]

@mygoalistokillbill
Copy link

any updates?

@zhicwu
Copy link
Contributor

zhicwu commented Jul 14, 2021

The first two work now but not the last. I agree with Aivean that we do need ClickHouseTuple(in addition to ClickHouseArray and ClickHouseBitmap etc.). Let me see if I can address the issue using RowBinary format.

@leiless
Copy link

leiless commented Oct 21, 2021

I cannot find ClickhouseTuple implementation in ClickHouse JDBC as of version 0.3.1-patch, the tuple may a suitable type for compound type.

However, the Nested type is internally discrete array columns.
Although ClickHouse will assure those discrete arrays will have the same array length upon insertion.

@zhicwu
Copy link
Contributor

zhicwu commented Oct 21, 2021

I cannot find ClickhouseTuple implementation in ClickHouse JDBC as of version 0.3.1-patch, the tuple may a suitable type for compound type.

Most data types including Tuple and mixed use of Array, Map and Tuple etc. are supported on develop branch. But it's still experimental and only works with gRPC and RowBinary format at the moment, for example:

// create table test_table(
//   array_column Array(Tuple(UInt8, LowCardinality(String))),
//   array_tuple Tuple(Map(String, String), String)
// )engine=Memory

// insert into test_table values([(1,'a'), (2,'b')], ({'a':'1','b':'2'},'c'))

ClickHouseNode server = ClickHouseNode.of("localhost", ClickHouseProtocol.GRPC, 9100, "default");
try (ClickHouseClient client = ClickHouseClient.newInstance(server.getProtocol());
    ClickHouseResponse resp = client.connect(server).format(ClickHouseFormat.RowBinaryWithNamesAndTypes)
      .query("select * from test_table").execute().get()) {
    for (ClickHouseRecord r : resp.records()) {
      List<?>[] array = (List<?>[]) r.getValue(0).asObject();
      List<?> tuple = r.getValue(1).asObject(List.class);

      Map<String, String> map = (Map<String, String>) tuple.get(0);
      ...
    }
}

JDBC driver will be enhanced accordingly.

@zhicwu
Copy link
Contributor

zhicwu commented Dec 4, 2021

Starting from 0.3.2-test1, JDBC driver supports both grpc and http protocol, and mixed used of nested types are supported.

For examples:

@Test(groups = "integration")
public void testArray() throws SQLException {
    try (ClickHouseConnection conn = newConnection(new Properties());
            Statement stmt = conn.createStatement()) {
        ResultSet rs = stmt.executeQuery("select [1,2,3] v1, ['a','b', 'c'] v2, arrayZip(v1, v2) v3");
        Assert.assertTrue(rs.next());

        Assert.assertEquals(rs.getObject(1), new short[] { 1, 2, 3 });
        Assert.assertEquals(rs.getArray(1).getArray(), new short[] { 1, 2, 3 });
        Assert.assertTrue(rs.getArray(1).getArray() == rs.getObject(1));

        Assert.assertEquals(rs.getObject(2), new String[] { "a", "b", "c" });
        Assert.assertEquals(rs.getArray(2).getArray(), new String[] { "a", "b", "c" });
        Assert.assertTrue(rs.getArray(2).getArray() == rs.getObject(2));

        Assert.assertEquals(rs.getObject(3), new List[] { Arrays.asList((short) 1, "a"),
                Arrays.asList((short) 2, "b"), Arrays.asList((short) 3, "c") });
        Assert.assertEquals(rs.getArray(3).getArray(), new List[] { Arrays.asList((short) 1, "a"),
                Arrays.asList((short) 2, "b"), Arrays.asList((short) 3, "c") });
        Assert.assertTrue(rs.getArray(3).getArray() == rs.getObject(3));

        Assert.assertFalse(rs.next());
    }
}

@Test(groups = "integration")
public void testTuple() throws SQLException {
    try (ClickHouseConnection conn = newConnection(new Properties());
            Statement stmt = conn.createStatement()) {
        ResultSet rs = stmt
                .executeQuery(
                        "select (1::Int16, 'a', 1.2::Float32, [1,2]::Array(Nullable(UInt8)), map(toUInt32(1),'a')) v");
        Assert.assertTrue(rs.next());
        List<?> v = rs.getObject(1, List.class);
        Assert.assertEquals(v.size(), 5);
        Assert.assertEquals(v.get(0), Short.valueOf((short) 1));
        Assert.assertEquals(v.get(1), "a");
        Assert.assertEquals(v.get(2), Float.valueOf(1.2F));
        Assert.assertEquals(v.get(3), new Short[] { 1, 2 });
        Assert.assertEquals(v.get(4), Collections.singletonMap(1L, "a"));
        Assert.assertFalse(rs.next());
    }
}

@zhicwu zhicwu added this to the 0.3.2 Release milestone Dec 4, 2021
@zhicwu zhicwu linked a pull request Dec 4, 2021 that will close this issue
10 tasks
@zhicwu zhicwu closed this as completed Dec 4, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging a pull request may close this issue.

6 participants