Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL exception during execution of a correct user defined function that just returns a relation table constructed by a simple SQL select query #2949

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2011-12-18 13:28:16 +0100
From: Babis <>
To: SQL devs <>
Version: 11.9.1 (Apr2012) [obsolete]
CC: @njnes

Last updated: 2012-05-25 12:58:47 +0200

Comment 16656

Date: 2011-12-18 13:28:16 +0100
From: Babis <>

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_2) AppleWebKit/534.52.7 (KHTML, like Gecko) Version/5.1.2 Safari/534.52.7
Build Identifier:

The user defines a function, F, that returns a table. The function does nothing but returns a relational table which is constructed from a simple SQL select query, Q. Although query Q is correct, using function F in another SQL query produces an SQL exception stating that the program contains errors.

Reproducible: Always

Steps to Reproduce:

  1. mclient -d test

  2. sql> create table r (u varchar(32), v varchar(32), r varchar(5));

  3. sql> CREATE FUNCTION refine ()
    RETURNS TABLE (u varchar(32), v varchar(32), r varchar(5))
    BEGIN
    RETURN TABLE (

                    (       SELECT R.u, R.v, R.r FROM R
    
                            INTERSECT
    
                            SELECT R.u, R.v, R.r FROM R
                    )
            );
    

END;
3. sql> select * from refine();

Actual Results:

SyntaxException:user.refine[0]:RETURN missing
TypeException:user.s4_1[5]:'user.refine' undefined in: (_7:bat[:oid,:str], _8:bat[:oid,:str], _9:bat[:oid,:str]) := user.refine()
SQLException:SQLengine:Program contains errors

Expected Results:

Expect to get an empty relation such as the following:
+---+---+---+
| u | v | r |
+===+===+===+
+---+---+---+
0 tuples (3.155ms)

Tested on Ubuntu 10.04 and Mac OS X 10.7.2 (Snow Leopard) for the Aug2011, Dec2011, and development branches. It works as expected only in the Aug2011 branch.

Comment 16657

Date: 2011-12-18 14:51:20 +0100
From: Babis <>

Created attachment 90
SQL exception in a while clause

I also attach a similar exception. The .sql file defines two functions, the first (refine) is the same as the one reported initially in this bug report. The second (trans) makes use of this function in a while clause. If you comment out the while statement, things are OK. If you leave the while loop and comment out the "INSERT INTO" statement, things are again OK. But if you leave them all (as it is in the attached .sql script), then you get the following exception:

sql>< bug.sql
operation successful
operation successful
operation successful
operation successful
sql>select * from trans();
SyntaxException:user.s24_1[151]:barrier '_32' without exit in s24_1[152]
SQLException:SQLengine:Program contains errors

I have tested this in the Aug2011 branch, because the other branches fail (as reported initially in this but report) even in the case that you execute a "select * from refine()" query.

Attached file: bug.sql (application/octet-stream, 707 bytes)
Description: SQL exception in a while clause

Comment 17090

Date: 2012-03-16 14:52:29 +0100
From: Babis <>

(In reply to comment 1)

Created attachment 90 [details]
SQL exception in a while clause

I also attach a similar exception. The .sql file defines two functions, the
first (refine) is the same as the one reported initially in this bug report.
The second (trans) makes use of this function in a while clause. If you comment
out the while statement, things are OK. If you leave the while loop and comment
out the "INSERT INTO" statement, things are again OK. But if you leave them all
(as it is in the attached .sql script), then you get the following exception:

sql>< bug.sql
operation successful
operation successful
operation successful
operation successful
sql>select * from trans();
SyntaxException:user.s24_1[151]:barrier '_32' without exit in s24_1[152]
SQLException:SQLengine:Program contains errors

I have tested this in the Aug2011 branch, because the other branches fail (as
reported initially in this but report) even in the case that you execute a
"select * from refine()" query.

Just to add that the bug is still active for the latest changeset of Dec2011. Time permitting I will come back with a test.

Comment 17233

Date: 2012-05-07 20:27:25 +0200
From: @njnes

fixed by removing incorrect chkProgram during optimizing. Should only be done at end of each optimizer (with a full program)

Comment 17234

Date: 2012-05-07 20:34:59 +0200
From: @njnes

Changeset 6c699953279b made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=6c699953279b

Changeset description:

fixed bug #2949. Checking program (types/declaration/flow) with half complete
programs fails. So only check at the end of the optimizer.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant