Test Your SQL Performance Know-How in 3 Minutes - the author says 60% of those who take this little quiz fail! You can select a database of your choice and the quiz will grill you on knowing the basics of indexes and when a query can make good use of particular indexes.
I chose Postgres as my database of choice and passed with a 4 out of 5. I attribute my mistake to a misreading but whatever, I have to accept that I messed one up. :)
Opinions, experiences and information about SQL and the databases that process it, from a guy who's been at it for over 20 years.
Friday, February 28, 2014
Friday, February 21, 2014
A simple but slightly weird Sybase issue
A QA guy came to pick my brain about a bug that was reported to him. A Sybase table had a VARCHAR(255). The bug was that when a too-large String was inserted via JDBC, the following exception was thrown:
com.sybase.jdbc3.jdbc.SybSQLException: Implicit conversion from datatype 'TEXT' to 'VARCHAR' is not allowed. Use the CONVERT function to run this query.
Mr. QA could not quite reproduce the problem. Whenever he tried to insert a large String, the data was truncated down to 255 bytes, but otherwise worked fine. The issue turned out to be when you attempt to insert a String larger than the maximum size allowed for a VARCHAR (32767 or thereabouts) the Sybase JDBC driver assumes that it must be a TEXT. Sort of makes sense since that's too big for a VARCHAR, right? So, without any explicit conversion, the TEXT String cannot "fit" into a VARCHAR column. Exception thrown!
From the user's perspective it's kind of funny - a too-big String truncates, a too-too-big String explodes!
com.sybase.jdbc3.jdbc.SybSQLException: Implicit conversion from datatype 'TEXT' to 'VARCHAR' is not allowed. Use the CONVERT function to run this query.
Mr. QA could not quite reproduce the problem. Whenever he tried to insert a large String, the data was truncated down to 255 bytes, but otherwise worked fine. The issue turned out to be when you attempt to insert a String larger than the maximum size allowed for a VARCHAR (32767 or thereabouts) the Sybase JDBC driver assumes that it must be a TEXT. Sort of makes sense since that's too big for a VARCHAR, right? So, without any explicit conversion, the TEXT String cannot "fit" into a VARCHAR column. Exception thrown!
From the user's perspective it's kind of funny - a too-big String truncates, a too-too-big String explodes!
Subscribe to:
Posts (Atom)