asterixdb-notifications mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael J. Carey (Jira)" <j...@apache.org>
Subject [jira] [Commented] (ASTERIXDB-2749) Bugs with subqueries and SQL++ functions
Date Mon, 15 Jun 2020 19:57:00 GMT

    [ https://issues.apache.org/jira/browse/ASTERIXDB-2749?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17136126#comment-17136126
] 

Michael J. Carey commented on ASTERIXDB-2749:
---------------------------------------------

The same stuff occurs if the GradeRecord def'n is just the id, btw.

> Bugs with subqueries and SQL++ functions
> ----------------------------------------
>
>                 Key: ASTERIXDB-2749
>                 URL: https://issues.apache.org/jira/browse/ASTERIXDB-2749
>             Project: Apache AsterixDB
>          Issue Type: Bug
>          Components: *DB - AsterixDB, SQL - Translator SQL++
>    Affects Versions: 0.9.4.1, 0.9.4.2
>            Reporter: Michael J. Carey
>            Assignee: Dmitry Lychagin
>            Priority: Major
>             Fix For: 0.9.4.2
>
>
> Here are two bugs that I encountered trying to do my CS122a grading using external datasets
and SQL++.  They're unrelated to the nature of the data, so here is a self-contained reproducer
on the AsterixDB side.  One of the bugs is really odd-looking; some weird rewrite bug related
to constant-folding perhaps?  The other one yields an internal error - I will attach logs
but you can also just reproduce it locally I suspect.
> DROP DATAVERSE GradingBug IF EXISTS;
> CREATE DATAVERSE GradingBug;
> USE GradingBug;
> CREATE TYPE GradeRecord AS OPEN {
> name: string,
> id: int,
> quizzes: double,
> hws: double,
> exams: double,
> piazza: double,
> score: double
> };
> CREATE DATASET Grades(GradeRecord) PRIMARY KEY id;
> INSERT INTO Grades
> (
> [{ "name": "Jason Smith", "id": 123, "quizzes": 100.0, "hws": 99.29, "exams": 94.33,
"piazza": 100.0, "score": 97.39 },
> { "name": "Jason Jones", "id": 234, "quizzes": 100.0, "hws": 95.64, "exams": 89.0, "piazza":
100.0, "score": 93.27 },
> { "name": "Jason Hi", "id": 345, "quizzes": 100.0, "hws": 100.14, "exams": 81.67, "piazza":
100.0, "score": 92.93 },
> { "name": "Jason Lo", "id": 456, "quizzes": 100.0, "hws": 90.93, "exams": 78.33, "piazza":
100.0, "score": 86.47 },
> { "name": "Jason To", "id": 567, "quizzes": 100.0, "hws": 95.71, "exams": 66.67, "piazza":
100.0, "score": 84.6 },
> { "name": "Jason Fro", "id": 678, "quizzes": 80.0, "hws": 85.71, "exams": 72.17, "piazza":
100.0, "score": 80.55 }]
> );
> CREATE FUNCTION newgrade(inscore)  {
> LET cutoffs = [
>    {"cutoff": 96.5, "gr": {"grade": "A+", "gpa": 4.0}},
>    {"cutoff": 92.5, "gr": {"grade": "A", "gpa": 4.0}},
>    {"cutoff": 90.0, "gr": {"grade": "A-", "gpa": 3.7}},
>    {"cutoff": 86.5, "gr": {"grade": "B+", "gpa": 3.3}},
>    {"cutoff": 82.5, "gr": {"grade": "B", "gpa": 3.0}},
>    {"cutoff": 80.0, "gr": {"grade": "B-", "gpa": 2.7}},
>    {"cutoff": 76.5, "gr": {"grade": "C+", "gpa": 2.3}},
>    {"cutoff": 72.5, "gr": {"grade": "C", "gpa": 2.0}},
>    {"cutoff": 70.0,"gr": { "grade": "C-", "gpa": 1.7}},
>    {"cutoff": 66.5, "gr": {"grade": "D+", "gpa": 1.3}},
>    {"cutoff": 62.5, "gr": {"grade": "D", "gpa": 1.0}},
>    {"cutoff": 60.0, "gr": {"grade": "D-", "gpa": 0.7}},
>    {"cutoff": 0.0,   "gr": {"grade": "F", "gpa": 0.0}}
> ]
> FROM cutoffs AS cg
> WHERE inscore >= cg.cutoff
> SELECT VALUE cg.gr
> ORDER BY cg.cutoff DESC
> LIMIT 1
> };
> -- The following query makes sure the cutoff function works
> SELECT newgrade(88.0)[0].grade, newgrade(88.0)[0].gpa;
> -- 1. The following query produces a recursive invocation error
> --     but has a typo - if you replace 88.0 with g.score it's fine!
>      SELECT g.name, g.id, g.score,
>                    newgrade(g.score)[0].grade AS letter,
>                    newgrade(88.0)[0].gpa 
>                -- newgrade(g.score)[0].gpa 
>      FROM Grades g;
> -- 2.  The following query produces the internal error.
> WITH NewGrades AS
>    (SELECT g.name, g.id, g.score,
>                    newgrade(g.score)[0].newgrade AS letter,
>                    newgrade(g.score)[0].gpa
>      FROM Grades g
>     )
> SELECT name, id, score, letter, gpa
> FROM NewGrades
> ORDER BY score DESC LIMIT 5;



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Mime
View raw message