Invalid query results when querying with non-existent column

[lgo]

While testing some results, I accidentally used the wrong name to refer to a column. When querying, rather than raising an error the query actually returned but with 0 results, as shown below. This isn't a big deal, but is a usability hiccup that can be pretty confusion when adhoc querying Pinot.

Query
Note: amount does not exist on the table. This was meant to be balance!

select count(*), sum(amount) from adjustments where type = 'foobar'

Result

{
  "resultTable": {
    "dataSchema": {
      "columnNames": [
        "count(*)",
        "sum(amount)"
      ],
      "columnDataTypes": [
        "LONG",
        "DOUBLE"
      ]
    },
    "rows": [
      [
        0,
        0
      ]
    ]
  },
  "exceptions": [],
  "numServersQueried": ...,
  "numServersResponded": ...,
  "numSegmentsQueried": ...,
  "numSegmentsProcessed": 0,
  "numSegmentsMatched": 0,
  "numConsumingSegmentsQueried": 0,
  "numDocsScanned": 0,
  "numEntriesScannedInFilter": 0,
  "numEntriesScannedPostFilter": 0,
  "numGroupsLimitReached": false,
  "totalDocs": ...,
  "timeUsedMs": 10,
  "segmentStatistics": [],
  "traceInfo": {},
  "minConsumingFreshnessTimeMs": 0
}

Here is a simplified example configuration we were using, in case this ends up being particular about the table config. (irrelevant columns were prunted from the example).

Schema

{
  "schemaName": "adjustment",
  "dimensionFieldSpecs": [
    {
      "name": "type",
      "dataType": "STRING"
    },
  ],
  "metricFieldSpecs": [
    {
      "name": "balance",
      "dataType": "DOUBLE"
    },
  ],
  "dateTimeFieldSpecs": [
    {
      "name": "created_at",
      "dataType": "LONG",
      "format": "1:SECONDS:EPOCH",
      "granularity": "1:HOURS"
    }
  ]
}

Table config

{
  "OFFLINE": {
    "tableName": "adjustment_OFFLINE",
    "tableType": "OFFLINE",
    "segmentsConfig": {
      "timeType": "SECONDS",
      "schemaName": "adjustment",
      "segmentPushFrequency": "HOURLY",
      "segmentPushType": "APPEND",
      "timeColumnName": "created_at",
      "replication": "1"
    },
    "tenants": {
      "broker": "DefaultTenant",
      "server": "DefaultTenant"
    },
    "tableIndexConfig": {
      "bloomFilterColumns": [
        "type"
      ],
      "loadMode": "MMAP",
      "noDictionaryColumns": [],
      "enableDefaultStarTree": false,
      "starTreeIndexConfigs": [
        {
          "dimensionsSplitOrder": [
            "type",
          ],
          "functionColumnPairs": [
            "SUM__balance",
            "COUNT"
          ],
          "maxLeafRecords": 1
        }
      ],
      "enableDynamicStarTreeCreation": false,
      "segmentPartitionConfig": {
        "columnPartitionMap": {
          "type": {
            "functionName": "Murmur",
            "numPartitions": 100
          }
        }
      },
      "aggregateMetrics": false,
      "nullHandlingEnabled": false,
      "invertedIndexColumns": [
        "type"
      ],
      "autoGeneratedInvertedIndex": false,
      "createInvertedIndexDuringSegmentGeneration": false
    },
    "metadata": {},
    "routing": {
      "segmentPrunerTypes": [
        "partition"
      ]
    }
  }
}

[xiangfu0]

I feel we should by default do column validation add an flag to loose this behavior for legacy offline tables which doesn't enforce this behavior. Thoughts? @mayankshriv @mcvsubbu

[mcvsubbu]

I think there was a PR that @jackjlli had sometime before? It is best if we enforce it as a part of SQL, and let PQL be. We are in the process of migrating from PQL to SQL, and can add this as an enforcement instead of retro fitting queries of a ton of tables that somehow get by today.

[jackjlli]

Yes, I have a PR to detect the column mismatch in the query:
#6066
We'll first monitor how many existing use cases are still firing queries with non-existing columns. After adjusting their behavior, we can restrict this in SQL.

[xiangfu0]

Closing — this has been implemented. Config pinot.broker.throw.exception.for.invalid.column was added to control behavior when querying with non-existent columns, preventing invalid query results.