[11051 [HY000j: errCode = 2, detallMessage = (172,120.180,12)[CANCELLEDICANCELLEDjExpected EQ 1 to be returned biexpression (SCALARSUBQUERY) SubqueryExpr ( Queryplan=Logicallimit ( limit=1, offset=0, phase=ORlGIN ), Correlatedslots=l. typeCoercionExpr=nul

[jiangxiaoqiang]

I am using Apache Doris 2.x to do a sql query, the sql look like this:

SELECT sum(1) As msgcount,
IFNULL((
  SELECT title
  FROM system_notify_message 
  WHERE user_id= 1798233723758948352 
  AND tenant_id = 18 
  AND tenant_id= 181LIMIT 1),'d') AS SySMSg 
FROM system_notify_message 
WHERE user_id= 1798233723758948352
AND tenant_id =181 
AND tenant_id=181 
GROUP BY tenant_id

when the subquery result is null, the sql will shows error:

[11051 [HY000j: errCode = 2, detallMessage = (172,120.180,12)[CANCELLEDICANCELLEDjExpected EQ 1 to be returned biexpression (SCALARSUBQUERY) SubqueryExpr ( Queryplan=Logicallimit ( limit=1, offset=0, phase=ORlGIN ), Correlatedslots=l.
typeCoercionExpr=nul

Am I missing something? I have already tried to add IFNULL function to make it return default value. what should I do to fixed this issue?

I have build the Apache Doris 2.0.3 and run SQL like this:

SELECT sum(1) As msgcount,
IFNULL((
  SELECT title
  FROM system_notify_message 
  WHERE user_id= 1798233723758948352  
  AND tenant_id= 181 LIMIT 1),'d') AS SySMSg 
FROM system_notify_message 
WHERE user_id= 1798233723758948352
AND tenant_id =181 
GROUP BY tenant_id

shows error:

`
SQL Error [1105] [HY000]: errCode = 2, detailMessage = (127.0.0.1)[CANCELLED][CANCELLED]Expected EQ 1 to be returned by expression (SCALARSUBQUERY) SubqueryExpr ( QueryPlan=LogicalLimit ( limit=1, offset=0, phase=ORIGIN ), CorrelatedSlots=[],

`

this is the table DML:

-- dolphin.system_notify_message definition

CREATE TABLE `system_notify_message` (
  `id` bigint(20) NULL,
  `user_id` bigint(20) NULL,
  `tenant_id` bigint(20) NULL,
  `title` varchar(32) NULL
) ENGINE=OLAP
UNIQUE KEY(`id`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);

BTW, keep the table's row to 0, do not put data into table. The expect result is null, not throw error. This SQL works fine in MySQL.

[ixzc]

which version of your Doris? i test this in version 2.1.3 , and didn't reproduce your issues.

[jiangxiaoqiang]

I am using Apache Doris 2.0.3. this will happen when the subquery return null. When the query:

SELECT title
  FROM system_notify_message 
  WHERE user_id= 1798233723758948352 
  AND tenant_id = 181
  LIMIT 1

has no data this will happen.

Sorry I am in a internal network and can not paste the full plain text sql. Add ifnull function: