bug: 1006=>Unable to get field named "18446744073709551612". Valid fields: ["36"] #17815
Description
Summary
Databend Query v1.2.725-nightly-25ee2d6e65(rust-1.88.0-nightly-2025-04-16T13:54:26.411332224Z)
How to reproduce:
CREATE OR REPLACE TABLE item (
i_item_sk INT,
i_item_id VARCHAR,
i_rec_start_date DATE,
i_rec_end_date DATE,
i_item_desc VARCHAR,
i_current_price DECIMAL(7,2),
i_wholesale_cost DECIMAL(7,2),
i_brand_id INT,
i_brand VARCHAR,
i_class_id INT,
i_class VARCHAR,
i_category_id INT,
i_category VARCHAR,
i_manufact_id INT,
i_manufact VARCHAR,
i_size VARCHAR,
i_formulation VARCHAR,
i_package VARCHAR,
i_unit VARCHAR,
i_value DECIMAL(7,2),
i_color VARCHAR,
i_units VARCHAR,
i_container VARCHAR,
i_manager_id INT,
i_product_name VARCHAR
);
CREATE OR REPLACE TABLE date_dim (
d_date_sk INT,
d_date_id VARCHAR,
d_date DATE,
d_month_seq INT,
d_week_seq INT,
d_quarter_seq INT,
d_year INT,
d_dow INT,
d_moy INT,
d_dom INT,
d_qoy INT,
d_fy_year INT,
d_fy_quarter_seq INT,
d_fy_week_seq INT,
d_day_name VARCHAR,
d_quarter_name VARCHAR,
d_holiday VARCHAR
);
CREATE OR REPLACE TABLE customer_address (
ca_address_sk INT,
ca_address_id VARCHAR,
ca_street_number VARCHAR,
ca_street_name VARCHAR,
ca_street_type VARCHAR,
ca_suite_number VARCHAR,
ca_city VARCHAR,
ca_county VARCHAR,
ca_state VARCHAR,
ca_zip VARCHAR,
ca_country VARCHAR,
ca_gmt_offset DECIMAL(5,2),
ca_location_type VARCHAR
);
CREATE OR REPLACE TABLE customer (
c_customer_sk INT,
c_customer_id VARCHAR,
c_current_cdemo_sk INT,
c_current_hdemo_sk INT,
c_current_addr_sk INT,
c_first_name VARCHAR,
c_last_name VARCHAR,
c_preferred_cust_flag VARCHAR,
c_birth_day INT,
c_birth_month INT,
c_birth_year INT,
c_birth_country VARCHAR,
c_login VARCHAR,
c_email_address VARCHAR,
c_last_review_date DATE
);
CREATE OR REPLACE TABLE customer_demographics (
cd_demo_sk INT,
cd_gender VARCHAR,
cd_marital_status VARCHAR,
cd_education_status VARCHAR,
cd_purchase_estimate INT,
cd_credit_rating VARCHAR,
cd_dep_count INT,
cd_dep_employed_count INT,
cd_dep_college_count INT
);
CREATE OR REPLACE TABLE catalog_sales (
cs_sold_date_sk INT,
cs_sold_time_sk INT,
cs_ship_date_sk INT,
cs_bill_customer_sk INT,
cs_ship_customer_sk INT,
cs_item_sk INT,
cs_promo_sk INT,
cs_order_number INT,
cs_quantity INT,
cs_list_price DECIMAL(7,2),
cs_sales_price DECIMAL(7,2),
cs_coupon_amt DECIMAL(7,2),
cs_ext_discount_amt DECIMAL(7,2),
cs_ext_sales_price DECIMAL(7,2),
cs_ext_coupon_amt DECIMAL(7,2),
cs_ext_list_price DECIMAL(7,2),
cs_ext_tax DECIMAL(7,2),
cs_coupon_id VARCHAR,
cs_ship_mode_sk INT,
cs_net_paid DECIMAL(7,2),
cs_net_paid_inc_tax DECIMAL(7,2),
cs_net_profit DECIMAL(7,2),
cs_bill_addr_sk INT,
cs_ship_addr_sk INT,
cs_bill_cdemo_sk INT,
cs_ship_cdemo_sk INT,
cs_warehouse_sk INT
);
INSERT INTO item (i_item_sk, i_item_id) VALUES (1, 'AAAAAAAABAAAAAAA');
INSERT INTO item (i_item_sk, i_item_id) VALUES (2, 'AAAAAAAACAAAAAAA');
INSERT INTO item (i_item_sk, i_item_id) VALUES (3, 'AAAAAAAADAAAAAAA');
INSERT INTO date_dim (d_date_sk, d_year, d_date) VALUES (1, 2001, '2001-01-01');
INSERT INTO date_dim (d_date_sk, d_year, d_date) VALUES (2, 2002, '2002-01-01');
INSERT INTO date_dim (d_date_sk, d_year, d_date) VALUES (3, 2001, '2001-02-01');
INSERT INTO customer_address (ca_address_sk, ca_country, ca_state, ca_county) VALUES (1, 'USA', 'ND', 'County1');
INSERT INTO customer_address (ca_address_sk, ca_country, ca_state, ca_county) VALUES (2, 'USA', 'WI', 'County2');
INSERT INTO customer_address (ca_address_sk, ca_country, ca_state, ca_county) VALUES (3, 'USA', 'AL', 'County3');
INSERT INTO customer_address (ca_address_sk, ca_country, ca_state, ca_county) VALUES (4, 'USA', 'NC', 'County4');
INSERT INTO customer_address (ca_address_sk, ca_country, ca_state, ca_county) VALUES (5, 'USA', 'OK', 'County5');
INSERT INTO customer_address (ca_address_sk, ca_country, ca_state, ca_county) VALUES (6, 'USA', 'MS', 'County6');
INSERT INTO customer_address (ca_address_sk, ca_country, ca_state, ca_county) VALUES (7, 'USA', 'TN', 'County7');
INSERT INTO customer_address (ca_address_sk, ca_country, ca_state, ca_county) VALUES (8, 'USA', 'XX', 'County8');
INSERT INTO customer (c_customer_sk, c_current_cdemo_sk, c_current_addr_sk, c_birth_month, c_birth_year) VALUES (1, 1, 1, 9, 1970);
INSERT INTO customer (c_customer_sk, c_current_cdemo_sk, c_current_addr_sk, c_birth_month, c_birth_year) VALUES (2, 2, 2, 5, 1980);
INSERT INTO customer (c_customer_sk, c_current_cdemo_sk, c_current_addr_sk, c_birth_month, c_birth_year) VALUES (3, 3, 3, 12, 1990);
INSERT INTO customer (c_customer_sk, c_current_cdemo_sk, c_current_addr_sk, c_birth_month, c_birth_year) VALUES (4, 4, 4, 4, 1975);
INSERT INTO customer (c_customer_sk, c_current_cdemo_sk, c_current_addr_sk, c_birth_month, c_birth_year) VALUES (5, 5, 5, 1, 1985);
INSERT INTO customer (c_customer_sk, c_current_cdemo_sk, c_current_addr_sk, c_birth_month, c_birth_year) VALUES (6, 6, 6, 10, 1995);
INSERT INTO customer (c_customer_sk, c_current_cdemo_sk, c_current_addr_sk, c_birth_month, c_birth_year) VALUES (7, 7, 7, 11, 1972);
INSERT INTO customer (c_customer_sk, c_current_cdemo_sk, c_current_addr_sk, c_birth_month, c_birth_year) VALUES (8, 8, 8, 2, 1982);
INSERT INTO customer_demographics (cd_demo_sk, cd_gender, cd_education_status, cd_dep_count) VALUES (1, 'M', 'College', 2);
INSERT INTO customer_demographics (cd_demo_sk, cd_gender, cd_education_status, cd_dep_count) VALUES (2, 'F', 'High School', 1);
INSERT INTO customer_demographics (cd_demo_sk, cd_gender, cd_education_status, cd_dep_count) VALUES (3, 'M', 'College', 3);
INSERT INTO customer_demographics (cd_demo_sk, cd_gender, cd_education_status, cd_dep_count) VALUES (4, 'F', 'High School', 0);
INSERT INTO customer_demographics (cd_demo_sk, cd_gender, cd_education_status, cd_dep_count) VALUES (5, 'M', 'College', 2);
INSERT INTO customer_demographics (cd_demo_sk, cd_gender, cd_education_status, cd_dep_count) VALUES (6, 'F', 'High School', 1);
INSERT INTO customer_demographics (cd_demo_sk, cd_gender, cd_education_status, cd_dep_count) VALUES (7, 'M', 'College', 3);
INSERT INTO customer_demographics (cd_demo_sk, cd_gender, cd_education_status, cd_dep_count) VALUES (8, 'F', 'High School', 0);
INSERT INTO catalog_sales (cs_sold_date_sk, cs_item_sk, cs_bill_cdemo_sk, cs_bill_customer_sk, cs_quantity, cs_list_price, cs_coupon_amt, cs_sales_price, cs_net_profit) VALUES (1, 1, 1, 1, 10, 100.00, 10.00, 90.00, 20.00);
INSERT INTO catalog_sales (cs_sold_date_sk, cs_item_sk, cs_bill_cdemo_sk, cs_bill_customer_sk, cs_quantity, cs_list_price, cs_coupon_amt, cs_sales_price, cs_net_profit) VALUES (1, 2, 2, 2, 5, 50.00, 5.00, 45.00, 10.00);
INSERT INTO catalog_sales (cs_sold_date_sk, cs_item_sk, cs_bill_cdemo_sk, cs_bill_customer_sk, cs_quantity, cs_list_price, cs_coupon_amt, cs_sales_price, cs_net_profit) VALUES (3, 3, 3, 3, 20, 200.00, 20.00, 180.00, 40.00);
INSERT INTO catalog_sales (cs_sold_date_sk, cs_item_sk, cs_bill_cdemo_sk, cs_bill_customer_sk, cs_quantity, cs_list_price, cs_coupon_amt, cs_sales_price, cs_net_profit) VALUES (1, 1, 4, 4, 10, 100.00, 10.00, 90.00, 20.00);
INSERT INTO catalog_sales (cs_sold_date_sk, cs_item_sk, cs_bill_cdemo_sk, cs_bill_customer_sk, cs_quantity, cs_list_price, cs_coupon_amt, cs_sales_price, cs_net_profit) VALUES (1, 2, 5, 5, 5, 50.00, 5.00, 45.00, 10.00);
INSERT INTO catalog_sales (cs_sold_date_sk, cs_item_sk, cs_bill_cdemo_sk, cs_bill_customer_sk, cs_quantity, cs_list_price, cs_coupon_amt, cs_sales_price, cs_net_profit) VALUES (3, 3, 6, 6, 20, 200.00, 20.00, 180.00, 40.00);
INSERT INTO catalog_sales (cs_sold_date_sk, cs_item_sk, cs_bill_cdemo_sk, cs_bill_customer_sk, cs_quantity, cs_list_price, cs_coupon_amt, cs_sales_price, cs_net_profit) VALUES (1, 1, 7, 7, 10, 100.00, 10.00, 90.00, 20.00);
INSERT INTO catalog_sales (cs_sold_date_sk, cs_item_sk, cs_bill_cdemo_sk, cs_bill_customer_sk, cs_quantity, cs_list_price, cs_coupon_amt, cs_sales_price, cs_net_profit) VALUES (1, 2, 8, 8, 5, 50.00, 5.00, 45.00, 10.00);
SELECT
i_item_id,
ca_country,
ca_state,
ca_county,
AVG(CAST(cs_quantity AS DECIMAL(12, 2))) AS agg1,
AVG(CAST(cs_list_price AS DECIMAL(12, 2))) AS agg2,
AVG(CAST(cs_coupon_amt AS DECIMAL(12, 2))) AS agg3,
AVG(CAST(cs_sales_price AS DECIMAL(12, 2))) AS agg4,
AVG(CAST(cs_net_profit AS DECIMAL(12, 2))) AS agg5,
AVG(CAST(c_birth_year AS DECIMAL(12, 2))) AS agg6,
AVG(CAST(cd1.cd_dep_count AS DECIMAL(12, 2))) AS agg7
FROM catalog_sales
JOIN customer_demographics cd1 ON cs_bill_cdemo_sk = cd1.cd_demo_sk
JOIN customer_demographics cd2 ON cs_bill_customer_sk = (SELECT c_customer_sk FROM customer WHERE c_current_cdemo_sk = cd2.cd_demo_sk)
JOIN customer c ON cs_bill_customer_sk = c_customer_sk
JOIN customer_address ca ON c_current_addr_sk = ca_address_sk
JOIN date_dim d ON cs_sold_date_sk = d_date_sk
JOIN item i ON cs_item_sk = i_item_sk
WHERE cd1.cd_gender = 'M'
AND cd1.cd_education_status = 'College'
AND c_birth_month IN (9, 5, 12, 4, 1, 10)
AND d_year = 2001
AND ca_state IN ('ND', 'WI', 'AL', 'NC', 'OK', 'MS', 'TN')
GROUP BY GROUPING SETS (
(i_item_id, ca_country, ca_state, ca_county),
(i_item_id, ca_country, ca_state),
(i_item_id, ca_country),
(i_item_id),
(ca_country, ca_state, ca_county),
(ca_country, ca_state),
(ca_country),
()
)
ORDER BY ca_country, ca_state, ca_county, i_item_id
LIMIT 100;