[ 
https://issues.apache.org/jira/browse/SPARK-62087?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Serge Rielau updated SPARK-62087:
---------------------------------
    Description: 
When a SQL statement contains parameter markers (named :param or positional ?), 
Spark substitutes the bound values into the SQL text before parsing. Errors 
raised by the parser or analyzer report positions and query context computed on 
the substituted text, so the reported position can depend on the bound values 
rather than on the SQL the user submitted.

Problems observed in the code:

1. Line and column for errors after a marker are taken from the substituted 
text. Changing only a bound value's length moves the reported column for the 
same SQL.
2. The stop index of a span that falls inside a substituted value is mapped to 
the start of the parameter marker, so the reported fragment is truncated or 
wrong.
3. Origin.positionAndText stores the original SQL text but keeps indexes from 
the substituted parse, so slicing the stored text with those indexes yields the 
wrong text. SQL scripting uses this for statement text 
(SingleStatement.getText).
4. SubstituteParamsParser inserts a separator space before a replacement that 
follows a quote character, but the PositionMapper does not account for it, so 
later offsets drift.
5. Nested parses of other text (for example, embedded SQL) can apply the outer 
statement's position mapping to unrelated text.
6. The position-mapping tests in ParametersSuite and ParameterSubstitutionSuite 
skip their assertions when no query context is present, so these problems are 
not caught.

Expected behavior:
 - For the same submitted SQL, error line, column, and fragment refer to the 
original SQL text and do not change when only a bound value changes.
 - Stop indexes inside a substituted value map to the end of the original 
marker.
 - SQL scripting statement text is the original statement text.
 - Position-mapping tests fail when query context is missing or incorrect.

Example (to be confirmed on Spark before the PR is merged):
SELECT :P AS c, missing_col FROM VALUES (1) t(x)
Run with a short and a long bound value for :P. The reported position of 
missing_col should be the same in both runs, based on the original SQL.

Scope: Spark SQL parser and analyzer error context, parameter substitution 
(SubstituteParamsParser, PositionMapper, SparkParserUtils, Origin), and SQL 
scripting statement text.

Out of scope: changing the parameter substitution order or adding a 
configuration to turn the fix off.

  was:
Summary: Error positions for parameterized queries refer to the substituted 
SQL, not the original SQL

Description:

When a SQL statement contains parameter markers (named :param or positional ?), 
Spark substitutes the bound values into the SQL text before parsing. Errors 
raised by the parser or analyzer report positions and query context computed on 
the substituted text, so the reported position can depend on the bound values 
rather than on the SQL the user submitted.

Problems observed in the code:

1. Line and column for errors after a marker are taken from the substituted 
text. Changing only a bound value's length moves the reported column for the 
same SQL.
2. The stop index of a span that falls inside a substituted value is mapped to 
the start of the parameter marker, so the reported fragment is truncated or 
wrong.
3. Origin.positionAndText stores the original SQL text but keeps indexes from 
the substituted parse, so slicing the stored text with those indexes yields the 
wrong text. SQL scripting uses this for statement text 
(SingleStatement.getText).
4. SubstituteParamsParser inserts a separator space before a replacement that 
follows a quote character, but the PositionMapper does not account for it, so 
later offsets drift.
5. Nested parses of other text (for example, embedded SQL) can apply the outer 
statement's position mapping to unrelated text.
6. The position-mapping tests in ParametersSuite and ParameterSubstitutionSuite 
skip their assertions when no query context is present, so these problems are 
not caught.

Expected behavior:
- For the same submitted SQL, error line, column, and fragment refer to the 
original SQL text and do not change when only a bound value changes.
- Stop indexes inside a substituted value map to the end of the original marker.
- SQL scripting statement text is the original statement text.
- Position-mapping tests fail when query context is missing or incorrect.

Example (to be confirmed on Spark before the PR is merged):
  SELECT :p AS c, missing_col FROM VALUES (1) t(x)
Run with a short and a long bound value for :p. The reported position of 
missing_col should be the same in both runs, based on the original SQL.

Scope: Spark SQL parser and analyzer error context, parameter substitution 
(SubstituteParamsParser, PositionMapper, SparkParserUtils, Origin), and SQL 
scripting statement text.

Out of scope: changing the parameter substitution order or adding a 
configuration to turn the fix off.


> Error positions for parameterized queries refer to the substituted SQL, not 
> the original SQL
> --------------------------------------------------------------------------------------------
>
>                 Key: SPARK-62087
>                 URL: https://issues.apache.org/jira/browse/SPARK-62087
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 4.4.0
>            Reporter: Serge Rielau
>            Priority: Major
>
> When a SQL statement contains parameter markers (named :param or positional 
> ?), Spark substitutes the bound values into the SQL text before parsing. 
> Errors raised by the parser or analyzer report positions and query context 
> computed on the substituted text, so the reported position can depend on the 
> bound values rather than on the SQL the user submitted.
> Problems observed in the code:
> 1. Line and column for errors after a marker are taken from the substituted 
> text. Changing only a bound value's length moves the reported column for the 
> same SQL.
> 2. The stop index of a span that falls inside a substituted value is mapped 
> to the start of the parameter marker, so the reported fragment is truncated 
> or wrong.
> 3. Origin.positionAndText stores the original SQL text but keeps indexes from 
> the substituted parse, so slicing the stored text with those indexes yields 
> the wrong text. SQL scripting uses this for statement text 
> (SingleStatement.getText).
> 4. SubstituteParamsParser inserts a separator space before a replacement that 
> follows a quote character, but the PositionMapper does not account for it, so 
> later offsets drift.
> 5. Nested parses of other text (for example, embedded SQL) can apply the 
> outer statement's position mapping to unrelated text.
> 6. The position-mapping tests in ParametersSuite and 
> ParameterSubstitutionSuite skip their assertions when no query context is 
> present, so these problems are not caught.
> Expected behavior:
>  - For the same submitted SQL, error line, column, and fragment refer to the 
> original SQL text and do not change when only a bound value changes.
>  - Stop indexes inside a substituted value map to the end of the original 
> marker.
>  - SQL scripting statement text is the original statement text.
>  - Position-mapping tests fail when query context is missing or incorrect.
> Example (to be confirmed on Spark before the PR is merged):
> SELECT :P AS c, missing_col FROM VALUES (1) t(x)
> Run with a short and a long bound value for :P. The reported position of 
> missing_col should be the same in both runs, based on the original SQL.
> Scope: Spark SQL parser and analyzer error context, parameter substitution 
> (SubstituteParamsParser, PositionMapper, SparkParserUtils, Origin), and SQL 
> scripting statement text.
> Out of scope: changing the parameter substitution order or adding a 
> configuration to turn the fix off.



--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to