[Bug] Using `limit` syntax for data test failures doesn't work for all databases

### Is this a new bug?

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

### Which packages are affected?

- [x] dbt-adapters
- [ ] dbt-tests-adapter
- [ ] dbt-athena
- [ ] dbt-athena-community
- [ ] dbt-bigquery
- [ ] dbt-postgres
- [ ] dbt-redshift
- [ ] dbt-snowflake
- [ ] dbt-spark

### Current Behavior

@colin-rogers-dbt made a great point here: https://github.com/dbt-labs/dbt-adapters/pull/376#discussion_r2069255527

I believe this would apply to this code as well:

https://github.com/dbt-labs/dbt-adapters/blob/6255adf01aa4e3004d0267047c02813ee10d6b5e/dbt-adapters/src/dbt/include/global_project/macros/materializations/tests/helpers.sql#L12

So I think we should update `default__get_test_sql` to use [`get_limit_subquery_sql`](https://github.com/dbt-labs/dbt-adapters/blob/6255adf01aa4e3004d0267047c02813ee10d6b5e/dbt-adapters/src/dbt/include/global_project/macros/adapters/show.sql#L21) (ideally renamed to `get_limit_sql` as proposed in https://github.com/dbt-labs/dbt-adapters/issues/1038).

### Expected Behavior

The default implementation should be as cross-database "safe" as possible.

### Steps To Reproduce

This might actually show itself as a bug due to `dbt-fabric`, etc. currently not hitting this code path.

But we could proactively refactor it anyways.

### Relevant log output

```shell

```

### Environment

```markdown
- OS:
- Python:
- dbt-adapters:
- :
```

### Additional Context

_No response_