Bigquery: Can't search field values for required partition tables #40673
Comments
ranquild
added
Priority:P1
not sure how much you redacted, but looking at the query from the log:
I don't see the filter clause on the partitioned column anywhere. Did you have a filter on the partitioned column when you built the query? if yes, could you add the full native query that were executed, (with redacted info of course). Also, I've tried to reproduce with this table CREATE TABLE big_table
(
id INT64,
category STRING
)
PARTITION BY _PARTITIONDATE
OPTIONS(require_partition_filter = TRUE);
INSERT INTO big_table
(
id,
category
)
SELECT id,
CASE
WHEN mod(id, 5) = 0 THEN 'A'
WHEN mod(id, 5) = 1 THEN 'B'
WHEN mod(id, 5) = 2 THEN 'C'
WHEN mod(id, 5) = 3 THEN 'D'
WHEN mod(id, 5) = 4 THEN 'E'
END AS category
FROM (
SELECT row_number() OVER() AS id
FROM unnest(generate_array(1, 50000)) ) tried to add a few filters and it works fine |
|
Thanks for the feedback. On my first screenshot the partition filter is visible, it's the "Dt is in the previous 48 months" (look on the top left corner) as this column represent the partition used on BigQuery. On the second screenshot, it is not visible because the bug makes the whole filter section of the editor disappearing. Here's the native query generated by Metabase in the bug case. I copied it from the editor mode just after the bug occured. For privacy reasons I give fake column names. SELECT
`datawarehouse.tablename`.`column_1` AS `column_1`,
`datawarehouse.tablename`.`column_2` AS `column_2`,
`datawarehouse.tablename`.`column_3` AS `column_3`,
`datawarehouse.tablename`.`column_4` AS `column_4`,
-- [...]
`datawarehouse.tablename`.`column_78` AS `column_78`,
`datawarehouse.tablename`.`type` AS `type`,
`datawarehouse.tablename`.`dt` AS `dt`
FROM
`datawarehouse.tablename`
WHERE
(
`datawarehouse.tablename`.`dt` >= DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL -48 month), month)
)
AND (
`datawarehouse.tablename`.`dt` < DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 month), month)
)
LIMIT
1048575And we can see that the filter on column We have it on multiple users and on different tables with filters on different columns but seems to be only text type column with "Is" or "Is not" filter categories. I hope these information help. Let me know if you need more context. |
|
I don't know the behavior of Metabase for this, but could it be that when we are typing the value for a text filter, Metabase tries to fetch the database for possible values in this fields (to proivide hints for the user in real time), but since it doesn't provide a Dt (partition) filter while doing this internal sub-query, it gets an error from the database server (BigQuery in my case) and don't handle it properly (by just not showing hints for possible values for example), causing the UI bug ? |
|
This will need to be pushed out to 49.3 - we have a lot of important fixes pending and do not want to delay them further. |
@Timelessprod you're absolutely correct ! the bug is in the query we make to search field values. |
qnkhuat
changed the title
|
The fix will be included in 49.4 |
|
@qnkhuat do you mean 49.4? we released 49.3 already |
|
yes. should be 49.4. |
|
Perfect thank you very much for the fix! |
Describe the bug
When creating a question from a table that has a lot of rows (multiple 10.000s) and some filters of type "Is"/"Is not" on a same text column, the UI break and no data is being displayed. Instead a warning sign is showing with message "Something's gone wrong" when hovering. This happens both in the visualiser and editor modes (see screenshots below).
In my case I loaded 48 months of data partitions (with Dt = 48 last months) and had already set filters on column "Type" to not be "BANK_DEPOSIT" nor "CARD" and I was about to add another value to exclude. Please note that in the editor mode, even when the bug occur I can click the preview button next to the Filter section and see actual results, but the 3rd filter was not applied and the whole filter section is not showing anymore. It also sometimes happen when applying the first filter.
This bug appeared after updating from v0.48.3 to v0.49.1. I cannot give an exact threshold but it happens with tables that vae around 40.000 to 80.000 rows. Under 10.000 we don't seem to have this bug. In some cases we can use "contains"/"does not contain" and it will work, but for some other cases we want a specific filtering on the whole value and not just a substring.
In the visualiser mode the bug seems to not happen when creating filter by clicking on a cell and apply a "Is" or "Is not" on the cell's value. When creating the filter from the column header it almost always occurs on the first filter.
Visualiser mode:
Editor mode:
To Reproduce
Either in editor mode or visualiser mode:
2. Apply 2 filters on a same column with parameter "Is" or "Is not"
3. Apply a third filter with "Is" or "Is not"
4. See the UI failing to display any data and not applying the later filter
Expected behavior
I expect to be able to add as many filters I want to a same column.
Logs
Information about your Metabase installation
{ "browser-info": { "language": "en-GB", "platform": "MacIntel", "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/123.0.0.0 Safari/537.36", "vendor": "Google Inc." }, "system-info": { "file.encoding": "ANSI_X3.4-1968", "java.runtime.name": "OpenJDK Runtime Environment", "java.runtime.version": "17.0.10+7-Debian-1deb12u1", "java.vendor": "Debian", "java.vendor.url": "https://tracker.debian.org/openjdk-17", "java.version": "17.0.10", "java.vm.name": "OpenJDK 64-Bit Server VM", "java.vm.version": "17.0.10+7-Debian-1deb12u1", "os.name": "Linux", "os.version": "6.1.58+", "user.language": "en", "user.timezone": "Etc/UTC" }, "metabase-info": { "databases": [ "bigquery-cloud-sdk" ], "hosting-env": "unknown", "application-database": "postgres", "application-database-details": { "database": { "name": "PostgreSQL", "version": "13.13" }, "jdbc-driver": { "name": "PostgreSQL JDBC Driver", "version": "42.7.2" } }, "run-mode": "prod", "version": { "date": "2024-03-21", "tag": "v0.49.1", "hash": "54ef5e9" }, "settings": { "report-timezone": null } } }Severity
Blocking some users
Additional context
Looking at the logs it seems that there's an issue with inferring the partitionning on our "dt" column even though there's an existing filter on dt = last 48 months given prior the bug occurs.
The text was updated successfully, but these errors were encountered: