hanke580 opened a new issue, #68074: URL: https://github.com/apache/doris/issues/68074
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version 4.1.3 (`apache/doris:all-in-one-4.1.3`) ### What's Wrong? Insert a JSON boolean into a `VARIANT` column and read it back as JSON: it comes back as the number `1`. The document does not round-trip. ```sql CREATE TABLE s (id INT, j VARIANT) DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 1 PROPERTIES('replication_num'='1'); INSERT INTO s VALUES (0, '{"b": true}'); SELECT CAST(j AS STRING) FROM s; -- {"b":1} expected {"b":true} ``` **The stored type is correct — only the serialization is wrong.** With `describe_extend_variant_column = true`, `DESC s` reports: ``` j.b boolean ``` So Doris inferred, stored and reports `boolean`. It is the VARIANT→JSON writer that emits `1`. Every JSON read route is affected: | expression | result | expected | |---|---|---| | `CAST(j AS STRING)` | `{"b":1}` | `{"b":true}` | | `CAST(j['b'] AS STRING)` | `1` | `true` | | `json_extract(CAST(j AS STRING), '$.b')` | `1` | `true` | A native `JSON` column in the same server is correct, which shows the JSON writer knows how to print a boolean and only the VARIANT path does not: ```sql CREATE TABLE r (id INT, jj JSON) DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 1 PROPERTIES('replication_num'='1'); INSERT INTO r VALUES (0, '{"b": true}'); SELECT CAST(jj AS STRING) FROM r; -- {"b":true} correct ``` #### JSONB is the only path type that prints a boolean correctly Holding everything else fixed and varying only what else the path contains — so only the inferred path type changes: | the path holds | `DESC` type | `CAST(j AS STRING)` | | |---|---|---|---| | only booleans | `boolean` | `{"b":1}` | ❌ | | booleans + a string | `json` | `{"b":true}` | ✅ | | booleans + an array | `json` | `{"b":true}` | ✅ | | booleans + a number | `bigint` | `{"b":1}` | ❌ — this one is [#68016](https://github.com/apache/doris/pull/68016) | Nested and array booleans behave the same way as the scalar: ``` {"o":{"b":true}} j.o.b boolean -> {"o":{"b":1}} {"a":[true,false]} j.a array<boolean> -> {"a":[1, 0]} ``` So a document is printed correctly only when its boolean path happens to be heterogeneous enough to be stored as JSONB. A clean, homogeneous `boolean` path — the common case — is the one that prints wrong. #### It also affects the sparse column, which makes it depend on document width Paths past `variant_max_subcolumns_count` (default 2048) go to the sparse column, which prints booleans as `1` as well. That produces a visible cliff for a path that *would* have been stored as JSONB: one extra unrelated key per document changes the printed value of an existing field. With a path holding `[[1]]`, `[null]`, `true`, `false`, `[true]` (heterogeneous → JSONB below the budget): | keys per document | `j.a` storage | `true` | `false` | `[true]` | |---|---|---|---|---| | 2048 | `json` subcolumn | `true` ✅ | `false` ✅ | `[true]` ✅ | | **2049** | sparse column | **`1`** ❌ | **`0`** ❌ | **`[1]`** ❌ | A homogeneous boolean path prints `1` on both sides of that boundary, consistent with the main report above. ### Why this is not #68016 [#68016](https://github.com/apache/doris/pull/68016) fixes the **type merge**: it adds a rule to `get_least_supertype_jsonb()` so that a path holding `TYPE_BOOLEAN` together with an int or float type resolves to JSONB instead of a numeric type. That repairs the `bool + number → bigint` row in the table above, where the value is genuinely lost. It does not apply here. A path holding only booleans never reaches a mixed-type merge — its type is already `boolean`, which is the *correct* type — so no supertype rule fires. The defect is downstream of typing, in how a `boolean` (and `array<boolean>`, and a sparse entry) is written out as JSON. After #68016 merges, the mixed case will print `true`, and `{"b": true}` on its own will still print `{"b":1}`. ### What You Expected? `{"b": true}` inserted into a `VARIANT` reads back as `{"b": true}`, matching the native `JSON` column and JSON semantics, where `true` and `1` are distinct values of distinct types. ### How to Reproduce? Two statements, default configuration, single-node `apache/doris:all-in-one-4.1.3`: ```sql CREATE TABLE s (id INT, j VARIANT) DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 1 PROPERTIES('replication_num'='1'); INSERT INTO s VALUES (0, '{"b": true}'); SET describe_extend_variant_column = true; DESC s; -- j.b boolean <- type is right SELECT CAST(j AS STRING) FROM s; -- {"b":1} <- output is wrong ``` Full sweep of the four path shapes, plus the sparse-column cliff: ```python #!/usr/bin/env python3 """Needs pymysql and a Doris FE on 127.0.0.1:9230 (user root, no password).""" import json, sys, pymysql PORT = int(sys.argv[1]) if len(sys.argv) > 1 else 9230 c = pymysql.connect(host="127.0.0.1", port=PORT, user="root", autocommit=True, connect_timeout=20) cur = c.cursor() def q(s): cur.execute(s); return cur.fetchall() if cur.description else None q("CREATE DATABASE IF NOT EXISTS bug026"); q("USE bug026") q("SET describe_extend_variant_column = true") q("SET default_variant_max_subcolumns_count = 2048") def probe(label, docs): q("DROP TABLE IF EXISTS z") q("CREATE TABLE z (id INT, j VARIANT) DUPLICATE KEY(id) " "DISTRIBUTED BY HASH(id) BUCKETS 1 PROPERTIES('replication_num'='1')") for i, d in enumerate(docs): q("INSERT INTO z VALUES (%d, '%s')" % (i, json.dumps(d))) t = {r[0]: r[1] for r in q("DESC z") if r[0].startswith("j.")} v = [r[1] for r in q("SELECT id, CAST(j AS STRING) FROM z ORDER BY id")] print(" %-26s type=%-22s -> %s" % (label, t, v)) print("path shape -> stored type -> JSON output") probe("only booleans", [{"b": True}, {"b": False}]) probe("booleans + string", [{"b": True}, {"b": "x"}]) probe("booleans + array", [{"b": True}, {"b": [1]}]) probe("booleans + number", [{"b": True}, {"b": 1}]) # = PR #68016 probe("nested boolean", [{"o": {"b": True}}]) probe("array of booleans", [{"a": [True, False]}]) # sparse-column cliff: one extra unrelated key flips a JSONB path into sparse def doc(a, total): d = {"a": a} for i in range(total - 1): d["k%05d" % i] = i return json.dumps(d) print("\nheterogeneous path either side of variant_max_subcolumns_count=2048") for total in (2048, 2049): t = "h%d" % total q("DROP TABLE IF EXISTS " + t) q("CREATE TABLE %s (id INT, j VARIANT) DUPLICATE KEY(id) " "DISTRIBUTED BY HASH(id) BUCKETS 1 PROPERTIES('replication_num'='1')" % t) for i, v in enumerate([[[1]], [None], True, False, [True]]): q("INSERT INTO %s VALUES (%d, '%s')" % (t, i, doc(v, total))) ty = {r[0]: r[1] for r in q("DESC " + t) if r[0] == "j.a"} vals = dict(q("SELECT id, json_extract(CAST(j AS STRING),'$.a') FROM %s ORDER BY id" % t)) print(" %d keys/doc -> j.a %s : true->%r false->%r [true]->%r" % (total, ty or "in sparse column", vals.get(2), vals.get(3), vals.get(4))) ``` Observed output on 4.1.3: ``` path shape -> stored type -> JSON output only booleans type={'j.b': 'boolean'} -> ['{"b":1}', '{"b":0}'] booleans + string type={'j.b': 'json'} -> ['{"b":true}', '{"b":"x"}'] booleans + array type={'j.b': 'json'} -> ['{"b":true}', '{"b":[1]}'] booleans + number type={'j.b': 'bigint'} -> ['{"b":1}', '{"b":1}'] nested boolean type={'j.o.b': 'boolean'} -> ['{"o":{"b":1}}'] array of booleans type={'j.a': 'array<boolean>'}-> ['{"a":[1, 0]}'] heterogeneous path either side of variant_max_subcolumns_count=2048 2048 keys/doc -> j.a {'j.a': 'json'} : true->'true' false->'false' [true]->'[true]' 2049 keys/doc -> j.a in sparse column : true->'1' false->'0' [true]->'[1]' ``` ### Anything Else? No error or warning anywhere; the only way to notice is to diff against what was inserted. `{"a": 1}` and `{"a": true}` on one path are indistinguishable after a round-trip, which also makes the JSON output ambiguous rather than merely differently formatted. ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
