0

Strangely I can save and run this scheduled query no problem

DECLARE runTimeVariable TIMESTAMP DEFAULT @run_time;
SELECT * FROM
  `myTable`   
  WHERE insert_timestamp > timestamp_sub(runTimeVariable, INTERVAL 7 DAY)

But I try to run

DECLARE runTimeVariable TIMESTAMP DEFAULT @run_time;
SELECT * FROM
  `myTable`   
  WHERE insert_timestamp > timestamp_sub(runTimeVariable, INTERVAL 7 DAY) 
  AND x > 0

or any other AND clause then I can't save it, I get Query error: Cannot query over table ... without a filter over column(s) ... that can be used for partition elimination at

Thanks!

Mark
  • 168
  • 1
  • 13
  • Does this answer your question? [Cannot query over table without a filter that can be used for partition elimination](https://stackoverflow.com/questions/51194105/cannot-query-over-table-without-a-filter-that-can-be-used-for-partition-eliminat) – SANN3 Jul 28 '21 at 07:37
  • Many thanks but no. I have no problem with my query when I run it directly using `currenty_timestamp()`. Only error is when it's a scheduled query using `@run_time` and also with an `AND` then it is rejected. Have also reported to Google. – Mark Jul 28 '21 at 09:06

1 Answers1

0

Well I have found a strange workaround which works fine (don't need a variable either)

SELECT * FROM `mytable`   
   WHERE insert_timestamp > timestamp_micros(0) # THIS IS THE STRANGE WORKAROUND
   AND insert_timestamp > timestamp_sub(current_timestamp()  , INTERVAL 7 DAY) # THIS IS TH REAL FILTER ON THE PARTITIONED FIELD
   AND x >0
Mark
  • 168
  • 1
  • 13