SQLParseException[The types of the columns within VALUES lists must match. Found `text_array` and `object_array` at position: 8]

[amotl]

Problem

Hi. The meltano/file-to-cratedb exercise started failing on CI last night.

sqlalchemy.exc.ProgrammingError: (crate.client.exceptions.ProgrammingError) SQLParseException[The types of the columns within VALUES lists must match. Found `text_array` and `object_array` at position: 8]

2026-04-08T03:59:27.287683Z [info     ] target-cratedb sqlalchemy.exc.ProgrammingError: (crate.client.exceptions.ProgrammingError) SQLParseException[The types of the columns within VALUES lists must match. Found `text_array` and `object_array` at position: 8]
2026-04-08T03:59:27.287811Z [info     ] target-cratedb [SQL: INSERT INTO melty.countries (code, name, native, phone, capital, currency, emoji, continent, languages, "_sdc_extracted_at", "_sdc_received_at", "_sdc_batched_at", "_sdc_deleted_at", "_sdc_sequence", "_sdc_table_version", "_sdc_sync_started_at") VALU ... 12401 characters truncated ... , (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
2026-04-08T03:59:27.287955Z [info     ] target-cratedb [parameters: ('AD', 'Andorra', 'Andorra', '376', 'Andorra la Vella', 'EUR', '🇦🇩', {'code': 'EU', 'name': 'Europe'}, [{'code': 'ca', 'name': 'Catalan'}], '2022-07-17T20:43:19.031894Z', '2026-04-08T03:59:26.511690Z', '2026-04-08T03:59:26.511681Z', None, 1775620766512, None, 1775620765708, 'AE', 'United Arab Emirates', 'دولة الإمارات العربية المتحدة', '971', 'Abu Dhabi', 'AED', '🇦🇪', {'code': 'AS', 'name': 'Asia'}, [{'code': 'ar', 'name': 'Arabic'}], '2022-07-17T20:43:19.032033Z', '2026-04-08T03:59:26.511948Z', '2026-04-08T03:59:26.511681Z', None, 1775620766512, None, 1775620765708, 'AF', 'Afghanistan', 'افغانستان', '93', 'Kabul', 'AFN', '🇦🇫', {'code': 'AS', 'name': 'Asia'}, [{'code': 'ps', 'name': 'Pashto'}, {'code': 'uz', 'name': 'Uzbek'}, {'code': 'tk', 'name': 'Turkmen'}], '2022-07-17T20:43:19.032097Z', '2026-04-08T03:59:26.512142Z', '2026-04-08T03:59:26.511681Z', None, 1775620766512, None, 1775620765708, 'AG', 'Antigua and Barbuda' ... 3900 parameters truncated ... None, 1775620765708, 'ZA', 'South Africa', 'South Africa', '27', 'Pretoria', 'ZAR', '🇿🇦', {'code': 'AF', 'name': 'Africa'}, [{'code': 'af', 'name': 'Afrikaans'}, {'code': 'en', 'name': 'English'}, {'code': 'nr', 'name': 'South Ndebele'}, {'code': 'st', 'name': 'Southern Sot ... (54 characters truncated) ... 'name': 'Tswana'}, {'code': 'ts', 'name': 'Tsonga'}, {'code': 've', 'name': 'Venda'}, {'code': 'xh', 'name': 'Xhosa'}, {'code': 'zu', 'name': 'Zulu'}], '2022-07-17T20:43:19.044031Z', '2026-04-08T03:59:26.545828Z', '2026-04-08T03:59:26.511681Z', None, 1775620766546, None, 1775620765708, 'ZM', 'Zambia', 'Zambia', '260', 'Lusaka', 'ZMW', '🇿🇲', {'code': 'AF', 'name': 'Africa'}, [{'code': 'en', 'name': 'English'}], '2022-07-17T20:43:19.044090Z', '2026-04-08T03:59:26.546080Z', '2026-04-08T03:59:26.511681Z', None, 1775620766546, None, 1775620765708, 'ZW', 'Zimbabwe', 'Zimbabwe', '263', 'Harare', 'USD,ZAR,BWP,GBP,AUD,CNY,INR,JPY', '🇿🇼', {'code': 'AF', 'name': 'Africa'}, [{'code': 'en', 'name': 'English'}, {'code': 'sn', 'name': 'Shona'}, {'code': 'nd', 'name': 'North Ndebele'}], '2022-07-17T20:43:19.044137Z', '2026-04-08T03:59:26.546206Z', '2026-04-08T03:59:26.511681Z', None, 1775620766546, None, 1775620765708)]

Evaluation

On a quick feeling, without conducting any other investigations yet, this might be a regression in CrateDB nightly? I can't think of any other moving parts in this area with Meltano or meltano-target-cratedb, but of course we will also evaluate that when applicable.

On the other hand, it might be a correctness fix/improvement for CrateDB, where meltano-target-cratedb was working incorrect/sloppy before, but CrateDB accepted its input.

Next steps

To shed more light on the case, the usual proceedings are to dissect the failing operation and find a minimal reproducer to report the issue to the tracker at crate/crate, handing it over to the core team to investigate. If the reply is that the update to CrateDB is correct, we need to find an improvement on downstream components, in this case likely the Meltano target adapter meltano-target-cratedb.

[amotl]

Right, the same is also happening on the CI integration tests of the upstream meltano-target-cratedb project, failing the test cases test_sqlalchemy_url_config and test_tap_countries: Both use the TapCountries data source for conducting their integration tests, so it is effectively the same case like above when inserting data into the melty.countries table.

[amotl]

I can't think of any other moving parts in this area with Meltano or meltano-target-cratedb, but of course we will also evaluate that [...]

Well, we just discovered that the tap-countries package is sourcing its data from https://countries.trevorblades.com/. In this case, it is also possible that the data source changed, and that it has nothing to do with CrateDB at all.

[amotl]

Inspecting the table with show create table melty.countries; shows difference between good case (CrateDB 6.2.0) vs. error case (CrateDB nightly).

"continent" OBJECT(DYNAMIC) AS (
"name" TEXT,
"code" TEXT
)
"languages" ARRAY(TEXT)

"continent" OBJECT(DYNAMIC),
"languages" ARRAY(TEXT)

What is apparent, is that the languages column is always ARRAY(TEXT), while ARRAY(OBJECT) would be (more) correct?

uv run --with=meltano tap-countries 2>/dev/null | grep Andorra | jq

{
  "type": "RECORD",
  "stream": "countries",
  "record": {
    "code": "AD",
    "native": "Andorra",
    "phone": "376",
    "continent": {
      "code": "EU",
      "name": "Europe"
    },
    "capital": "Andorra la Vella",
    "currency": "EUR",
    "languages": [
      {
        "code": "ca",
        "name": "Catalan"
      }
    ],
    "emoji": "🇦🇩",
    "name": "Andorra"
  },
  "time_extracted": "2026-04-08T21:16:42.182092Z"
}

[amotl]

Observations

We found that the "good" case was actually wrong: While CrateDB accepted the input, the storage representation shows the ARRAY was stored as TEXT type. We didn't notice that, and will provide a test case to validate the flaw, and hopefully a fix for meltano-target-cratedb.

"languages": [
  "{\"code\":\"ca\",\"name\":\"Catalan\"}"
]

Details

crash --format=json -c "select * from melty.countries where name='Andorra';"

CONNECT OK
[
  {
    "code": "AD",
    "name": "Andorra",
    "native": "Andorra",
    "phone": "376",
    "capital": "Andorra la Vella",
    "currency": "EUR",
    "emoji": "\ud83c\udde6\ud83c\udde9",
    "continent": {
      "name": "Europe",
      "code": "EU"
    },
    "languages": [
      "{\"code\":\"ca\",\"name\":\"Catalan\"}"
    ],
    "_sdc_extracted_at": 1775682612900,
    "_sdc_received_at": 1775682613639,
    "_sdc_batched_at": 1775682613639,
    "_sdc_deleted_at": null,
    "_sdc_sequence": 1775682613640,
    "_sdc_table_version": null,
    "_sdc_sync_started_at": 1775682612816
  }
]
SELECT 1 row in set (0.004 sec)

Evaluation

We will also check if CrateDB's changelog mentions this update correctly, and where it was coming from. Currently, we think CrateDB improved in correctness, and the flaw is in the Meltano CrateDB adapter, which might need to improve its data (type) handling specifically for CrateDB and its container types.

[amotl]

Analysis

We will also check if CrateDB's changelog mentions this update correctly, and where it was coming from. Currently, we think CrateDB improved in correctness [...]

We think it is coming from this improvement to CrateDB.

• Set param type hints for http arguments crate#19212

The SQL DDL emitted by the Meltano adapter is certainly wrong, and CrateDB complains rightfully after improving in this regard.

CREATE TABLE melty.countries
(
    code                   STRING NOT NULL,
    name                   STRING,
    native                 STRING,
    phone                  STRING,
    capital                STRING,
    currency               STRING,
    emoji                  STRING,
    continent              OBJECT,
    languages              ARRAY (STRING),
    "_sdc_extracted_at"    TIMESTAMP WITHOUT TIME ZONE,
    "_sdc_received_at"     TIMESTAMP WITHOUT TIME ZONE,
    "_sdc_batched_at"      TIMESTAMP WITHOUT TIME ZONE,
    "_sdc_deleted_at"      TIMESTAMP WITHOUT TIME ZONE,
    "_sdc_sequence"        LONG,
    "_sdc_table_version"   LONG,
    "_sdc_sync_started_at" LONG,
    PRIMARY KEY (code)
);

Action 1

Make the Meltano adapter emit the correct SQL DDL.

• SQL DDL for ARRAY inner type is wrong, trips import from tap-countries meltano-target-cratedb#93

Action 2

We may think about adding this insight to the CrateDB changelog proactively, along the lines of:

Because of this improvement, CrateDB will now also be more strict with type handling when using the HTTP protocol. For example, while a column defined like ARRAY(STRING) happily also accepted OBJECT inner types like [{"foo": "bar"}] over the HTTP protocol, CrateDB stored them wrongly as opaque strings. Upcoming CrateDB releases will deny that.

If we could have another wish, we would like to suggest an improved error message, when possible. Instead of saying:

The types of the columns within VALUES lists must match. Found text_array and object_array.

... what about this?

Column type mismatch in column languages. Found text_array vs. object_array.

^^ Are you able to detect the mismatching column name to include it within the error message?

/cc @matriv

[amotl]

That's a minimal reproduction that worked on CrateDB until yesterday, but fails today.

crash -c 'create table t01 (id text, data array(string));'
http localhost:4200/_sql \
  stmt='insert into t01 (id, data) values (?, ?), (?, ?)' \
  args:='["a", [{"foo": "bar"}], "b", [{"foo": "bar"}]]' --print bB
crash -c 'select * from t01;'

The HTTP conversation including the error of the insert statement is:

{
    "args": [
        "a",
        [
            {
                "foo": "bar"
            }
        ],
        "b",
        [
            {
                "foo": "bar"
            }
        ]
    ],
    "stmt": "insert into t01 (id, data) values (?, ?), (?, ?)"
}


{
    "error": {
        "code": 4000,
        "message": "SQLParseException[The types of the columns within VALUES lists must match. Found `text_array` and `object_array` at position: 1]"
    }
}

Please note the operation still succeeds on both versions of CrateDB 6.2.0 vs. nightly when only submitting a single record.

http localhost:4200/_sql \
  stmt='insert into t01 (id, data) values (?, ?)' \
  args:='["a", [{"foo":"bar"}]]' --print bB

The successful HTTP conversation is:

{
    "args": [
        "a",
        [
            {
                "foo": "bar"
            }
        ]
    ],
    "stmt": "insert into t01 (id, data) values (?, ?)"
}


{
    "cols": [],
    "duration": 57.587383,
    "rowcount": 1,
    "rows": [
        []
    ]
}

In this case, the inner value of the array is still stored (slightly erroneously) as an opaque string.

$ crash -c 'select * from t01;'
CONNECT OK
+----+-----------------------+
| id | data                  |
+----+-----------------------+
| a  | ["{\"foo\":\"bar\"}"] |
+----+-----------------------+
SELECT 1 row in set (0.028 sec)