[Bug] Test is running on deferred to / production node during `retry` if `--favor-state` flag is used in previous command

### Is this a new bug in dbt-core?

- [x] I believe this is a new bug in dbt-core
- [x] I have searched the existing issues, and I could not find an existing issue for this bug

### Current Behavior

I _think_ this is an edge case that we may not have covered with `retry` + `--favor-state`. When an invocation with `--favor-state` fails, and therefore, user invokes `retry` after fixing the issue (e.g. permissions), test are being run on the "deferred to" / "production" node (`db.prod.foo`) - instead of the node that is actually built with `retry` (i.e. `db.ci.foo`).

### Expected Behavior

`retry` should behave like how the failed command should have behaved - meaning, if `dbt build -s state:modified --defer --state target_old --favor-state` would have been:
* Building model `db.ci.foo`. ✅
* Running tests on `db.ci.foo`. ✅

Then `retry` (of the command `dbt build -s state:modified --defer --state target_old --favor-state`) should also do that. Instead `retry` is:
* Building model `db.ci.foo`. ✅
* Running tests on `db.prod.foo`. ❌

### Steps To Reproduce

## Project setup

```yaml
# ~/.dbt/profiles.yml
sf:
target: ci
outputs:
ci:
type: snowflake
role: transformer
database: db
schema: ci
...
prod:
type: snowflake
role: transformer
database: db
schema: prod
...

# dbt_project.yml
name: analytics
profile: sf
version: "1.0.0"

models:
analytics:
+materialized: table

# models/schema.yml
models:
- name: foo
columns:
- name: c
tests:
- not_null
```

```sql
-- models/foo.sql
select 1 as c
```

### Scenario 1 - no need to use the retry function.

To contrast with the actual issue - first we will do a build where there is no need to use the retry function.

```sql
-- Run on snowflake to remove any existing objects.
drop table if exists db.prod.foo;
drop table if exists db.ci.foo;
```

With the project example above, do a prod build and save the manifest to defer to.

```sh
$ rm -rf target target_old && dbt --debug build -t prod && mv target target_old
...
01:39:32 1 of 2 START sql table model prod.foo .......................................... [RUN]
01:39:32 Re-using an available connection from the pool (formerly list_db_prod, now model.analytics.foo)
01:39:32 Began compiling node model.analytics.foo
01:39:32 Writing injected SQL for node "model.analytics.foo"
01:39:32 Began executing node model.analytics.foo
01:39:32 Writing runtime sql for node "model.analytics.foo"
01:39:32 Using snowflake connection "model.analytics.foo"
01:39:32 On model.analytics.foo: /* {"app": "dbt", "dbt_version": "1.9.10", "profile_name": "sf", "target_name": "prod", "node_id": "model.analytics.foo"} */
create or replace transient table db.prod.foo
as (select 1 as c
)
;
01:39:34 SQL status: SUCCESS 1 in 1.859 seconds
01:39:34 Sending event: {'category': 'dbt', 'action': 'run_model', 'label': 'd7ed2d14-312c-42c0-8597-6594945862d7', 'context': []}
01:39:34 1 of 2 OK created sql table model prod.foo ..................................... [SUCCESS 1 in 1.93s]
...
01:39:34 2 of 2 START test not_null_foo_c ............................................... [RUN]
01:39:34 Re-using an available connection from the pool (formerly model.analytics.foo, now test.analytics.not_null_foo_c.07527d7849)
01:39:34 Began compiling node test.analytics.not_null_foo_c.07527d7849
01:39:34 Writing injected SQL for node "test.analytics.not_null_foo_c.07527d7849"
01:39:34 Began executing node test.analytics.not_null_foo_c.07527d7849
01:39:34 Writing runtime sql for node "test.analytics.not_null_foo_c.07527d7849"
01:39:34 Using snowflake connection "test.analytics.not_null_foo_c.07527d7849"
01:39:34 On test.analytics.not_null_foo_c.07527d7849: /* {"app": "dbt", "dbt_version": "1.9.10", "profile_name": "sf", "target_name": "prod", "node_id": "test.analytics.not_null_foo_c.07527d7849"} */
select
count(*) as failures,
count(*) != 0 as should_warn,
count(*) != 0 as should_error
from (
select c
from db.prod.foo
where c is null
) dbt_internal_test
01:39:34 SQL status: SUCCESS 1 in 0.594 seconds
01:39:34 2 of 2 PASS not_null_foo_c ..................................................... [PASS in 0.63s]
```

Prod run builds `db.prod.foo` and ran the not null tests on it - nothing surprising.

Let's modify model `foo.sql`:

```sql
-- models/foo.sql
select null as c
```

And do a CI / deferral build:

```sh
$ dbt --debug build -t ci -s state:modified --defer --state target_old --favor-state
...
01:43:29 1 of 2 START sql table model ci.foo ............................................ [RUN]
01:43:29 Re-using an available connection from the pool (formerly list_db_ci, now model.analytics.foo)
01:43:29 Began compiling node model.analytics.foo
01:43:29 Writing injected SQL for node "model.analytics.foo"
01:43:29 Began executing node model.analytics.foo
01:43:29 Writing runtime sql for node "model.analytics.foo"
01:43:29 Using snowflake connection "model.analytics.foo"
01:43:29 On model.analytics.foo: /* {"app": "dbt", "dbt_version": "1.9.10", "profile_name": "sf", "target_name": "ci", "node_id": "model.analytics.foo"} */
create or replace transient table db.ci.foo
as (select null as c
)
;
01:43:30 SQL status: SUCCESS 1 in 1.172 seconds
01:43:30 Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '2cf8f6aa-356d-4eec-a958-58eb66fe58b9', 'context': []}
01:43:30 1 of 2 OK created sql table model ci.foo ....................................... [SUCCESS 1 in 1.24s]
...
01:43:30 2 of 2 START test not_null_foo_c ............................................... [RUN]
01:43:30 Re-using an available connection from the pool (formerly model.analytics.foo, now test.analytics.not_null_foo_c.07527d7849)
01:43:30 Began compiling node test.analytics.not_null_foo_c.07527d7849
01:43:30 Writing injected SQL for node "test.analytics.not_null_foo_c.07527d7849"
01:43:30 Began executing node test.analytics.not_null_foo_c.07527d7849
01:43:30 Writing runtime sql for node "test.analytics.not_null_foo_c.07527d7849"
01:43:30 Using snowflake connection "test.analytics.not_null_foo_c.07527d7849"
01:43:30 On test.analytics.not_null_foo_c.07527d7849: /* {"app": "dbt", "dbt_version": "1.9.10", "profile_name": "sf", "target_name": "ci", "node_id": "test.analytics.not_null_foo_c.07527d7849"} */
select
count(*) as failures,
count(*) != 0 as should_warn,
count(*) != 0 as should_error
from (
select c
from db.ci.foo
where c is null
) dbt_internal_test
01:43:31 SQL status: SUCCESS 1 in 0.544 seconds
01:43:31 2 of 2 FAIL 1 not_null_foo_c ................................................... [FAIL 1 in 0.57s]
...
```

Here, the CI run correctly builds `db.ci.foo` as well as correctly ran the not null test on `db.ci.foo`.

### Scenario 2 - include retry in the picture.

This is basically identical to our setup before - but this time we're going to have the `ci` target use an incorrect role - "accidentally". Retry is basically used for these scenarios, the role accidentally lost it's permissions, we accidentally had an incorrect role, etc - mostly "accidents" or "oopsies" - things that we know simply rerunning will resolve the issue after fixing the oopsie (e.g. go into snowflake and regrant permissions, fix the typo on the role).

```yaml
# ~/.dbt/profiles.yml
sf:
target: ci
outputs:
ci:
type: snowflake
role: my_testing_role # This role has no access to schema 'ci'. Accidentally used.
database: db
schema: ci
...
prod:
type: snowflake
role: transformer
database: db
schema: prod
...

# dbt_project.yml
name: analytics
profile: sf
version: "1.0.0"

models:
analytics:
+materialized: table

# models/schema.yml
models:
- name: foo
columns:
- name: c
tests:
- not_null
```

```sql
-- models/foo.sql
select 1 as c
```

First, be sure to remove any existing tables to keep parity with the scenario above.

```sql
-- Run on snowflake to remove any existing objects.
drop table if exists db.prod.foo;
drop table if exists db.ci.foo;
```

Now do a prod build:

```sh
$ rm -rf target target_old && dbt --debug build -t prod && mv target target_old
...
01:49:43 On model.analytics.foo: /* {"app": "dbt", "dbt_version": "1.9.10", "profile_name": "sf", "target_name": "prod", "node_id": "model.analytics.foo"} */
create or replace transient table db.prod.foo
as (select 1 as c
)
;
...
01:49:44 On test.analytics.not_null_foo_c.07527d7849: /* {"app": "dbt", "dbt_version": "1.9.10", "profile_name": "sf", "target_name": "prod", "node_id": "test.analytics.not_null_foo_c.07527d7849"} */
select
count(*) as failures,
count(*) != 0 as should_warn,
count(*) != 0 as should_error
from (
select c
from db.prod.foo
where c is null
) dbt_internal_test
01:49:44 SQL status: SUCCESS 1 in 0.598 seconds
01:49:44 2 of 2 PASS not_null_foo_c ..................................................... [PASS in 0.63s]
```

Like above, we're going to do a CI run after modifying foo:

```sql
-- models/foo.sql
select null as c
```

```sh
$ dbt --debug build -t ci -s state:modified --defer --state target_old --favor-state
...
01:54:17 1 of 2 START sql table model ci.foo ............................................ [RUN]
01:54:17 Re-using an available connection from the pool (formerly list_db_ci, now model.analytics.foo)
01:54:17 Began compiling node model.analytics.foo
01:54:17 Writing injected SQL for node "model.analytics.foo"
01:54:17 Began executing node model.analytics.foo
01:54:17 Writing runtime sql for node "model.analytics.foo"
01:54:17 Using snowflake connection "model.analytics.foo"
01:54:17 On model.analytics.foo: /* {"app": "dbt", "dbt_version": "1.9.10", "profile_name": "sf", "target_name": "ci", "node_id": "model.analytics.foo"} */
create or replace transient table db.ci.foo
as (select null as c
)
;
01:54:17 Snowflake adapter: Snowflake query id: 01c02252-0709-f072-000d-37835734c42e
01:54:17 Snowflake adapter: Snowflake error: 003001 (42501): SQL access control error:
Insufficient privileges to operate on schema 'CI'.
01:54:17 Database Error in model foo (models/foo.sql)
003001 (42501): SQL access control error:
Insufficient privileges to operate on schema 'CI'.
compiled code at target/run/analytics/models/foo.sql
01:54:17 Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '429636d2-3558-45a6-bfd2-ad01919c2a9d', 'context': []}
01:54:17 1 of 2 ERROR creating sql table model ci.foo ................................... [ERROR in 0.34s]
01:54:17 Finished running node model.analytics.foo
01:54:17 Marking all children of 'model.analytics.foo' to be skipped because of status 'error'. Reason: Database Error in model foo (models/foo.sql)
003001 (42501): SQL access control error:
Insufficient privileges to operate on schema 'CI'.
compiled code at target/run/analytics/models/foo.sql.
01:54:17 Began running node test.analytics.not_null_foo_c.07527d7849
01:54:17 2 of 2 SKIP test not_null_foo_c ................................................ [SKIP]
...
```

^ Recall that we said, this time we're accidentally using an incorrect role `my_testing_role` which does not have access to the `ci` schema.

Let's go ahead and correct the oopsie:

```yaml
# ~/.dbt/profiles.yml
sf:
target: ci
outputs:
ci:
type: snowflake
role: transformer # Correct this to use the intended role.
database: db
schema: ci
...
prod:
type: snowflake
role: transformer
database: db
schema: prod
...
```

And after oopsie corrections, a user would rightly run `retry`:

```sh
$ dbt --debug retry
...
02:00:26 1 of 2 START sql table model ci.foo ............................................ [RUN]
02:00:26 Re-using an available connection from the pool (formerly list_db_ci, now model.analytics.foo)
02:00:26 Began compiling node model.analytics.foo
02:00:26 Writing injected SQL for node "model.analytics.foo"
02:00:26 Began executing node model.analytics.foo
02:00:27 Writing runtime sql for node "model.analytics.foo"
02:00:27 Using snowflake connection "model.analytics.foo"
02:00:27 On model.analytics.foo: /* {"app": "dbt", "dbt_version": "1.9.10", "profile_name": "sf", "target_name": "ci", "node_id": "model.analytics.foo"} */
create or replace transient table db.ci.foo
as (select null as c
)
;
02:00:29 SQL status: SUCCESS 1 in 2.112 seconds
02:00:29 Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '66a309f9-32c6-4a8a-92ec-f6276f5353a2', 'context': []}
02:00:29 1 of 2 OK created sql table model ci.foo ....................................... [SUCCESS 1 in 2.17s]
...
02:00:29 2 of 2 START test not_null_foo_c ............................................... [RUN]
02:00:29 Re-using an available connection from the pool (formerly model.analytics.foo, now test.analytics.not_null_foo_c.07527d7849)
02:00:29 Began compiling node test.analytics.not_null_foo_c.07527d7849
02:00:29 Writing injected SQL for node "test.analytics.not_null_foo_c.07527d7849"
02:00:29 Began executing node test.analytics.not_null_foo_c.07527d7849
02:00:29 Writing runtime sql for node "test.analytics.not_null_foo_c.07527d7849"
02:00:29 Using snowflake connection "test.analytics.not_null_foo_c.07527d7849"
02:00:29 On test.analytics.not_null_foo_c.07527d7849: /* {"app": "dbt", "dbt_version": "1.9.10", "profile_name": "sf", "target_name": "ci", "node_id": "test.analytics.not_null_foo_c.07527d7849"} */
select
count(*) as failures,
count(*) != 0 as should_warn,
count(*) != 0 as should_error
from (
select c
from db.prod.foo
where c is null
) dbt_internal_test
02:00:29 SQL status: SUCCESS 1 in 0.240 seconds
02:00:29 2 of 2 PASS not_null_foo_c ..................................................... [PASS in 0.28s]
```

What we observe here is that the `retry` of the previous failed command `dbt --debug build -t ci -s state:modified --defer --state target_old --favor-state`:
* Correctly built`foo` as `db.ci.foo`. ✅
* Incorrectly tested `db.prod.foo`. ❌

^ This incorrect test returned an incorrect test outcome - it should have failed as there exist nulls in `db.ci.foo`.

I believe, one would expect that the test would also run on `db.ci.foo`.

### Relevant log output

```shell

```

### Environment

```markdown
- OS: macOS
- Python: 3.11.9
- dbt: 1.9.10 (also tested with 1.10 and 1.11 - we still see this behaviour)
```

### Which database adapter are you using with dbt?

snowflake

### Additional Context

`--favor-state` does not do much in this single model toy example but one can imagine a more complex dbt project (more models, etc) where this would (just to call out that simply excluding `--favor-state` would not make an ideal solution).