[ http://issues.apache.org/jira/browse/DERBY-364?page=comments#action_12313698 ]
Daniel John Debrunner commented on DERBY-364: --------------------------------------------- This is not an issue specific to VTI's, the statement given is a syntax error because SQLSTATE is a reserved word. Selecting SQLSTATE from any table expression will give the similar syntax error. Selecting a column that is not the the VTI gives the correct error. ij> select x from t; ERROR 42X04: Column 'X' is either not in any table in the FROM list or appears w ithin a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'X' is not a column in the target table. ij> select sqlstate from t; ERROR 42X01: Syntax error: Encountered "sqlstate" at line 1, column 8. ij> select x from new org.apache.derby.diag.LockTable() as t; ERROR 42X04: Column 'X' is either not in any table in the FROM list or appears w ithin a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'X' is not a column in the target table. > Provide a more helpful message when the incorrect column name is given in a > SELECT statement for a VTI > ------------------------------------------------------------------------------------------------------ > > Key: DERBY-364 > URL: http://issues.apache.org/jira/browse/DERBY-364 > Project: Derby > Type: Improvement > Components: SQL > Versions: 10.0.2.0, 10.0.2.1, 10.1.0.0 > Reporter: David Van Couvering > Priority: Trivial > > If you specify a column name incorrectly in a SELECT statement from a VTI, > you get a fairly unhelpful error message > ij> SELECT SQLSTATE FROM new org.apache.derby.diag.ErrorMessages() vti; > ERROR 42X01: Syntax error: Encountered "SQLSTATE" at line 1, column 8. > ij> SELECT SQL_STATE FROM new org.apache.derby.diag.ErrorMessages() vti; > -- list of SQL States returned -- > If you do the same thing from a regular table, the error message is much more > clear: > ij> create table foo(id integer); > 0 rows inserted/updated/deleted > ij> select ixd from foo; > ERROR 42X04: Column 'IXD' is either not in any table in the FROM list or > appears > within a join specification and is outside the scope of the join > specification > or appears in a HAVING clause and is not in the GROUP BY list. If this is a > CREA > TE or ALTER TABLE statement then 'IXD' is not a column in the target table. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
