Why UDF returns NULL for multi-row query but not for single row?

Question

I ran the following query to retrieve data for a single investment ID:

select *, getProductClass(InstrumentID, etf_option_mapper) as ProductClass 
from investor_pos 
where InvestorID = '7399000270'

It returned this result:

	BrokerID	InvestorID	InstrumentID	YdPosition	Position	ProductClass
0	6001	7399000270	10008862	15	15	588000

I then expanded the query to include multiple investment IDs:

select *, getProductClass(InstrumentID, etf_option_mapper) as ProductClass 
from investor_pos 
where InvestorID in ('7399000270', '00130239')

Now I see this result:

	BrokerID	InvestorID	InstrumentID	YdPosition	Position	ProductClass
0	0089	00130239	IF2509	3	3	IF
1	0089	00130239	IM2509	-2	-2	IM
2	0089	00130239	IM2509	2	2	IM
3	0089	00130239	IF2509	-3	-3	IF
4	6001	7399000270	10008862	15	15

Note the ProductClass value is now NULL on the last line. However, when I query for the single investment code, this specific data row returns a non-NULL result (corresponding to the above 588000 value).

Why would this produce different results? Could it be due to limitations of iif function when handling vectors versus scalars?

The complete reproducible script is provided below:

investor_pos.csv

BrokerID	InvestorID	InstrumentID	YdPosition	Position
0089	00130239	IF2509	3	3
0089	00130239	IM2509	-2	-2
0089	00130239	IM2509	2	2
0089	00130239	IF2509	-3	-3
6001	7399000270	10008862	15	15

etf_option_mapper

etf_option_mapper = dict("STRING", ANY)
etf_option_mapper["10008862"] = [588000]

scripts

investor_pos = loadText("<YourPath>/nvestor_pos.csv")
def getProductClass(InstrumentID,etf_option_mapper){
    productClass = left(InstrumentID,4).regexReplace("[0-9]+","").regexReplace('HO','IH').regexReplace('IO','IF').regexReplace('MO','IM')
    return iif(isDigit(InstrumentID),etf_option_mapper[InstrumentID][0],productClass)
}

select *,getProductClass(InstrumentID,etf_option_mapper) as ProductClass from investor_pos where InvestorID='7399000270'
select *,getProductClass(InstrumentID,etf_option_mapper) as ProductClass from investor_pos where InvestorID in ('7399000270','00130239')

Looks like the etf_option_mapper is reset so the index lookup no longer returns a value. I'm not up enough with dolphindb to know why that might happen, but if it were me I would have defined the mapping as a table somewhere and join to the table to get these values anyway. — Joel Coehoorn
– Joel Coehoorn, Commented Aug 28 at 16:34
Is getProductClass(InstrumentID, etf_option_mapper) result numeric or string? ('IF','IM', 588000). Also try run query `` ... where InvestorID in ('7399000270', '00130239')`` with order by InvestorId desc and order by InvestorId asc. — ValNik
– ValNik, Commented Aug 28 at 17:04

amin_mirr · Accepted Answer · 2025-09-10 20:30:20Z

Short answer: it’s a type-mixing issue, not a bug in iif.

In your UDF the two branches of iif(...) return different types:

productClass → STRING (built from left(...).regexReplace(...))
etf_option_mapper[InstrumentID][0] → INT (588000)

When you query one row, the result column can be INT (so you see 588000). When you query many rows, most rows take the STRING branch ("IF", "IM"). DolphinDB has to fix the output column to a single type for the whole select; it resolves it to STRING. For the last row where the INT branch should be used, that INT can’t be coerced to STRING in this context, so DolphinDB puts NULL there.

Fix

Make both branches return the same type. Easiest is to cast the INT branch to STRING. This should work:

def getProductClass(InstrumentID, etf_option_mapper){
    productClass = left(InstrumentID,4)
        .regexReplace("[0-9]+","")
        .regexReplace("HO","IH")
        .regexReplace("IO","IF")
        .regexReplace("MO","IM")
    return iif(isDigit(InstrumentID),
               string(etf_option_mapper[InstrumentID][0]),  // cast to STRING
               productClass)
}

keep mapper values numeric if you like

Or, store the mapper as strings:

// make mapping string -> string
etf_option_mapper = dict("STRING", "STRING")
etf_option_mapper["10008862"] = "588000"

The core rule to remember: in select the result column must have one data type; if iif branches disagree, explicitly cast one side.

Collectives™ on Stack Overflow

Why UDF returns NULL for multi-row query but not for single row?

1 Answer 1

Fix

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Fix

Comments

Your Answer

Sign up or log in

Post as a guest

Related