[BUG]: Transpiler error for Synapse query #2036
Description
Is there an existing issue for this?
- I have searched the existing issues
Category of Bug / Issue
Converter bug
Current Behavior
Converter/Transpiler is not converting synapse SQL to databricks SQL and errors, as follows:
C:\Windows\System32>databricks labs lakebridge transpile --input-source "C:\Users*REDACTED**REDACTED*\Documents\Customers*REDACTED*\Transpile Test" --output-folder "C:\Users*REDACTED**REDACTED*\Documents\Customers*REDACTED*\Transpile Output\Test" --source-dialect synapse --debug
12:29:09 Info: start pid=29100 version=0.266.0 args="C:\ProgramData\chocolatey\lib\databricks-cli\tools\databricks.exe, labs, lakebridge, transpile, --input-source, C:\Users\REDACTED\REDACTED\Documents\Customers\REDACTED\Transpile Test, --output-folder, C:\Users\REDACTED\REDACTED\Documents\Customers\REDACTED\Transpile Output\Test, --source-dialect, synapse, --debug"
12:29:09 Debug: Loading installed version info from: C:\Users*REDACTED*.databricks\labs\lakebridge\state\version.json pid=29100
12:29:09 Debug: Loading login configuration from: C:\Users*REDACTED*.databricks\labs\lakebridge\config\login.json pid=29100
12:29:09 Debug: Using workspace-level login profile: lakebridge pid=29100
12:29:09 Debug: Loading lakebridge profile from C:\Users*REDACTED*.databrickscfg pid=29100 sdk=true
12:29:09 Debug: Resolved login: Config: host=https://adb-4451440371665687.7.azuredatabricks.net, cluster_id=17c68fce6c47779b, profile=lakebridge, config_file=C:\Users*REDACTED*.databrickscfg, databricks_cli_path=C:\ProgramData\chocolatey\lib\databricks-cli\tools\databricks.exe. Env: DATABRICKS_CLUSTER_ID, DATABRICKS_CLI_PATH pid=29100 sdk=true
12:29:09 Debug: Passing down environment variables: DATABRICKS_CLI_PATH, DATABRICKS_AUTH_TYPE, DATABRICKS_HOST, DATABRICKS_CLUSTER_ID pid=29100
12:29:09 Debug: Forwarding subprocess: C:\Users*REDACTED*.databricks\labs\lakebridge\state\venv\Scripts\python.exe C:\Users*REDACTED*.databricks\labs\lakebridge\lib\src\databricks\labs\lakebridge\cli.py {"command":"transpile","flags":{"catalog-name":"","error-file-path":"","input-source":"C:\Users\REDACTED\REDACTED\Documents\Customers\REDACTED\Transpile Test","log_level":"debug","output-folder":"C:\Users\REDACTED\REDACTED\Documents\Customers\REDACTED\Transpile Output\Test","schema-name":"","skip-validation":"true","source-dialect":"synapse","transpiler-config-path":""},"output_type":""} pid=29100
12:29:09 Debug: starting: C:\Users*REDACTED*.databricks\labs\lakebridge\state\venv\Scripts\python.exe C:\Users*REDACTED*.databricks\labs\lakebridge\lib\src\databricks\labs\lakebridge\cli.py {"command":"transpile","flags":{"catalog-name":"","error-file-path":"","input-source":"C:\Users\REDACTED\REDACTED\Documents\Customers\REDACTED\Transpile Test","log_level":"debug","output-folder":"C:\Users\REDACTED\REDACTED\Documents\Customers\REDACTED\Transpile Output\Test","schema-name":"","skip-validation":"true","source-dialect":"synapse","transpiler-config-path":""},"output_type":""} pid=29100
12:29:12 DEBUG [databricks.sdk] Loaded from environment
12:29:12 DEBUG [databricks.sdk] Ignoring pat auth, because databricks-cli is preferred
12:29:12 DEBUG [databricks.sdk] Ignoring basic auth, because databricks-cli is preferred
12:29:12 DEBUG [databricks.sdk] Ignoring metadata-service auth, because databricks-cli is preferred
12:29:12 DEBUG [databricks.sdk] Ignoring oauth-m2m auth, because databricks-cli is preferred
12:29:12 DEBUG [databricks.sdk] Ignoring github-oidc auth, because databricks-cli is preferred
12:29:12 DEBUG [databricks.sdk] Ignoring azure-client-secret auth, because databricks-cli is preferred
12:29:12 DEBUG [databricks.sdk] Ignoring github-oidc-azure auth, because databricks-cli is preferred
12:29:12 DEBUG [databricks.sdk] Ignoring azure-cli auth, because databricks-cli is preferred
12:29:12 DEBUG [databricks.sdk] Ignoring external-browser auth, because databricks-cli is preferred
12:29:12 DEBUG [databricks.sdk] Attempting to configure auth: databricks-cli
12:29:12 INFO [databricks.sdk] Using Databricks CLI authentication
12:29:12 DEBUG [databricks.sdk] GET /api/2.0/preview/scim/v2/Me
< 200 OK
< {
< "active": true,
< "displayName": "REDACTED",
< "emails": [
< {
< "primary": true,
< "type": "work",
< "value": "REDACTED"
< }
< ],
< "entitlements": [
< {
< "value": "REDACTED"
< },
< "... (1 additional elements)"
< ],
< "externalId": "REDACTED",
< "groups": [
< {
< "$ref": "Groups/REDACTED",
< "display": "admins",
< "type": "direct",
< "value": "REDACTED"
< }
< ],
< "id": "7364867442204854",
< "name": {
< "familyName": "REDACTED",
< "givenName": "REDACTED"
< },
< "schemas": [
< "urn:ietf:params:scim:schemas:core:2.0:User",
< "... (1 additional elements)"
< ],
< "userName": "REDACTED"
< }
12:29:12 DEBUG [d.l.blueprint.installation] Loading TranspileConfig from config.yml
12:29:13 DEBUG [databricks.sdk] GET /api/2.0/workspace/export?path=/Users/REDACTED/.lakebridge/config.yml&direct_download=true
< 200 OK
< [raw stream]
12:29:13 DEBUG [d.l.l.contexts.application] Added User-Agent extra cmd=execute-transpile
12:29:13 DEBUG [d.l.l.contexts.application] Added User-Agent extra transpiler_source_tech=synapse
12:29:13 DEBUG [d.l.l.contexts.application] Added User-Agent extra transpiler_plugin_name=Bladebridge
12:29:13 DEBUG [databricks.sdk] GET /api/2.0/preview/scim/v2/Me
< 200 OK
< {
< "active": true,
< "displayName": "REDACTED",
< "emails": [
< {
< "primary": true,
< "type": "work",
< "value": "REDACTED"
< }
< ],
< "entitlements": [
< {
< "value": "REDACTED"
< },
< "... (1 additional elements)"
< ],
< "externalId": "REDACTED",
< "groups": [
< {
< "$ref": "Groups/REDACTED",
< "display": "admins",
< "type": "direct",
< "value": "REDACTED"
< }
< ],
< "id": "REDACTED",
< "name": {
< "familyName": "REDACTED",
< "givenName": "REDACTED"
< },
< "schemas": [
< "urn:ietf:params:scim:schemas:core:2.0:User",
< "... (1 additional elements)"
< ],
< "userName": "REDACTED"
< }
12:29:13 DEBUG [d.l.l.contexts.application] Added User-Agent extra cmd=execute-transpile
12:29:13 DEBUG [d.l.l.t.lsp.lsp_engine] Detected virtual environment to use at: C:\Users*REDACTED*.databricks\labs\remorph-transpilers\bladebridge\lib.venv
12:29:13 DEBUG [d.l.l.t.lsp.lsp_engine] Using PATH for launching LSP server: C:\Users*REDACTED*.databricks\labs\remorph-transpilers\bladebridge\lib.venv\Scripts;C:\Users*REDACTED*.databricks\labs\lakebridge\state\venv\bin;C:\Program Files\Microsoft SDKs\Azure\CLI2\wbin;C:\Program Files\Java\jdk-17;C:\Program Files\Python310;C:\Windows\System32;C:\Program Files\Microsoft SDKs\Azure\CLI2;C:\Program Files\Microsoft VS Code\bin;C:\Program Files\Git\cmd;C:\Program Files\Azure Data Studio\bin;C:\Program Files\dotnet;C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn;C:\Program Files\Microsoft SQL Server\160\DTS\Binn;C:\Program Files\PowerShell\7;C:\ProgramData\chocolatey\bin;C:\Users*REDACTED*\AppData\Local\Programs\Python\Launcher;C:\Users*REDACTED*\AppData\Local\Microsoft\WindowsApps;C:\Users*REDACTED*\AppData\Local\Microsoft\WinGet\Packages\Databricks.DatabricksCLI_Microsoft.Winget.Source_8wekyb3d8bbwe
12:29:13 DEBUG [d.l.l.t.lsp.lsp_engine] Starting LSP engine: C:\Users*REDACTED*.databricks\labs\remorph-transpilers\bladebridge\lib.venv\Scripts\python.exe ['-m', 'databricks.labs.bladebridge.server', '--log_level=DEBUG'] (cwd=C:\Users*REDACTED*.databricks\labs\remorph-transpilers\bladebridge\lib)
12:29:13 DEBUG [d.l.l.t.lsp.lsp_engine] LSP init params: InitializeParams(capabilities=ClientCapabilities(workspace=None, text_document=None, notebook_document=None, window=None, general=None, experimental=None), process_id=39280, client_info=ClientInfo(name='lakebridge', version='0.10.9'), locale=None, root_path=None, root_uri='file:///C:/Users/REDACTED/OneDrive%20-%20Telefonica%20Tech%20UK%20Limited/Documents/Customers/REDACTED/Transpile%20Test', initialization_options={'remorph': {'source-dialect': 'synapse'}, 'options': {'overrides-file': None}, 'custom': {}}, trace=None, work_done_token=None, workspace_folders=None)
12:29:14 DEBUG [d.l.l.t.lsp.lsp_engine] Registered capability: document/transpileToDatabricks
12:29:14 DEBUG [d.l.l.transpiler.execute] Starting to process input directory: C:\Users*REDACTED**REDACTED*\Documents\Customers*REDACTED*\Transpile Test
12:29:14 DEBUG [d.l.l.transpiler.execute] Transpiling files from folder: C:\Users*REDACTED**REDACTED*\Documents\Customers*REDACTED*\Transpile Test -> C:\Users*REDACTED**REDACTED*\Documents\Customers*REDACTED*\Transpile Output\Test
12:29:14 DEBUG [d.l.l.transpiler.execute] Processing next 2 files: [WindowsPath('C:/Users/REDACTED/REDACTED/Documents/Customers/REDACTED/Transpile Test/SQL.sql'), WindowsPath('C:/Users/REDACTED/REDACTED/Documents/Customers/REDACTED/Transpile Test/TRANSFORM.FunctionWorkTimeInMinutes.sql')]
12:29:14 DEBUG [d.l.l.transpiler.execute] Started processing file: C:\Users*REDACTED**REDACTED*\Documents\Customers*REDACTED*\Transpile Test\SQL.sql
12:29:20 DEBUG [d.l.l.transpiler.execute] Finished transpiling file: C:\Users*REDACTED**REDACTED*\Documents\Customers*REDACTED*\Transpile Test\SQL.sql (result: TranspileResult(transpiled_code="SELECT DATEPART('MONTH', GETDATE());", success_count=1, error_list=[TranspileError(code='CONVERSION-FAILURE', kind=<ErrorKind.INTERNAL: 'INTERNAL'>, severity=<ErrorSeverity.ERROR: 'ERROR'>, path=WindowsPath('C:/Users/REDACTED/REDACTED/Documents/Customers/REDACTED/Transpile Test/SQL.sql'), message='Error transpiling file: SQL.sql:\n', range=CodeRange(start=CodePosition(line=0, character=0), end=CodePosition(line=1, character=36)))]))
12:29:20 INFO [d.l.l.transpiler.execute] Processed file: C:\Users*REDACTED**REDACTED*\Documents\Customers*REDACTED*\Transpile Test\SQL.sql (errors: 1)
12:29:20 DEBUG [d.l.l.transpiler.execute] Started processing file: C:\Users*REDACTED**REDACTED*\Documents\Customers*REDACTED*\Transpile Test\TRANSFORM.FunctionWorkTimeInMinutes.sql
12:29:26 DEBUG [d.l.l.transpiler.execute] Finished transpiling file: C:\Users*REDACTED**REDACTED*\Documents\Customers*REDACTED*\Transpile Test\TRANSFORM.FunctionWorkTimeInMinutes.sql (result: TranspileResult(transpiled_code="CREATE FUNCTION (@StartDate DATETIME, @FinishDate DATETIME)\nRETURNS BIGINT\nAS\nBEGIN\n\n DECLARE @temp BIGINT\n SET @temp=0\n\n DECLARE @firstday DATE\n SET @firstday = CONVERT(DATE, @StartDate, 112)\n\n DECLARE @lastday DATE\n SET @lastday = CONVERT(DATE, @FinishDate, 112)\n\n DECLARE @starttime TIME\n SET @starttime = CONVERT(TIME, @StartDate)\n\n DECLARE @Finishtime TIME\n SET @Finishtime = CONVERT(TIME, @FinishDate)\n\n DECLARE @workStart TIME\n SET @workStart = '09:00'\n\n DECLARE @WorkFinish TIME\n SET @WorkFinish = '17:00'\n\n DECLARE @DailyWorkTime BIGINT\n SET @DailyWorkTime = DATEDIFF(MINUTE, @workStart, @WorkFinish)\n\n IF (@starttime<@workStart)\n BEGIN\n SET @starttime = @workStart\n END\n\n IF (@Finishtime>@WorkFinish)\n BEGIN\n SET @Finishtime=@WorkFinish\n END\n\n IF (@Finishtime<@workStart)\n BEGIN\n SET @Finishtime=@workStart\n END\n\n IF (@starttime>@WorkFinish)\n BEGIN\n SET @starttime = @WorkFinish\n END\n\n DECLARE @CurrentDate DATE\n SET @CurrentDate = @firstday\n DECLARE @LastDate DATE\n SET @LastDate = @lastday\n\n WHILE(@CurrentDate<=@LastDate)\n BEGIN \n IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)\n BEGIN\n IF (@CurrentDate!=@firstday) AND (@CurrentDate!=@lastday)\n BEGIN\n SET @temp = @temp + @DailyWorkTime\n END\n --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes\n ELSE IF (@CurrentDate=@firstday) AND (@CurrentDate!=@lastday)\n BEGIN\n SET @temp = @temp + DATEDIFF(MINUTE, @starttime, @WorkFinish)\n END\n\n ELSE IF (@CurrentDate!=@firstday) AND (@CurrentDate=@lastday)\n BEGIN\n SET @temp = @temp + DATEDIFF(MINUTE, @workStart, @Finishtime)\n END\n --IF it starts and finishes in the same date\n ELSE IF (@CurrentDate=@firstday) AND (@CurrentDate=@lastday)\n BEGIN\n SET @temp = DATEDIFF(MINUTE, @starttime, @Finishtime)\n END\n END\n SET @CurrentDate = DATEADD(day, 1, @CurrentDate)\n END\n\n -- Return the result of the function\n IF @temp<0\n BEGIN\n SET @temp=0\n END\n RETURN @temp\n\nEND\nGO\n\n\n", success_count=1, error_list=[TranspileError(code='CONVERSION-FAILURE', kind=<ErrorKind.INTERNAL: 'INTERNAL'>, severity=<ErrorSeverity.ERROR: 'ERROR'>, path=WindowsPath('C:/Users/REDACTED/REDACTED/Documents/Customers/REDACTED/Transpile Test/TRANSFORM.FunctionWorkTimeInMinutes.sql'), message='Error transpiling file: TRANSFORM.FunctionWorkTimeInMinutes.sql:\n', range=CodeRange(start=CodePosition(line=0, character=0), end=CodePosition(line=93, character=0)))]))
12:29:26 INFO [d.l.l.transpiler.execute] Processed file: C:\Users*REDACTED**REDACTED*\Documents\Customers*REDACTED*\Transpile Test\TRANSFORM.FunctionWorkTimeInMinutes.sql (errors: 1)
12:29:26 DEBUG [d.l.l.transpiler.execute] Transpiler results: TranspileStatus(file_list=[WindowsPath('C:/Users/REDACTED/REDACTED/Documents/Customers/REDACTED/Transpile Test/SQL.sql'), WindowsPath('C:/Users/REDACTED/REDACTED/Documents/Customers/REDACTED/Transpile Test/TRANSFORM.FunctionWorkTimeInMinutes.sql')], no_of_transpiled_queries=2, error_list=[TranspileError(code='CONVERSION-FAILURE', kind=<ErrorKind.INTERNAL: 'INTERNAL'>, severity=<ErrorSeverity.ERROR: 'ERROR'>, path=WindowsPath('C:/Users/REDACTED/REDACTED/Documents/Customers/REDACTED/Transpile Test/SQL.sql'), message='Error transpiling file: SQL.sql:\n', range=CodeRange(start=CodePosition(line=0, character=0), end=CodePosition(line=1, character=36))), TranspileError(code='CONVERSION-FAILURE', kind=<ErrorKind.INTERNAL: 'INTERNAL'>, severity=<ErrorSeverity.ERROR: 'ERROR'>, path=WindowsPath('C:/Users/REDACTED/REDACTED/Documents/Customers/REDACTED/Transpile Test/TRANSFORM.FunctionWorkTimeInMinutes.sql'), message='Error transpiling file: TRANSFORM.FunctionWorkTimeInMinutes.sql:\n', range=CodeRange(start=CodePosition(line=0, character=0), end=CodePosition(line=93, character=0)))])
12:29:26 DEBUG [d.l.l.transpiler.execute] Transpiler Status: {'total_files_processed': 2, 'total_queries_processed': 2, 'analysis_error_count': 0, 'parsing_error_count': 0, 'validation_error_count': 0, 'generation_error_count': 0, 'error_log_file': 'C:\Users\REDACTED\errors.log'}
12:29:27 INFO [d.l.l.transpiler.execute] Done transpiling.
12:29:27 ERROR [src/databricks/labs/lakebridge] C:\Users*REDACTED**REDACTED*\Documents\Customers*REDACTED*\Transpile Test\SQL.sql: 1 found
12:29:27 ERROR [src/databricks/labs/lakebridge] C:\Users*REDACTED**REDACTED*\Documents\Customers*REDACTED*\Transpile Test\TRANSFORM.FunctionWorkTimeInMinutes.sql: 1 found
total_files_processed total_queries_processed analysis_error_count parsing_error_count validation_error_count generation_error_count error_log_file
2 2 0 0 0 0 C:\Users*REDACTED*\errors.log
12:29:27 Info: completed execution pid=29100 exit_code=0
12:29:27 Debug: no telemetry logs to upload pid=29100
Files are unchanged in output folder.
Also tried the same command for mssql conversion.
Tried uninstalling and reinstalling lakebridge and targeting different cluster versions - but no success
Error log is attached: errors.log
LSP log is attached: lsp-server.log
Example file to convert: SQL.sql
Expected Behavior
Files are converted/transpiled successfully from synapse to databricks
Steps To Reproduce
Environment
Windows 11
Python 3.13.7
Databricks CLI v0.266.0
Databricks runtime 16.4 LTS
On command-line run:
databricks labs lakebridge transpile --input-source "" --output-folder "" --source-dialect synapse --debug
Relevant log output or Exception details
See above for attached logsLogs Confirmation
- I ran the command line with
--debug - I have attached the
lsp-server.logunder USER_HOME/.databricks/labs/remorph-transpilers/<converter_name>/lib/lsp-server.log
Sample Query
See above for example queryOperating System
Windows
Version
latest via Databricks CLI