[ 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)