search

dbt-labs / dbt-utils

Utility functions for dbt projects.
https://hub.getdbt.com/dbt-labs/dbt_utils/latest/
Apache License 2.0
1.39k stars 500 forks source link

get_tables_by_pattern_sql, ilike doesn't work with SQL Server #877

Closed craiha closed 2 months ago

craiha commented 8 months ago

Describe the bug

Azure SQL and SQL Server doesn't have the ilike SQL operator. The SQL code generated by get_tables_by_pattern_sql uses the ilike operator. The bug was found while running the generate-source macro from the https://github.com/dbt-labs/dbt-codegen package.

Steps to reproduce

  1. Create an Azure SQL database
  2. Add one table to the database
  3. Set up a dbt project connecting to the database
  4. run the command: dbt run-operation generate_source --args '{schema_name: y,database_name: x}'

Expected results

Source YAML code

Actual results

('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

============================== 13:42:18.998428 | 11325469-e83a-4fe8-8f85-afe9ab89d79a ============================== 13:42:18.998428 [info ] [MainThread]: Running with dbt=1.7.8 13:42:19.000386 [debug] [MainThread]: running dbt with arguments {'printer_width': '80', 'indirect_selection': 'eager', 'log_cache_events': 'False', 'write_json': 'True', 'partial_parse': 'True', 'cache_selected_only': 'False', 'warn_error': 'None', 'debug': 'False', 'fail_fast': 'False', 'log_path': 'C:\Users\me\Documents\Projects\project1\databas\datalager_dm\logs', 'profiles_dir': 'C:\Users\me\.dbt', 'version_check': 'True', 'use_colors': 'True', 'use_experimental_parser': 'False', 'no_print': 'None', 'quiet': 'False', 'log_format': 'default', 'invocation_command': 'dbt run-operation generate_source --args {schema_name: y,database_name: x}', 'warn_error_options': 'WarnErrorOptions(include=[], exclude=[])', 'introspect': 'True', 'target_path': 'None', 'static_parser': 'True', 'send_anonymous_usage_stats': 'True'} 13:42:19.552476 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'project_id', 'label': '11325469-e83a-4fe8-8f85-afe9ab89d79a', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x000001DE561C6FB0>]} 13:42:19.712082 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'adapter_info', 'label': '11325469-e83a-4fe8-8f85-afe9ab89d79a', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x000001DE5612F280>]} 13:42:19.713081 [info ] [MainThread]: Registered adapter: sqlserver=1.7.2 13:42:19.765939 [debug] [MainThread]: checksum: 67f0013ca5f0bd43af9a0873dd50792fde83ef69de63b71cacd0b4ac656c52e5, vars: {}, profile: , target: , version: 1.7.8 13:42:20.017233 [debug] [MainThread]: Partial parsing enabled: 0 files deleted, 0 files added, 0 files changed. 13:42:20.067133 [debug] [MainThread]: Partial parsing enabled, no changes found, skipping parsing 13:42:20.082060 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'load_project', 'label': '11325469-e83a-4fe8-8f85-afe9ab89d79a', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x000001DE56E40130>]} 13:42:20.107991 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'resource_counts', 'label': '11325469-e83a-4fe8-8f85-afe9ab89d79a', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x000001DE56CE1A50>]} 13:42:20.108989 [info ] [MainThread]: Found 12 models, 18 tests, 0 sources, 0 exposures, 0 metrics, 579 macros, 0 groups, 0 semantic models 13:42:20.110982 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'runnable_timing', 'label': '11325469-e83a-4fe8-8f85-afe9ab89d79a', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x000001DE56CE3670>]} 13:42:20.112016 [debug] [MainThread]: Acquiring new sqlserver connection 'macro_generate_source' 13:42:20.113011 [debug] [MainThread]: On macro_generate_source: COMMIT 13:42:20.172850 [debug] [MainThread]: Using sqlserver connection "macro_generate_source" 13:42:20.173847 [debug] [MainThread]: On macro_generate_source: / {"app": "dbt", "dbt_version": "1.7.8", "profile_name": "datalager_dm", "target_name": "dev", "connection_name": "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 x.information_schema.tables
    where table_schema ilike 'y'
    and table_name ilike '%'
    and table_name not ilike ''

13:42:20.175808 [debug] [MainThread]: Opening a new connection, currently in state init 13:42:20.176806 [debug] [MainThread]: fabric adapter: Using connection string: DRIVER={ODBC Driver 17 for SQL Server};SERVER=sql-project1-dev-datalager-weu.database.windows.net;Database=sqldb-project1-dev-datalager-weu;encrypt=Yes;TrustServerCertificate=No;APP=dbt-sqlserver/1.7.4;ConnectRetryCount=1 13:42:21.667370 [debug] [MainThread]: fabric adapter: Connected to db: sqldb-project1-dev-datalager-weu 13:42:21.702273 [debug] [MainThread]: fabric 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)") 13:42:21.704270 [debug] [MainThread]: On macro_generate_source: ROLLBACK 13:42:21.706264 [debug] [MainThread]: On macro_generate_source: Close 13:42:21.707263 [debug] [MainThread]: fabric adapter: Error running SQL: macro generate_source 13:42:21.709255 [debug] [MainThread]: fabric adapter: Rolling back transaction. 13:42:21.711251 [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)") 13:42:21.718232 [debug] [MainThread]: Traceback (most recent call last): File "C:\Users\me\dbt-env\lib\site-packages\dbt\adapters\fabric\fabric_connection_manager.py", line 257, in exception_handler yield File "C:\Users\me\dbt-env\lib\site-packages\dbt\adapters\fabric\fabric_connection_manager.py", line 435, in add_query cursor.execute(sql) pyodbc.ProgrammingError: ('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)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "C:\Users\me\dbt-env\lib\site-packages\dbt\task\run_operation.py", line 58, in run self._run_unsafe(package_name, macro_name) File "C:\Users\me\dbt-env\lib\site-packages\dbt\task\run_operation.py", line 43, in _run_unsafe res = adapter.execute_macro( File "C:\Users\me\dbt-env\lib\site-packages\dbt\adapters\base\impl.py", line 1112, in execute_macro result = macro_function(kwargs) File "C:\Users\me\dbt-env\lib\site-packages\dbt\clients\jinja.py", line 330, in call return self.call_macro(*args, *kwargs) File "C:\Users\me\dbt-env\lib\site-packages\dbt\clients\jinja.py", line 257, in call_macro return macro(args, kwargs) File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 763, in call return self._invoke(arguments, autoescape) File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 777, in _invoke rv = self._func(arguments) File "