ilike not supported in T-SQL #90
Description
Describe the bug
ilike not supported in T-SQL. When running generate_source macro the complied SQL throws an error.
Steps to reproduce
dbt run-operation generate_source --args '{"schema_name": "sink"}'
Expected results
.yml file containing a list of all tables in sink schema
Actual results
Encountered an error while running operation: Database Error
('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'ilike'. (4145) (SQLExecDirectW)")
Screenshots and log output
�[0m17:06:09.584072 [info ] [MainThread]: Running with dbt=1.3.0
�[0m17:06:09.584072 [debug] [MainThread]: running dbt with arguments {'write_json': True, 'use_colors': True, 'printer_width': 80, 'version_check': True, 'partial_parse': True, 'static_parser': True, 'profiles_dir': 'C:\\Users\xxxxx\\.dbt', 'send_anonymous_usage_stats': True, 'event_buffer_size': 100000, 'quiet': False, 'no_print': False, 'macro': 'generate_source', 'args': '{schema_name: sink}', 'which': 'run-operation', 'rpc_method': 'run-operation', 'indirect_selection': 'eager'}
�[0m17:06:09.584072 [debug] [MainThread]: Tracking: tracking
�[0m17:06:09.601213 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'start', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x00000204239B7708>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x00000204239B7848>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x00000204239B7788>]}
�[0m17:06:10.319117 [debug] [MainThread]: Partial parsing enabled: 0 files deleted, 0 files added, 0 files changed.
�[0m17:06:10.319117 [debug] [MainThread]: Partial parsing enabled, no changes found, skipping parsing
�[0m17:06:10.348178 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'load_project', 'label': '51552c34-aa33-467e-b0ca-b695db136455', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x0000020423A57C48>]}
�[0m17:06:10.394193 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'runnable_timing', 'label': '51552c34-aa33-467e-b0ca-b695db136455', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x0000020423CDAF08>]}
�[0m17:06:10.399190 [debug] [MainThread]: Acquiring new sqlserver connection "macro_generate_source"
�[0m17:06:10.401180 [debug] [MainThread]: On macro_generate_source: COMMIT
�[0m17:06:10.503190 [debug] [MainThread]: SQLServer adapter: Using sqlserver connection "macro_generate_source".
�[0m17:06:10.504188 [debug] [MainThread]: SQLServer adapter: On macro_generate_source:
select distinct
table_schema as "table_schema",
table_name as "table_name",
case table_type
when 'BASE TABLE' then 'table'
when 'EXTERNAL TABLE' then 'external'
when 'MATERIALIZED VIEW' then 'materializedview'
else lower(table_type)
end as "table_type"
from sqlserver.information_schema.tables
where table_schema ilike 'sink'
and table_name ilike '%'
and table_name not ilike ''
�[0m17:06:10.504188 [debug] [MainThread]: Opening a new connection, currently in state init
�[0m17:06:10.505184 [debug] [MainThread]: SQLServer adapter: Using connection string: DRIVER={ODBC Driver 17 for SQL Server};SERVER=sqlserver.database.windows.net,1433;Database=sqldb;UID={auser};PWD=***;encrypt=Yes;TrustServerCertificate=No;Application Name=dbt-sqlserver/1.3.0
�[0m17:06:11.768674 [debug] [MainThread]: SQLServer adapter: Connected to db: sqldb
�[0m17:06:11.881675 [debug] [MainThread]: SQLServer adapter: Database error: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'ilike'. (4145) (SQLExecDirectW)")
�[0m17:06:11.882676 [debug] [MainThread]: On macro_generate_source: ROLLBACK
�[0m17:06:11.883675 [debug] [MainThread]: On macro_generate_source: Close
�[0m17:06:11.884673 [debug] [MainThread]: SQLServer adapter: Error running SQL: macro generate_source
�[0m17:06:11.885676 [debug] [MainThread]: SQLServer adapter: Rolling back transaction.
�[0m17:06:11.887677 [error] [MainThread]: Encountered an error while running operation: Database Error
('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'ilike'. (4145) (SQLExecDirectW)")
�[0m17:06:11.889678 [debug] [MainThread]:
�[0m17:06:11.891678 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x0000020423B7E248>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x0000020423B7E388>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x0000020423B7EDC8>]}
�[0m17:06:11.892680 [debug] [MainThread]: Flushing usage events
�[0m17:06:12.288065 [debug] [MainThread]: Connection 'macro_generate_source' was properly closed.
System information
The contents of your packages.yml file:
packages:
- package: dbt-labs/dbt_utils
version: 0.9.2
- package: dbt-labs/codegen
version: 0.8.1
Which database are you using dbt with?
- postgres
- redshift
- bigquery
- snowflake
- other (specify: Azure SQL )
The output of dbt --version:
Core:
- installed: 1.3.0
- latest: 1.3.0 - Up to date!
Plugins:
- sqlserver: 1.3.0 - Up to date!
The operating system you're using:
The output of python --version:
py --version
Python 3.7.8
Additional context
Issue with ilike