Details
Description
Follow up of MDEV-27899.
With reference to the sample reproducer provided there, the SELECT query works, but when the query is used to create a view, then the view has the same issue originally reported.
So, having created and populated:
create table SyncopeUser (plainAttrs JSON); |
insert into SyncopeUser values ( |
'[
|
{
|
"values": [
|
{
|
"stringValue": "Rossini"
|
}
|
],
|
"schema": "surname"
|
},
|
{
|
"values": [
|
{
|
"dateValue": 1243288800000
|
},
|
{
|
"dateValue": 1274824800000
|
}
|
],
|
"schema": "loginDate"
|
},
|
{
|
"uniqueValue": {
|
"stringValue": "Gioacchino Rossini"
|
},
|
"schema": "fullname"
|
},
|
{
|
"uniqueValue": {
|
"stringValue": "rossini@apache.org"
|
},
|
"schema": "userId"
|
}
|
]'); |
then the following query works as expected, e.g. attrUniqueValue is non-null for some rows:
SELECT attrs.* |
FROM SyncopeUser u, JSON_TABLE(plainAttrs, '$[*]' COLUMNS ( |
plainSchema VARCHAR(255) PATH '$.schema', |
attrUniqueValue JSON PATH '$.uniqueValue' ERROR ON ERROR) |
) AS attrs; |
e.g.
| plainSchema | attrUniqueValue |
|---|---|
| surname | [NULL] |
| loginDate | [NULL] |
| fullname | {"stringValue": "Gioacchino Rossini" } |
| userId | {"stringValue": "rossini@apache.org"} |
So far, so good.
If we create a view as follows, instead:
CREATE VIEW user_search AS SELECT attrs.* |
FROM SyncopeUser u, JSON_TABLE(plainAttrs, '$[*]' COLUMNS ( |
plainSchema VARCHAR(255) PATH '$.schema', |
attrUniqueValue JSON PATH '$.uniqueValue' ERROR ON ERROR) |
) AS attrs; |
all seems to be fine, but any query like
SELECT * from user_search |
will return the error
[4178] [HY000]: Can't store an array or an object in the scalar column 'attrUniqueValue' of JSON_TABLE 'attrs'.
|
Attachments
Issue Links
- relates to
-
MDEV-27899 NULL values from JSON NESTED PATH with JSON column
-
- Closed
-