MySQL Bugs: #22314: as in SQL statements

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:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:	Windows (Windows XP)
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
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

Hi Werner,

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

Configuration:
Windows XP
JRE 1.5.0_06
mysql-connector-java-5.0.3

Java code:

package mytest;

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

import testsuite.BaseTestCase;

public class Bug22314 extends BaseTestCase {

	public Bug22314(String name) {
		super(name);
	}

	public void testBug22314() throws Exception {
		createTestTable();
		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);
			System.out.println(rs.getString("name"));
		}
		super.dropTable("BUG_22314");
	}

	public static void main(String[] args) {
		junit.textui.TestRunner.run(Bug22314.class);
	}

	private void createTestTable() throws Exception {
		super.dropTable("BUG_22314");
		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'
Done.
Done.
Connected to 5.0.18-nt
ToddFarmer
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?

Thanks!

Test case

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

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.

Werner,

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.

Thanks!

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) {
      Main.getMainFrame().setCursor(Main.curWait);
      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);
      }
      Main.getMainFrame().setCursor(Main.curDefault);
   }

With the older version of MysqlConnector/J 3.1.13 it works fine.

Todd, 

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.

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.

Werner,

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).

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.

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.

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.

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".

Fixed in 5.0.4.