bigQuery.create(DatasetInfo.of("temp"));
bigQuery.create(TableInfo.of(TableId.of("temp", "countries"), StandardTableDefinition.of(
Schema.of(
Field.newBuilder("population", StandardSQLTypeName.INT64).build(),
Field.newBuilder("gender", StandardSQLTypeName.STRING).build()
)
)));
bigQuery.create(TableInfo.of(TableId.of("temp", "result"), StandardTableDefinition.of(
Schema.of(Field.newBuilder("diff", StandardSQLTypeName.INT64).build())
)));
bigQuery.query(QueryJobConfiguration.newBuilder("" +
"SELECT SUM(CASE WHEN gender = 'MALE' THEN -population ELSE population END) as diff\n" +
"FROM `temp.countries` " +
"GROUP BY gender"
).build());
Failed with
Caused by: com.google.cloud.bigquery.BigQueryException: failed to query SELECT SUM(CASE WHEN gender = 'MALE' THEN -population ELSE population END) as diff
FROM `temp.countries` GROUP BY gender: no such function: zetasqlite_unary_minus
at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.translate(HttpBigQueryRpc.java:115)
at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.queryRpc(HttpBigQueryRpc.java:720)
at com.google.cloud.bigquery.BigQueryImpl$35.call(BigQueryImpl.java:1322)
at com.google.cloud.bigquery.BigQueryImpl$35.call(BigQueryImpl.java:1319)
at com.google.api.gax.retrying.DirectRetryingExecutor.submit(DirectRetryingExecutor.java:103)
at com.google.cloud.bigquery.BigQueryRetryHelper.run(BigQueryRetryHelper.java:86)
at com.google.cloud.bigquery.BigQueryRetryHelper.runWithRetries(BigQueryRetryHelper.java:49)
at com.google.cloud.bigquery.BigQueryImpl.queryRpc(BigQueryImpl.java:1318)
at com.google.cloud.bigquery.BigQueryImpl.query(BigQueryImpl.java:1306)
at com.payaut.report.AbstractDBIntegrationTest$Initializer.initialize(AbstractDBIntegrationTest.java:52)
at org.springframework.boot.SpringApplication.applyInitializers(SpringApplication.java:604)
at org.springframework.boot.SpringApplication.prepareContext(SpringApplication.java:373)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:306)
at org.springframework.boot.test.context.SpringBootContextLoader.loadContext(SpringBootContextLoader.java:136)
at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContextInternal(DefaultCacheAwareContextLoaderDelegate.java:141)
at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:90)
... 72 more
How to reproduce
I am using java gcloud SDK
Failed with