Incorrect caclutation for join case

[yanshuyuan]

First, I create the test data

CREATE TABLE ltp.sales ("user_id int,  product   varchar(100),  price     double);  
CREATE TABLE ltp.profile (user_id int, gender varchar(2), age int);

Then I mock some data for them

List<String> productList = Arrays.asList("milk", "egg", "juice");
        for (int i = 0; i < 300000; i++) {
            int randInt = ThreadLocalRandom.current().nextInt(0, 3);
            String product = productList.get(randInt);
            double price = (randInt + 2) * 10
                    + ThreadLocalRandom.current().nextInt(0, 10);
            stmt.execute(String
                    .format("INSERT INTO ltp.sales (user_id, product, price) VALUES(%d, '%s', %.0f)",
                            i, product, price));
        }

List<String> genderList = Arrays.asList("F", "M");
        for (int i = 0; i < 300000; i++) {
            int randInt = ThreadLocalRandom.current().nextInt(0,
                    genderList.size());
            String gender = genderList.get(randInt);
            int age = ((randInt + 2) * 10
                    + ThreadLocalRandom.current().nextInt(0, 10)) % 5;
            stmt.execute(String
                    .format("INSERT INTO ltp.profile (user_id, gender, age) VALUES(%d, '%s', %d)",
                            i, gender, age));
        }

Now, wo create the scramble:

CREATE SCRAMBLE ltp.profile_uniform_scrambled from ltp.profile METHOD UNIFORM ON user_id RATIO 0.2 BLOCKSIZE 1000
CREATE SCRAMBLE ltp.profile_hash_scrambled from ltp.profile METHOD HASH ON user_id RATIO 0.2 BLOCKSIZE 1000;
CREATE SCRAMBLE ltp.sales_uniform_scrambled from ltp.sales METHOD UNIFORM ON user_id RATIO 0.2 BLOCKSIZE 1000;
CREATE SCRAMBLE ltp.sales_hash_scrambled from ltp.sales METHOD HASH ON user_id RATIO 0.2 BLOCKSIZE 1000;

I start to estimate:

SELECT 
        product, 
        SUM(price) as total_price
    FROM (
        SELECT
            product,
            user_id,
            SUM(price) as price
        FROM 
            ltp.sales
        GROUP BY
            product, 
            user_id
        HAVING
            SUM(price) > 10
    ) t1 INNER JOIN (
        SELECT 
            user_id
        FROM 
            ltp.profile
        WHERE 
            age IN (1, 2, 3)
            AND 
            gender = 'F'
    ) t2
    ON t1.user_id = t2.user_id
    GROUP BY product

the result gap is so large, that's why?

[pyongjoo]

Sorry for the late reply. To support complex queries in a more principled way, we are working on a new system.