{"id":958,"date":"2014-03-19T13:11:45","date_gmt":"2014-03-19T20:11:45","guid":{"rendered":"http:\/\/10kdev.net\/?p=958"},"modified":"2014-03-20T07:15:05","modified_gmt":"2014-03-20T14:15:05","slug":"what-is-string-integer-in-sql","status":"publish","type":"post","link":"http:\/\/10kdev.net\/?p=958","title":{"rendered":"What is String * Integer in SQL?"},"content":{"rendered":"

I saw a weird usage of SQL in a Java application. \u00a0Apparently 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.<\/p>\n

select * from table where concat('', pkey * 1) = pkey<\/pre>\n
I’m totally serial.<\/p>\n
Started with a quick SQLite test. \u00a0Say you have a table like this:<\/p>\n
CREATE TABLE \"badindex\" (\r\n    \"tkey\" TEXT NOT NULL,\r\n    \"tdata\" TEXT\r\n)<\/pre>\n
where tkey is the primary key. But its a string + integer, or an integer.<\/p>\n
select * from badindex where concat('', tkey * 1) = tkey<\/pre>\n
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.<\/p>\n
I whipped the table up in SQLite and used this data:<\/p>\n\n\n\n\n\n\n
tkey<\/b><\/td>\ntdata<\/b><\/td>\n<\/tr>\n
ss123<\/td>\ntest 123<\/td>\n<\/tr>\n
tt456<\/td>\ntest 456<\/td>\n<\/tr>\n
789<\/td>\ntest 789<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SELECT * FROM badindex WHERE ('' || (tkey * 1)) = tkey<\/pre>\n
Answer: I got the record back for tkey=789. \u00a0In SQLite I also get the same answer back \u00a0for this:<\/p>\n
SELECT * FROM badindex WHERE (tkey * 1) = tkey<\/pre>\n
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. \u00a0The man behind the curtain at work.<\/p>\n
First, doing a little test in MySQL<\/strong> where tkey = ‘string1’<\/strong><\/em>:<\/p>\n
SELECT concat('', tkey * 1) = tkey<\/pre>\n
Answer: 0. \u00a0<\/strong><\/p>\n
BUT . . . .<\/p>\n
SELECT (tkey * 1) = tkey<\/pre>\n
Answer 1<\/strong>.\u00a0<\/strong><\/p>\n
Yikes. If I set tkey = ‘1’<\/strong> <\/em>then I get 1 back for both<\/strong> <\/em>results. \u00a0The concat() with a null string makes a difference.<\/p>\n
I put the badindex table into MySQL<\/strong>, and here are my results:<\/p>\n
SELECT * FROM badindex WHERE ('' || (tkey * 1)) = tkey<\/pre>\n
Answer: I got the record back for tkey=789.<\/p>\n
Now this in MySQL:<\/p>\n
SELECT * FROM badindex WHERE (tkey * 1) = tkey\r\n\r\nAnswer:  I get ALL records back.\r\n\r\nIn MySQL, string * integer = 1 (true)\r\n\r\nIn SQLite, string * integer = 0 (false)\r\n\r\nIN MySQL, concat('',string * integer) = 1 (true);\r\n\r\n(note: implicit value of an integer -i.e. '789' becomes 789 - occurs when string = integer in both databases)<\/em><\/pre>\n
—<\/p>\n
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. \u00a0I’ve spent many an hour pouring over Oracle code trying to figure out the man behind the curtain; a time waster. \u00a0So I’d likely remove this, fix it proper so others can support it without the time waste, and go home.<\/p>\n
\"\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
I saw a weird usage of SQL in a Java application. \u00a0Apparently 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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[23,8],"tags":[],"_links":{"self":[{"href":"http:\/\/10kdev.net\/index.php?rest_route=\/wp\/v2\/posts\/958"}],"collection":[{"href":"http:\/\/10kdev.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/10kdev.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/10kdev.net\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/10kdev.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=958"}],"version-history":[{"count":5,"href":"http:\/\/10kdev.net\/index.php?rest_route=\/wp\/v2\/posts\/958\/revisions"}],"predecessor-version":[{"id":963,"href":"http:\/\/10kdev.net\/index.php?rest_route=\/wp\/v2\/posts\/958\/revisions\/963"}],"wp:attachment":[{"href":"http:\/\/10kdev.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=958"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10kdev.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=958"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10kdev.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=958"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}