ryanzryu opened a new issue, #24477: URL: https://github.com/apache/doris/issues/24477
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version 1.2.6 ### What's Wrong? i have data： MySQL [tpch_100_d]> select * from lineitem_uni; +------------+------------+--------------+-----------+-----------+------------+-----------------+------------+-------+--------------+--------------+--------------+---------------+----------------+------------+--------------------------+ | l_shipdate | l_orderkey | l_linenumber | l_partkey | l_suppkey | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_commitdate | l_receiptdate | l_shipinstruct | l_shipmode | l_comment | +------------+------------+--------------+-----------+-----------+------------+-----------------+------------+-------+--------------+--------------+--------------+---------------+----------------+------------+--------------------------+ | 1992-01-05 | 473564324 | 1 | 3570296 | 320306 | 16.00 | 21857.92 | 0.03 | 0.03 | A | F | 1992-03-18 | 1992-01-18 | COLLECT COD | RAIL | sly. packages wake furio | | 1992-01-06 | 473564324 | 1 | 3570296 | 320306 | 16.00 | 21857.92 | 0.03 | 0.03 | A | F | 1992-03-18 | 1992-01-18 | COLLECT COD | RAIL | sly. packages wake furio | | 1992-01-13 | 473564324 | 2 | 3570296 | 320306 | 19.00 | 21857.92 | 0.03 | 0.03 | A | F | 1992-03-18 | 1992-01-18 | COLLECT COD | RAIL | sly. packages wake furio | | 1992-01-05 | 473564323 | 1 | 3570296 | 320306 | 16.00 | 21857.92 | 0.03 | 0.03 | A | F | 1992-03-18 | 1992-01-18 | COLLECT COD | RAIL | sly. packages wake furio | +------------+------------+--------------+-----------+-----------+------------+-----------------+------------+-------+--------------+--------------+--------------+---------------+----------------+------------+--------------------------+ 4 rows in set (0.03 sec) i get two result when useing 2 sql only dense_rank() partition by condition different MySQL [tpch_100_d]> select sum(`quantity`) from ( -> select l_shipdate, -> l_orderkey, -> sum(l_quantity) as quantity, -> dense_rank() over (partition by l_shipdate,l_orderkey) as r -> from lineitem_uni group by l_shipdate,l_orderkey) tab1 -> where l_orderkey=473564324; +-----------------+ | sum(`quantity`) | +-----------------+ | 51.00 | +-----------------+ 1 row in set (0.01 sec) MySQL [tpch_100_d]> select sum(`quantity`) from ( -> select l_shipdate, -> l_orderkey, -> sum(l_quantity) as quantity, -> dense_rank() over (partition by l_shipdate) as r -> from lineitem_uni group by l_shipdate,l_orderkey) tab1 -> where l_orderkey=473564324; +-----------------+ | sum(`quantity`) | +-----------------+ | 67.00 | +-----------------+ 1 row in set (0.03 sec) ### What You Expected? get the result which where condition effect ### How to Reproduce? create table： CREATE TABLE `lineitem_uni` ( `l_shipdate` date NOT NULL, `l_orderkey` bigint(20) NOT NULL, `l_linenumber` int(11) NOT NULL, `l_partkey` int(11) NOT NULL, `l_suppkey` int(11) NOT NULL, `l_quantity` decimalv3(15, 2) NOT NULL, `l_extendedprice` decimalv3(15, 2) NOT NULL, `l_discount` decimalv3(15, 2) NOT NULL, `l_tax` decimalv3(15, 2) NOT NULL, `l_returnflag` varchar(1) NOT NULL, `l_linestatus` varchar(1) NOT NULL, `l_commitdate` date NOT NULL, `l_receiptdate` date NOT NULL, `l_shipinstruct` varchar(25) NOT NULL, `l_shipmode` varchar(10) NOT NULL, `l_comment` varchar(44) NOT NULL ) ENGINE=OLAP UNIQUE KEY(`l_shipdate`, `l_orderkey`) COMMENT 'OLAP' DISTRIBUTED BY HASH(`l_orderkey`) BUCKETS 96 PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "colocate_with" = "tpch_group", "in_memory" = "false", "storage_format" = "V2", "function_column.sequence_col" = "l_linenumber", "disable_auto_compaction" = "false" ); data: insert into lineitem_uni values('1992-01-05',473564324,1,3570296,320306,16,21857.92,0.03,0.03,'A','F','1992-03-18','1992-01-18','COLLECT COD','RAIL','sly. packages wake furio'); insert into lineitem_uni values('1992-01-06',473564324,1,3570296,320306,15,21857.92,0.03,0.03,'A','F','1992-03-18','1992-01-18','COLLECT COD','RAIL','sly. packages wake furio'); insert into lineitem_uni values('1992-01-13',473564324,2,3570296,320306,16,21857.92,0.03,0.03,'A','F','1992-03-18','1992-01-18','COLLECT COD','RAIL','sly. packages wake furio'); insert into lineitem_uni values('1992-01-05',473564323,1,3570296,320306,14,21857.92,0.03,0.03,'A','F','1992-03-18','1992-01-18','COLLECT COD','RAIL','sly. packages wake furio'); ### Anything Else? If have any other questions, you can find me in the developer WeChat group. ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
