[ https://issues.apache.org/jira/browse/DRILL-7237?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Volodymyr Vysotskyi updated DRILL-7237: --------------------------------------- Description: *Description:* The following issue can be reproduced on the fix for the [DRILL-7050|https://issues.apache.org/jira/browse/DRILL-7050]. _For the query with > 1 row in subquery results where the data type of these results *is not varchar*:_ {noformat} SELECT e.full_name, ( SELECT ine.employee_id FROM cp.`employee.json` ine WHERE ine.position_id = e.position_id ) as empl_id FROM cp.`employee.json` e LIMIT 20 {noformat} _We have the following correct and informative error:_ {noformat} Query Failed: An Error Occurred org.apache.drill.common.exceptions.UserRemoteException: FUNCTION ERROR: Input for single_value function has more than one row Fragment 0:0 [Error Id: b770098f-b1c7-4647-9f41-9e986a0e47b7 on maprhost:31010] {noformat} _But when in the result set of the subquery we have *a varchar data type*:_ {noformat} SELECT e.full_name, ( SELECT ine.first_name FROM cp.`employee.json` ine WHERE ine.position_id = e.position_id ) as empl_id FROM cp.`employee.json` e LIMIT 20 {noformat} *Actual result:* _Drill throws the following error:_ {noformat} org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: IllegalStateException: Workspace variable 'value' in aggregation function 'single_value' is not allowed to have variable length type. Fragment 0:0 Please, refer to logs for more information. [Error Id: 32325ba9-d2b3-4216-acf6-8e80dfe4a56a on maprhost:31010] {noformat} Log file is in the attachment "drillbit.log" *Expected result:* Drill should return the same informative error to any of a data types in the subquery result set. was: *Description:* The following issue can be reproduced on the EBF for the [DRILL-7050|https://issues.apache.org/jira/browse/DRILL-7050]. _For the query with > 1 row in subquery results where the data type of these results *is not varchar*:_ {noformat} SELECT e.full_name, ( SELECT ine.employee_id FROM cp.`employee.json` ine WHERE ine.position_id = e.position_id ) as empl_id FROM cp.`employee.json` e LIMIT 20 {noformat} _We have the following correct and informative error:_ {noformat} Query Failed: An Error Occurred org.apache.drill.common.exceptions.UserRemoteException: FUNCTION ERROR: Input for single_value function has more than one row Fragment 0:0 [Error Id: b770098f-b1c7-4647-9f41-9e986a0e47b7 on maprhost:31010] {noformat} _But when in the result set of the subquery we have *a varchar data type*:_ {noformat} SELECT e.full_name, ( SELECT ine.first_name FROM cp.`employee.json` ine WHERE ine.position_id = e.position_id ) as empl_id FROM cp.`employee.json` e LIMIT 20 {noformat} *Actual result:* _Drill throws the following error:_ {noformat} org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: IllegalStateException: Workspace variable 'value' in aggregation function 'single_value' is not allowed to have variable length type. Fragment 0:0 Please, refer to logs for more information. [Error Id: 32325ba9-d2b3-4216-acf6-8e80dfe4a56a on maprhost:31010] {noformat} Log file is in the attachment "drillbit.log" *Expected result:* Drill should return the same informative error to any of a data types in the subquery result set. > IllegalStateException in aggregation function 'single_value' when there is a > varchar datatype in the subquery results > --------------------------------------------------------------------------------------------------------------------- > > Key: DRILL-7237 > URL: https://issues.apache.org/jira/browse/DRILL-7237 > Project: Apache Drill > Issue Type: Bug > Affects Versions: 1.14.0, 1.15.0 > Reporter: Denys Ordynskiy > Assignee: Volodymyr Vysotskyi > Priority: Major > Fix For: 1.17.0 > > Attachments: drillbit.log > > > *Description:* > The following issue can be reproduced on the fix for the > [DRILL-7050|https://issues.apache.org/jira/browse/DRILL-7050]. > _For the query with > 1 row in subquery results where the data type of these > results *is not varchar*:_ > {noformat} > SELECT > e.full_name, > ( > SELECT > ine.employee_id > FROM > cp.`employee.json` ine > WHERE > ine.position_id = e.position_id > ) as empl_id > FROM > cp.`employee.json` e > LIMIT 20 > {noformat} > _We have the following correct and informative error:_ > {noformat} > Query Failed: An Error Occurred > org.apache.drill.common.exceptions.UserRemoteException: FUNCTION ERROR: Input > for single_value function has more than one row Fragment 0:0 [Error Id: > b770098f-b1c7-4647-9f41-9e986a0e47b7 on maprhost:31010] > {noformat} > _But when in the result set of the subquery we have *a varchar data type*:_ > {noformat} > SELECT > e.full_name, > ( > SELECT > ine.first_name > FROM > cp.`employee.json` ine > WHERE > ine.position_id = e.position_id > ) as empl_id > FROM > cp.`employee.json` e > LIMIT 20 > {noformat} > *Actual result:* > _Drill throws the following error:_ > {noformat} > org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: > IllegalStateException: Workspace variable 'value' in aggregation function > 'single_value' is not allowed to have variable length type. Fragment 0:0 > Please, refer to logs for more information. [Error Id: > 32325ba9-d2b3-4216-acf6-8e80dfe4a56a on maprhost:31010] > {noformat} > Log file is in the attachment "drillbit.log" > *Expected result:* > Drill should return the same informative error to any of a data types in the > subquery result set. -- This message was sent by Atlassian JIRA (v7.6.3#76005)