From: Date: March 18 2003 4:47am
Subject: Interactive query works, JDBC fails, related to correlation names
List-Archive: http://lists.mysql.com/java/5211
Message-Id: <SOUOKJ6BAXV522MHJUT04J51DA71.3e7696da@M-STUARTF>
MIME-Version: 1.0
Content-Type: text/plain; charset="windows-1252"

Windows 2000 w/SP3, using Connector/J 3.0.6-stable against
mySQL 3.23.55.  Also tried
  mysql-connector-java-3.0-nightly-20030311-bin.jar
  mysql-connector-java-3.1-nightly-20030311-bin.jar
to no avail.

Found a fairly simple query that works interactively but not
through JDBC.  Seems to be an issue with correlation names,
but I don't see why JDBC would do anything but pass the
query through.

mysql> describe AgentName;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| ID         | int(11)     |      | PRI | 0       |       |
| PrintsAs   | varchar(64) |      | MUL |         |       |
| AgentID    | int(11)     |      | MUL | 0       |       |
| Family     | varchar(32) |      |     |         |       |
| Given      | varchar(32) |      |     |         |       |
| Generation | varchar(16) |      |     |         |       |
| Title      | varchar(16) |      |     |         |       |
| SortsAs    | varchar(32) |      |     |         |       |
+------------+-------------+------+-----+---------+-------+

mysql> describe Collaboration;
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| AgentID      | int(11) |      | PRI | 0       |       |
| MemberID     | int(11) |      |     | 0       |       |
| MemberSeq    | int(11) |      | PRI | 0       |       |
| MemberNameID | int(11) |      |     | 0       |       |
+--------------+---------+------+-----+---------+-------+

The following works fine using the mySQL Windows client:
  mysql> SELECT AN.AgentID, AN.ID, PrintsAs, Family,
      ->        Given, Generation, Title, SortsAs
      ->   FROM AgentName AS AN, Collaboration AS CO
      ->   WHERE CO.AgentID = 1
      ->     AND CO.MemberNameID = AN.ID
      ->   ORDER BY CO.MemberSeq;
Using JDBC, I get
  General error,  message from server: "Table 'db.AN' doesn't exist"
Changing the correlation name from AN to XX changed the message, but
not the problem.

Modifying the JDBC query by replacing the correlation name in the
WHERE clause with the actual table name:
  SELECT AN.AgentID, AN.ID, PrintsAs, Family,
         Given, Generation, Title, SortsAs
    FROM AgentName AS AN, Collaboration AS CO
    WHERE CO.AgentID = 1
      AND CO.MemberNameID = AgentName.ID
    ORDER BY CO.MemberSeq
yields
  General error,  message from server: "Unknown table 'AgentName' in where clause"
which is possibly a clue as to what's going on, as the table definitely
exists.

I was able to get the JDBC query working with
  SELECT AgentName.AgentID, AgentName.ID, PrintsAs, Family
         Given, Generation, Title, SortsAs
    FROM AgentName, Collaboration AS CO
    WHERE CO.AgentID = 1
      AND CO.MemberNameID = AgentName.ID
    ORDER BY CO.MemberSeq
That seems to point at a problem with correlation names.  However,
I have several similar queries using the same tables and correlation
names that don't have problems.

I tried another combo and got a different error message
  SELECT AgentName.AgentID, AgentName.ID, PrintsAs, Family,
         Given, Generation, Title, SortsAs
    FROM AgentName AS AN, Collaboration AS CO
    WHERE CO.AgentID = 0
      AND CO.MemberNameID = AN.ID
    ORDER BY CO.MemberSeq
yields
  General error,  message from server: "Unknown table 'AgentName' in field list"

Suggestions welcomed for pinning this down further.