Hi authors,
I had a question about the following entry in the benchmark:
{
"norm": "SELECT AVG('value' - TIME_TO_STR(CAST(identifier AS TIMESTAMP), '''value''')) FROM identifier AS identifier INNER JOIN identifier AS identifier ON identifier = identifier WHERE identifier = '''value'''",
"sqlite": "SELECT AVG(2009 - STRFTIME('%Y', T2.BirthDate)) FROM Person AS T1 INNER JOIN Employee AS T2 ON T1.BusinessEntityID = T2.BusinessEntityID WHERE T1.PersonType = 'SP'",
"postgres": "SELECT AVG(2009 - TO_CHAR(CAST(T2.BirthDate AS TIMESTAMP), 'YYYY')) FROM Person AS T1 INNER JOIN Employee AS T2 ON T1.BusinessEntityID = T2.BusinessEntityID WHERE T1.PersonType = 'SP'",
"id": "BIRD"
}When executing the PostgreSQL variant, we get a runtime error because TO_CHAR returns TEXT and PostgreSQL does not allow subtraction between integer and text:
operator does not exist: integer - text
SQLite does not raise this error because it silently coerces the text result of STRFTIME to a number.
Is this translation intended to be semantically correct and executable, or is it acceptable for some PostgreSQL entries in the benchmark to be non-executable? Thanks!
Hi authors,
I had a question about the following entry in the benchmark:
{ "norm": "SELECT AVG('value' - TIME_TO_STR(CAST(identifier AS TIMESTAMP), '''value''')) FROM identifier AS identifier INNER JOIN identifier AS identifier ON identifier = identifier WHERE identifier = '''value'''", "sqlite": "SELECT AVG(2009 - STRFTIME('%Y', T2.BirthDate)) FROM Person AS T1 INNER JOIN Employee AS T2 ON T1.BusinessEntityID = T2.BusinessEntityID WHERE T1.PersonType = 'SP'", "postgres": "SELECT AVG(2009 - TO_CHAR(CAST(T2.BirthDate AS TIMESTAMP), 'YYYY')) FROM Person AS T1 INNER JOIN Employee AS T2 ON T1.BusinessEntityID = T2.BusinessEntityID WHERE T1.PersonType = 'SP'", "id": "BIRD" }When executing the PostgreSQL variant, we get a runtime error because
TO_CHARreturnsTEXTand PostgreSQL does not allow subtraction betweenintegerandtext:SQLite does not raise this error because it silently coerces the text result of
STRFTIMEto a number.Is this translation intended to be semantically correct and executable, or is it acceptable for some PostgreSQL entries in the benchmark to be non-executable? Thanks!