search

trinodb / trino

Official repository of Trino, the distributed SQL query engine for big data, formerly known as PrestoSQL (https://trino.io)
https://trino.io
Apache License 2.0
10.41k stars 3k forks source link

CLI failed to parse the query result on complex map type #1146

Open oneonestar opened 5 years ago

oneonestar commented 5 years ago

Presto CLI failed to parse the query result if the key of a map is a complex type. I think this is somehow related to the serialization and deserialization of JSON.

Table:

CREATE TABLE test4(
   a map(int, row(d1 varchar, d2 int)),
   b map(row(d1 varchar, d2 int), int),
   c map(array(int), int),
   d map(map(int, int), int),
);
INSERT INTO test4 VALUES(
  map(ARRAY[2,4], ARRAY[ROW('d1_1', 1),ROW('d1_1', 2)]),
  map(ARRAY[ROW('d1_1', 1),ROW('d1_1', 2)], ARRAY[2,4]),
  map(ARRAY[ARRAY[1],ARRAY[2]], ARRAY[3, 4]),
  map(ARRAY[map(ARRAY[1, 2], ARRAY[3, 4])], ARRAY[1])
);
SELECT a from test; -- Succeeded
SELECT b from test; -- Failed
SELECT c from test; -- Failed
SELECT d from test; -- Failed

Query:

presto:test> select b from test;
java.lang.RuntimeException: Error fetching next at http://localhost:8080/v1/statement/executing/20190719_042344_00029_mts7c/xadeeabefe5264ea1af03e48e62379018/0 returned an invalid response: JsonResponse{statusCode=200, statusMessage=OK, headers={content-type=[application/json], date=[Fri, 19 Jul 2019 05:17:01 GMT], vary=[Accept-Encoding, User-Agent], x-content-type-options=[nosniff]}, hasValue=false} [Error: {"id":"20190719_042344_00029_mts7c","infoUri":"http://localhost:8080/ui/query.html?20190719_042344_00029_mts7c","partialCancelUri":"http://172.18.148.54:8080/v1/stage/20190719_042344_00029_mts7c.1","nextUri":"http://localhost:8080/v1/statement/executing/20190719_042344_00029_mts7c/xadeeabefe5264ea1af03e48e62379018/1","columns":[{"name":"b","type":"map(row(d1 varchar,d2 integer),integer)","typeSignature":{"rawType":"map","typeArguments":[{"rawType":"row","typeArguments":[{"rawType":"varchar","typeArguments":[],"literalArguments":[],"arguments":[{"kind":"LONG_LITERAL","value":2147483647}]},{"rawType":"integer","typeArguments":[],"literalArguments":[],"arguments":[]}],"literalArguments":["d1","d2"],"arguments":[{"kind":"NAMED_TYPE_SIGNATURE","value":{"fieldName":{"name":"d1","delimited":false},"typeSignature":{"rawType":"varchar","typeArguments":[],"literalArguments":[],"arguments":[{"kind":"LONG_LITERAL","value":2147483647}]}}},{"kind":"NAMED_TYPE_SIGNATURE","value":{"fieldName":{"name":"d2","delimited":false},"typeSignature":{"rawType":"integer","typeArguments":[],"literalArguments":[],"arguments":[]}}}]},{"rawType":"integer","typeArguments":[],"literalArguments":[],"arguments":[]}],"literalArguments":[],"arguments":[{"kind":"TYPE_SIGNATURE","value":{"rawType":"row","typeArguments":[{"rawType":"varchar","typeArguments":[],"literalArguments":[],"arguments":[{"kind":"LONG_LITERAL","value":2147483647}]},{"rawType":"integer","typeArguments":[],"literalArguments":[],"arguments":[]}],"literalArguments":["d1","d2"],"arguments":[{"kind":"NAMED_TYPE_SIGNATURE","value":{"fieldName":{"name":"d1","delimited":false},"typeSignature":{"rawType":"varchar","typeArguments":[],"literalArguments":[],"arguments":[{"kind":"LONG_LITERAL","value":2147483647}]}}},{"kind":"NAMED_TYPE_SIGNATURE","value":{"fieldName":{"name":"d2","delimited":false},"typeSignature":{"rawType":"integer","typeArguments":[],"literalArguments":[],"arguments":[]}}}]}},{"kind":"TYPE_SIGNATURE","value":{"rawType":"integer","typeArguments":[],"literalArguments":[],"arguments":[]}}]}}],"data":[[{"[d1_1, 1]":2,"[d1_1, 2]":4}]],"stats":{"state":"RUNNING","queued":false,"scheduled":true,"nodes":1,"totalSplits":24,"queuedSplits":9,"runningSplits":7,"completedSplits":8,"cpuTimeMillis":4,"wallTimeMillis":27,"queuedTimeMillis":0,"elapsedTimeMillis":47,"processedRows":1,"processedBytes":69,"peakMemoryBytes":0,"spilledBytes":0,"rootStage":{"stageId":"0","state":"RUNNING","done":false,"nodes":1,"totalSplits":16,"queuedSplits":9,"runningSplits":7,"completedSplits":0,"cpuTimeMillis":0,"wallTimeMillis":3,"processedRows":0,"processedBytes":0,"subStages":[{"stageId":"1","state":"RUNNING","done":false,"nodes":1,"totalSplits":8,"queuedSplits":0,"runningSplits":0,"completedSplits":8,"cpuTimeMillis":4,"wallTimeMillis":24,"processedRows":1,"processedBytes":69,"subStages":[]}]},"progressPercentage":33.333333333333336},"warnings":[]}
]
    at io.prestosql.client.StatementClientV1.requestFailedException(StatementClientV1.java:463)
    at io.prestosql.client.StatementClientV1.advance(StatementClientV1.java:402)
    at io.prestosql.cli.StatusPrinter.printInitialStatusUpdates(StatusPrinter.java:131)
    at io.prestosql.cli.Query.renderQueryOutput(Query.java:151)
    at io.prestosql.cli.Query.renderOutput(Query.java:136)
    at io.prestosql.cli.Console.process(Console.java:344)
    at io.prestosql.cli.Console.runConsole(Console.java:271)
    at io.prestosql.cli.Console.run(Console.java:155)
    at io.prestosql.cli.Presto.main(Presto.java:31)
Caused by: java.lang.IllegalArgumentException: Unable to create class io.prestosql.client.QueryResults from JSON response:
[{"id":"20190719_042344_00029_mts7c","infoUri":"http://localhost:8080/ui/query.html?20190719_042344_00029_mts7c","partialCancelUri":"http://172.18.148.54:8080/v1/stage/20190719_042344_00029_mts7c.1","nextUri":"http://localhost:8080/v1/statement/executing/20190719_042344_00029_mts7c/xadeeabefe5264ea1af03e48e62379018/1","columns":[{"name":"b","type":"map(row(d1 varchar,d2 integer),integer)","typeSignature":{"rawType":"map","typeArguments":[{"rawType":"row","typeArguments":[{"rawType":"varchar","typeArguments":[],"literalArguments":[],"arguments":[{"kind":"LONG_LITERAL","value":2147483647}]},{"rawType":"integer","typeArguments":[],"literalArguments":[],"arguments":[]}],"literalArguments":["d1","d2"],"arguments":[{"kind":"NAMED_TYPE_SIGNATURE","value":{"fieldName":{"name":"d1","delimited":false},"typeSignature":{"rawType":"varchar","typeArguments":[],"literalArguments":[],"arguments":[{"kind":"LONG_LITERAL","value":2147483647}]}}},{"kind":"NAMED_TYPE_SIGNATURE","value":{"fieldName":{"name":"d2","delimited":false},"typeSignature":{"rawType":"integer","typeArguments":[],"literalArguments":[],"arguments":[]}}}]},{"rawType":"integer","typeArguments":[],"literalArguments":[],"arguments":[]}],"literalArguments":[],"arguments":[{"kind":"TYPE_SIGNATURE","value":{"rawType":"row","typeArguments":[{"rawType":"varchar","typeArguments":[],"literalArguments":[],"arguments":[{"kind":"LONG_LITERAL","value":2147483647}]},{"rawType":"integer","typeArguments":[],"literalArguments":[],"arguments":[]}],"literalArguments":["d1","d2"],"arguments":[{"kind":"NAMED_TYPE_SIGNATURE","value":{"fieldName":{"name":"d1","delimited":false},"typeSignature":{"rawType":"varchar","typeArguments":[],"literalArguments":[],"arguments":[{"kind":"LONG_LITERAL","value":2147483647}]}}},{"kind":"NAMED_TYPE_SIGNATURE","value":{"fieldName":{"name":"d2","delimited":false},"typeSignature":{"rawType":"integer","typeArguments":[],"literalArguments":[],"arguments":[]}}}]}},{"kind":"TYPE_SIGNATURE","value":{"rawType":"integer","typeArguments":[],"literalArguments":[],"arguments":[]}}]}}],"data":[[{"[d1_1, 1]":2,"[d1_1, 2]":4}]],"stats":{"state":"RUNNING","queued":false,"scheduled":true,"nodes":1,"totalSplits":24,"queuedSplits":9,"runningSplits":7,"completedSplits":8,"cpuTimeMillis":4,"wallTimeMillis":27,"queuedTimeMillis":0,"elapsedTimeMillis":47,"processedRows":1,"processedBytes":69,"peakMemoryBytes":0,"spilledBytes":0,"rootStage":{"stageId":"0","state":"RUNNING","done":false,"nodes":1,"totalSplits":16,"queuedSplits":9,"runningSplits":7,"completedSplits":0,"cpuTimeMillis":0,"wallTimeMillis":3,"processedRows":0,"processedBytes":0,"subStages":[{"stageId":"1","state":"RUNNING","done":false,"nodes":1,"totalSplits":8,"queuedSplits":0,"runningSplits":0,"completedSplits":8,"cpuTimeMillis":4,"wallTimeMillis":24,"processedRows":1,"processedBytes":69,"subStages":[]}]},"progressPercentage":33.333333333333336},"warnings":[]}
]
    at io.prestosql.client.JsonResponse.<init>(JsonResponse.java:70)
    at io.prestosql.client.JsonResponse.execute(JsonResponse.java:144)
    at io.prestosql.client.StatementClientV1.advance(StatementClientV1.java:388)
    ... 7 more
Caused by: java.lang.IllegalArgumentException: Invalid JSON string for [simple type, class io.prestosql.client.QueryResults]
    at io.airlift.json.JsonCodec.fromJson(JsonCodec.java:137)
    at io.prestosql.client.JsonResponse.<init>(JsonResponse.java:67)
    ... 9 more
Caused by: com.fasterxml.jackson.databind.exc.InvalidDefinitionException: Cannot construct instance of `io.prestosql.client.QueryResults`, problem: Cannot cast java.lang.String to java.util.List
 at [Source: (String)"{"id":"20190719_042344_00029_mts7c","infoUri":"http://localhost:8080/ui/query.html?20190719_042344_00029_mts7c","partialCancelUri":"http://172.18.148.54:8080/v1/stage/20190719_042344_00029_mts7c.1","nextUri":"http://localhost:8080/v1/statement/executing/20190719_042344_00029_mts7c/xadeeabefe5264ea1af03e48e62379018/1","columns":[{"name":"b","type":"map(row(d1 varchar,d2 integer),integer)","typeSignature":{"rawType":"map","typeArguments":[{"rawType":"row","typeArguments":[{"rawType":"varchar","typ"[truncated 2410 chars]; line: 1, column: 2909]
    at com.fasterxml.jackson.databind.exc.InvalidDefinitionException.from(InvalidDefinitionException.java:67)
    at com.fasterxml.jackson.databind.DeserializationContext.instantiationException(DeserializationContext.java:1608)
    at com.fasterxml.jackson.databind.deser.std.StdValueInstantiator.wrapAsJsonMappingException(StdValueInstantiator.java:484)
    at com.fasterxml.jackson.databind.deser.std.StdValueInstantiator.rewrapCtorProblem(StdValueInstantiator.java:503)
    at com.fasterxml.jackson.databind.deser.std.StdValueInstantiator.createFromObjectWith(StdValueInstantiator.java:285)
    at com.fasterxml.jackson.databind.deser.ValueInstantiator.createFromObjectWith(ValueInstantiator.java:229)
    at com.fasterxml.jackson.databind.deser.impl.PropertyBasedCreator.build(PropertyBasedCreator.java:195)
    at com.fasterxml.jackson.databind.deser.BeanDeserializer._deserializeUsingPropertyBased(BeanDeserializer.java:488)
    at com.fasterxml.jackson.databind.deser.BeanDeserializerBase.deserializeFromObjectUsingNonDefault(BeanDeserializerBase.java:1287)
    at com.fasterxml.jackson.databind.deser.BeanDeserializer.deserializeFromObject(BeanDeserializer.java:326)
    at com.fasterxml.jackson.databind.deser.BeanDeserializer.deserialize(BeanDeserializer.java:159)
    at com.fasterxml.jackson.databind.ObjectMapper._readMapAndClose(ObjectMapper.java:4013)
    at com.fasterxml.jackson.databind.ObjectMapper.readValue(ObjectMapper.java:3042)
    at io.airlift.json.JsonCodec.fromJson(JsonCodec.java:134)
    ... 10 more
Caused by: java.lang.ClassCastException: Cannot cast java.lang.String to java.util.List
    at java.lang.Class.cast(Class.java:3369)
    at io.prestosql.client.FixJsonDataUtils.fixValue(FixJsonDataUtils.java:108)
    at io.prestosql.client.FixJsonDataUtils.fixValue(FixJsonDataUtils.java:102)
    at io.prestosql.client.FixJsonDataUtils.fixData(FixJsonDataUtils.java:74)
    at io.prestosql.client.QueryResults.<init>(QueryResults.java:69)
    at sun.reflect.GeneratedConstructorAccessor14.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.fasterxml.jackson.databind.introspect.AnnotatedConstructor.call(AnnotatedConstructor.java:124)
    at com.fasterxml.jackson.databind.deser.std.StdValueInstantiator.createFromObjectWith(StdValueInstantiator.java:283)
    ... 19 more
Query is gone (server restarted?)

Pretty Print Json Dump:

[
  {
    "id": "20190718_145220_00038_z3fp8",
    "infoUri": "http://localhost:8080/ui/query.html?20190718_145220_00038_z3fp8",
    "partialCancelUri": "http://192.168.5.86:8080/v1/stage/20190718_145220_00038_z3fp8.1",
    "nextUri": "http://localhost:8080/v1/statement/executing/20190718_145220_00038_z3fp8/x799f87e8f7fd47e88cace8807ef170dd/1",
    "columns": [
      {
        "name": "f",
        "type": "map(row(d1 varchar,d2 integer),integer)",
        "typeSignature": {
          "rawType": "map",
          "typeArguments": [
            {
              "rawType": "row",
              "typeArguments": [
                {
                  "rawType": "varchar",
                  "typeArguments": [],
                  "literalArguments": [],
                  "arguments": [
                    {
                      "kind": "LONG_LITERAL",
                      "value": 2147483647
                    }
                  ]
                },
                {
                  "rawType": "integer",
                  "typeArguments": [],
                  "literalArguments": [],
                  "arguments": []
                }
              ],
              "literalArguments": [
                "d1",
                "d2"
              ],
              "arguments": [
                {
                  "kind": "NAMED_TYPE_SIGNATURE",
                  "value": {
                    "fieldName": {
                      "name": "d1",
                      "delimited": false
                    },
                    "typeSignature": {
                      "rawType": "varchar",
                      "typeArguments": [],
                      "literalArguments": [],
                      "arguments": [
                        {
                          "kind": "LONG_LITERAL",
                          "value": 2147483647
                        }
                      ]
                    }
                  }
                },
                {
                  "kind": "NAMED_TYPE_SIGNATURE",
                  "value": {
                    "fieldName": {
                      "name": "d2",
                      "delimited": false
                    },
                    "typeSignature": {
                      "rawType": "integer",
                      "typeArguments": [],
                      "literalArguments": [],
                      "arguments": []
                    }
                  }
                }
              ]
            },
            {
              "rawType": "integer",
              "typeArguments": [],
              "literalArguments": [],
              "arguments": []
            }
          ],
          "literalArguments": [],
          "arguments": [
            {
              "kind": "TYPE_SIGNATURE",
              "value": {
                "rawType": "row",
                "typeArguments": [
                  {
                    "rawType": "varchar",
                    "typeArguments": [],
                    "literalArguments": [],
                    "arguments": [
                      {
                        "kind": "LONG_LITERAL",
                        "value": 2147483647
                      }
                    ]
                  },
                  {
                    "rawType": "integer",
                    "typeArguments": [],
                    "literalArguments": [],
                    "arguments": []
                  }
                ],
                "literalArguments": [
                  "d1",
                  "d2"
                ],
                "arguments": [
                  {
                    "kind": "NAMED_TYPE_SIGNATURE",
                    "value": {
                      "fieldName": {
                        "name": "d1",
                        "delimited": false
                      },
                      "typeSignature": {
                        "rawType": "varchar",
                        "typeArguments": [],
                        "literalArguments": [],
                        "arguments": [
                          {
                            "kind": "LONG_LITERAL",
                            "value": 2147483647
                          }
                        ]
                      }
                    }
                  },
                  {
                    "kind": "NAMED_TYPE_SIGNATURE",
                    "value": {
                      "fieldName": {
                        "name": "d2",
                        "delimited": false
                      },
                      "typeSignature": {
                        "rawType": "integer",
                        "typeArguments": [],
                        "literalArguments": [],
                        "arguments": []
                      }
                    }
                  }
                ]
              }
            },
            {
              "kind": "TYPE_SIGNATURE",
              "value": {
                "rawType": "integer",
                "typeArguments": [],
                "literalArguments": [],
                "arguments": []
              }
            }
          ]
        }
      }
    ],
    "data": [
      [
        {
          "[d1_1, 1]": 2,
          "[d1_1, 2]": 4
        }
      ],
      [
        {
          "[d1_1, 1]": 2,
          "[d1_1, 2]": 4
        }
      ]
    ],
    "stats": {
      "state": "RUNNING",
      "queued": false,
      "scheduled": true,
      "nodes": 1,
      "totalSplits": 24,
      "queuedSplits": 6,
      "runningSplits": 10,
      "completedSplits": 8,
      "cpuTimeMillis": 4,
      "wallTimeMillis": 20,
      "queuedTimeMillis": 0,
      "elapsedTimeMillis": 38,
      "processedRows": 3,
      "processedBytes": 207,
      "peakMemoryBytes": 0,
      "spilledBytes": 0,
      "rootStage": {
        "stageId": "0",
        "state": "RUNNING",
        "done": false,
        "nodes": 1,
        "totalSplits": 16,
        "queuedSplits": 6,
        "runningSplits": 10,
        "completedSplits": 0,
        "cpuTimeMillis": 1,
        "wallTimeMillis": 5,
        "processedRows": 0,
        "processedBytes": 0,
        "subStages": [
          {
            "stageId": "1",
            "state": "RUNNING",
            "done": false,
            "nodes": 1,
            "totalSplits": 8,
            "queuedSplits": 0,
            "runningSplits": 0,
            "completedSplits": 8,
            "cpuTimeMillis": 3,
            "wallTimeMillis": 15,
            "processedRows": 3,
            "processedBytes": 207,
            "subStages": []
          }
        ]
      },
      "progressPercentage": 33.333333333333336
    },
    "warnings": []
  }
]
electrum commented 5 years ago

This is a known, long-standing issue. Unfortunately, we screwed up and encoded maps as normal JSON maps, which only allow strings as the keys. They need to be encoded as a list of entry pairs, but that would break existing clients, and it’s never been a priority to figure out how to solve this in a compatible way.

This is one of the things to be fixed by the v2 protocol, which had been stalled for a while, but just last week we had a long meeting and finally resolved most of the blocking design issues.

martint commented 5 years ago

Yeah, this is a long-standing issue for maps that contain keys of complex types. Unfortunately, there's no easy way to fix this without breaking backward compatibility, so we've been pushing it until we start work on the V2 protocol. We had good discussions with the folks at TreasureData during the conference in Japan, and they have expressed interest in working on that project. cc @xerial @Lewuathe

hackeryang commented 1 year ago

We also encountered a similar problem:
image
Then we found that it can be fixed by some modification of source codes mentioned in this issue: https://github.com/trinodb/trino/issues/4589