[ https://issues.apache.org/jira/browse/DRILL-7237?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16834660#comment-16834660 ]
ASF GitHub Bot commented on DRILL-7237: --------------------------------------- vvysotskyi commented on pull request #1782: DRILL-7237: Fix single_value aggregate function for variable length types URL: https://github.com/apache/drill/pull/1782#discussion_r281586802 ########## File path: exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestAggregateFunctions.java ########## @@ -640,11 +644,107 @@ public void testSingleValueFunction() throws Exception { } @Test - public void testSingleValueWithMultipleValuesInput() throws Exception { + public void testHashAggSingleValueFunction() throws Exception { + List<String> tableNames = Arrays.asList( + "cp.`parquet/alltypes_required.parquet`", + "cp.`parquet/alltypes_optional.parquet`"); + for (String tableName : tableNames) { + QueryDataBatch result = + testSqlWithResults(String.format("select * from %s limit 1", tableName)).get(0); + + Map<String, Object> resultingValues = new HashMap<>(); + + RecordBatchLoader loader = new RecordBatchLoader(getAllocator()); + loader.load(result.getHeader().getDef(), result.getData()); + + for (VectorWrapper<?> vectorWrapper : loader.getContainer()) { + final String fieldName = vectorWrapper.getField().getName(); + Object object = vectorWrapper.getValueVector().getAccessor().getObject(0); + // VarCharVector returns Text instance, but baseline values should contain String value + if (object instanceof Text) { + object = object.toString(); + } + resultingValues.put(fieldName, object); + } + loader.clear(); + result.release(); + + List<Boolean> optionValues = Arrays.asList(true, false); + + try { + for (Boolean optionValue : optionValues) { + for (Map.Entry<String, Object> entry : resultingValues.entrySet()) { + String columnName = String.format("`%s`", entry.getKey()); + + // disable interval types when stream agg is disabled due to DRILL-7241 + if (optionValue || !columnName.startsWith("`col_intrvl")) { + setSessionOption(PlannerSettings.STREAMAGG.getOptionName(), optionValue); + testBuilder() + .sqlQuery("select single_value(t.%1$s) as %1$s\n" + + "from (select %1$s from %2$s limit 1) t group by t.%1$s", columnName, tableName) + .ordered() + .baselineRecords(Collections.singletonList(ImmutableMap.of(columnName, entry.getValue()))) + .go(); + } + } + } + } finally { + resetSessionOption(PlannerSettings.STREAMAGG.getOptionName()); + } + } + } + + @Test + public void testSingleValueWithComplexInput() throws Exception { + String query = "select single_value(t1.a) as any_a, single_value(f) as any_f, single_value(m) as any_m," + + "single_value(p) as any_p from (select * from cp.`store/json/test_anyvalue.json` limit 1) t1"; + testBuilder() + .sqlQuery(query) + .unOrdered() + .baselineColumns("any_a", "any_f", "any_m", "any_p") + .baselineValues(TestBuilder.listOf(TestBuilder.mapOf("b", 10L, "c", 15L), + TestBuilder.mapOf("b", 20L, "c", 45L)), + TestBuilder.listOf(TestBuilder.mapOf("g", TestBuilder.mapOf("h", + TestBuilder.listOf(TestBuilder.mapOf("k", 10L), TestBuilder.mapOf("k", 20L))))), + TestBuilder.listOf(TestBuilder.mapOf("n", TestBuilder.listOf(1L, 2L, 3L))), + TestBuilder.mapOf("q", TestBuilder.listOf(27L, 28L, 29L))) + .go(); + } + + @Test + public void testSingleValueWithMultipleValuesInputsAllTypes() throws Exception { + List<String> tableNames = Arrays.asList( + "cp.`parquet/alltypes_required.parquet`", + "cp.`parquet/alltypes_optional.parquet`"); + for (String tableName : tableNames) { + QueryDataBatch result = + testSqlWithResults(String.format("select * from %s limit 1", tableName)).get(0); + + RecordBatchLoader loader = new RecordBatchLoader(getAllocator()); + loader.load(result.getHeader().getDef(), result.getData()); + + List<String> columns = StreamSupport.stream(loader.getContainer().spliterator(), false) + .map(vectorWrapper -> vectorWrapper.getField().getName()) + .collect(Collectors.toList()); + loader.clear(); + result.release(); + for (String columnName : columns) { + try { + test("select single_value(t.%1$s) as %1$s from %2$s t", columnName, tableName); Review comment: It was done because after the first query is failed, `thrown` checks the error, but the loop is not continued. ---------------------------------------------------------------- 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. For queries about this service, please contact Infrastructure at: us...@infra.apache.org > 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)