48 is beyond the maximum value that can be held by 1 bits #9641
Description
Hi again, I’m encountering another issue that’s similar to a previous one — still related to a UNION query. This time it’s throwing a different error, and I suspect it might again be caused by NULL values in one of the subqueries.
I’ve included the relevant table schema, sample data, and the SQL query below. Could you please help take a look?
query sql：
WITH `visible_collection_ids` AS (
SELECT `id`
FROM
`collection` AS `c`
WHERE
(1 <> `c`.`id`)
)
SELECT
*,
COUNT(*) OVER () AS `total_count`
FROM
(
SELECT
5 AS `model_ranking`,
`c`.`id`,
`c`.`name`,
`c`.`description`,
`c`.`entity_id`,
`c`.`display`,
`c`.`collection_preview`,
`c`.`dataset_query`,
`c`.`collection_id`,
`dashboard_id`,
`c`.`archived_directly`,
'card' AS `model`,
`c`.`collection_position`,
NULL AS `authority_level`,
NULL AS `personal_owner_id`,
NULL AS `location`,
`u`.`email` AS `last_edit_email`,
`u`.`first_name` AS `last_edit_first_name`,
`u`.`last_name` AS `last_edit_last_name`,
`mr`.`status` AS `moderated_status`,
NULL AS `icon`,
`u`.`id` AS `last_edit_user`,
`r`.`timestamp` AS `last_edit_timestamp`,
`c`.`database_id`,
NULL AS `collection_type`,
`c`.`archived`,
`last_used_at`,
NULL AS `table_id`,
NULL AS `is_upload`,
NULL AS `query_type`
FROM
`report_card` AS `c`
LEFT JOIN `revision` AS `r` ON (`r`.`model_id` = `c`.`id`)
AND (`r`.`most_recent` = TRUE)
AND (`r`.`model` = 'Card')
LEFT JOIN `moderation_review` AS `mr` ON (`mr`.`moderated_item_id` = `c`.`id`)
AND (`mr`.`most_recent` = TRUE)
AND (`mr`.`moderated_item_type` = 'card')
LEFT JOIN `core_user` AS `u` ON `u`.`id` = `r`.`user_id`
WHERE
(
(`collection_id` IS NULL)
OR (
`collection_id` IN (
SELECT
`id`
FROM
`visible_collection_ids`
)
)
)
AND (
(`collection_id` = 2)
AND (`c`.`archived_directly` = FALSE)
)
AND (`c`.`dashboard_id` IS NULL)
AND (`archived` = FALSE)
AND (`c`.`type` = 'question')
AND (`collection_position` IS NOT NULL)
UNION ALL
SELECT
7 AS `model_ranking`,
`id`,
`name`,
`description`,
`entity_id`,
NULL AS `display`,
NULL AS `collection_preview`,
NULL AS `dataset_query`,
`id` AS `collection_id`,
NULL AS `dashboard_id`,
`archived_directly`,
'collection' AS `model`,
NULL AS `collection_position`,
`authority_level`,
`personal_owner_id`,
`location`,
NULL AS `last_edit_email`,
NULL AS `last_edit_first_name`,
NULL AS `last_edit_last_name`,
NULL AS `moderated_status`,
NULL AS `icon`,
NULL AS `last_edit_user`,
NULL AS `last_edit_timestamp`,
NULL AS `database_id`,
`type` AS `collection_type`,
`archived`,
NULL AS `last_used_at`,
NULL AS `table_id`,
NULL AS `is_upload`,
NULL AS `query_type`
FROM
`collection` AS `col`
WHERE
(
(
(`col`.`id` IS NULL)
OR (
`col`.`id` IN (
SELECT
`id`
FROM
`visible_collection_ids`
)
)
)
AND (
(`col`.`location` LIKE '/2/%')
AND NOT EXISTS (
SELECT
1
FROM
`collection` AS `c2`
WHERE
(
(`c2`.`id` IS NULL)
OR (
`c2`.`id` IN (
SELECT
`id`
FROM
`visible_collection_ids`
)
)
)
AND (
`col`.`location` = CONCAT(`c2`.`location`, `c2`.`id`, '/')
)
AND (`c2`.`id` <> 2)
)
)
)
AND (`personal_owner_id` IS NULL)
AND (
(`archived` = FALSE)
AND (`id` <> 1)
)
AND (`namespace` IS NULL)
AND (
(`namespace` IS NULL)
OR (`namespace` <> 'snippets')
)
AND (NULL IS NOT NULL)
UNION ALL
SELECT
1 AS `model_ranking`,
`d`.`id`,
`d`.`name`,
`d`.`description`,
`d`.`entity_id`,
NULL AS `display`,
NULL AS `collection_preview`,
NULL AS `dataset_query`,
`d`.`collection_id`,
NULL AS `dashboard_id`,
`d`.`archived_directly`,
'dashboard' AS `model`,
`d`.`collection_position`,
NULL AS `authority_level`,
NULL AS `personal_owner_id`,
NULL AS `location`,
`u`.`email` AS `last_edit_email`,
`u`.`first_name` AS `last_edit_first_name`,
`u`.`last_name` AS `last_edit_last_name`,
`mr`.`status` AS `moderated_status`,
NULL AS `icon`,
`u`.`id` AS `last_edit_user`,
`r`.`timestamp` AS `last_edit_timestamp`,
NULL AS `database_id`,
NULL AS `collection_type`,
`archived`,
`last_viewed_at` AS `last_used_at`,
NULL AS `table_id`,
NULL AS `is_upload`,
NULL AS `query_type`
FROM
`report_dashboard` AS `d`
LEFT JOIN `moderation_review` AS `mr` ON (`mr`.`moderated_item_id` = `d`.`id`)
AND (`mr`.`most_recent` = TRUE)
AND (`mr`.`moderated_item_type` = 'dashboard')
LEFT JOIN `revision` AS `r` ON (`r`.`model_id` = `d`.`id`)
AND (`r`.`most_recent` = TRUE)
AND (`r`.`model` = 'Dashboard')
LEFT JOIN `core_user` AS `u` ON `u`.`id` = `r`.`user_id`
WHERE
(
(`collection_id` IS NULL)
OR (
`collection_id` IN (
SELECT
`id`
FROM
`visible_collection_ids`
)
)
)
AND (
(`collection_id` = 2)
AND (`d`.`archived_directly` <> TRUE)
)
AND (`archived` = FALSE)
AND (`collection_position` IS NOT NULL)
UNION ALL
SELECT
3 AS `model_ranking`,
`c`.`id`,
`c`.`name`,
`c`.`description`,
`c`.`entity_id`,
`c`.`display`,
`c`.`collection_preview`,
`c`.`dataset_query`,
`c`.`collection_id`,
`dashboard_id`,
`c`.`archived_directly`,
'dataset' AS `model`,
`c`.`collection_position`,
NULL AS `authority_level`,
NULL AS `personal_owner_id`,
NULL AS `location`,
`u`.`email` AS `last_edit_email`,
`u`.`first_name` AS `last_edit_first_name`,
`u`.`last_name` AS `last_edit_last_name`,
`mr`.`status` AS `moderated_status`,
NULL AS `icon`,
`u`.`id` AS `last_edit_user`,
`r`.`timestamp` AS `last_edit_timestamp`,
`c`.`database_id`,
NULL AS `collection_type`,
`c`.`archived`,
`last_used_at`,
`c`.`table_id`,
`t`.`is_upload`,
`c`.`query_type`
FROM
`report_card` AS `c`
LEFT JOIN `revision` AS `r` ON (`r`.`model_id` = `c`.`id`)
AND (`r`.`most_recent` = TRUE)
AND (`r`.`model` = 'Card')
LEFT JOIN `moderation_review` AS `mr` ON (`mr`.`moderated_item_id` = `c`.`id`)
AND (`mr`.`most_recent` = TRUE)
AND (`mr`.`moderated_item_type` = 'card')
LEFT JOIN `core_user` AS `u` ON `u`.`id` = `r`.`user_id`
LEFT JOIN `metabase_table` AS `t` ON `t`.`id` = `c`.`table_id`
WHERE
(
(`collection_id` IS NULL)
OR (
`collection_id` IN (
SELECT
`id`
FROM
`visible_collection_ids`
)
)
)
AND (
(`collection_id` = 2)
AND (`c`.`archived_directly` = FALSE)
)
AND (`c`.`dashboard_id` IS NULL)
AND (`archived` = FALSE)
AND (`c`.`type` = 'model')
AND (`collection_position` IS NOT NULL)
UNION ALL
SELECT
4 AS `model_ranking`,
`c`.`id`,
`c`.`name`,
`c`.`description`,
`c`.`entity_id`,
`c`.`display`,
`c`.`collection_preview`,
`c`.`dataset_query`,
`c`.`collection_id`,
`dashboard_id`,
`c`.`archived_directly`,
'metric' AS `model`,
`c`.`collection_position`,
NULL AS `authority_level`,
NULL AS `personal_owner_id`,
NULL AS `location`,
`u`.`email` AS `last_edit_email`,
`u`.`first_name` AS `last_edit_first_name`,
`u`.`last_name` AS `last_edit_last_name`,
`mr`.`status` AS `moderated_status`,
NULL AS `icon`,
`u`.`id` AS `last_edit_user`,
`r`.`timestamp` AS `last_edit_timestamp`,
NULL AS `database_id`,
NULL AS `collection_type`,
`c`.`archived`,
`last_used_at`,
NULL AS `table_id`,
NULL AS `is_upload`,
NULL AS `query_type`
FROM
`report_card` AS `c`
LEFT JOIN `revision` AS `r` ON (`r`.`model_id` = `c`.`id`)
AND (`r`.`most_recent` = TRUE)
AND (`r`.`model` = 'Card')
LEFT JOIN `moderation_review` AS `mr` ON (`mr`.`moderated_item_id` = `c`.`id`)
AND (`mr`.`most_recent` = TRUE)
AND (`mr`.`moderated_item_type` = 'card')
LEFT JOIN `core_user` AS `u` ON `u`.`id` = `r`.`user_id`
WHERE
(
(`collection_id` IS NULL)
OR (
`collection_id` IN (
SELECT
`id`
FROM
`visible_collection_ids`
)
)
)
AND (
(`collection_id` = 2)
AND (`c`.`archived_directly` = FALSE)
)
AND (`c`.`dashboard_id` IS NULL)
AND (`archived` = FALSE)
AND (`c`.`type` = 'metric')
AND (`collection_position` IS NOT NULL)
UNION ALL
SELECT
DISTINCT 2 AS `model_ranking`,
`p`.`id`,
`p`.`name`,
NULL AS `description`,
`p`.`entity_id`,
NULL AS `display`,
NULL AS `collection_preview`,
NULL AS `dataset_query`,
`p`.`collection_id`,
NULL AS `dashboard_id`,
NULL AS `archived_directly`,
'pulse' AS `model`,
`p`.`collection_position`,
NULL AS `authority_level`,
NULL AS `personal_owner_id`,
NULL AS `location`,
NULL AS `last_edit_email`,
NULL AS `last_edit_first_name`,
NULL AS `last_edit_last_name`,
NULL AS `moderated_status`,
NULL AS `icon`,
NULL AS `last_edit_user`,
NULL AS `last_edit_timestamp`,
NULL AS `database_id`,
NULL AS `collection_type`,
NULL AS `archived`,
NULL AS `last_used_at`,
NULL AS `table_id`,
NULL AS `is_upload`,
NULL AS `query_type`
FROM
`pulse` AS `p`
LEFT JOIN `pulse_card` AS `pc` ON `p`.`id` = `pc`.`pulse_id`
WHERE
(`p`.`collection_id` = 2)
AND (`p`.`archived` = FALSE)
AND (`p`.`alert_condition` IS NULL)
AND (`p`.`dashboard_id` IS NULL)
AND (`p`.`collection_position` IS NOT NULL)
UNION ALL
SELECT
8 AS `model_ranking`,
`id`,
`name`,
`description`,
`entity_id`,
NULL AS `display`,
NULL AS `collection_preview`,
NULL AS `dataset_query`,
`collection_id`,
NULL AS `dashboard_id`,
NULL AS `archived_directly`,
'timeline' AS `model`,
NULL AS `collection_position`,
NULL AS `authority_level`,
NULL AS `personal_owner_id`,
NULL AS `location`,
NULL AS `last_edit_email`,
NULL AS `last_edit_first_name`,
NULL AS `last_edit_last_name`,
NULL AS `moderated_status`,
`icon`,
NULL AS `last_edit_user`,
NULL AS `last_edit_timestamp`,
NULL AS `database_id`,
NULL AS `collection_type`,
NULL AS `archived`,
NULL AS `last_used_at`,
NULL AS `table_id`,
NULL AS `is_upload`,
NULL AS `query_type`
FROM
`timeline` AS `timeline`
WHERE
(1 = 2)
AND (`collection_id` = 2)
AND (`archived` = FALSE)
) AS `dummy_alias`
ORDER BY
`authority_level` ASC,
`collection_type` ASC,
LOWER(`name`) ASC;
the result is :
Query 1 ERROR at Line 1: : 48 is beyond the maximum value that can be held by 1 bits