Bug #40034 Substring with TEXT Datatype Column returns Empty String

• Submitted: 15 Oct 2008 5:28
• Modified: 15 Oct 2008 7:11
• Reporter: Chintan Shah
• Status: Not a Bug
• Category: Connector / ODBC
• Severity: S1 (Critical)
• Version: MYSQL: 5.0.22, ODBC: 5.1
• OS: Windows (XP Professional SP 2)
• CPU Architecture: Any
• Tags: TEXT DataType Substring Problem

[15 Oct 2008 5:28] Chintan Shah

Description:
Whenever I am selecting Sustring from my Text Type table field, I am getting empty string.

I am using ODBC 5.1 with ASP.NET.

How to repeat:
Create a table with Text Datatype. Say Table is tblTextTest and Text column in table is TextColumn.

Enter some data in table.

Then running the following query returns null string:

SELECT SUBSTRING(TextColumn, 0, 2) FROM tblTextTest;

[15 Oct 2008 7:11] Tonci Grgin

Hi Chintan and thanks for your report.

This is not a bug as you have error in your SQL syntax. Please refer to http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring:
  o For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.
Your starting index is 0.

Next, please do test your problems in command line client too to avoid such reports. Also, a test case would be nice.

Thanks for your interest in MySQL.