[I] SUBSTRING_INDEX函数 分隔符出现的次数不支持动态变化 [doris-website]

["SearchWinter (via GitHub)" <gi...@apache.org>]

SearchWinter opened a new issue, #3023:
URL: https://github.com/apache/doris-website/issues/3023

   sql:
    SELECT n,SUBSTRING_INDEX(bindedition, ',', n)
    FROM (
    SELECT "_35_,_21_,_30_,_33_" bindedition
    ) t1
    INNER JOIN 
    (
    SELECT 1 AS n 
    UNION ALL 
    SELECT 2 AS n  
    UNION ALL 
    SELECT 3 AS n 
    )t2 ON 1=1
   
   mysql:
   "n"	"SUBSTRING_INDEX(bindedition, ',', n)"
   "1"	"_35_"
   "2"	"_35_,_21_"
   "3"	"_35_,_21_,_30_"
   
   Version : doris-2.1.5-rc02
   doris:
   n|substring_index(`bindedition`, ',', CAST(`n` AS INT))|
   -+-----------------------------------------------------+
   1|_35_                                                 |
   2|_35_                                                 |
   3|_35_                                                 |
   
   
   SUBSTRING_INDEX(<content>, <delimiter>, <field>)
   <content> | 需要截取的字符串。类型：VARCHAR
   <delimiter> | 分隔符，大小写敏感且多字节安全。类型：VARCHAR
   <field> | 分隔符出现的次数。正数从左计数，负数从右计数。类型：INT
   doris里面关联出来的结果始终是，   SELECT SUBSTRING_INDEX("_35_,_21_,_30_,_33_", ',', 1)  n=1时的结果，与mysql查询结果有差异
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org

Re: [I] SUBSTRING_INDEX函数 分隔符出现的次数不支持动态变化 [doris-website]

["boluor (via GitHub)" <gi...@apache.org>]

boluor commented on issue #3023:
URL: https://github.com/apache/doris-website/issues/3023#issuecomment-4525175803

   Thanks for the careful report. The behavior described against 2.1.5-rc02 looks like an engine-side constant-folding bug at that time, not a doc bug. I re-ran your exact query (and several variants) against a current Doris 4.x cluster — `SUBSTRING_INDEX` now honors a dynamic `n` column correctly and matches MySQL.
   
   Reproduction (Doris 4.x):
   
   ```sql
   SELECT n, SUBSTRING_INDEX(bindedition, ',', n) AS res
   FROM (SELECT "_35_,_21_,_30_,_33_" AS bindedition) t1
   INNER JOIN (SELECT 1 AS n UNION ALL SELECT 2 AS n UNION ALL SELECT 3 AS n) t2 ON 1=1
   ORDER BY n;
   ```
   
   Result:
   
   ```
   +---+-----------------+
   | n | res             |
   +---+-----------------+
   | 1 | _35_            |
   | 2 | _35_,_21_       |
   | 3 | _35_,_21_,_30_  |
   +---+-----------------+
   ```
   
   Also confirmed working with `n` from a persistent table, with `CAST(n AS INT)`, and with negative `n` (right-side counting): `n=-1 → _33_`, `n=-2 → _30_,_33_`, `n=-3 → _21_,_30_,_33_`.
   
   `SUBSTRING_INDEX`'s `<field>` argument is documented to accept INT and does broadcast over column values in current versions, so the docs need no change. The fix should also be in recent 2.1.x patch releases — please try the latest 2.1 build if you need to stay on that branch.
   
   Closing as engine-fixed. Please reopen if you can still reproduce on a recent 2.1.x or 3.x release.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org