Repository: ClickHouse/ClickHouse
File: tests/queries/0_stateless/03779_materialized_view_database_context.sh (Shell)

Code before:
#!/usr/bin/env bash
# Test that materialized views use the correct database context when calling
# parameterized views (issue #87082)

CURDIR=$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)
# shellcheck source=../shell_config.sh
. "$CURDIR"/../shell_config.sh

DB1="${CLICKHOUSE_DATABASE}_1"
DB2="${CLICKHOUSE_DATABASE}_2"

# Clean up
$CLICKHOUSE_CLIENT --query "DROP DATABASE IF EXISTS $DB1"
$CLICKHOUSE_CLIENT --query "DROP DATABASE IF EXISTS $DB2"

# Setup db1 with table, parameterized view, and REFRESH MV
$CLICKHOUSE_CLIENT --query "CREATE DATABASE $DB1"
$CLICKHOUSE_CLIENT --query "CREATE TABLE $DB1.tbl1 (id Int32, data String) ENGINE = MergeTree ORDER BY id"
$CLICKHOUSE_CLIENT --query "INSERT INTO $DB1.tbl1 VALUES (1, 'db1_data')"
$CLICKHOUSE_CLIENT --query "CREATE VIEW $DB1.v1 AS SELECT id, data, {p:String} as p FROM $DB1.tbl1"
$CLICKHOUSE_CLIENT --query "CREATE MATERIALIZED VIEW $DB1.mv1 REFRESH EVERY 1 SECOND ORDER BY id AS SELECT id, data, p FROM $DB1.v1(p='from_mv1_db1')"
$CLICKHOUSE_CLIENT --query "SYSTEM WAIT VIEW $DB1.mv1"
$CLICKHOUSE_CLIENT --query "SELECT 'mv1_db1:', * FROM $DB1.mv1 ORDER BY id"

# Setup second database with same structure but different data
$CLICKHOUSE_CLIENT --query "CREATE DATABASE $DB2"
$CLICKHOUSE_CLIENT --query "CREATE TABLE $DB2.tbl1 (id Int32, data String) ENGINE = MergeTree ORDER BY id"
$CLICKHOUSE_CLIENT --query "INSERT INTO $DB2.tbl1 VALUES (2, 'db2_data')"
$CLICKHOUSE_CLIENT --query "CREATE VIEW $DB2.v1 AS SELECT id, data, {p:String} as p FROM $DB2.tbl1"
$CLICKHOUSE_CLIENT --query "CREATE MATERIALIZED VIEW $DB2.mv1 REFRESH EVERY 1 SECOND ORDER BY id AS SELECT id, data, p FROM $DB2.v1(p='from_mv1_db2')"
$CLICKHOUSE_CLIENT --query "SYSTEM WAIT VIEW $DB2.mv1"
$CLICKHOUSE_CLIENT --query "SELECT 'mv1_db2:', * FROM $DB2.mv1 ORDER BY id"

# Test that qualified references still work
$CLICKHOUSE_CLIENT --query "CREATE MATERIALIZED VIEW $DB1.mv_qualified_crossdb REFRESH EVERY 1 SECOND ORDER BY id AS SELECT id, data, p FROM $DB2.v1(p='qualified_crossdb')"
$CLICKHOUSE_CLIENT --query "SYSTEM WAIT VIEW $DB1.mv_qualified_crossdb"
$CLICKHOUSE_CLIENT --query "SELECT 'qualified:', * FROM $DB1.mv_qualified_crossdb ORDER BY id"

# Create second set of tables and views for testing ALTER MODIFY QUERY
$CLICKHOUSE_CLIENT --query "CREATE TABLE $DB1.tbl2 (id Int32, data String) ENGINE = MergeTree ORDER BY id"
$CLICKHOUSE_CLIENT --query "INSERT INTO $DB1.tbl2 VALUES (3, 'db1_v2_data')"
$CLICKHOUSE_CLIENT --query "CREATE VIEW $DB1.v2 AS SELECT id, data, {p:String} as p FROM $DB1.tbl2"

$CLICKHOUSE_CLIENT --query "CREATE TABLE $DB2.tbl2 (id Int32, data String) ENGINE = MergeTree ORDER BY id"
$CLICKHOUSE_CLIENT --query "INSERT INTO $DB2.tbl2 VALUES (4, 'db2_v2_data')"
$CLICKHOUSE_CLIENT --query "CREATE VIEW $DB2.v2 AS SELECT id, data, {p:String} as p FROM $DB2.tbl2"

Review comment (refactor):
I'm sorry, but shouldn't you test here and on the other reference in Line 30 the unqualified reference?

```sql
CREATE MATERIALIZED VIEW $DB1.mv1 REFRESH EVERY 1 SECOND ORDER BY id AS SELECT id, data, p FROM v1(p='from_mv1_db1')
```

That doesn't seem to work. If I understand the issue properly, I'd expect this test to work, and right now it doesn't:

```bash
#!/usr/bin/env bash
# Test that materialized views use the MV's database context (not session context)
# when resolving UNQUALIFIED parameterized view references at creation time.
#
# This is a regression test for issue #87082.
# The key difference from 03779_materialized_view_database_context.sh is that
# this test uses UNQUALIFIED view references (v1 instead of $DB1.v1) to verify
# that the database resolution uses the MV's target database.

CURDIR=$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)
# shellcheck source=../shell_config.sh
. "$CURDIR"/../shell_config.sh

DB1="${CLICKHOUSE_DATABASE}_1"
DB2="${CLICKHOUSE_DATABASE}_2"

# Clean up
$CLICKHOUSE_CLIENT --query "DROP DATABASE IF EXISTS $DB1"
$CLICKHOUSE_CLIENT --query "DROP DATABASE IF EXISTS $DB2"

# Setup db1 with table and parameterized view
$CLICKHOUSE_CLIENT --query "CREATE DATABASE $DB1"
$CLICKHOUSE_CLIENT --query "CREATE TABLE $DB1.tbl1 (id Int32, data String) ENGINE = MergeTree ORDER BY id"
$CLICKHOUSE_CLIENT --query "INSERT INTO $DB1.tbl1 VALUES (1, 'db1_data')"
$CLICKHOUSE_CLIENT --query "CREATE VIEW $DB1.v1 AS SELECT id, data, {p:String} as p FROM $DB1.tbl1"

$CLICKHOUSE_CLIENT --query "CREATE DATABASE $DB2"
$CLICKHOUSE_CLIENT --query "USE DATABASE $DB2;"

# KEY TEST: Create MV in DB1 with UNQUALIFIED view reference "v1" (not "$DB1.v1")
# The unqualified v1 should resolve to DB1.v1 (MV's database), not the session's current database (DB2.v1)
$CLICKHOUSE_CLIENT --query "CREATE MATERIALIZED VIEW $DB1.mv1 REFRESH EVERY 1 SECOND ORDER BY id AS SELECT id, data, p FROM v1(p='from_mv1_db1')"
$CLICKHOUSE_CLIENT --query "SYSTEM WAIT VIEW $DB1.mv1"
$CLICKHOUSE_CLIENT --query "SELECT 'mv1_db1:', * FROM $DB1.mv1 ORDER BY id"

# Cleanup
$CLICKHOUSE_CLIENT --query "DROP DATABASE $DB1"
$CLICKHOUSE_CLIENT --query "DROP DATABASE $DB2"
```

Code after:
#!/usr/bin/env bash
# Test that materialized views use the correct database context when calling
# parameterized views (issue #87082)

CURDIR=$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)
# shellcheck source=../shell_config.sh
. "$CURDIR"/../shell_config.sh

DB1="${CLICKHOUSE_DATABASE}_1"
DB2="${CLICKHOUSE_DATABASE}_2"

# Cleanup any existing test databases
$CLICKHOUSE_CLIENT -q "
    DROP DATABASE IF EXISTS $DB1;
    DROP DATABASE IF EXISTS $DB2
"

# Test original issue #87082: UNQUALIFIED parameterized view references in REFRESH MVs
# When session is in the same database as the MV, CREATE works (session context correct)
# Before fix: RUNTIME execution of the refreshable materialized view failed with "Unknown table function v1"
# After fix: RUNTIME succeeds (context set to MV's database during refresh)

# Setup DB1 with unqualified references (session in DB1)
# Test that it works at runtime (failed before fix) and returns data from DB1
$CLICKHOUSE_CLIENT -m -q "
    CREATE DATABASE $DB1;
    USE $DB1;
    CREATE TABLE tbl1 (id Int32, data String) ENGINE = MergeTree ORDER BY id;
    INSERT INTO tbl1 VALUES (1, 'db1_data');
    CREATE VIEW v1 AS SELECT id, data, {p:String} as p FROM tbl1;
    CREATE MATERIALIZED VIEW mv1 REFRESH EVERY 1 SECOND ORDER BY id AS SELECT id, data, p FROM v1(p='from_mv1_db1');
    SYSTEM WAIT VIEW mv1;
    SELECT 'mv1_db1:', * FROM mv1 ORDER BY id;
"

# Setup second database with same structure (session in DB2)
# Test that it also works at runtime (failed before fix) and returns data from DB2
# This verifies that each MV uses its own database context correctly
$CLICKHOUSE_CLIENT -m -q "
    CREATE DATABASE $DB2;
    USE $DB2;
    CREATE TABLE tbl1 (id Int32, data String) ENGINE = MergeTree ORDER BY id;
    INSERT INTO tbl1 VALUES (2, 'db2_data');
    CREATE VIEW v1 AS SELECT id, data, {p:String} as p FROM tbl1;
    CREATE MATERIALIZED VIEW mv1 REFRESH EVERY 1 SECOND ORDER BY id AS SELECT id, data, p FROM v1(p='from_mv1_db2');
    SYSTEM WAIT VIEW mv1;