Bug #5401 Simple select statement returns... NOTHING...
Submitted: 3 Sep 2004 22:47 Modified: 12 Jan 2005 21:41
Reporter: Boris Benko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.1 OS:Any (All)
Assigned to: Sergey Petrunya CPU Architecture:Any

[3 Sep 2004 22:47] Boris Benko 
Description:
Hi there folks!

I'm using ControlCenter 0.9.4, but also using a Java app, which shows the very same thing...

Here is a bug

I have a statement:

SELECT medij_operacija_ID, u_date FROM `medij_operacija` where oper = 1 and film_medij_ID = 406

And it returns:

+--------------------+------------+
| medij_operacija_ID | u_date     |
+--------------------+------------+
|                 41 | 2004-09-03 |
+--------------------+------------+

That's a correct answer.

But if I enter the following query:

SELECT medij_operacija_ID FROM `medij_operacija` where oper = 1 and film_medij_ID = 406

(please note that just an atribute 'u_date' is missing!!!)

I got:

EMPTY SET!!! No rows returned... I'm using InnoDB.

How to repeat:
Here is a table definition:

CREATE TABLE IF NOT EXISTS medij_operacija (
  medij_operacija_ID  integer NOT NULL AUTO_INCREMENT,		-- primarni klju?
  film_ID integer,								-- v primeru rezervacije se lahko vpi?e ID filma, ki se rezervira, namesto medija samega
  film_medij_ID integer, 							-- kateri medij se je izposodil
  stranka_ID integer,								-- na ra?un katere stranke
  vrsta_medija_ID integer,							-- katera vrsta medija se rezervira
  zac_operacije date default '0000-00-00',					-- za?etek operacije (izposoja ali rezervacija)
  zac_operacije_t time,								-- ?as za?etka operacije
  plan_kon_operacije date default '0000-00-00',				-- planirani konec operacije (izposojh
  kon_operacije   date,							-- dejanski konec operacije
  oper integer NOT NULL,							-- kak?na operacija nad medijem 1= izposoja, 2=vrnitev, 3=rezervacija, 4=preklic rezervacije
  racun_ID integer,								-- na katerem ra?unu se je obra?unalo
  i_operator  varchar(30) default '',						-- operater, ki je vnesel zapis
  i_date  date default '0000-00-00',						-- datum vnosa zapisa
  u_operator  varchar(30) default '',						-- opeater, ki je zapis a?uriral
  u_date  date default '0000-00-00',						-- datum a?uriranja zapisa
  is_deleted integer,								-- ali je zapis aktiven
  INDEX (film_medij_ID),
  INDEX (racun_ID),
  INDEX (stranka_ID),
  INDEX(oper),
  INDEX (is_deleted),
  PRIMARY KEY (medij_operacija_ID),
  FOREIGN KEY (film_medij_ID) REFERENCES film_medij (film_medij_ID),
  FOREIGN KEY (stranka_ID) REFERENCES stranka (stranka_ID),
  FOREIGN KEY (racun_ID) REFERENCES racun (racun_ID)
) TYPE=InnoDB COMMENT='POD. TABELA OPERACIJ NAD MEDIJI';

Here is a full select of the table:

+--------------------+---------+---------------+------------+-----------------+---------------+-----------------+--------------------+---------------+------+----------+------------+------------+------------+------------+------------+
| medij_operacija_ID | film_ID | film_medij_ID | stranka_ID | vrsta_medija_ID | zac_operacije | zac_operacije_t | plan_kon_operacije | kon_operacije | oper | racun_ID | i_operator | i_date     | u_operator | u_date     | is_deleted |
+--------------------+---------+---------------+------------+-----------------+---------------+-----------------+--------------------+---------------+------+----------+------------+------------+------------+------------+------------+
|                  1 |  [NULL] |           398 |         37 |          [NULL] | 2004-09-02    | 01:55:35        | 2004-09-04         | 2004-09-02    |    2 |        1 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                  2 |  [NULL] |           398 |         37 |          [NULL] | 2004-09-02    | 01:56:29        | 2004-09-04         | 2004-09-02    |    2 |        1 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                  3 |  [NULL] |           400 |         37 |          [NULL] | 2004-09-02    | 02:02:02        | 2004-09-04         | 2004-09-02    |    2 |        2 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                  4 |  [NULL] |           402 |         37 |          [NULL] | 2004-09-02    | 02:03:26        | 2004-09-04         | 2004-09-02    |    2 |        3 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                  5 |  [NULL] |           403 |         37 |          [NULL] | 2004-09-02    | 02:03:40        | 2004-09-04         | 2004-09-02    |    2 |        3 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                  6 |  [NULL] |           406 |         37 |          [NULL] | 2004-09-01    | 02:04:58        | 2004-09-03         | 2004-09-02    |    2 |        4 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                  7 |  [NULL] |           408 |         37 |          [NULL] | 2004-09-02    | 02:05:27        | 2004-09-04         | 2004-09-02    |    2 |        4 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                  8 |  [NULL] |           357 |         37 |          [NULL] | 2004-08-31    | 02:38:56        | 2004-09-02         | 2004-09-02    |    2 |        5 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                  9 |  [NULL] |           408 |         37 |          [NULL] | 2004-08-25    | 02:45:58        | 2004-08-27         | 2004-09-02    |    2 |        5 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 10 |  [NULL] |           366 |         37 |          [NULL] | 2004-09-02    | 09:12:22        | 2004-09-04         | 2004-09-02    |    2 |        6 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 11 |  [NULL] |           259 |         37 |          [NULL] | 2004-09-02    | 09:25:45        | 2004-09-04         | 2004-09-02    |    2 |        7 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 12 |  [NULL] |           368 |         37 |          [NULL] | 2004-09-02    | 09:33:23        | 2004-09-04         | 2004-09-02    |    2 |        8 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 13 |  [NULL] |           484 |         37 |          [NULL] | 2004-09-02    | 09:37:06        | 2004-09-04         | 2004-09-02    |    2 |        9 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 14 |  [NULL] |           581 |         37 |          [NULL] | 2004-09-02    | 09:38:46        | 2004-09-04         | 2004-09-02    |    2 |       10 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 15 |  [NULL] |           292 |         37 |          [NULL] | 2004-09-02    | 10:01:10        | 2004-09-04         | 2004-09-02    |    2 |       10 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 16 |  [NULL] |           411 |         37 |          [NULL] | 2004-09-02    | 10:06:16        | 2004-09-04         | 2004-09-02    |    2 |       12 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 17 |  [NULL] |           581 |         37 |          [NULL] | 2004-09-02    | 10:09:32        | 2004-09-04         | 2004-09-02    |    2 |       13 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 18 |  [NULL] |            25 |         37 |          [NULL] | 2004-09-02    | 10:18:52        | 2004-09-04         | 2004-09-02    |    2 |       14 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 19 |  [NULL] |           256 |         37 |          [NULL] | 2004-09-02    | 10:29:51        | 2004-09-04         | 2004-09-02    |    2 |       15 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 20 |  [NULL] |           257 |         37 |          [NULL] | 2004-09-02    | 10:30:33        | 2004-08-23         | 2004-09-02    |    2 |       16 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 21 |  [NULL] |           260 |         37 |          [NULL] | 2004-09-02    | 10:31:29        | 2004-09-05         | 2004-09-02    |    2 |       17 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 22 |  [NULL] |           260 |         37 |          [NULL] | 2004-09-02    | 10:32:53        | 2004-09-08         | 2004-09-02    |    2 |       18 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 23 |  [NULL] |           366 |         37 |          [NULL] | 2004-09-02    | 10:35:57        | 2004-09-17         | 2004-09-02    |    2 |       19 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 24 |  [NULL] |           260 |         37 |          [NULL] | 2004-09-02    | 10:36:58        | 2004-08-13         | 2004-09-02    |    2 |       19 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 25 |  [NULL] |           260 |         37 |          [NULL] | 2004-09-02    | 10:43:40        | 2004-09-04         | 2004-09-02    |    2 |       20 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 26 |  [NULL] |           260 |         37 |          [NULL] | 2004-09-02    | 10:47:36        | 2004-08-18         | 2004-09-02    |    2 |       20 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 27 |  [NULL] |           260 |         37 |          [NULL] | 2004-09-02    | 10:48:47        | 2004-08-18         | 2004-09-02    |    2 |       21 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 28 |  [NULL] |           260 |         37 |          [NULL] | 2004-09-02    | 10:56:32        | 2004-08-18         | 2004-09-02    |    2 |       22 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 29 |  [NULL] |           366 |         37 |          [NULL] | 2004-09-02    | 11:02:24        | 2004-10-22         | 2004-09-02    |    2 |       23 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 30 |  [NULL] |           483 |         37 |          [NULL] | 2004-09-02    | 11:06:28        | 2004-09-04         | 2004-09-02    |    2 |       23 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 31 |  [NULL] |           483 |         37 |          [NULL] | 2004-09-02    | 11:08:07        | 2004-10-12         | 2004-09-02    |    2 |       24 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 32 |  [NULL] |           483 |         37 |          [NULL] | 2004-09-02    | 11:13:31        | 2004-09-14         | 2004-09-02    |    2 |       25 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 33 |  [NULL] |           483 |         37 |          [NULL] | 2004-09-02    | 11:15:55        | 2004-09-14         | 2004-09-02    |    2 |       26 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 34 |  [NULL] |           483 |         37 |          [NULL] | 2004-09-02    | 11:17:19        | 2004-09-14         | 2004-09-02    |    2 |       27 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 35 |  [NULL] |           483 |         37 |          [NULL] | 2004-09-02    | 11:24:35        | 2004-09-14         | 2004-09-02    |    2 |       28 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 36 |  [NULL] |           483 |         37 |          [NULL] | 2004-09-02    | 11:56:03        | 2004-09-14         | 2004-09-02    |    2 |       29 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 37 |  [NULL] |           483 |         37 |          [NULL] | 2004-09-02    | 12:58:04        | 2004-09-04         | 2004-09-02    |    2 |       30 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 38 |  [NULL] |           483 |         37 |          [NULL] | 2004-09-02    | 12:59:36        | 2004-09-04         | 2004-09-02    |    2 |       31 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 39 |  [NULL] |           483 |         37 |          [NULL] | 2004-09-02    | 13:00:07        | 2004-09-05         | [NULL]        |    1 |       32 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 40 |  [NULL] |           482 |         37 |          [NULL] | 2004-09-02    | 13:13:40        | 2004-09-04         | 2004-09-02    |    2 |       33 | borisb     | 2004-09-02 | borisb     | 2004-09-02 |     [NULL] |
|                 41 |  [NULL] |           406 |         37 |          [NULL] | 2004-09-03    | 21:29:05        | 2004-09-05         | [NULL]        |    1 |   [NULL] | borisb     | 2004-09-03 | borisb     | 2004-09-03 |     [NULL] |
+--------------------+---------+---------------+------------+-----------------+---------------+-----------------+--------------------+---------------+------+----------+------------+------------+------------+------------+------------+

Suggested fix:
I'm worried that such a simple statement does not work...

There is a fix that I include *any* other attribute from the table (no matter if I'm using it or not, but...). I've got an application with many thousand lines of code and....

I'm really worried that such a statement does not work...
[6 Sep 2004 11:22] MySQL Verification Team 
Verified on the latest BK 5.0 tree.
[7 Oct 2004 18:33] Heikki Tuuri 
Victoria,

is the bug present in 4.0 or 4.1?

Regards,

Heikki
[7 Oct 2004 19:29] MySQL Verification Team 
table structure and data

Attachment: dump.txt (text/plain), 6.56 KiB.
[7 Oct 2004 19:32] MySQL Verification Team 
No, I was able reproduce it only on 5.0.
I've uploaded file dump.sql with CREATE TABLE and INSERT statements. 
ALTER TABLE .. TYPE=MyISAM fixes this problem.
[7 Oct 2004 19:43] Heikki Tuuri 
Victoria,

ok then the bug is probably in MySQL. InnoDB is the same in 4.1 as in 5.0.

Thank you,

Heikki
[30 Nov 2004 23:02] Heikki Tuuri 
Sergey P,

see the email I sent to you. Looks like the ROR code cannot handle 'refs' (row id's) that it gets from ::position() correctly.

Regards,

Heikki
[2 Dec 2004 12:42] Sergey Petrunya 
Heikki,
  the ROR code can hande 'refs' it gets from ::position. The problem is that when one performs an 'index only' scan with innodb, innodb resets NULL mask for the entire row. Other handlers don't do that. I'll be commiting the patch soon.
[3 Dec 2004 11:29] Sergey Petrunya 
ChangeSet@1.1703, 2004-12-03 14:25:07+03:00, sergefp@mysql.com
[12 Jan 2005 21:41] Sergey Petrunya 
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html