[Bug] Cannot update views in numerically named database

[frqthijs]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

When re-running dbt in order to update existing views in an Azure SQL database, the following error is thrown.

Encountered an error:
Field "path" of type dbt.contracts.relation.Path in dbt.adapters.base.relation.BaseRelation has invalid value {'database': Decimal('1234567890'), 'schema': 'base_schema', 'identifier': 'DD_custom'}

This only happens when the related view exists in the database, if the view is dropped, dbt runs without errors.

Relevant part of profiles.yml

`

prod:

   type: sqlserver

   driver: 'ODBC Driver 17 for SQL Server'

   threads: 1

   server:  xxx.database.windows.net                    //azure sql server

   port: 1433
   database: '1234567890'                                   

`

Expected Behavior

The existing views in the database are updated

Steps To Reproduce

1. create a database with a name consisting only of numbers
2. create relevant table(s) in database
3. Run dbt (no error)
4. Run dbt again (error)
5. Drop view(s) from database
6. Run dbt (no error)
7. Run dbt again (error)
   dbt_FAILURE.log
   dbt_SUCCESS.log

Relevant log output

2021-11-05 13:55:38.039362 (ThreadPoolExecutor-1_0): Opening a new connection, currently in state closed
2021-11-05 13:55:38.039362 (ThreadPoolExecutor-1_0): Using connection string: DRIVER={ODBC Driver 17 for SQL Server};SERVER=azuresql.database.windows.net,1433;Database=1234567890;UID={sqluser};PWD=***;Application Name=dbt-sqlserver/0.20.1
2021-11-05 13:55:38.039362 (ThreadPoolExecutor-1_0): Connected to db: 1234567890
2021-11-05 13:55:38.065386 (ThreadPoolExecutor-1_0): SQL status: OK in 0.03 seconds
2021-11-05 13:55:38.066410 (ThreadPoolExecutor-1_0): On list_1234567890_base_schema: ROLLBACK
2021-11-05 13:55:38.066410 (ThreadPoolExecutor-1_0): On list_1234567890_base_schema: Close
2021-11-05 13:55:38.067411 (MainThread): Connection 'master' was properly closed.
2021-11-05 13:55:38.067411 (MainThread): Connection 'list_1234567890' was properly closed.
2021-11-05 13:55:38.068432 (MainThread): Connection 'list_1234567890_base_schema' was properly closed.
2021-11-05 13:55:38.068432 (MainThread): Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x00000197226F3520>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x0000019724097EB0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x000001972406C580>]}
2021-11-05 13:55:38.068432 (MainThread): Flushing usage events
2021-11-05 13:55:38.523030 (MainThread): Encountered an error:
2021-11-05 13:55:38.524991 (MainThread): Field "path" of type dbt.contracts.relation.Path in dbt.adapters.base.relation.BaseRelation has invalid value {'database': Decimal('1234567890'), 'schema': 'base_schema', 'identifier': 'DD_Custom'}
2021-11-05 13:55:38.536038 (MainThread): Traceback (most recent call last):
  File "<string>", line 13, in from_dict
  File "<string>", line 35, in from_dict
  File "<string>", line 6, in __init__
  File "c:\_dev\_git\dbt-carwise-1\env\lib\site-packages\dbt\contracts\relation.py", line 95, in __post_init__
    raise CompilationException(
dbt.exceptions.CompilationException: Compilation Error
  Got an invalid path database: 1234567890

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "c:\_dev\_git\dbt-carwise-1\env\lib\site-packages\dbt\main.py", line 125, in main
    results, succeeded = handle_and_check(args)
  File "c:\_dev\_git\dbt-carwise-1\env\lib\site-packages\dbt\main.py", line 203, in handle_and_check
    task, res = run_from_args(parsed)
  File "c:\_dev\_git\dbt-carwise-1\env\lib\site-packages\dbt\main.py", line 256, in run_from_args
    results = task.run()
  File "c:\_dev\_git\dbt-carwise-1\env\lib\site-packages\dbt\task\runnable.py", line 425, in run
    result = self.execute_with_hooks(selected_uids)
  File "c:\_dev\_git\dbt-carwise-1\env\lib\site-packages\dbt\task\runnable.py", line 383, in execute_with_hooks
    self.before_run(adapter, selected_uids)
  File "c:\_dev\_git\dbt-carwise-1\env\lib\site-packages\dbt\task\run.py", line 411, in before_run
    self.populate_adapter_cache(adapter)
  File "c:\_dev\_git\dbt-carwise-1\env\lib\site-packages\dbt\task\runnable.py", line 363, in populate_adapter_cache
    adapter.set_relations_cache(self.manifest)
  File "c:\_dev\_git\dbt-carwise-1\env\lib\site-packages\dbt\adapters\base\impl.py", line 384, in set_relations_cache
    self._relations_cache_for_schemas(manifest)
  File "c:\_dev\_git\dbt-carwise-1\env\lib\site-packages\dbt\adapters\base\impl.py", line 361, in _relations_cache_for_schemas
    for relation in future.result():
  File "C:\Users\XXXX\AppData\Local\Programs\Python\Python38\lib\concurrent\futures\_base.py", line 437, in result
    return self.__get_result()
  File "C:\Users\XXXX\AppData\Local\Programs\Python\Python38\lib\concurrent\futures\_base.py", line 389, in __get_result
    raise self._exception
  File "C:\Users\XXXX\AppData\Local\Programs\Python\Python38\lib\concurrent\futures\thread.py", line 57, in run
    result = self.fn(*self.args, **self.kwargs)
  File "c:\_dev\_git\dbt-carwise-1\env\lib\site-packages\dbt\utils.py", line 474, in connected
    return func(*args, **kwargs)
  File "c:\_dev\_git\dbt-carwise-1\env\lib\site-packages\dbt\adapters\sql\impl.py", line 217, in list_relations_without_caching
    relations.append(self.Relation.create(
  File "c:\_dev\_git\dbt-carwise-1\env\lib\site-packages\dbt\adapters\base\relation.py", line 280, in create
    return cls.from_dict(kwargs)
  File "<string>", line 15, in from_dict
mashumaro.exceptions.InvalidFieldValue: Field "path" of type dbt.contracts.relation.Path in dbt.adapters.base.relation.BaseRelation has invalid value {'database': Decimal('1234567890'), 'schema': 'base_schema', 'identifier': 'DD_Custom'}

Environment

- OS: Windows 10 21H1 Build: 19043.1288
- Python: 3.8.10
- dbt: 0.21.0 and 0.20.0
- dbt-sqlserver: 0.20.1

What database are you using dbt with?

other (mention it in "Additional Context")

Additional Context

Maybe related to #2208 ?

The database name in profiles.yml has to be quoted, otherwise the error

Profile loading failed for the following reason:
Runtime Error
Credentials in profile "carwise", target "prod" invalid: 1234567890 is not of type 'string'

is thrown. single or double quotes makes no difference.

[jtcohen6]

@frqthijs Thanks for the detailed bug report, and for including the full stacktrace!

This only happens when the related view exists in the database, if the view is dropped, dbt runs without errors.

Indeed, it looks like the error is occurring in the list_relations_without_caching method, specifically here:

dbt-core/core/dbt/adapters/sql/impl.py

Lines 217 to 223 in dd84f9a

	relations.append(self.Relation.create(
	database=_database,
	schema=_schema,
	identifier=name,
	quote_policy=quote_policy,
	type=_type
	))

You're right to see the relation to #2208, which fixed this for cataloging, but not for caching. The real fix for this came in v0.21, by turning off all agate type coercion for the results of macros (#3499). Now, when dbt hears back 1234 from the database, it takes it to be '1234', rather than Decimal('1234').

So, if I run with v0.20.2:

$ dbt run
Running with dbt=0.20.2
Found 1 model, 3 tests, 0 snapshots, 0 analyses, 376 macros, 0 operations, 0 seed files, 0 sources, 0 exposures

Encountered an error:
Field "path" of type dbt.contracts.relation.Path in dbt.adapters.postgres.relation.PostgresRelation has invalid value {'database': Decimal('1234'), 'schema': 'dbt_jcohen', 'identifier': 'model_a'}

Versus with v0.21.0:

$ dbt run
Running with dbt=0.21.0
Found 1 model, 3 tests, 0 snapshots, 0 analyses, 391 macros, 0 operations, 0 seed files, 0 sources, 0 exposures

11:23:38 | Concurrency: 5 threads (target='dev')
11:23:38 |
11:23:38 | 1 of 1 START view model dbt_jcohen.model_a........................... [RUN]
11:23:38 | 1 of 1 OK created view model dbt_jcohen.model_a...................... [CREATE VIEW in 0.09s]
11:23:38 |
11:23:38 | Finished running 1 view model in 0.52s.

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

You may need to wait for a compatible release of dbt-sqlserver, which is in the works (dbt-msft/dbt-sqlserver#173). In the meantime, I'm going to close this issue, as it's resolved in newer versions of dbt Core.