Michael J. Carey created ASTERIXDB-2749:
-------------------------------------------
Summary: 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
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)
