Weird behavior when using alias 'id'

General Discussions - English
1

Hi,

I am experiencing a weird behavior when trying to assign alias ‘id’.

Examples:

  1. Select property from json attribute ‘doc’ and assign alias ‘id’ without raw mode

Query
curl localhost:9308/sql --data-urlencode “query=SELECT doc.properties[‘cmis:objectId’] id FROM objects WHERE repository_id=‘8c0552c4-3c17-4bbb-9af5-8b597d5c399f’ AND type_query_name=‘cmis:document’ LIMIT 0,1”

Response
{“took”:2,“timed_out”:false,“hits”:{“total”:10000,“hits”:[{"_id":“140366310252176”,"_score":1,"_source":{}}]}}

  1. Select property from json attribute ‘doc’ and assign alias ‘id’ with raw mode

Query
curl localhost:9308/sql -d “mode=raw&query=SELECT doc.properties[‘cmis:objectId’] as id FROM objects WHERE repository_id=‘8c0552c4-3c17-4bbb-9af5-8b597d5c399f’ AND type_query_name=‘cmis:document’ LIMIT 0,1”

Response
{“columns”:[{“id”:{“type”:“string”}}],
“data”:[
{“id”:"{“id”:“cmis:objectId”,“queryName”:“cmis:objectId”,“type”:“id”,“value”:[“15002”]}"}
],
“total”:0,
“error”:"",
“warning”:""
}

  1. Select text field ‘name’ and assign alias ‘id’ with raw mode

Query
curl localhost:9308/sql -d “mode=raw&query=select name as id from test”

{“columns”:[{“id”:{“type”:“string”}}],
“data”:[
{“id”:""}
],
“total”:0,
“error”:"",
“warning”:""
}

  1. Select text field ‘name’ and assign alias ‘id’ without raw mode

Query
curl localhost:9308/sql --data-urlencode “query=select name as id from test”

Response
{“took”:0,“timed_out”:false,“hits”:{“total”:1,“hits”:[{"_id":“0”,"_score":1,"_source":{}}]}}

Conclusion
I think that this is somehow connected to the reserved ‘id’ property and I am not sure what to think of this.

Examples 1 and 2 show that in raw mode you can assign alias ‘id’ when applied to json type property.

Examples 3 and 4 show that you can not assign alias ‘id’ to text field in any mode. I am also not sure why ‘data’ reports one object with empty ‘id’ property.

In my use case I need ‘id’ alias and I can make it work with some tweaks but I would like to know if there is something I am missing here and I would like to understand this behavior better.

Thanks.