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

When qualifying a column name with its synonym name in the select list, Derby throws SQLSTATE 42X04.

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.3.1, 10.2.1.6, 10.3.1.4
    • Fix Version/s: 10.1.3.3, 10.2.2.0, 10.3.1.4
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Any environment

      Description

      When qualifying a synonym with its name in the select list, Derby throws SQLSTATE 42X04 but if there is no qualification of the column, then the select statement executes successfully.

      ij version 10.3
      ij> connect 'jdbc:derby:wombat;create=true' user 'user1' as user1;
      WARNING 01J01: Database 'wombat' not created, connection made to existing database instead.
      ij> create schema test1;
      0 rows inserted/updated/deleted
      ij> create schema test2;
      0 rows inserted/updated/deleted
      ij> create table test1.t1 ( id bigint not null );
      0 rows inserted/updated/deleted
      ij> create synonym test2.t1 for test1.t1;
      0 rows inserted/updated/deleted
      ij> set schema test1;
      0 rows inserted/updated/deleted
      ij> select t1.id from t1;
      ID
      --------------------

      0 rows selected
      ij> set schema test2;
      0 rows inserted/updated/deleted
      ij> select t1.id from t1;
      ERROR 42X04: Column 'T1.ID' 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 'T1.ID' is not a column in the target table

      ij> select id from t1;
      ID
      --------------------

      0 rows selected

      ------------------ Java Information ------------------
      Java Version: 1.4.2_12
      Java Vendor: Sun Microsystems Inc.
      Java home: C:\Program Files\Java\j2re1.4.2_12
      Java classpath: classes;.
      OS name: Windows XP
      OS architecture: x86
      OS version: 5.1
      Java user name: Yip
      Java user home: C:\Documents and Settings\Yip
      Java user dir: C:\work3\derby\trunk
      java.specification.name: Java Platform API Specification
      java.specification.version: 1.4
      --------- Derby Information --------
      JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
      [C:\work3\derby\trunk\classes] 10.3.0.0 alpha - (438358)
      ------------------------------------------------------
      ----------------- Locale Information -----------------
      Current Locale : [English/United States [en_US]]
      Found support for locale: [de_DE]
      version: 10.3.0.0 alpha - (438358)
      Found support for locale: [es]
      version: 10.3.0.0 alpha - (438358)
      Found support for locale: [fr]
      version: 10.3.0.0 alpha - (438358)
      Found support for locale: [it]
      version: 10.3.0.0 alpha - (438358)
      Found support for locale: [ja_JP]
      version: 10.3.0.0 alpha - (438358)
      Found support for locale: [ko_KR]
      version: 10.3.0.0 alpha - (438358)
      Found support for locale: [pt_BR]
      version: 10.3.0.0 alpha - (438358)
      Found support for locale: [zh_CN]
      version: 10.3.0.0 alpha - (438358)
      Found support for locale: [zh_TW]
      version: 10.3.0.0 alpha - (438358)
      ------------------------------------------------------

        Attachments

        1. derby1784-10.1-diff01.txt
          33 kB
          Yip Ng
        2. derby1784-10.1-stat01.txt
          0.7 kB
          Yip Ng
        3. derby1784-trunk-diff03b.txt
          29 kB
          Yip Ng
        4. derby1784-trunk-stat03b.txt
          0.7 kB
          Yip Ng
        5. derby1784-trunk-diff03a.txt
          29 kB
          Yip Ng
        6. derby1784-trunk-stat03a.txt
          0.7 kB
          Yip Ng
        7. derby1784-trunk-diff02.txt
          4 kB
          Yip Ng
        8. derby1784-trunk-stat02.txt
          0.3 kB
          Yip Ng
        9. derby1784-trunk-diff01.txt
          4 kB
          Yip Ng
        10. derby1784-trunk-stat01.txt
          0.3 kB
          Yip Ng

          Activity

            People

            • Assignee:
              yipng Yip Ng
              Reporter:
              yipng Yip Ng
            • Votes:
              1 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: