When using views in TiDB v6.5.1, nested CASE WHEN statements result in error 1105

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidbv6.5.1使用视图库的表的时候嵌套case when会报错,显示错误 1105

| username: Jjjjayson_zeng

【TiDB Usage Environment】Production Environment
【TiDB Version】v6.5.1
【Reproduction Path】An error occurs when using nested case when in a view table
【Encountered Problem: Problem Phenomenon and Impact】

image
image1288×724 40.5 KB

【Resource Configuration】Not related to configuration
【Attachments: Screenshots/Logs/Monitoring】

2
| username: Billmay表妹 | Original post link

Error code 1105 indicates that the specified column cannot be found in the schema column. Therefore, your error message “Error 1105” might be due to the query in the view involving multiple CTEs. TiDB will clone the schema column information and assign a hashcode during the buildTableRef and BuildSelectSubq processes (used for mapping the relationship between parent and child columns during the projection elimination phase). Using an outdated hashcode will cause the associated condition columns to be incorrectly mapped.

If your SQL uses nested CASE WHEN, it might cause TiDB to fail to correctly recognize the column name during execution, resulting in error 1105. This might be because TiDB generates a temporary column name when processing the CASE WHEN statement, and this column name might conflict with the column name in your view, causing TiDB to be unable to find the specified column.

To resolve this issue, you can try using aliases to avoid column name conflicts. For example, change the temporary column name in the CASE WHEN statement to a name that does not conflict with the column names in the view, as shown below:

SELECT
  v1.id,
  v1.name,
  CASE
    WHEN v1.age > 18 THEN 'Adult'
    ELSE 'Minor'
  END AS age_group
FROM my_view v1;

In this example, we changed the temporary column name in the CASE WHEN statement to age_group.

3
| username: xingzhenxiang | Original post link

Try changing ‘as ‘B1’’ to ‘as B1’.

4
| username: Jjjjayson_zeng | Original post link

The image you provided is not accessible. Please provide the text you need translated.

5
| username: Jjjjayson_zeng | Original post link

Still reporting an error.

6
| username: Running | Original post link

Try using the original syntax of the case field when field condition then result1 else result2 end.

7
| username: tidb菜鸟一只 | Original post link

Can you execute that IFNULL statement alone?
SELECT ISNULL(MAX(CASE WHEN 1=1 THEN (CASE WHEN a.i_re >0 THEN ‘1’ ELSE ‘0’ END)
ELSE ‘0’ END ),0) FROM a;
I executed it directly and got an error saying
Incorrect parameter count in the call to native function ‘isnull’.

8
| username: Jjjjayson_zeng | Original post link

The issue was resolved because the use of max was incorrect, and it has nothing to do with TiDB. Thank you, everyone.

9
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.