column data type is always STRING when results are empty.

[amrishlal]

This caused a bit of confusion when one of our users ran an query on their table and the metadata that came back showed that all the columns in the result set were STRING. I debugged the issue and narrowed down the cause to the code below:

  private static SelectionResultsBlock buildEmptySelectionQueryResults(QueryContext queryContext) {
    List<ExpressionContext> selectExpressions = queryContext.getSelectExpressions();
    int numSelectExpressions = selectExpressions.size();
    String[] columnNames = new String[numSelectExpressions];
    for (int i = 0; i < numSelectExpressions; i++) {
      columnNames[i] = selectExpressions.get(i).toString();
    }
    ColumnDataType[] columnDataTypes = new ColumnDataType[numSelectExpressions];
    // NOTE: Use STRING column data type as default for selection query
    Arrays.fill(columnDataTypes, ColumnDataType.STRING);
    DataSchema dataSchema = new DataSchema(columnNames, columnDataTypes);
    return new SelectionResultsBlock(dataSchema, Collections.emptyList());
  }

Note the use of Arrays.fill(columnDataTypes, ColumnDataType.STRING) which sets all the columns in result metadata to STRING when resultset is empty. I am wondering if can modify this to return either no metadata or no column datatypes or are there backward compatibly concerns around making such a change?

[Jackie-Jiang]

When no row selected, we can consider returning empty result table (no data schema, no row) in the broker response. Another related issue is for aggregation query: #9531. There is indeed backward compatible concern around this change when the client/user expects non-empty result table (with data schema but no row).
Can you please also check the behavior for other query engines when there is no row selected? E.g. Presto, Postgres, MySQL

[amrishlal]

Can you please also check the behavior for other query engines when there is no row selected? E.g. Presto, Postgres, MySQL

For both MySQL and Postgres, result set column datatypes are set to actual table column type when query result doesn't contain any row. I verified both through SQL and JDBC api. Datatypes of column with expressions are also being set even when resultset is empty. For example if column b is of type DATE then the query SELECT cast(b as varchar(20) FROM mytable will show result column datatype as VARCHAR.

[amrishlal]

Empty result generated when number of segments selected is zero (ServerQueryExecutorV1Impl.java):

    if (numSelectedSegments == 0) {
      if (queryContext.isExplain()) {
        instanceResponse = getExplainResponseForNoMatchingSegment(numTotalSegments, queryContext);
      } else {
        instanceResponse =
            new InstanceResponseBlock(ResultsBlockUtils.buildEmptyQueryResults(queryContext), queryContext);
      }
    }

When no row selected, we can consider returning empty result table (no data schema, no row) in the broker response.

Yes, I think this will work well from Pinot side.