Error with type coercion with `CREATE TABLE AS SELECT` ... inserting `VALUES`

### Describe the bug

This was reported by @findepi on https://github.com/apache/datafusion/pull/12864 in https://github.com/apache/datafusion/pull/12864#issuecomment-2437123342. I am pulling it into its own ticket so we can track it / find it more easily

> I think as long as there is no regression we should move on and file the ticket for the remaining issue.

Sorry for not following earlier, was on a full-day event yesterday.

There are regressions.
I kind of felt it's obvious from the way it works, sorry for not providing good examples earlier.

### Before the change

```
> CREATE OR REPLACE TABLE t(a int) AS SELECT length(a) FROM (VALUES ('+123')) t(a); SELECT * FROM t;
0 row(s) fetched.
Elapsed 0.005 seconds.

+---+
| a |
+---+
| 4 |
+---+
```

```
> CREATE OR REPLACE TABLE t(a int) AS SELECT length(a) FROM (VALUES ('abcd')) t(a); SELECT * FROM t;
0 row(s) fetched.
Elapsed 0.078 seconds.

+---+
| a |
+---+
| 4 |
+---+
```

### on current `main`

Wrong result:

```
> CREATE OR REPLACE TABLE t(a int) AS SELECT length(a) FROM (VALUES ('+123')) t(a); SELECT * FROM t;
0 row(s) fetched.
Elapsed 0.071 seconds.

+---+
| a |
+---+
| 3 |
```

failure
```
> CREATE OR REPLACE TABLE t(a int) AS SELECT length(a) FROM (VALUES ('abcd')) t(a); SELECT * FROM t;
Arrow error: Cast error: Cannot cast string 'abcd' to value of Int32 type
```

### To Reproduce

_No response_

### Expected behavior

_No response_

### Additional context

_No response_

Start by reproducing the two CREATE TABLE AS SELECT examples involving VALUES and length(a), then trace the SQL planning and type-coercion path for those expressions. Done means both examples produce the prior results, including length('+123') returning 4 and the 'abcd' case succeeding without regressions.

Written by the indexing model from the issue text.