Open dai-chen opened 2 years ago
Just faced this in another context:
opensearchsql> select 1+1, 1+1;
TransportError(500, 'IllegalArgumentException', {'error': {'type': 'IllegalArgumentException', 'reason': 'There was internal problem at backend', 'details': 'Multiple entries with same key: 1+1=2 and 1+1=2'}, 'status': 503})
With the same stacktrace.
Just faced this in another context:
opensearchsql> select 1+1, 1+1; TransportError(500, 'IllegalArgumentException', {'error': {'type': 'IllegalArgumentException', 'reason': 'There was internal problem at backend', 'details': 'Multiple entries with same key: 1+1=2 and 1+1=2'}, 'status': 503})
With the same stacktrace.
Yes, I think it's the same cause here. Not sure what's use case. But I think we'd better to throw semantic exception with clear message.
I think we should throw no exception.
mysql> select 1+1, 1+1;
+-----+-----+
| 1+1 | 1+1 |
+-----+-----+
| 2 | 2 |
+-----+-----+
1 row in set (0.00 sec)
Exception happens in ImmutableMap.Builder<String, ExprValue>::build()
in ProjectOperator::next
: https://github.com/opensearch-project/sql/blob/main/core/src/main/java/org/opensearch/sql/planner/physical/ProjectOperator.java#L55-L83
Maybe using a Multimap
will help us, but this require changes in 20+ other files.
Note, this function also rereferred in https://github.com/opensearch-project/sql/issues/458.
Just checked a PoC by adding a zero-based counter to the map keys, it works:
mapBuilder.put(expr.getNameOrAlias() + cnt++, exprValue);
It is not returned by user (why? how?), but we need more investigation. Maybe this fix will be easier.
curl -s -XPOST "http://localhost:9200/_plugins/_sql" -H 'Content-Type: application/json' -d "{\"query\": \"SELECT 1+1, 1+1\"}"
{
"schema": [
{
"name": "1+1",
"type": "integer"
},
{
"name": "1+1",
"type": "integer"
}
],
"datarows": [
[
2,
2
]
],
"total": 1,
"size": 1,
"status": 200
}
I think we should throw no exception.
mysql> select 1+1, 1+1; +-----+-----+ | 1+1 | 1+1 | +-----+-----+ | 2 | 2 | +-----+-----+ 1 row in set (0.00 sec)
Exception happens in
ImmutableMap.Builder<String, ExprValue>::build()
inProjectOperator::next
: https://github.com/opensearch-project/sql/blob/main/core/src/main/java/org/opensearch/sql/planner/physical/ProjectOperator.java#L55-L83 Maybe using aMultimap
will help us, but this require changes in 20+ other files. Note, this function also rereferred in #458.
Yes, I tested other database and they have no problem with such kind of query. I think we can add the support if this can be quick fixed, otherwise just improve the error.
As a work around, aliasing the duplicate expressions works -- select 1+1 as A, 1+1 as B
This will be relevant for JOIN support (#49) because the joined tables can have columns with the same name.
IIRC, SQL Server resolved this by appending _<number>
to each column name. We can do the same -- if we do not disambiguate non-unique expression, we are forcing client software to deal with that.
What is the bug? Currently duplicate name in SELECT statement is not supported and runtime exception is thrown at execution time.
How can one reproduce the bug? Steps to reproduce the behavior:
What is the expected behavior? Either semantic check performed and semantic exception thrown with clear error message Or support this case as other database if needed.
What is your host/environment?
Do you have any screenshots? N/A
Do you have any additional context? Tested MySQL and SQLServer. Both has same behavior as below:
Exception stacktrace: