[GitHub] [doris] jacktengg opened a new issue, #24046: Doirs INTERSECT 结果错误

Thu, 07 Sep 2023 02:21:03 -0700 


jacktengg opened a new issue, #24046:
URL: https://github.com/apache/doris/issues/24046

   ### Discussed in https://github.com/apache/doris/discussions/23918
   
   <div type='discussions-op-text'>
   
   <sup>Originally posted by **qiaoshiling** September  5, 2023</sup>
   Doris版本 1.2.5
   我现在有一张表a，想取到他在三种不同条件下的交集 使用到了INTERSECT关键字
   测试语句
   
   ```plpgsql
   
    (
   SELECT
        a.sku_code
   FROM
        (
           SELECT  *  FROM      a
       ) a
   WHERE
          a.is_lst > 0 AND a.site_code = 'UK'
    )
   INTERSECT
    (
   SELECT
        b.sku_code
   FROM
       (
          SELECT * FROM a 
       ) b
   WHERE
         b.is_lst >= 0 AND b.site_code = 'DE'
    )
   INTERSECT
    (
   SELECT
        c.sku_code
   FROM
       (
        SELECT  * FROM  a 
       ) c
    WHERE
        c.is_lst >= 0   AND c.site_code = 'ES'
    )
   ``` 
   
   每次查询结果数据量从 198~200 不等
   后经过sql 修改
   ```plpgsql
   select
        mqxwmfam.*
   from
        ((
        SELECT
                a.sku_code
        FROM
            (
                select * from   a
            ) a
   where
      a.is_lst > 0 AND a.site_code = 'UK')
   INTERSECT
                (
        SELECT
                b.sku_code
        FROM
                (
                select * from   a
                ) b
        WHERE
                b.is_lst >= 0 AND b.site_code = 'DE' ) ) mqxwmfam
   INTERSECT
        (
       SELECT
                c.sku_code
       FROM
                (
        select  * from  a
            ) c
   WHERE
    c.is_lst >= 0       AND c.site_code = 'ES' )
   ```
   
   可以看到我将第一个 INTERSECT 变为了一个子查询并取了别名再进行交集运算，数据稳定在了201条。
   是否可以告知我Doris INTERSECT关键字的实现逻辑以及为什么会出现这样的问题
   如果更多的子集求交集应该怎么写 一层一层的做嵌套么？</div>


-- 
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: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to