_PARTITIONTIME filter is added to queries on table without partitions

[peikk0]

Bug Report

I have a couple very small tables without any partitioning, and the timerange filter on _PARTITIONTIME is added to queries on those tables, causing the query to fail because the column doesn't exist.

Again it would be nice to have an option for disabling any sort of automatic query alteration, I prefer setting the partition filter explicitly when it is needed.

Expected Behavior

This query using a table without partitioning stays unaltered and works:

SELECT
  CURRENT_TIMESTAMP() AS time,
  COUNT(state) AS value
FROM
  `$my_table`
WHERE
  state = 'ACTIVE'
GROUP BY 1
ORDER BY 1 ASC

Actual Behavior

The query above is modified to:

SELECT
  CURRENT_TIMESTAMP() AS time,
  COUNT(state) AS value
FROM
  `$my_table`
where _PARTITIONTIME >= '2021-02-28 11:00:00' AND _PARTITIONTIME < '2021-03-31 10:59:59' AND 
  state = 'ACTIVE'
GROUP BY 1
ORDER BY 1 ASC LIMIT 1248

and fails with Unrecognized name: _PARTITIONTIME; Did you mean _PARTITION_NAME? at [6:7]

This doesn't happen if the WHERE clause is missing though.

Steps to Reproduce the Problem

1. Create a table without partition
2. Add a panel querying this table and using a WHERE clause

Specifications

• Version: 2.0.1
• Platform: n/a
• Grafana Version: 7.5.3

[peikk0]

As a workaround I'm currently adding an empty results query at the beginning using another table that does have a time partition so that it gets the _PARTITIONTIME filter instead of the actual query:

SELECT CURRENT_TIMESTAMP(), 0 FROM `$source_table` WHERE 1 = 2 UNION ALL
SELECT ...

[ofir5300]

Hi @peikk0, I am aware that you are not using any partition in your query so I pre assume this solved issue (#309) won't necessarily handle your case..
But can you please upgrade to our published v2.0.2 and let me know if the issue still occurs?

[bacheson]

@ofir5300 this problem still exists in v2.0.2 ... this worked seamlessly in v1.x

also using $__timeFilter(timestamp) always injects _PARTITIONTIME when the table isn't partitioned:
_PARTITIONTIME >= '2021-07-08 18:31:58' AND _PARTITIONTIME < '2021-07-08 19:31:58'

and then results in:
invalidQuery: Unrecognized name: _PARTITIONTIME at [x:x]

[ofir5300]

Ok, thank you for sharing this information @bacheson

[ofir5300]

Well I was using a table with no partition but was not able to reproduce that issue, I might be missing something though.

@peikk0
Does this happens to you when writing raw sql? If so - no additions should be made to your where clause.
Are you 100% positive the given tables are not partitioned?

[peikk0]

@ofir5300 I have changed jobs in the meantime so I'm no longer able to test this particular issue. That was happening with raw SQL queries with a table using a JSON file as external data, no partition was configured (no need for one as the file was tiny and there wasn't any field that would have been a good fit for that anyway).

Also all my BigQuery panels were using raw SQL and all of them had the WHERE clause automatically modified with the partition filter, which was particularly annoying with some queries that needed to run outside of the selected timerange (eg. time-shifted graphs and table). I still think there need to be an option to explicitly disable this feature, either per query or globally.

[ofir5300]

Related issue:
#362

[mgerasimchuk]

Well I was using a table with no partition but was not able to reproduce that issue, I might be missing something though.

@peikk0
Does this happens to you when writing raw sql? If so - no additions should be made to your where clause.
Are you 100% positive the given tables are not partitioned?

Hi @ofir5300,
you may try to use graph panel to reproduce this issue.
If I use "single stat" panel, I don't face this issue, because "single stat" panel sends only one request to the database.
But the "graph" panel, sends multiple requests for each interval, and during this process injects a _PARTITIONTIME.

Grafana Query:

SELECT
  TIMESTAMP(format("%t %02d:00:00", Date, HourOfDay)) as time,
  c.CampaignName,
  SUM(cs.Impressions) AS Impressions,
  SUM(cs.Interactions) AS Interactions,
  SUM(cs.Clicks) AS Clicks,
  SUM(cs.Conversions) AS Conversions,
  (SUM(cs.Cost) / 1000000) AS Cost,
FROM
  `***.scheduled_transfer_google_ads_formerly_adwords.Campaign_***` c
LEFT JOIN
  `***.scheduled_transfer_google_ads_formerly_adwords.HourlyCampaignStats_***` cs
ON
  (c.CampaignId = cs.CampaignId)
WHERE 
  c.CampaignStatus="ENABLED"
GROUP BY
  time, c.CampaignName
HAVING
  time > TIMESTAMP_SECONDS(${__from:date:seconds}) AND time < TIMESTAMP_SECONDS(${__to:date:seconds})
ORDER BY
  time
LIMIT
  9000

Rendered query:

SELECT
  TIMESTAMP(format("%t %02d:00:00", Date, HourOfDay)) as time,
  c.CampaignName,
  SUM(cs.Impressions) AS Impressions,
  SUM(cs.Interactions) AS Interactions,
  SUM(cs.Clicks) AS Clicks,
  SUM(cs.Conversions) AS Conversions,
  (SUM(cs.Cost) / 1000000) AS Cost,
FROM
  `***.scheduled_transfer_google_ads_formerly_adwords.Campaign_***` c
LEFT JOIN
  `***.scheduled_transfer_google_ads_formerly_adwords.HourlyCampaignStats_***` cs
ON
  (c.CampaignId = cs.CampaignId)
where _PARTITIONTIME >= '2018-01-25 08:51:56' AND _PARTITIONTIME < '2018-01-25 19:13:42' AND  
  c.CampaignStatus="ENABLED"
GROUP BY
  time, c.CampaignName
HAVING
  time > TIMESTAMP_SECONDS(1516870316) AND time < TIMESTAMP_SECONDS(1516907622)
ORDER BY
  time
LIMIT
  9000