Hi Karl, On Fri, 19 Sep 2014 06:23:36 -0400 Karl Wright <[email protected]> wrote:
KW> Just to be sure, can you run the same query in the mysql command-line
KW> interface, and make sure it returns what is expected? For the id list,
KW> substitute a comma-separated list of id column values, and for the $ data
KW> columns, substitute names of your choice.
KW>
KW> After that, we can instrument the output of the JDBC calls in the
KW> connector, if needed, to help put together a MySQL bug ticket.
I have executed the query on the command line and the data column was
NULL. After a bit of testing I saw that this is related to the nature
of CONCAT. If one of the columns used for the concat contains a null
value then the whole concat returns NULL.
mysql> SELECT CONCAT('foo', 'bar');
+----------------------+
| CONCAT('foo', 'bar') |
+----------------------+
| foobar |
+----------------------+
1 row in set (0.02 sec)
mysql> SELECT CONCAT('foo', NULL);
+---------------------+
| CONCAT('foo', NULL) |
+---------------------+
| NULL |
+---------------------+
1 row in set (0.02 sec)
Currently I'm working around that issue using IFNULL for columns that
may be null:
SELECT id AS $(IDCOLUMN),
CONCAT("http://my.base.url/show.html?record=";, id) AS $(URLCOLUMN),
CONCAT(name, " ", IFNULL(description, ""), " ", IFNULL(what_ever, ""))
AS $(DATACOLUMN)
FROM accounts WHERE id IN $(IDLIST)
Maybe this is worth an entry in the user documentation? :-)
Regards,
Jens
--
19. Scheiding 2014, 13:11
Homepage : http://www.wegtam.com
Play Rogue, visit exotic locations, meet strange creatures and kill
them.
pgpoGh7o58vAL.pgp
Description: PGP signature
