Danny Chen created CALCITE-4206: ----------------------------------- Summary: RelDecorrelator outputs wong plan for correlate sort with fetch limit Key: CALCITE-4206 URL: https://issues.apache.org/jira/browse/CALCITE-4206 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.25.0 Reporter: Danny Chen Assignee: Danny Chen Fix For: 1.26.0
For query {code:sql} -- create cities table CREATE TABLE cities ( name STRING NOT NULL, state STRING NOT NULL, pop INT NOT NULL ) WITH ( 'connector' = 'kafka', 'topic' = 'cities', 'properties.bootstrap.servers' = 'kafka:9092', 'properties.group.id' = 'mygroup', 'scan.startup.mode' = 'earliest-offset', 'format' = 'json' ); -- fill cities table INSERT INTO cities VALUES ('Los_Angeles', 'CA', 3979576), ('Phoenix', 'AZ', 1680992), ('Houston', 'TX', 2320268), ('San_Diego', 'CA', 1423851), ('San_Francisco', 'CA', 881549), ('New_York', 'NY', 8336817), ('Dallas', 'TX', 1343573), ('San_Antonio', 'TX', 1547253), ('San_Jose', 'CA', 1021795), ('Chicago', 'IL', 2695598), ('Austin', 'TX', 978908); -- execute query SELECT state, name FROM (SELECT DISTINCT state FROM cities) states, LATERAL ( SELECT name, pop FROM cities WHERE state = states.state ORDER BY pop DESC LIMIT 3 ); -- result state name CA Los_Angeles NY New_York IL Chicago -- expected result state | name ------+------------- TX | Dallas AZ | Phoenix IL | Chicago TX | Houston CA | San_Jose NY | New_York CA | San_Diego CA | Los_Angeles TX | San_Antonio {code} The current plan after decorrelation is {code:xml} // {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)