Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-3066

WHERE clause not accepted on derived expression columns

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Closed
    • Minor
    • Resolution: Duplicate
    • None
    • None
    • SQL
    • None

    Description

      A very simple query involving an expression column with a derived name in the WHERE clause fails:

      ij> select a+b as s from t where s > 2;
      ERROR 42X04: Column 'S' 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 CREATE or ALTER TABLE statement then 'S' is not a column in the target table.

      Noticed this while working on DERBY-2998, and AFAIK this is a valid query and a limitation in derby.

      There is a workaround for this using a nested select (output from a test table):
      ij> select * from (select a+b as s from t) as t(s) where s > 2;
      S
      -----------
      10
      10
      10

      3 rows selected

      Attachments

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            Unassigned Unassigned
            thomanie Thomas Nielsen
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment