Bug #22314 as in SQL statements
Submitted: 13 Sep 2006 14:57 Modified: 31 Mar 2014 12:11
Reporter: Werner Walczyszewski Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:5.0.3 OS:Windows (Windows XP)
Assigned to: Alexander Soklakov CPU Architecture:Any

[13 Sep 2006 14:57] Werner Walczyszewski
In an SQL statement the word AS don't work.

for example:

SELECT CONCAT(firstname, lastname) AS name 

Error:  Unknown column name: name

How to repeat:
SELECT CONCAT(firstname, lastname) as name from tablexxx
[21 Sep 2006 22:54] Todd Farmer
Hi Werner,

Thank you for the bug report.  I was unable to reproduce the error you describe.
 Below is my test case:

Windows XP
JRE 1.5.0_06

Java code:

package mytest;

import java.sql.ResultSet;
import java.sql.Statement;

import testsuite.BaseTestCase;

public class Bug22314 extends BaseTestCase {

	public Bug22314(String name) {

	public void testBug22314() throws Exception {
		Statement s = this.conn.createStatement();
		ResultSet rs = s
				.executeQuery("SELECT CONCAT(firstname, lastname) AS name FROM BUG_22314");
		while (rs.next()) {
			String name = rs.getString("name");
			super.assertEquals("ToddFarmer", name);

	public static void main(String[] args) {

	private void createTestTable() throws Exception {
		this.stmt.executeUpdate("CREATE TABLE BUG_22314 "
				+ "(firstname VARCHAR(10), lastname VARCHAR(10))");
		this.stmt.executeUpdate("INSERT INTO BUG_22314"
				+ " VALUES ('Todd', 'Farmer')");

Java output:

.Loading JDBC driver 'com.mysql.jdbc.Driver'
Connected to 5.0.18-nt
Time: 0.703
OK (1 test)

Can you please look at the test case executed and determine if it is representative of the conditions under which you experienced errors?

[21 Sep 2006 22:54] Todd Farmer
Test case

Attachment: Bug22314.java (text/x-java), 1016 bytes.

[22 Sep 2006 7:05] Werner Walczyszewski
This reported bug is not a bug from Mysql. It is a bug in Jasperreports 1.2.6 for Mysqlversions newer 4.1. But I don't get an Exception when I use the older  Version (3.1.13) of mysqlConnector.
[26 Sep 2006 15:07] Todd Farmer

Do you have a full stack trace and the SQL that is being executed by JasperReports?  You should also create a test case that you can execute in your runtime environment without JasperReports to determine if the problem is isolated to a bug within that library.

[26 Sep 2006 16:33] Werner Walczyszewski
The SQL-Statement is:

ResultSet reportAdressetiketten() throws SQLException  {
      String sql = "select t3.bezeichnung as anrede, ltrim(concat(nachname, ' ',      vorname, ' ', titel)) as name, " +
            "firmenname, ltrim(concat(plzstaat, '-', postleitzahl, ' ', stadt)) as wohnort, strasse, staat, kopien " +
            "from auswahladressen t1 inner join (adresskartei t2, anrede t3) " +
            "on (t1.adressnummer = t2.adressnummer and t2.anrede = t3.anredecode) " +
            "order by nachname, vorname";
      return stmt.executeQuery(sql);

After executing this SQL-Statement I didn't get an Exception.

But after executing

static void createReport(String reportFile, HashMap hashMap, Object jrds) {
      try {
         JasperPrint report = JasperFillManager.fillReport(new File(reportFile).getPath(), hashMap, (JRResultSetDataSource)jrds);
         JasperViewer.viewReport(report, false);

I get the following stacktrace

net.sf.jasperreports.engine.JRException: Unknown column name : anrede
        at net.sf.jasperreports.engine.JRResultSetDataSource.getColumnIndex(JRResultSetDataSource.java:357)
        at net.sf.jasperreports.engine.JRResultSetDataSource.getFieldValue(JRResultSetDataSource.java:113)
        at net.sf.jasperreports.engine.fill.JRFillDataset.setOldValues(JRFillDataset.java:788)
        at net.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:752)
        at net.sf.jasperreports.engine.fill.JRBaseFiller.next(JRBaseFiller.java:1019)
        at net.sf.jasperreports.engine.fill.JRHorizontalFiller.fillReport(JRHorizontalFiller.java:129)
        at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:758)
        at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:685)
        at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:89)
        at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:601)
        at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:517)
        at trautenfels.Utilities.createReport(Utilities.java:50)
        at trautenfels.MainFrame.itemAdressEtikettenActionPerformed(MainFrame.java:753)
        at trautenfels.MainFrame.access$1000(MainFrame.java:22)
        at trautenfels.MainFrame$11.actionPerformed(MainFrame.java:236)
        at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849)
        at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169)
        at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
        at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
        at javax.swing.AbstractButton.doClick(AbstractButton.java:302)
        at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:1000)
        at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:1041)
        at java.awt.Component.processMouseEvent(Component.java:5488)
        at javax.swing.JComponent.processMouseEvent(JComponent.java:3126)
        at java.awt.Component.processEvent(Component.java:5253)
        at java.awt.Container.processEvent(Container.java:1966)
        at java.awt.Component.dispatchEventImpl(Component.java:3955)
        at java.awt.Container.dispatchEventImpl(Container.java:2024)
        at java.awt.Component.dispatchEvent(Component.java:3803)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
        at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892)
        at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
        at java.awt.Container.dispatchEventImpl(Container.java:2010)
        at java.awt.Window.dispatchEventImpl(Window.java:1778)
        at java.awt.Component.dispatchEvent(Component.java:3803)
        at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
        at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149)
        at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)

      } catch (Exception ex) {
         JOptionPane.showMessageDialog(null, ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);

With the older version of MysqlConnector/J 3.1.13 it works fine.
[3 Oct 2006 11:19] Jake Day

I too have seen this issue this morning. However, I can't recreate a simple example! 

I'm using MySQL5.0 on Windows XP. I added the new 5.0 JDBC driver to my classpath yesterday replacing the 3.1 version and some stuff I built ages ago in Eclipse/BIRT suddenly broke, with the same errors as Werner describes. 

However, it doesn't seem to be as simple as the "AS" command doesn't work in its entirity. I've run some simple tests and it seems fine... 

Apologies that this doesn't help at all in identifying the problem, but i've not been able to "recreate" a small example to demonstrate the issue. If you wanted to know more / view my PC to see the error, please drop me an email.
[3 Oct 2006 17:49] Werner Walczyszewski
It is definitely not a fault from Mysql. My problem is only an incompatibility between Jasperreports and the new MysqlConnector/J 5.0.3

In some foras you can read that the JDBC Expert group has something changed from
metadata.getColumnName() to metadata.getColumnLabel() for aliases. But there is also an advice to use "useOldAliasMetadataBehavior=true" in the JDBC URL Options.
I did it, but it don't work with MySql-5.0.24a-community.
[3 Oct 2006 18:04] Mark Matthews

Adding "useOldAliasMetadataBehavior=true" to your URL _only_ works with 5.0.4 (not yet released, you'd have to use a nightly snapshot from http://downloads.mysql.com/snapshots.php#connector-j)

From your posts, I can't tell if that's what you're using or not. Given that when you configure that property, Connector/J uses the exact same metadata code as 3.1, I'd need more information from your side to track down exactly what's happening (having you run your code in a debugger, and set some breakpoints in certain places would help).
[18 Oct 2006 22:25] Mark Downer
I believe Werner is correct....I installed the connector/J and the CONCAT AS no longer worked for my dropdown...when I reinstalled the earlier 3.0.1 everything worked fine....the problem is that the DataProvider will show string instead of the name of the newly concated column, the Java gives a run time error.
[18 Oct 2006 23:07] Werner Walczyszewski
Yes you are right. I saw in some foras that the new Connector/J use metadata.getColumnLabel() instead of metadata.getColumnName(). But that is not a fault of Connector/J, it's a fault of the third Party software. In my way it is a incompatibility of Jasperreports.
[19 Oct 2006 0:31] Mark Matthews
I still havent heard from any bug reporter whether they've assured themselves that they're actually running with 5.0.4-nightly that has the fix, as out-of-the-box, 5.0.4 has the exact same code path as 3.1.x. You have to explicitly ask for the "new" behavior, which while JDBC-compliant, is not what applications tend to expect.
[20 Nov 2006 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[31 Mar 2014 12:11] Alexander Soklakov
Fixed in 5.0.4.