主页 / dba / 问题 / 257808
Accepted
Steven Hibble
Asked: 2020-01-21 11:51:40 +0800 CST

STRING_AGG 可以多次使用不同的分隔符吗？

  • 772

我正在做一些字符串聚合，并注意到对我来说看起来像一个错误。STRING_AGG在具有不同分隔符参数的同一查询中使用了两次。但是，两者都产生了相同的结果（在两种情况下都使用了“第一个”分隔符）。它似乎只在某些情况下发生。这是一个错误还是这是记录在案的行为？

首先，设置：

CREATE TABLE #Data
  ([Group] INT
 , Member  CHAR(1));

INSERT INTO #Data
VALUES (1, 'a'), (1, 'b')
     , (2, 'c'), (2, 'd');

从表与VALUES构造函数。没有WITHIN GROUP ORDER BY，一切都很好。有了它，bug就出现了

SELECT Commas = STRING_AGG(Member, ', ') --WITHIN GROUP(ORDER BY Member)
     , Colons = STRING_AGG(Member, '::') --WITHIN GROUP(ORDER BY Member)
FROM #Data;

SELECT Commas = STRING_AGG(Member, ', ') --WITHIN GROUP(ORDER BY Member)
     , Colons = STRING_AGG(Member, '::') --WITHIN GROUP(ORDER BY Member)
FROM (VALUES (1, 'a'), (1, 'b')
           , (2, 'c'), (2, 'd')) [v] ([Group], Member);

添加 aGROUP BY添加排序，现在两种情况都存在错误：

SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG(Member, '::')
FROM #Data
GROUP BY [Group];

SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG(Member, '::')
FROM (VALUES (1, 'a'), (1, 'b')
           , (2, 'c'), (2, 'd')) [v] ([Group], Member)
GROUP BY [Group];

加上它肯定坏了……就像内心深处一样。将两个表达式组合在 a 中HAVING，您将一无所获。单独使用其中一个，您就可以获得数据。

SELECT [Group]
     , Colons = STRING_AGG(Member, '::')
     , Commas = STRING_AGG(Member, ', ')
FROM #Data
GROUP BY [Group]
HAVING STRING_AGG(Member, ', ') LIKE '%, %'
   AND STRING_AGG(Member, '::') LIKE '%::%';

切换列的顺序会切换使用的分隔符。

我使用这些跟踪标志进行调查，但我找不到任何关于“优化”第二个聚合的信息。

OPTION (QUERYTRACEON 3604 -- Output info to client
      , QUERYTRACEON 8619 -- Show applied optimization rules
      , QUERYTRACEON 8606 -- Show logical query trees
      , QUERYTRACEON 8607 -- Show physical query tree
      , RECOMPILE);
sql-server sql-server-2017

1 个回答

  1. Best Answer

    这是一个错误。这个答案表明它已在 CU17 中修复，但我刚刚测试并发现它是否定的（并且没有迹象表明该修复程序打算在此处向后移植）。

    我试过了

    SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG(Member, '::')
FROM (VALUES (1, 'a'), (1, 'b')
           , (2, 'c'), (2, 'd')) [v] ([Group], Member)
GROUP BY [Group]
OPTION (QUERYTRACEON 3604 -- Output info to client
      , QUERYTRACEON 8607 -- Optimization output tree, before post-optimization rewrite phase.
      , QUERYTRACEON 7352 -- The final query tree after Post-optimization re-write.
      , RECOMPILE);
;

    2017年CU17。这给出了计划

    在此处输入图像描述

    在 2019 年，没有计算标量，流聚合已定义值

    [Expr1010] = Scalar Operator(STRING_AGG([Union1009],', ')), 
[Expr1011] = Scalar Operator(STRING_AGG([Union1009],'::'))

    2017 年，流聚合已定义价值

    [Expr1010] = Scalar Operator(STRING_AGG([Union1009],', '))

    还有一个带有表达式的附加计算标量

    [Expr1011] = Scalar Operator([Expr1010])

    跟踪标志的输出8607在 2017 年和 2019 年是相同的，但7352有所不同，所以我认为这是一个错误的优化后重写。

    作为一种解决方法，您可以更改表达式以执行如下无操作，以防止出现这种错误的优化。

    SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG('' + Member, '::')
    • 11

相关问题