Alias name on expression not recognized

Question

I was writing a demo code snippet in JAVA on database values as NULL.

I wrote some simple expressions using NULL in them and gave alias names for those expressions., but without the AS key word. I am well aware that it is optional.

When I ran the SQL query at MySQL command line and also using JAVA the results were same.

But strangely, (though, it may not be for some of you), I found that on one of the expressions it did not use the defined alias name but it used entire expression along with the expression name, with a space char in between them. That space was a separator between the expression and alias name in the sql statement.

And when I used AS keyword explicitly on the same expression, result was as expected.

SQL statement used:

select 
    length( null ) null_len -- to check if null data has any length
  , length( '' ) empty_len  -- to check length of an empty string
  , null = '' 'n=e?'        -- to check if null is equal to an empty string
  , null = '' "n=e?"        -- same as above but alias name within dbl quotes
  , null = '' as 'n=e??'    -- same with AS keyword on alias name used
  , '' is null 'e=n?'       -- to check if empty is equal to null
from dual;

Results on MySQL command line console:

+----------+-----------+------------------+------------------+-------+------+
| null_len | empty_len | null = '' 'n=e?' | null = '' "n=e?" | n=e?? | e=n? |
+----------+-----------+------------------+------------------+-------+------+
|     NULL |         0 |             NULL |             NULL |  NULL |    0 |
+----------+-----------+------------------+------------------+-------+------+

Results using Java with ResultSetMetaData:

for( int i=1; i <= numberOfColumns; i++ ) {
  String columnLabel     = rsmd.getColumnLabel( i );
  String columnName      = rsmd.getColumnLabel( i );
  //String columnType      = rsmd.getColumnTypeName( i );
  //String columnClassName = rsmd.getColumnClassName( i );

  System.out.println( "Column Label: " + columnLabel );
  System.out.println( "Column Name : " + columnLabel );
  //System.out.println( "Column Type : " + columnType );
  //System.out.println( "Column Class: " + columnClassName );

  System.out.println();
} // for count of columns

Output:

Column Label: null_len
Column Name : null_len

Column Label: empty_len
Column Name : empty_len

Column Label: null='' 'n=e?'
Column Name : null='' 'n=e?'

Column Label: null='' "n=e?"
Column Name : null='' "n=e?"

Column Label: n=e??
Column Name : n=e??

Column Label: e=n?
Column Name : e=n?

Issue (May not be !)

Why not the alias name is recognized without AS keyword?
Is that a known fact and issue?

Note: I have used JAVA, just to test and demonstrate the problem. Problem is not with behaviour of JAVA. And hence, answers need not be JAVA specific.

score 1 · Accepted Answer · answered Apr 09 '14 at 17:40

1

See http://dev.mysql.com/doc/refman/5.6/en/string-literals.html

Quoted strings placed next to each other are concatenated to a single string.

One obscure use for this is to allow us to put long strings into a result set, but make the column header a shorter prefix of the long string. Weird, but possible. I admit I've never used this feature.

mysql> select 'abc' 'def';
+--------+
| abc    |
+--------+
| abcdef |
+--------+

To do what you want, you can either use the AS keyword, or else put the alias in back-ticks as a delimited identifier instead of a string literal.

mysql> select 'abc' `def`;
+-----+
| def |
+-----+
| abc |
+-----+

answered Apr 09 '14 at 17:40

Bill Karwin

538,548
86
673
828

I read the doc long ago but missed to recall. Your first example raised one more query! Why `'abc'` only was taken as column header and not `'abc' 'def'` as seen in my query results posted.? – Ravinder Reddy Apr 09 '14 at 17:55
Good question, I'm not sure. It might be mandated by the SQL specification. – Bill Karwin Apr 09 '14 at 18:09
I could not find such specification. If you come across, please also include it with the answer. – Ravinder Reddy Apr 09 '14 at 18:28
I found it in the SQL:2003 Foundation document, section 7.12, *Syntax rule 12.o.ii* (if you're reading along at home). It says it's implementation-dependent. So each implementor of an SQL database can decide how it's supposed to work. – Bill Karwin Apr 09 '14 at 18:55

score 0 · Answer 2 · edited May 23 '17 at 11:45

It appears that neither apostrophes nor quotes are a good practice for escaping the column name. My recommendation would be to use grave accents, it appears to work fine in all cases:

select 
    length( null ) null_len -- to check if null data has any length
  , length( '' ) empty_len  -- to check length of an empty string
  , null = '' `n=e?`        -- to check if null is equal to an empty string
  , null = '' as `n=e??`    -- same with AS keyword on alias name used
  , '' is null `e=n?`       -- to check if empty is equal to null

Here the results are shown in SQL Fiddle. Some more details on this can be found here

Alias name on expression not recognized

2 Answers2