TPCDS Q58: Ambiguous column 'item_id':

[erdembilegtbd]

WITH
ss_items AS
(
SELECT
i_item_id AS item_id,
sum(ss_ext_sales_price) AS ss_item_rev
FROM store_sales, item, date_dim
WHERE (ss_item_sk = i_item_sk) AND (d_date IN (
SELECT d_date
FROM date_dim
WHERE d_week_seq = (
SELECT d_week_seq
FROM date_dim
WHERE d_date = '2000-01-03'
)
)) AND (ss_sold_date_sk = d_date_sk)
GROUP BY i_item_id
),
cs_items AS
(
SELECT
i_item_id AS item_id,
sum(cs_ext_sales_price) AS cs_item_rev
FROM catalog_sales, item, date_dim
WHERE (cs_item_sk = i_item_sk) AND (d_date IN (
SELECT d_date
FROM date_dim
WHERE d_week_seq = (
SELECT d_week_seq
FROM date_dim
WHERE d_date = '2000-01-03'
)
)) AND (cs_sold_date_sk = d_date_sk)
GROUP BY i_item_id
),
ws_items AS
(
SELECT
i_item_id AS item_id,
sum(ws_ext_sales_price) AS ws_item_rev
FROM web_sales, item, date_dim
WHERE (ws_item_sk = i_item_sk) AND (d_date IN (
SELECT d_date
FROM date_dim
WHERE d_week_seq = (
SELECT d_week_seq
FROM date_dim
WHERE d_date = '2000-01-03'
)
)) AND (ws_sold_date_sk = d_date_sk)
GROUP BY i_item_id
)
SELECT
ss_items.item_id,
ss_item_rev,
(ss_item_rev / (((ss_item_rev + cs_item_rev) + ws_item_rev) / 3)) * 100 AS ss_dev,
cs_item_rev,
(cs_item_rev / (((ss_item_rev + cs_item_rev) + ws_item_rev) / 3)) * 100 AS cs_dev,
ws_item_rev,
(ws_item_rev / (((ss_item_rev + cs_item_rev) + ws_item_rev) / 3)) * 100 AS ws_dev,
((ss_item_rev + cs_item_rev) + ws_item_rev) / 3 AS average
FROM ss_items, cs_items, ws_items
WHERE (ss_items.item_id = cs_items.item_id) AND (ss_items.item_id = ws_items.item_id) AND ((ss_item_rev >= ('0.9' * cs_item_rev)) AND (ss_item_rev <= ('1.1' * cs_item_rev))) AND ((ss_item_rev >= ('0.9' * ws_item_rev)) AND (ss_item_rev <= ('1.1' * ws_item_rev))) AND ((cs_item_rev >= ('0.9' * ss_item_rev)) AND (cs_item_rev <= ('1.1' * ss_item_rev))) AND ((cs_item_rev >= ('0.9' * ws_item_rev)) AND (cs_item_rev <= ('1.1' * ws_item_rev))) AND ((ws_item_rev >= ('0.9' * ss_item_rev)) AND (ws_item_rev <= ('1.1' * ss_item_rev))) AND ((ws_item_rev >= ('0.9' * cs_item_rev)) AND (ws_item_rev <= ('1.1' * cs_item_rev)))
ORDER BY
item_id ASC,
ss_item_rev ASC
LIMIT 100;

Received exception from server (version 21.8.7):
Code: 352. DB::Exception: Received from 127.0.0.1:10002. DB::Exception: Ambiguous column 'item_id': While processing WITH ss_items AS (SELECT i_item_id AS item_id, sum(ss_ext_sales_price) AS ss_item_rev FROM store_sales, item, date_dim WHERE (ss_item_sk = i_item_sk) AND (d_date IN (SELECT d_date FROM date_dim WHERE d_week_seq = (SELECT d_week_seq FROM date_dim WHERE d_date = '2000-01-03'))) AND (ss_sold_date_sk = d_date_sk) GROUP BY i_item_id), cs_items AS (SELECT i_item_id AS item_id, sum(cs_ext_sales_price) AS cs_item_rev FROM catalog_sales, item, date_dim WHERE (cs_item_sk = i_item_sk) AND (d_date IN (SELECT d_date FROM date_dim WHERE d_week_seq = (SELECT d_week_seq FROM date_dim WHERE d_date = '2000-01-03'))) AND (cs_sold_date_sk = d_date_sk) GROUP BY i_item_id), ws_items AS (SELECT i_item_id AS item_id, sum(ws_ext_sales_price) AS ws_item_rev FROM web_sales, item, date_dim WHERE (ws_item_sk = i_item_sk) AND (d_date IN (SELECT d_date FROM date_dim WHERE d_week_seq = (SELECT d_week_seq FROM date_dim WHERE d_date = '2000-01-03'))) AND (ws_sold_date_sk = d_date_sk) GROUP BY i_item_id) SELECT _--ss_items.item_id AS ss_items.item_id, ss_item_rev, (ss_item_rev / (((ss_item_rev + cs_item_rev) + ws_item_rev) / 3)) * 100 AS ss_dev, cs_item_rev, (cs_item_rev / (((ss_item_rev + cs_item_rev) + ws_item_rev) / 3)) * 100 AS cs_dev, ws_item_rev, (ws_item_rev / (((ss_item_rev + cs_item_rev) + ws_item_rev) / 3)) * 100 AS ws_dev, ((ss_item_rev + cs_item_rev) + ws_item_rev) / 3 AS average FROM ss_items ALL INNER JOIN cs_items ON ss_items.item_id = cs_items.item_id ALL INNER JOIN ws_items ON ss_items.item_id = ws_items.item_id WHERE (_--ss_items.item_id = _--cs_items.item_id) AND (_--ss_items.item_id = _--ws_items.item_id) AND ((ss_item_rev >= ('0.9' * cs_item_rev)) AND (ss_item_rev <= ('1.1' * cs_item_rev))) AND ((ss_item_rev >= ('0.9' * ws_item_rev)) AND (ss_item_rev <= ('1.1' * ws_item_rev))) AND ((cs_item_rev >= ('0.9' * ss_item_rev)) AND (cs_item_rev <= ('1.1' * ss_item_rev))) AND ((cs_item_rev >= ('0.9' * ws_item_rev)) AND (cs_item_rev <= ('1.1' * ws_item_rev))) AND ((ws_item_rev >= ('0.9' * ss_item_rev)) AND (ws_item_rev <= ('1.1' * ss_item_rev))) AND ((ws_item_rev >= ('0.9' * cs_item_rev)) AND (ws_item_rev <= ('1.1' * cs_item_rev))) ORDER BY item_id ASC, ss_item_rev ASC LIMIT 100 SQLSTATE: 42000.

[erdembilegtbd]

Query is here: https://github.com/ByConity/byconity-tpcds/blob/main/sql/standard/58.sql

[erdembilegtbd]

After passing that:

Code: 43. DB::Exception: Received from 127.0.0.1:10002. DB::Exception: Illegal types String and Decimal(38, 2) of arguments of function multiply: While processing ((((cs_item_rev <= ('1.1' * ss_item_rev)) AND (cs_item_rev >= ('0.9' * ss_item_rev))) AND (ss_item_rev <= ('1.1' * cs_item_rev))) AND (ss_item_rev >= ('0.9' * cs_item_rev))) AND ((item_id AS _--ss_items.item_id) = (cs_items.item_id AS _--cs_items.item_id)) SQLSTATE: 42000.