MySQL Bugs: #27422: SELECT statement returns no records when expected otherwise.

Bug #27422	SELECT statement returns no records when expected otherwise.
Submitted:	25 Mar 2007 2:34	Modified:	1 Feb 2008 13:33
Reporter:	Yuri Koba	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.00.11	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
SELECT statement returns no records when expected to return records with NULL-valued INTEGER fields. I am using ADO connection with C++Builder 6.

How to repeat:
Use ADO connection with C++Builder 6.

For any table T1 that has at least one record with NULL-valued INTEGER field the statement

SELECT * FROM T1

will return empty result.

Windows XP

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Explanation: Hi Yuri and thanks for your report. Next time, please post a complete test case. Another thing is that I can't have all the SW there is on the world so, even though I'm all for Delphi and Borland, I don't have C++Builder.

Test:
  DDL:
mysql> create table bug27422 (
    -> Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> TestNullInt INT UNSIGNED DEFAULT NULL,
    -> JustAnotherFld VARCHAR(10) DEFAULT NULL);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into bug27422 values (NULL,1,"1"),(NULL,2,"2"),(NULL,3,NULL),(NULL
,NULL,"4"),(NULL,5,"5"),(NULL,NULL,"6");
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

Establishing that MySQL server 5.0.38BK on WinXP Pro SP2 localhost has all the data entered:
mysql> SELECT * FROM bug27422;
Field   1:  `Id`
Catalog:    `def`
Database:   `test`
Table:      `bug27422`
Org_table:  `bug27422`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY UNSIGNED AUTO_INCREMENT NUM PART_KEY

Field   2:  `TestNullInt`
Catalog:    `def`
Database:   `test`
Table:      `bug27422`
Org_table:  `bug27422`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      UNSIGNED NUM

Field   3:  `JustAnotherFld`
Catalog:    `def`
Database:   `test`
Table:      `bug27422`
Org_table:  `bug27422`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     10
Max_length: 1
Decimals:   0
Flags:

+----+-------------+----------------+
| Id | TestNullInt | JustAnotherFld |
+----+-------------+----------------+
|  1 |           1 | 1              |
|  2 |           2 | 2              |
|  3 |           3 | NULL           |
|  4 |        NULL | 4              |
|  5 |           5 | 5              |
|  6 |        NULL | 6              |
+----+-------------+----------------+
6 rows in set (0.00 sec)

Test case using MS generic ODBC client to establish the fact that connector/ODBC returns *ALL* rows:
SELECT * FROM bug27422

SQLExecDirect:
	In:hstmt = 0x008B18D0, szSqlStr = "", cbSqlStr = -3
	Return:	SQL_SUCCESS=0

Get Data All:
"Id", "TestNullInt", "JustAnotherFld"
1, 1, "1"
2, 2, "2"
3, 3, <Null>
4, <Null>, "4"
5, 5, "5"
6, <Null>, "6"
6 rows fetched from 3 columns.

VBS test case to establish that all rows can be fetched via ADO/ODBC bridge:
set conn=CreateObject("ADODB.Connection")
Conn.Open("DRIVER={MySQL Connector/ODBC v5}; SERVER=localhost; DATABASE=test; UID=root; PWD=; OPTION=3;")
on error resume next
on error goto 0
set rs=Conn.Execute("select * from bug27422")
do while not rs.EOF
  Wscript.Echo rs(0)
  if IsNULL(rs(1)) Then
    Wscript.Echo "NULL"
  else
    Wscript.Echo rs(1)
  end if
  if IsNULL(rs(2)) Then
    Wscript.Echo "NULL"
  else
    Wscript.Echo rs(2)
  end if
  rs.movenext
loop
rs.close
set rs=nothing
conn.close
set conn=nothing

I am using mysql-connector-odbc-5.0-win32-nightly-20070322 driver.

If I use Microsoft Access to connect using ODBC - I never had problems.
Problem was with C++Builder only.
If I were to query your table 'bug27422' I get an exception in C++Builder only.

In C++Builder:

  TADOConnection *ADOConnection1 = new TADOConnection(NULL);
  TADOQuery *ADOQuery1 = new TADOQuery(NULL);

  ADOConnection1->ConnectionString = "Provider=MSDASQL.1;Persist Security Info=True;DATABASE=test;DSN=MySQL1;PWD=Test;PORT=3306;SERVER=localhost;UID=Test;";
  ADOQuery1->Connection = ADOConnection1;
  ADOConnection1->Open();

  ADOQuery1->SQL->Clear();
  ADOQuery1->SQL->Add("SELECT * FROM bug27422");
  ADOQuery1->Open();

  int n = ADOQuery1->RecordCount;

At this point I am getting an error

Project1.exe raised exception class EOleException with message
'Data provider or other service returned an E_FAIL status'.
Process stoped

Yuri, I will take a second look a this. In the meantime, can you please do the following:
 - Change provider (directly to connector/ODBC driver), ie. bypass "Provider=MSDASQL.1"
and inform me of result.

Tonci

I have changed to the following

  ADOConnection1->ConnectionString = "Persist Security Info=True;DATABASE=test;DSN=MySQL1;PWD=Test;PORT=3306;SERVER=localhost;UID=Test;";

The outcome did not change. The same exception is thrown.

By the way if I use the version ODBC 3.51.12 everything is fine. This is only with 5.00.11-beta version.

Thanks

Yuri, I don't have appropriate Borland tool to check with so I'll have to ask someone to do it for me. It may take a while.
As for 5.0.11, it's stil beta so we are aware that some things do not work.

Verified as described on Borland Delphi 2005. Since test case is attached I can send Delphi one upon request.

Delphi2007 test project

Attachment: Bug27422.zip (application/x-zip-compressed, text), 6.15 KiB.

Using c/ODBC 5.1.2 rev.986 and 3.51.23 rev.968 against MySQL server 5.0.54BK shows no problems. I used Delphi2007 and attached test case. Both variants, using DSN and manually formed connect string work.

Closing the report now.