10,000 Foot Developer

I saw a weird usage of SQL in a Java application.  Apparently whomever had written the code did not understand how to generate a new Entity with Hibernate and retrieve it’s primary key, and created a pkey field on a table that was, sometimes, string + integer. And sometimes just integer. In the test code though was needed to figure out how to get the maximum integer from that field to increment the next index (instead of letting Hibernate/database (MySQL) handle it. OK, the code is total B.S. but they had this little SQL novelty buried in the code to figure out it a field was a number or a string + number.

select * from table where concat('', pkey * 1) = pkey

I’m totally serial.

Started with a quick SQLite test.  Say you have a table like this:

CREATE TABLE "badindex" (
    "tkey" TEXT NOT NULL,
    "tdata" TEXT
)

where tkey is the primary key. But its a string + integer, or an integer.

select * from badindex where concat('', tkey * 1) = tkey

OK, now note the concat operator — MySQL does not have a || operator (or + for that matter) for concatenation by default. I guess you need to be in ANSI mode and set PIPES_AS_CONCAT=true in the config. Whatever. So you need the concat() function.

I whipped the table up in SQLite and used this data:

tkey	tdata
ss123	test 123
tt456	test 456
789	test 789

SELECT * FROM badindex WHERE ('' || (tkey * 1)) = tkey

Answer: I got the record back for tkey=789.  In SQLite I also get the same answer back  for this:

SELECT * FROM badindex WHERE (tkey * 1) = tkey

SQLite has no concat() operator so I used the pipes. When I looked at the SQL put in the code for MySQL, I became a bit suspicious that MySQL was doing a little more with concat() than just ANSI SQL.  The man behind the curtain at work.

First, doing a little test in MySQL where tkey = ‘string1’:

SELECT concat('', tkey * 1) = tkey

Answer: 0.  

BUT . . . .

SELECT (tkey * 1) = tkey

Answer 1. 

Yikes. If I set tkey = ‘1’ then I get 1 back for both results.  The concat() with a null string makes a difference.

I put the badindex table into MySQL, and here are my results:

SELECT * FROM badindex WHERE ('' || (tkey * 1)) = tkey

Answer: I got the record back for tkey=789.

Now this in MySQL:

SELECT * FROM badindex WHERE (tkey * 1) = tkey

Answer:  I get ALL records back.

In MySQL, string * integer = 1 (true)

In SQLite, string * integer = 0 (false)

IN MySQL, concat('',string * integer) = 1 (true);

(note: implicit value of an integer -i.e. '789' becomes 789 - occurs when string = integer in both databases)

—

Personally I think using little tricks like this is not a good thing, especially if one doesn’t know how to use Hibernate in Java.  I’ve spent many an hour pouring over Oracle code trying to figure out the man behind the curtain; a time waster.  So I’d likely remove this, fix it proper so others can support it without the time waste, and go home.