Error in using case when query: Subquery returns more than 1 row

translator_bot · June 21, 2024, 4:08am

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

Original topic: 使用case when查询报错：Subquery returns more than 1 row

| username: CAICAI

category_base.sql (89.1 MB)
category_team_developer_config.sql (6.0 MB)
【 TiDB Usage Environment】Test
【 TiDB Version】7.5
Table Structure:

CREATE TABLE `category_base` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `platformCode` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Platform Code',
  `siteCode` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Site Name',
  `categoryId` char(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Category ID',
  `parentId` char(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Parent ID',
  `categoryIdPath` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `categoryIdPathReverse` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `categoryName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Category Name',
  `categoryNamePath` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT 'Category Name Path',
  `type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Category Type (homePage: Home Page; newProductList: New Product List)',
  `url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Category URL',
  `level` int unsigned NOT NULL DEFAULT '0' COMMENT 'Level',
  `isDel` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'Is Deleted 1. Deleted 0. Not Deleted, default is 0',
  `isLeaf` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'Has Subcategory 1. Yes 0. No',
  `version` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Version',
  `createdTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT 'Creation Date',
  `createdUser` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Creator ID',
  `updateTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT 'Last Update Time',
  `updateUser` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Updater',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_platform_site_level_type` (`platformCode`,`siteCode`,`categoryId`,`level`,`type`) USING BTREE,
  KEY `idx_site_category` (`siteCode`,`categoryName`) USING BTREE,
  KEY `idx_categoryId` (`categoryId`) USING BTREE,
  KEY `idx_parentId` (`parentId`) USING BTREE,
  KEY `idx_categoryIdPath` (`categoryIdPath`) USING BTREE,
  KEY `idx_categoryIdPathReverse` (`categoryIdPathReverse`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=329579 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='category base';

CREATE TABLE `category_team_developer_config` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto Increment ID',
  `teamConfigId` int unsigned NOT NULL COMMENT 'Team Category Config ID',
  `teamName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Team Name',
  `platform` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Platform',
  `site` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Site',
  `categoryId` char(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '0' COMMENT 'Category ID',
  `level` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Category Level',
  `parentCategoryId` char(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '0' COMMENT 'Parent Category ID',
  `developer` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Developer, separated by commas',
  `createTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT 'Config Creation Time',
  `createUser` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Config Creator',
  `updateTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT 'Config Update Time',
  `updateUser` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Config Updater',
  `isDel` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Is Deleted 1. Yes 0. No',
  `delTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT 'Deletion Time',
  `delUser` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'Deleter',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_t_p_s_c_d` (`teamName`,`platform`,`site`,`categoryId`,`delTime`) USING BTREE,
  KEY `idx_platform` (`platform`) USING BTREE,
  KEY `idx_site` (`site`) USING BTREE,
  KEY `idx_categoryId` (`categoryId`) USING BTREE,
  KEY `idx_teamConfigId` (`teamConfigId`) USING BTREE,
  KEY `idx_del` (`isDel`) USING BTREE,
  KEY `idex_teamName` (`teamName`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=56710 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='Team Category Developer Config';

Query SQL:

 SELECT   a.`teamConfigId`, a.`teamName`, a.`platform`,
            CASE
            WHEN a.platform = 'EB'
            THEN (SELECT categoryIdPath FROM `category_base` WHERE isDel = 0 AND platformCode = a.platform AND siteCode = a.site AND categoryId = a.categoryId AND a.level = `level`)
            WHEN a.platform = 'SM'
            THEN (SELECT categoryIdPath FROM `category_base` WHERE isDel = 0 AND platformCode = a.platform AND siteCode = a.site AND categoryId = a.categoryId AND `type` = 'java' AND a.level = `level`)
            WHEN a.platform = 'YA'
            THEN (SELECT categoryIdPath FROM `category_base` WHERE isDel = 0 AND platformCode = a.platform AND siteCode = a.site AND categoryId = a.categoryId AND `type` = 'homePage' AND a.level = `level`)
            WHEN a.platform = 'BB'
            THEN (SELECT categoryIdPath FROM `category_base` WHERE isDel = 0 AND platformCode = a.platform AND siteCode = a.site AND categoryId = a.categoryId AND `type` = 'list' AND a.level = `level`)
            END AS `categoryIdPath`
        FROM
            `category_team_developer_config` as `a`
        WHERE 
            a.`isDel` = 0 AND (FIND_IN_SET('liupan', `developer`)) LIMIT 0,10

Error Message:

> 1242 - Subquery returns more than 1 row
> Time: 0.067s

The same table structure and data work fine in MySQL and DB2 databases, but it reports an error in TiDB. After checking the data, this error should not occur.

You can test this SQL and script data to see if my writing is problematic or if this is a bug.

Data:
category_base.sql (89.1 MB)
category_team_developer_config.sql (6.0 MB)

translator_bot · June 21, 2024, 4:09am

| username: Kongdom | Original post link

Try adding “LIMIT 1” to the SQL statement after “then”.

translator_bot · June 21, 2024, 4:09am

| username: CAICAI | Original post link

Adding this will definitely work, but under the current conditions, it won’t go into the ‘when’ clause with more than 2 entries. How will it report an error?

translator_bot · June 21, 2024, 4:09am

| username: Kongdom | Original post link

If it can be added, it means that more than 2 will be run.

translator_bot · June 21, 2024, 4:09am

| username: CAICAI | Original post link

The correct and safe way is to add “LIMIT 1” inside the “WHEN” clause. However, I don’t think this data should fall into the condition of having more than 2 rows. The same SQL and data work fine in MySQL and DB2.

translator_bot · June 21, 2024, 4:09am

| username: Kongdom | Original post link

Does the attached SQL contain reproducible data?

translator_bot · June 21, 2024, 4:09am

| username: CAICAI | Original post link

Included

translator_bot · June 21, 2024, 4:09am

| username: 裤衩儿飞上天 | Original post link

The data in the green box should include the data that meets the following three conditions, right? There must be more than one entry. Is there any condition missing?

translator_bot · June 21, 2024, 4:09am

| username: Kongdom | Original post link

Sharp-eyed

translator_bot · June 21, 2024, 4:09am

| username: CAICAI | Original post link

The data for this SQL is ‘SM’, it should follow the case platform = ‘SM’, and should not follow the one in your screenshot.

translator_bot · June 21, 2024, 4:09am

| username: CAICAI | Original post link

There is only one piece of data for this condition, platform = ‘SM’.
So it should not go to
WHEN a.platform = ‘EB’
THEN (SELECT categoryIdPath FROM category_base WHERE isDel = 0 AND platformCode = a.platform AND siteCode = a.site AND categoryId = a.categoryId AND a.level = level)
This SQL statement to make a judgment. Here, platform = ‘EB’ does not match this data. It should follow this condition to find this SQL:
WHEN a.platform = ‘SM’
THEN (SELECT categoryIdPath FROM category_base WHERE isDel = 0 AND platformCode = a.platform AND siteCode = a.site AND categoryId = a.categoryId AND type = ‘java’ AND a.level = level)

translator_bot · June 21, 2024, 4:09am

| username: Kongdom | Original post link

After restoring the database, the issue can be reproduced. According to the first case, multiple records can be queried. It is recommended to add LIMIT 1.

translator_bot · June 21, 2024, 4:09am

| username: Kongdom | Original post link

Note that TiDB is a distributed database. It retrieves data from various TiKV nodes and then performs association and filtering on the TiDB side. Therefore, it is necessary to ensure that the data retrieval at the TiKV layer is logically correct.

From the execution plan, it is evident that the execution is parallel. The original statement only returns a single row due to the conditions passed in. If other conditions are passed in, multiple rows may still be returned, so it is crucial to ensure logical correctness.

translator_bot · June 21, 2024, 4:09am

| username: CAICAI | Original post link

What you mentioned is the same in other databases; multiple records may appear. To ensure logical correctness and avoid errors, adding “LIMIT 1” is indeed correct. However, the current issue is that an error occurs with the data under this condition. Although this is a distributed system, it should still fetch based on the filtered data (this condition should only return one record).

translator_bot · June 21, 2024, 4:09am

| username: Kongdom | Original post link

According to this logic, the database should first retrieve data from each node based on the WHERE condition (which only has one condition), and then in the second step, fetch the filtered data from each node. The first and second steps cannot be executed in parallel. Looking at the execution plan above, currently, the first and second steps are executed in parallel.

translator_bot · June 21, 2024, 4:09am

| username: 小龙虾爱大龙虾 | Original post link

Almighty community

translator_bot · June 21, 2024, 4:09am

| username: Hacker_ph6hjOO7 | Original post link

github.com/pingcap/tidb

Unexpected behavior for `case when {false condition} then {subquery...} else {...} end`

opened 02:35AM - 24 Jun 22 UTC

solotzg

type/bug sig/execution severity/moderate affects-4.0 affects-5.0 affects-5.1 affects-5.2 affects-5.3 affects-5.4 affects-6.0 affects-6.1

## Bug Report Please answer these questions before submitting your issue. Tha…nks! ref #34217 ### 1. Minimal reproduce step (Required) ``` DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; create table t1(c1 int); insert into t1 values(2), (3); select * from t1 where (case when 0 <> 0 then (select c1 from t1) else 0 end) > 1; explain select * from t1 where (case when 0 <> 0 then (select c1 from t1) else 0 end) > 1; select * from t1 where (case when (select count(1) from t1) = 0 then (select c1 from t1) else 0 end) > 1; ``` ### 2. What did you expect to see? (Required) MySQL ``` mysql> select * from t1 where (case when 0 <> 0 then (select c1 from t1) else 0 end) > 1; Empty set (0.00 sec) ``` ### 3. What did you see instead (Required) ``` MySQL [test]> select * from t1 where (case when 0 <> 0 then (select c1 from t1) else 0 end) > 1; ERROR 1242 (21000): Subquery returns more than 1 row MySQL [test]> explain select * from t1 where (case when 0 <> 0 then (select c1 from t1) else 0 end) > 1; ERROR 1242 (21000): Subquery returns more than 1 row MySQL [test]> select * from t1 where (case when (select count(1) from t1) = 0 then (select c1 from t1) else 0 end) > 1; ERROR 1242 (21000): Subquery returns more than 1 row ``` ### 4. What is your TiDB version? (Required) >= release-4.0

translator_bot · June 21, 2024, 4:09am

| username: dba远航 | Original post link

This error occurs when fetching a single row but getting multiple rows.

translator_bot · June 21, 2024, 4:09am

| username: system | Original post link

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