[JBoss JIRA] (TEIID-3707) Wrong Data returned when a procedure is executed in the SELECT clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3707?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3707.
-----------------------------------
Fix Version/s: 8.11.5
Resolution: Done
This was a regression in TEIID-1518. It requires a fairly specific set of circumstances to reproduce, so it does not occur in all situations involving deterministic subqueries.
> Wrong Data returned when a procedure is executed in the SELECT clause
> ---------------------------------------------------------------------
>
> Key: TEIID-3707
> URL: https://issues.jboss.org/browse/TEIID-3707
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 7.6
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 8.12, 8.11.5
>
> Attachments: sample_coords.sql, wrong_data.jpg
>
>
> I've found the following problem when executing a stored procedure in the SELECT clause. It calculates wrong data in a seemingly random fashion.
> This is a stored procedure which was created to determine whether a given coordinate lies within a specific rectangle. If this procedure is tested in a simple manner (SELECT .. FROM (EXEC ..)) the results are correctly retuned (0 = outside the rectangle and 1 otherwise).
> {code:sql}
> CREATE virtual procedure point_inside_store (
> pos_x float
> ,pos_y float
> ) RETURNS (
> "insideFence" integer
> ) AS
> BEGIN
> DECLARE integer insideFence = 0 ;
> DECLARE float lowerLimit = 0.0 ;
> DECLARE float upperLimit = 17.0 ;
> DECLARE float leftLimit = 0.0 ;
> DECLARE float rightLimit = 53.0 ;
> IF (
> pos_x >= leftLimit
> AND pos_x <= rightLimit
> AND pos_y >= lowerLimit
> AND pos_y <= upperLimit
> )
> BEGIN
> insideFence = 1 ;
> END
> SELECT
> insideFence ;
> END
> {code}
> If now the same procedure is included in a SELECT clause of a query:
> {code:sql}
> SELECT
> "citmp.KoordX"
> ,"citmp.KoordY"
> ,(
> SELECT
> "store.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "store"
> ) as "insideStore"
> ,(
> SELECT
> "firstsection.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "firstsection"
> ) as "insideFirstsection"
> FROM
> "test.sample_coords" as "citmp"
> ORDER BY
> insideStore ASC
> ,insideFirstsection DESC;;
> {code}
> it calculates different results. The same coordinates that yielded 0 before now yield 1.
> !wrong_data.jpg|thumbnail!
> Note that the main query has 2 columns executing the exact same procedure but there are result sets, that have different values in the last two columns. This should not be possible.
> In attachment you will find sample_coords table with a sample of coordinates.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 6 months
[JBoss JIRA] (TEIID-3707) Wrong Data returned when a procedure is executed in the SELECT clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3707?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3707:
----------------------------------
Affects Version/s: 7.6
(was: 8.12)
> Wrong Data returned when a procedure is executed in the SELECT clause
> ---------------------------------------------------------------------
>
> Key: TEIID-3707
> URL: https://issues.jboss.org/browse/TEIID-3707
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 7.6
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 8.12
>
> Attachments: sample_coords.sql, wrong_data.jpg
>
>
> I've found the following problem when executing a stored procedure in the SELECT clause. It calculates wrong data in a seemingly random fashion.
> This is a stored procedure which was created to determine whether a given coordinate lies within a specific rectangle. If this procedure is tested in a simple manner (SELECT .. FROM (EXEC ..)) the results are correctly retuned (0 = outside the rectangle and 1 otherwise).
> {code:sql}
> CREATE virtual procedure point_inside_store (
> pos_x float
> ,pos_y float
> ) RETURNS (
> "insideFence" integer
> ) AS
> BEGIN
> DECLARE integer insideFence = 0 ;
> DECLARE float lowerLimit = 0.0 ;
> DECLARE float upperLimit = 17.0 ;
> DECLARE float leftLimit = 0.0 ;
> DECLARE float rightLimit = 53.0 ;
> IF (
> pos_x >= leftLimit
> AND pos_x <= rightLimit
> AND pos_y >= lowerLimit
> AND pos_y <= upperLimit
> )
> BEGIN
> insideFence = 1 ;
> END
> SELECT
> insideFence ;
> END
> {code}
> If now the same procedure is included in a SELECT clause of a query:
> {code:sql}
> SELECT
> "citmp.KoordX"
> ,"citmp.KoordY"
> ,(
> SELECT
> "store.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "store"
> ) as "insideStore"
> ,(
> SELECT
> "firstsection.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "firstsection"
> ) as "insideFirstsection"
> FROM
> "test.sample_coords" as "citmp"
> ORDER BY
> insideStore ASC
> ,insideFirstsection DESC;;
> {code}
> it calculates different results. The same coordinates that yielded 0 before now yield 1.
> !wrong_data.jpg|thumbnail!
> Note that the main query has 2 columns executing the exact same procedure but there are result sets, that have different values in the last two columns. This should not be possible.
> In attachment you will find sample_coords table with a sample of coordinates.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 6 months
[JBoss JIRA] (TEIID-3707) Wrong Data returned when a procedure is executed in the SELECT clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3707?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3707:
---------------------------------------
Yes, I can reproduce it as well. The small cache that is kept to lessen repeated subquery invocation has a bug that causes the purging of items from the first to second level to have the wrong cache key. This is why reproducing this is dependent upon the procedure being expressed as a subquery and the amount of data from the parent query.
> Wrong Data returned when a procedure is executed in the SELECT clause
> ---------------------------------------------------------------------
>
> Key: TEIID-3707
> URL: https://issues.jboss.org/browse/TEIID-3707
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 8.12
>
> Attachments: sample_coords.sql, wrong_data.jpg
>
>
> I've found the following problem when executing a stored procedure in the SELECT clause. It calculates wrong data in a seemingly random fashion.
> This is a stored procedure which was created to determine whether a given coordinate lies within a specific rectangle. If this procedure is tested in a simple manner (SELECT .. FROM (EXEC ..)) the results are correctly retuned (0 = outside the rectangle and 1 otherwise).
> {code:sql}
> CREATE virtual procedure point_inside_store (
> pos_x float
> ,pos_y float
> ) RETURNS (
> "insideFence" integer
> ) AS
> BEGIN
> DECLARE integer insideFence = 0 ;
> DECLARE float lowerLimit = 0.0 ;
> DECLARE float upperLimit = 17.0 ;
> DECLARE float leftLimit = 0.0 ;
> DECLARE float rightLimit = 53.0 ;
> IF (
> pos_x >= leftLimit
> AND pos_x <= rightLimit
> AND pos_y >= lowerLimit
> AND pos_y <= upperLimit
> )
> BEGIN
> insideFence = 1 ;
> END
> SELECT
> insideFence ;
> END
> {code}
> If now the same procedure is included in a SELECT clause of a query:
> {code:sql}
> SELECT
> "citmp.KoordX"
> ,"citmp.KoordY"
> ,(
> SELECT
> "store.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "store"
> ) as "insideStore"
> ,(
> SELECT
> "firstsection.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "firstsection"
> ) as "insideFirstsection"
> FROM
> "test.sample_coords" as "citmp"
> ORDER BY
> insideStore ASC
> ,insideFirstsection DESC;;
> {code}
> it calculates different results. The same coordinates that yielded 0 before now yield 1.
> !wrong_data.jpg|thumbnail!
> Note that the main query has 2 columns executing the exact same procedure but there are result sets, that have different values in the last two columns. This should not be possible.
> In attachment you will find sample_coords table with a sample of coordinates.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 6 months
[JBoss JIRA] (TEIID-3707) Wrong Data returned when a procedure is executed in the SELECT clause
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-3707?page=com.atlassian.jira.plugin... ]
dalex dalex updated TEIID-3707:
-------------------------------
Affects Version/s: 8.12
(was: 8.1)
> Wrong Data returned when a procedure is executed in the SELECT clause
> ---------------------------------------------------------------------
>
> Key: TEIID-3707
> URL: https://issues.jboss.org/browse/TEIID-3707
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 8.12
>
> Attachments: sample_coords.sql, wrong_data.jpg
>
>
> I've found the following problem when executing a stored procedure in the SELECT clause. It calculates wrong data in a seemingly random fashion.
> This is a stored procedure which was created to determine whether a given coordinate lies within a specific rectangle. If this procedure is tested in a simple manner (SELECT .. FROM (EXEC ..)) the results are correctly retuned (0 = outside the rectangle and 1 otherwise).
> {code:sql}
> CREATE virtual procedure point_inside_store (
> pos_x float
> ,pos_y float
> ) RETURNS (
> "insideFence" integer
> ) AS
> BEGIN
> DECLARE integer insideFence = 0 ;
> DECLARE float lowerLimit = 0.0 ;
> DECLARE float upperLimit = 17.0 ;
> DECLARE float leftLimit = 0.0 ;
> DECLARE float rightLimit = 53.0 ;
> IF (
> pos_x >= leftLimit
> AND pos_x <= rightLimit
> AND pos_y >= lowerLimit
> AND pos_y <= upperLimit
> )
> BEGIN
> insideFence = 1 ;
> END
> SELECT
> insideFence ;
> END
> {code}
> If now the same procedure is included in a SELECT clause of a query:
> {code:sql}
> SELECT
> "citmp.KoordX"
> ,"citmp.KoordY"
> ,(
> SELECT
> "store.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "store"
> ) as "insideStore"
> ,(
> SELECT
> "firstsection.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "firstsection"
> ) as "insideFirstsection"
> FROM
> "test.sample_coords" as "citmp"
> ORDER BY
> insideStore ASC
> ,insideFirstsection DESC;;
> {code}
> it calculates different results. The same coordinates that yielded 0 before now yield 1.
> !wrong_data.jpg|thumbnail!
> Note that the main query has 2 columns executing the exact same procedure but there are result sets, that have different values in the last two columns. This should not be possible.
> In attachment you will find sample_coords table with a sample of coordinates.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 6 months
[JBoss JIRA] (TEIID-3707) Wrong Data returned when a procedure is executed in the SELECT clause
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-3707?page=com.atlassian.jira.plugin... ]
dalex dalex edited comment on TEIID-3707 at 9/18/15 9:11 AM:
-------------------------------------------------------------
yes, I've reproduced it in 8.12 beta 1. I've just fixed affects version because had tested only 8.12 beta 1.
was (Author: dalex005):
yes, I've reproduced it in 8.12 beta 1.
> Wrong Data returned when a procedure is executed in the SELECT clause
> ---------------------------------------------------------------------
>
> Key: TEIID-3707
> URL: https://issues.jboss.org/browse/TEIID-3707
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 8.12
>
> Attachments: sample_coords.sql, wrong_data.jpg
>
>
> I've found the following problem when executing a stored procedure in the SELECT clause. It calculates wrong data in a seemingly random fashion.
> This is a stored procedure which was created to determine whether a given coordinate lies within a specific rectangle. If this procedure is tested in a simple manner (SELECT .. FROM (EXEC ..)) the results are correctly retuned (0 = outside the rectangle and 1 otherwise).
> {code:sql}
> CREATE virtual procedure point_inside_store (
> pos_x float
> ,pos_y float
> ) RETURNS (
> "insideFence" integer
> ) AS
> BEGIN
> DECLARE integer insideFence = 0 ;
> DECLARE float lowerLimit = 0.0 ;
> DECLARE float upperLimit = 17.0 ;
> DECLARE float leftLimit = 0.0 ;
> DECLARE float rightLimit = 53.0 ;
> IF (
> pos_x >= leftLimit
> AND pos_x <= rightLimit
> AND pos_y >= lowerLimit
> AND pos_y <= upperLimit
> )
> BEGIN
> insideFence = 1 ;
> END
> SELECT
> insideFence ;
> END
> {code}
> If now the same procedure is included in a SELECT clause of a query:
> {code:sql}
> SELECT
> "citmp.KoordX"
> ,"citmp.KoordY"
> ,(
> SELECT
> "store.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "store"
> ) as "insideStore"
> ,(
> SELECT
> "firstsection.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "firstsection"
> ) as "insideFirstsection"
> FROM
> "test.sample_coords" as "citmp"
> ORDER BY
> insideStore ASC
> ,insideFirstsection DESC;;
> {code}
> it calculates different results. The same coordinates that yielded 0 before now yield 1.
> !wrong_data.jpg|thumbnail!
> Note that the main query has 2 columns executing the exact same procedure but there are result sets, that have different values in the last two columns. This should not be possible.
> In attachment you will find sample_coords table with a sample of coordinates.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 6 months
[JBoss JIRA] (TEIID-3707) Wrong Data returned when a procedure is executed in the SELECT clause
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-3707?page=com.atlassian.jira.plugin... ]
dalex dalex commented on TEIID-3707:
------------------------------------
yes, I've reproduced it in 8.12 beta 1.
> Wrong Data returned when a procedure is executed in the SELECT clause
> ---------------------------------------------------------------------
>
> Key: TEIID-3707
> URL: https://issues.jboss.org/browse/TEIID-3707
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.1
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 8.12
>
> Attachments: sample_coords.sql, wrong_data.jpg
>
>
> I've found the following problem when executing a stored procedure in the SELECT clause. It calculates wrong data in a seemingly random fashion.
> This is a stored procedure which was created to determine whether a given coordinate lies within a specific rectangle. If this procedure is tested in a simple manner (SELECT .. FROM (EXEC ..)) the results are correctly retuned (0 = outside the rectangle and 1 otherwise).
> {code:sql}
> CREATE virtual procedure point_inside_store (
> pos_x float
> ,pos_y float
> ) RETURNS (
> "insideFence" integer
> ) AS
> BEGIN
> DECLARE integer insideFence = 0 ;
> DECLARE float lowerLimit = 0.0 ;
> DECLARE float upperLimit = 17.0 ;
> DECLARE float leftLimit = 0.0 ;
> DECLARE float rightLimit = 53.0 ;
> IF (
> pos_x >= leftLimit
> AND pos_x <= rightLimit
> AND pos_y >= lowerLimit
> AND pos_y <= upperLimit
> )
> BEGIN
> insideFence = 1 ;
> END
> SELECT
> insideFence ;
> END
> {code}
> If now the same procedure is included in a SELECT clause of a query:
> {code:sql}
> SELECT
> "citmp.KoordX"
> ,"citmp.KoordY"
> ,(
> SELECT
> "store.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "store"
> ) as "insideStore"
> ,(
> SELECT
> "firstsection.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "firstsection"
> ) as "insideFirstsection"
> FROM
> "test.sample_coords" as "citmp"
> ORDER BY
> insideStore ASC
> ,insideFirstsection DESC;;
> {code}
> it calculates different results. The same coordinates that yielded 0 before now yield 1.
> !wrong_data.jpg|thumbnail!
> Note that the main query has 2 columns executing the exact same procedure but there are result sets, that have different values in the last two columns. This should not be possible.
> In attachment you will find sample_coords table with a sample of coordinates.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 6 months
[JBoss JIRA] (TEIID-3707) Wrong Data returned when a procedure is executed in the SELECT clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3707?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3707:
---------------------------------------
Did you mean to log this against 8.1? Have you reproduced this in 8.11/8.12?
> Wrong Data returned when a procedure is executed in the SELECT clause
> ---------------------------------------------------------------------
>
> Key: TEIID-3707
> URL: https://issues.jboss.org/browse/TEIID-3707
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.1
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 8.12
>
> Attachments: sample_coords.sql, wrong_data.jpg
>
>
> I've found the following problem when executing a stored procedure in the SELECT clause. It calculates wrong data in a seemingly random fashion.
> This is a stored procedure which was created to determine whether a given coordinate lies within a specific rectangle. If this procedure is tested in a simple manner (SELECT .. FROM (EXEC ..)) the results are correctly retuned (0 = outside the rectangle and 1 otherwise).
> {code:sql}
> CREATE virtual procedure point_inside_store (
> pos_x float
> ,pos_y float
> ) RETURNS (
> "insideFence" integer
> ) AS
> BEGIN
> DECLARE integer insideFence = 0 ;
> DECLARE float lowerLimit = 0.0 ;
> DECLARE float upperLimit = 17.0 ;
> DECLARE float leftLimit = 0.0 ;
> DECLARE float rightLimit = 53.0 ;
> IF (
> pos_x >= leftLimit
> AND pos_x <= rightLimit
> AND pos_y >= lowerLimit
> AND pos_y <= upperLimit
> )
> BEGIN
> insideFence = 1 ;
> END
> SELECT
> insideFence ;
> END
> {code}
> If now the same procedure is included in a SELECT clause of a query:
> {code:sql}
> SELECT
> "citmp.KoordX"
> ,"citmp.KoordY"
> ,(
> SELECT
> "store.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "store"
> ) as "insideStore"
> ,(
> SELECT
> "firstsection.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "firstsection"
> ) as "insideFirstsection"
> FROM
> "test.sample_coords" as "citmp"
> ORDER BY
> insideStore ASC
> ,insideFirstsection DESC;;
> {code}
> it calculates different results. The same coordinates that yielded 0 before now yield 1.
> !wrong_data.jpg|thumbnail!
> Note that the main query has 2 columns executing the exact same procedure but there are result sets, that have different values in the last two columns. This should not be possible.
> In attachment you will find sample_coords table with a sample of coordinates.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 6 months
[JBoss JIRA] (TEIID-3707) Wrong Data returned when a procedure is executed in the SELECT clause
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-3707?page=com.atlassian.jira.plugin... ]
dalex dalex updated TEIID-3707:
-------------------------------
Description:
I've found the following problem when executing a stored procedure in the SELECT clause. It calculates wrong data in a seemingly random fashion.
This is a stored procedure which was created to determine whether a given coordinate lies within a specific rectangle. If this procedure is tested in a simple manner (SELECT .. FROM (EXEC ..)) the results are correctly retuned (0 = outside the rectangle and 1 otherwise).
{code:sql}
CREATE virtual procedure point_inside_store (
pos_x float
,pos_y float
) RETURNS (
"insideFence" integer
) AS
BEGIN
DECLARE integer insideFence = 0 ;
DECLARE float lowerLimit = 0.0 ;
DECLARE float upperLimit = 17.0 ;
DECLARE float leftLimit = 0.0 ;
DECLARE float rightLimit = 53.0 ;
IF (
pos_x >= leftLimit
AND pos_x <= rightLimit
AND pos_y >= lowerLimit
AND pos_y <= upperLimit
)
BEGIN
insideFence = 1 ;
END
SELECT
insideFence ;
END
{code}
If now the same procedure is included in a SELECT clause of a query:
{code:sql}
SELECT
"citmp.KoordX"
,"citmp.KoordY"
,(
SELECT
"store.insideFence"
FROM
(
EXEC procs.point_inside_store (
CAST (
"citmp.KoordX" AS float
)
,CAST (
"citmp.KoordY" AS float
)
)
) as "store"
) as "insideStore"
,(
SELECT
"firstsection.insideFence"
FROM
(
EXEC procs.point_inside_store (
CAST (
"citmp.KoordX" AS float
)
,CAST (
"citmp.KoordY" AS float
)
)
) as "firstsection"
) as "insideFirstsection"
FROM
"test.sample_coords" as "citmp"
ORDER BY
insideStore ASC
,insideFirstsection DESC;;
{code}
it calculates different results. The same coordinates that yielded 0 before now yield 1.
!wrong_data.jpg|thumbnail!
Note that the main query has 2 columns executing the exact same procedure but there are result sets, that have different values in the last two columns. This should not be possible.
In attachment you will find sample_coords table with a sample of coordinates.
was:
I've found the following problem when executing a stored procedure in the SELECT clause. It calculates wrong data in a seemingly random fashion.
This is a stored procedure which was created to determine whether a given coordinate lies within a specific rectangle. If this procedure is tested in a simple manner (SELECT .. FROM (EXEC ..)) the results are correctly retuned (0 = outside the rectangle and 1 otherwise).
{code:sql}
CREATE virtual procedure point_inside_store (
pos_x float
,pos_y float
) RETURNS (
"insideFence" integer
) AS
BEGIN
DECLARE integer insideFence = 0 ;
DECLARE float lowerLimit = 0.0 ;
DECLARE float upperLimit = 17.0 ;
DECLARE float leftLimit = 0.0 ;
DECLARE float rightLimit = 53.0 ;
IF (
pos_x >= leftLimit
AND pos_x <= rightLimit
AND pos_y >= lowerLimit
AND pos_y <= upperLimit
)
BEGIN
insideFence = 1 ;
END
SELECT
insideFence ;
END
{code}
If now the same procedure is included in a SELECT clause of a query:
{code:sql}
SELECT
"citmp.KoordX"
,"citmp.KoordY"
,(
SELECT
"store.insideFence"
FROM
(
EXEC procs.point_inside_store (
CAST (
"citmp.KoordX" AS float
)
,CAST (
"citmp.KoordY" AS float
)
)
) as "store"
) as "insideStore"
,(
SELECT
"firstsection.insideFence"
FROM
(
EXEC procs.point_inside_store (
CAST (
"citmp.KoordX" AS float
)
,CAST (
"citmp.KoordY" AS float
)
)
) as "firstsection"
) as "insideFirstsection"
FROM
"test.sample_coords" as "citmp"
ORDER BY
insideStore ASC
,insideFirstsection DESC;;
{code}
it calculates different results. The same coordinates that yielded 0 before now yield 1.
!wrong_data.jpg|thumbnail!
Note that the main query has 2 columns executing the exact same procedure but there are result sets, that have different values in the last two columns. This should not be possible.
In attachment you will find sample_coords table with sample coordinates.
> Wrong Data returned when a procedure is executed in the SELECT clause
> ---------------------------------------------------------------------
>
> Key: TEIID-3707
> URL: https://issues.jboss.org/browse/TEIID-3707
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.1
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 8.12
>
> Attachments: sample_coords.sql, wrong_data.jpg
>
>
> I've found the following problem when executing a stored procedure in the SELECT clause. It calculates wrong data in a seemingly random fashion.
> This is a stored procedure which was created to determine whether a given coordinate lies within a specific rectangle. If this procedure is tested in a simple manner (SELECT .. FROM (EXEC ..)) the results are correctly retuned (0 = outside the rectangle and 1 otherwise).
> {code:sql}
> CREATE virtual procedure point_inside_store (
> pos_x float
> ,pos_y float
> ) RETURNS (
> "insideFence" integer
> ) AS
> BEGIN
> DECLARE integer insideFence = 0 ;
> DECLARE float lowerLimit = 0.0 ;
> DECLARE float upperLimit = 17.0 ;
> DECLARE float leftLimit = 0.0 ;
> DECLARE float rightLimit = 53.0 ;
> IF (
> pos_x >= leftLimit
> AND pos_x <= rightLimit
> AND pos_y >= lowerLimit
> AND pos_y <= upperLimit
> )
> BEGIN
> insideFence = 1 ;
> END
> SELECT
> insideFence ;
> END
> {code}
> If now the same procedure is included in a SELECT clause of a query:
> {code:sql}
> SELECT
> "citmp.KoordX"
> ,"citmp.KoordY"
> ,(
> SELECT
> "store.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "store"
> ) as "insideStore"
> ,(
> SELECT
> "firstsection.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "firstsection"
> ) as "insideFirstsection"
> FROM
> "test.sample_coords" as "citmp"
> ORDER BY
> insideStore ASC
> ,insideFirstsection DESC;;
> {code}
> it calculates different results. The same coordinates that yielded 0 before now yield 1.
> !wrong_data.jpg|thumbnail!
> Note that the main query has 2 columns executing the exact same procedure but there are result sets, that have different values in the last two columns. This should not be possible.
> In attachment you will find sample_coords table with a sample of coordinates.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 6 months
[JBoss JIRA] (TEIID-3707) Wrong Data returned when a procedure is executed in the SELECT clause
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-3707?page=com.atlassian.jira.plugin... ]
dalex dalex updated TEIID-3707:
-------------------------------
Issue Type: Bug (was: Feature Request)
> Wrong Data returned when a procedure is executed in the SELECT clause
> ---------------------------------------------------------------------
>
> Key: TEIID-3707
> URL: https://issues.jboss.org/browse/TEIID-3707
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.1
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 8.12
>
> Attachments: sample_coords.sql, wrong_data.jpg
>
>
> I've found the following problem when executing a stored procedure in the SELECT clause. It calculates wrong data in a seemingly random fashion.
> This is a stored procedure which was created to determine whether a given coordinate lies within a specific rectangle. If this procedure is tested in a simple manner (SELECT .. FROM (EXEC ..)) the results are correctly retuned (0 = outside the rectangle and 1 otherwise).
> {code:sql}
> CREATE virtual procedure point_inside_store (
> pos_x float
> ,pos_y float
> ) RETURNS (
> "insideFence" integer
> ) AS
> BEGIN
> DECLARE integer insideFence = 0 ;
> DECLARE float lowerLimit = 0.0 ;
> DECLARE float upperLimit = 17.0 ;
> DECLARE float leftLimit = 0.0 ;
> DECLARE float rightLimit = 53.0 ;
> IF (
> pos_x >= leftLimit
> AND pos_x <= rightLimit
> AND pos_y >= lowerLimit
> AND pos_y <= upperLimit
> )
> BEGIN
> insideFence = 1 ;
> END
> SELECT
> insideFence ;
> END
> {code}
> If now the same procedure is included in a SELECT clause of a query:
> {code:sql}
> SELECT
> "citmp.KoordX"
> ,"citmp.KoordY"
> ,(
> SELECT
> "store.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "store"
> ) as "insideStore"
> ,(
> SELECT
> "firstsection.insideFence"
> FROM
> (
> EXEC procs.point_inside_store (
> CAST (
> "citmp.KoordX" AS float
> )
> ,CAST (
> "citmp.KoordY" AS float
> )
> )
> ) as "firstsection"
> ) as "insideFirstsection"
> FROM
> "test.sample_coords" as "citmp"
> ORDER BY
> insideStore ASC
> ,insideFirstsection DESC;;
> {code}
> it calculates different results. The same coordinates that yielded 0 before now yield 1.
> !wrong_data.jpg|thumbnail!
> Note that the main query has 2 columns executing the exact same procedure but there are result sets, that have different values in the last two columns. This should not be possible.
> In attachment you will find sample_coords table with sample coordinates.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 6 months
[JBoss JIRA] (TEIID-3707) Wrong Data returned when a procedure is executed in the SELECT clause
by dalex dalex (JIRA)
dalex dalex created TEIID-3707:
----------------------------------
Summary: Wrong Data returned when a procedure is executed in the SELECT clause
Key: TEIID-3707
URL: https://issues.jboss.org/browse/TEIID-3707
Project: Teiid
Issue Type: Feature Request
Components: Query Engine
Affects Versions: 8.1
Reporter: dalex dalex
Assignee: Steven Hawkins
Priority: Blocker
Fix For: 8.12
Attachments: sample_coords.sql, wrong_data.jpg
I've found the following problem when executing a stored procedure in the SELECT clause. It calculates wrong data in a seemingly random fashion.
This is a stored procedure which was created to determine whether a given coordinate lies within a specific rectangle. If this procedure is tested in a simple manner (SELECT .. FROM (EXEC ..)) the results are correctly retuned (0 = outside the rectangle and 1 otherwise).
{code:sql}
CREATE virtual procedure point_inside_store (
pos_x float
,pos_y float
) RETURNS (
"insideFence" integer
) AS
BEGIN
DECLARE integer insideFence = 0 ;
DECLARE float lowerLimit = 0.0 ;
DECLARE float upperLimit = 17.0 ;
DECLARE float leftLimit = 0.0 ;
DECLARE float rightLimit = 53.0 ;
IF (
pos_x >= leftLimit
AND pos_x <= rightLimit
AND pos_y >= lowerLimit
AND pos_y <= upperLimit
)
BEGIN
insideFence = 1 ;
END
SELECT
insideFence ;
END
{code}
If now the same procedure is included in a SELECT clause of a query:
{code:sql}
SELECT
"citmp.KoordX"
,"citmp.KoordY"
,(
SELECT
"store.insideFence"
FROM
(
EXEC procs.point_inside_store (
CAST (
"citmp.KoordX" AS float
)
,CAST (
"citmp.KoordY" AS float
)
)
) as "store"
) as "insideStore"
,(
SELECT
"firstsection.insideFence"
FROM
(
EXEC procs.point_inside_store (
CAST (
"citmp.KoordX" AS float
)
,CAST (
"citmp.KoordY" AS float
)
)
) as "firstsection"
) as "insideFirstsection"
FROM
"test.sample_coords" as "citmp"
ORDER BY
insideStore ASC
,insideFirstsection DESC;;
{code}
it calculates different results. The same coordinates that yielded 0 before now yield 1.
!wrong_data.jpg|thumbnail!
Note that the main query has 2 columns executing the exact same procedure but there are result sets, that have different values in the last two columns. This should not be possible.
In attachment you will find sample_coords table with sample coordinates.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
10 years, 6 months