Order by clause failing when alias used for table name

[mookkiah]

When we use alias in parent table like below

select movie.film_id, title, release_year "year",
language_id,rental_duration,rental_rate,replacement_cost, film_rating_id, stars
from film movie,
film_rating
where movie.film_id = film_rating.film_id

Order by clause constructs the invalid query as below

select movie.film_id, title, release_year "year",
 language_id,rental_duration,rental_rate,replacement_cost, film_rating_id, stars
  from film movie,
  film_rating
  where movie.film_id = film_rating.film_id AND film.release_year = 2012 ORDER BY film.film_id

I tested this with Postgres database. To test with other database to decide this is common to all database or specific to Postgres, I will do pull request by modifying a test case.

[mookkiah]

Pull request sent restsql/restsql-test#3

[restsql]

Yes that looks like a flaw. I don't think the framework models the SQL query's table aliases. Not sure if that's available from the result set metadata or not. If this is a problem for order bys, then it's likely a problem for query parameters (where clause extensions) as well.

I guess the way to fix this is to get the SQL query alias from the metadata, add it to the internal model and use that instead of the real table name.

Note that you can currently define an alias to name the serialized records (XML element or JSON record). It's an alias attribute of the table element inside the metadata element of the resource definition.

A more realistic test case is joining the same table to itself, otherwise you can always use the fully qualified schema/table name to qualify a column. Is that what you'd like to do?

[mookkiah]

I agree with you. After posting this bug I started thinking about solution. Then I understood that the alias in framework used to construct the resource not the alias in query.

Yes, we have scenario where we need to join the (parent/child) table itself.

To support backward compatibility and support this feature, I would think of adding another field in TableMetaData called queryAlias.

Let me attempt this change in a new branch and see whether it is good to have or not.