[Bug]: merge_sql_type() does not handle varchar or nvarchar lengths less than max

[BuzzCutNorman]

Singer SDK Version

0.13.1

Python Version

3.9

Bug scope

Targets (data type handling, batching, SQL object generation, etc.)

Operating System

Windows

Description

The merge_sql_types function only handles instances in which one of the character columns has null or 0 lengths. Both values are converted to max by SQLAlchemy so the function is set to prefer them. If there is no max present for example only 64 or 64 the function throws and error letting you know it can't merge the two types.

ValueError: Unable to merge sql types: VARCHAR(64) COLLATE "SQL_Latin1_General_CP1_CI_AS", VARCHAR(64)

This was first noticed here #1125 (comment)

Code

assert len(sql_types) == 2
        # Get the generic type class
        for opt in sql_types:
            # Get the length
            opt_len: int = getattr(opt, "length", 0)
            generic_type = type(opt.as_generic())

            if isinstance(generic_type, type):
                if issubclass(
                    generic_type,
                    (sqlalchemy.types.String, sqlalchemy.types.Unicode),
                ):
                    # If length None or 0 then is varchar max ?
                    if (opt_len is None) or (opt_len == 0):
                        return opt
                elif isinstance(
                    generic_type,
                    (sqlalchemy.types.String, sqlalchemy.types.Unicode),
                ):
                    # If length None or 0 then is varchar max ?
                    if (opt_len is None) or (opt_len == 0):
                        return opt
                # If best conversion class is equal to current type
                # return the best conversion class
                elif str(opt) == str(current_type):
                    return opt

        raise ValueError(
            f"Unable to merge sql types: {', '.join([str(t) for t in sql_types])}"
        )


2022-11-11T15:26:46.648150Z [info     ] Traceback (most recent call last): cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.648150Z [info     ]   File "C:\Program Files\Python39\lib\runpy.py", line 197, in _run_module_as_main cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.648150Z [info     ]     return _run_code(code, main_globals, None, cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.648150Z [info     ]   File "C:\Program Files\Python39\lib\runpy.py", line 87, in _run_code cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.648150Z [info     ]     exec(code, run_globals)    cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.648150Z [info     ]   File "C:\development\projects\test-mssql\.meltano\loaders\target-mssql\venv\Scripts\target-mssql.exe\__main__.py", line 7, in <module> cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.663779Z [info     ]   File "C:\development\projects\test-mssql\.meltano\loaders\target-mssql\venv\lib\site-packages\click\core.py", line 1130, in __call__ cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.663779Z [info     ]     return self.main(*args, **kwargs) cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.663779Z [info     ]   File "C:\development\projects\test-mssql\.meltano\loaders\target-mssql\venv\lib\site-packages\click\core.py", line 1055, in main cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.663779Z [info     ]     rv = self.invoke(ctx)      cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.663779Z [info     ]   File "C:\development\projects\test-mssql\.meltano\loaders\target-mssql\venv\lib\site-packages\click\core.py", line 1404, in invoke cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.663779Z [info     ]     return ctx.invoke(self.callback, **ctx.params) cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.679404Z [info     ]   File "C:\development\projects\test-mssql\.meltano\loaders\target-mssql\venv\lib\site-packages\click\core.py", line 760, in invoke cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.679404Z [info     ]     return __callback(*args, **kwargs) cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.679404Z [info     ]   File "C:\development\MeltanoContribute\sdk\singer_sdk\target_base.py", line 566, in cli cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.679404Z [info     ]     target.listen(file_input)  cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.679404Z [info     ]   File "C:\development\MeltanoContribute\sdk\singer_sdk\io_base.py", line 34, in listen cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.695027Z [info     ]     self._process_lines(file_input) cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.695027Z [info     ]   File "C:\development\MeltanoContribute\sdk\singer_sdk\target_base.py", line 267, in _process_lines cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.695027Z [info     ]     counter = super()._process_lines(file_input) cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.695027Z [info     ]   File "C:\development\MeltanoContribute\sdk\singer_sdk\io_base.py", line 78, in _process_lines cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.695027Z [info     ]     self._process_schema_message(line_dict) cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.695027Z [info     ]   File "C:\development\MeltanoContribute\sdk\singer_sdk\target_base.py", line 366, in _process_schema_message cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.710652Z [info     ]     _ = self.get_sink(         cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.710652Z [info     ]   File "C:\development\MeltanoContribute\sdk\singer_sdk\target_base.py", line 160, in get_sink cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.710652Z [info     ]     return self.add_sink(stream_name, schema, key_properties) cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.710652Z [info     ]   File "C:\development\MeltanoContribute\sdk\singer_sdk\target_base.py", line 235, in add_sink cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.710652Z [info     ]     sink.setup()               cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.726273Z [info     ]   File "C:\development\MeltanoContribute\sdk\singer_sdk\sinks\sql.py", line 215, in setup cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.726273Z [info     ]     self.connector.prepare_table( cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.726273Z [info     ]   File "C:\development\MeltanoContribute\sdk\singer_sdk\connectors\sql.py", line 685, in prepare_table cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.726273Z [info     ]     self.prepare_column(       cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.726273Z [info     ]   File "C:\development\MeltanoContribute\sdk\singer_sdk\connectors\sql.py", line 710, in prepare_column cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.741894Z [info     ]     self._adapt_column_type(   cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.741894Z [info     ]   File "C:\development\MeltanoContribute\sdk\singer_sdk\connectors\sql.py", line 981, in _adapt_column_type cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql      
2022-11-11T15:26:46.741894Z [info     ]     compatible_sql_type = self.merge_sql_types([current_type, sql_type]) cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.741894Z [info     ]   File "C:\development\MeltanoContribute\sdk\singer_sdk\connectors\sql.py", line 803, in merge_sql_types cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.741894Z [info     ]     raise ValueError(          cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.757517Z [info     ] ValueError: Unable to merge sql types: VARCHAR(64) COLLATE "SQL_Latin1_General_CP1_CI_AS", VARCHAR(64) cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-11T15:26:46.788768Z [error    ] Loader failed