Not found column xxx in block. (NOT_FOUND_COLUMN_IN_BLOCK) version 22.9.2.7

[seca-rca]

Query:

select *
from ab_12_aaa aa
left join ab_12_bbb bb
on bb.id = aa.id and bb.`_year` = aa.`_year`
where bb.theyear >= 2019

How to reproduce

• ch version: 22.9.2.7
• mysql interface
• Non-default settings: none

tables:

CREATE TABLE ab_12_aaa
(
    `id` String,
    `subid` Int32,
    `prodcat` String,
    `prodtype` String,
    `quality` String,
    `m1` Float64,
    `m2` Float64,
    `r1` Float64,
    `r2` Float64,
    `d1` Float64,
    `d2` Float64,
    `pcs` Float64,
    `qty` Float64,
    `amt` Float64,
    `amts` Float64,
    `prc` Float64,
    `prcs` Float64,
    `suqty` Float64,
    `suamt` Float64,
    `_year` String
)
ENGINE = MergeTree
ORDER BY (_year, prodcat, prodtype, quality, d1, id)
SETTINGS index_granularity = 8192

CREATE TABLE ab_12_bbb
(
    `id` String,
    `sales_type` String,
    `date` Date32,
    `o1` String,
    `o2` String,
    `o3` String,
    `o4` String,
    `o5` String,
    `short` String,
    `a1` String,
    `a2` String,
    `a3` String,
    `idx` String,
    `a4` String,
    `ctx` String,
    `_year` String,
    `theyear` UInt16 MATERIALIZED toYear(`date`),
    `themonth` UInt8 MATERIALIZED toMonth(`date`),
    `theweek` UInt8 MATERIALIZED toISOWeek(`date`)
)
ENGINE = MergeTree
ORDER BY (theyear, themonth, _year, id, sales_type, date)
SETTINGS index_granularity = 8192

Expected behavior

Query runs.

Error message and/or stacktrace

DB::Exception: Not found column theyear in block. (NOT_FOUND_COLUMN_IN_BLOCK) (version 22.9.2.7 (official build))

[seca-rca]

Maybe related: #3140

[seca-rca]

Maybe related: #13274

[den-crane]

bb.attr = aa.attr

There is no column attr in both tables.

[seca-rca]

@den-crane Hello Denny, I corrected the example, please review one more time. Sorry for the mistake. Thanks.

[den-crane]

Right, because of aliased tables.

CREATE TABLE l (y String)Engine Memory;

CREATE TABLE r (d Date, y String,  ty UInt16 MATERIALIZED toYear(d)) Engine Memory;

select * from l L left join r R on  L.y = R.y  where R.ty >= 2019
DB::Exception: Not found column ty in block. (NOT_FOUND_COLUMN_IN_BLOCK)

select * from l  left join r  on  l.y = r.y  where r.ty >= 2019
OK

try

SELECT *
FROM ab_12_aaa
LEFT JOIN ab_12_bbb ON (ab_12_aaa.id = ab_12_bbb.id) AND (ab_12_bbb._year = ab_12_aaa._year)
WHERE ab_12_bbb.theyear >= 2019

[alexey-milovidov]

Proven to be fixed by Analyzer.
This issue is waiting when the Analyzer will be enabled by default.