Bug #9705 Too few results compared to mysql client
Submitted: 7 Apr 2005 3:44 Modified: 5 May 2005 12:41
Reporter: o s Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.10a OS:
Assigned to: Konstantin Osipov CPU Architecture:Any

[7 Apr 2005 3:44] o s
Description:
I have a query that yields 9 rows when using connector/J with a PreparedStatement vs 10 rows when using a Statement or the MySQL client.

Actually, this behavior continues even if the query is larger -- after 9 rows, all others aren't returned. 

How to repeat:
1. Create the necessary table using the data dump below.

2. Execute the java code below. 

3. The output from the java code includes the (correct) fully expanded statement (without the ? placeholders). Copy and paste this into a mysql client. Notice that 10 rows are returned vs just 9 using connector/J. 

------------BEGIN DATA DUMP
-- MySQL dump 9.11
--
-- Host: localhost    Database: zzz
-- ------------------------------------------------------
-- Server version	4.1.10a

--
-- Table structure for table `games`
--

CREATE TABLE games (
  gameId mediumint(8) unsigned NOT NULL auto_increment,
  home char(3) NOT NULL default '',
  away char(3) NOT NULL default '',
  homeScore tinyint(3) unsigned default NULL,
  awayScore tinyint(3) unsigned default NULL,
  week tinyint(2) unsigned default NULL,
  season smallint(4) unsigned default NULL,
  `time` datetime default NULL,
  isFinalScore tinyint(1) default NULL,
  PRIMARY KEY  (gameId),
  UNIQUE KEY homeSeasonWeek (home,season,week),
  UNIQUE KEY awaySeasonWeek (away,season,week),
  KEY seasonWeek (season,week)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `games`
--

INSERT INTO games VALUES (546,'ATL','STL',34,17,2,2004,'2004-09-19 13:00:00',1);
INSERT INTO games VALUES (654,'WAS','TB',16,10,1,2004,'2004-09-12 13:00:00',1);
INSERT INTO games VALUES (679,'DEN','KC',34,24,1,2004,'2004-09-12 20:30:00',1);
INSERT INTO games VALUES (682,'HOU','SD',20,27,1,2004,'2004-09-12 13:00:00',1);
INSERT INTO games VALUES (689,'NYJ','CIN',31,24,1,2004,'2004-09-12 13:00:00',1);
INSERT INTO games VALUES (704,'BUF','JAC',10,13,1,2004,'2004-09-12 13:00:00',1);
INSERT INTO games VALUES (707,'MIA','TEN',7,17,1,2004,'2004-09-12 13:00:00',1);
INSERT INTO games VALUES (713,'STL','ARI',17,10,1,2004,'2004-09-12 13:00:00',1);
INSERT INTO games VALUES (715,'PHI','NYG',31,17,1,2004,'2004-09-12 16:15:00',1);
INSERT INTO games VALUES (729,'SF','ATL',19,21,1,2004,'2004-09-12 16:15:00',1);
-------------------------END DATA DUMP

--------------------------BEGIN JAVA CODE
final int[] seasons = new int[]{ 2004, 2004, 2004, 2004, 2004, 2004, 2004, 2004,
            2004, 2004, };
        final int[] weeks = new int[]{ 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, };
        final String[] teams = new String[]{ "WAS", "DEN", "HOU", "NYJ", "BUF", "MIA",
            "STL", "PHI", "SF ", "ATL", };

        Connection conn = null;

        try {
            Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
            conn = DriverManager.getConnection( "jdbc:mysql://:3306/test", "", "" );
            PreparedStatement stmt = conn
                .prepareStatement( "SELECT * FROM games WHERE ( home = ? AND season = ? AND week = ? ) OR ( home = ? AND season = ? AND week = ? ) OR ( home = ? AND season = ? AND week = ? ) OR ( home = ? AND season = ? AND week = ? ) OR ( home = ? AND season = ? AND week = ? ) OR ( home = ? AND season = ? AND week = ? ) OR ( home = ? AND season = ? AND week = ? ) OR ( home = ? AND season = ? AND week = ? ) OR ( home = ? AND season = ? AND week = ? ) OR ( home = ? AND season = ? AND week = ? );" );

            for ( int i = 0; i < seasons.length; i++ ) {
                stmt.setString( ( i * 3 ) + 1, teams[ i ] );
                stmt.setInt( ( i * 3 ) + 2, seasons[ i ] );
                stmt.setInt( ( i * 3 ) + 3, weeks[ i ] );
            }

            System.out.println( stmt.toString() );
            ResultSet rs = stmt.executeQuery();
            int numRows = 0;

            while ( rs.next() ) {
                numRows++;
            }

            System.out.println( numRows + " rows" );
        }
        catch ( SQLException e ) {
            e.printStackTrace();
        }
        finally {
            conn.close();
        }
---------------------------END JAVA CODE

------------------------BEGIN FULLY-EXPANDED SQL
SELECT * FROM games WHERE ( home = 'WAS' AND season = 2004 AND week = 1 ) OR ( home = 'DEN' AND season = 2004 AND week = 1 ) OR ( home = 'HOU' AND season = 2004 AND week = 1 ) OR ( home = 'NYJ' AND season = 2004 AND week = 1 ) OR ( home = 'BUF' AND season = 2004 AND week = 1 ) OR ( home = 'MIA' AND season = 2004 AND week = 1 ) OR ( home = 'STL' AND season = 2004 AND week = 1 ) OR ( home = 'PHI' AND season = 2004 AND week = 1 ) OR ( home = 'SF ' AND season = 2004 AND week = 1 ) OR ( home = 'ATL' AND season = 2004 AND week = 2 );
-------------------------------END FULLY-EXPANDED SQL
[7 Apr 2005 4:13] Mark Matthews
Seems to be a server issue, as the following testcase demonstrates:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 167 to server version: 4.1.10-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE games (
    ->   gameId mediumint(8) unsigned NOT NULL auto_increment,
    ->   home char(3) NOT NULL default '',
    ->   away char(3) NOT NULL default '',
    ->   homeScore tinyint(3) unsigned default NULL,
    ->   awayScore tinyint(3) unsigned default NULL,
    ->   week tinyint(2) unsigned default NULL,
    ->   season smallint(4) unsigned default NULL,
    ->   `time` datetime default NULL,
    ->   isFinalScore tinyint(1) default NULL,
    ->   PRIMARY KEY  (gameId),
    ->   UNIQUE KEY homeSeasonWeek (home,season,week),
    ->   UNIQUE KEY awaySeasonWeek (away,season,week),
    ->   KEY seasonWeek (season,week)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO games VALUES (546,'ATL','STL',34,17,2,2004,'2004-09-19
    '> 13:00:00',1);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO games VALUES (654,'WAS','TB',16,10,1,2004,'2004-09-12
    '> 13:00:00',1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO games VALUES (679,'DEN','KC',34,24,1,2004,'2004-09-12
    '> 20:30:00',1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO games VALUES (682,'HOU','SD',20,27,1,2004,'2004-09-12
    '> 13:00:00',1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO games VALUES (689,'NYJ','CIN',31,24,1,2004,'2004-09-12
    '> 13:00:00',1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO games VALUES (704,'BUF','JAC',10,13,1,2004,'2004-09-12
    '> 13:00:00',1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO games VALUES (707,'MIA','TEN',7,17,1,2004,'2004-09-12
    '> 13:00:00',1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO games VALUES (713,'STL','ARI',17,10,1,2004,'2004-09-12
    '> 13:00:00',1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO games VALUES (715,'PHI','NYG',31,17,1,2004,'2004-09-12
    '> 16:15:00',1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO games VALUES (729,'SF','ATL',19,21,1,2004,'2004-09-12
    '> 16:15:00',1);
Query OK, 1 row affected (0.00 sec)

mysql> prepare foo from "SELECT * FROM games WHERE ( home = ?
    "> AND season = ? AND week = ? ) OR ( home = ? AND season = ? AND week = ? )    "> OR ( home = ? AND season = ? AND week = ? ) OR ( home = ? AND season = ?
    "> AND week = ? ) OR ( home = ? AND season = ? AND week = ? ) OR ( home = ?
    "> AND season = ? AND week = ? ) OR ( home = ? AND season = ? AND week = ? )    "> OR ( home = ? AND season = ? AND week = ? ) OR ( home = ? AND season = ?
    "> AND week = ? ) OR ( home = ? AND season = ? AND week = ? )";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @param_1='WAS';
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_2=2004;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_3=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_4='DEN';
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_5=2004;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_6=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_7='HOU';
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_8=2004;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_9=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_10='NYJ';
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_11=2004;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_12=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_13='BUF';
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_14=2004;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_15=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_16='MIA';
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_17=2004;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_18=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_19='STL';
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_20=2004;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_21=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_22='PHI';
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_23=2004;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_24=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_25='SF ';
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_26=2004;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_27=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_28='ATL';
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_29=2004;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param_30=2;
Query OK, 0 rows affected (0.00 sec)

mysql> execute foo using @param_1, @param_2, @param_3, @param_4, @param_5, @param_6, @param_7, @param_8, @param_9, @param_10, @param_11, @param_12, @param_13, @param_14, @param_15, @param_16, @param_17, @param_18, @param_19, @param_20, @param_21, @param_22, @param_23, @param_24, @param_25, @param_26, @param_27, @param_28, @param_29, @param_30;
+--------+------+------+-----------+-----------+------+--------+---------------------+--------------+
| gameId | home | away | homeScore | awayScore | week | season | time      | isFinalScore |
+--------+------+------+-----------+-----------+------+--------+---------------------+--------------+
|    654 | WAS  | TB   |        16 |        10 |    1 |   2004 | 2004-09-12 13:00:00 |            1 |
|    679 | DEN  | KC   |        34 |        24 |    1 |   2004 | 2004-09-12 20:30:00 |            1 |
|    682 | HOU  | SD   |        20 |        27 |    1 |   2004 | 2004-09-12 13:00:00 |            1 |
|    689 | NYJ  | CIN  |        31 |        24 |    1 |   2004 | 2004-09-12 13:00:00 |            1 |
|    704 | BUF  | JAC  |        10 |        13 |    1 |   2004 | 2004-09-12 13:00:00 |            1 |
|    707 | MIA  | TEN  |         7 |        17 |    1 |   2004 | 2004-09-12 13:00:00 |            1 |
|    713 | STL  | ARI  |        17 |        10 |    1 |   2004 | 2004-09-12 13:00:00 |            1 |
|    715 | PHI  | NYG  |        31 |        17 |    1 |   2004 | 2004-09-12 16:15:00 |            1 |
|    729 | SF   | ATL  |        19 |        21 |    1 |   2004 | 2004-09-12 16:15:00 |            1 |
+--------+------+------+-----------+-----------+------+--------+---------------------+--------------+
9 rows in set (0.02 sec)

mysql> SELECT * FROM games WHERE ( home = 'WAS' AND season = 2004 AND week = 1 )    -> OR ( home = 'DEN' AND season = 2004 AND week = 1 ) OR ( home = 'HOU' AND
    -> season = 2004 AND week = 1 ) OR ( home = 'NYJ' AND season = 2004 AND week    -> = 1 ) OR ( home = 'BUF' AND season = 2004 AND week = 1 ) OR ( home = 'MIA'
    -> AND season = 2004 AND week = 1 ) OR ( home = 'STL' AND season = 2004 AND
    -> week = 1 ) OR ( home = 'PHI' AND season = 2004 AND week = 1 ) OR ( home =    -> 'SF ' AND season = 2004 AND week = 1 ) OR ( home = 'ATL' AND season = 2004
    -> AND week = 2 );
+--------+------+------+-----------+-----------+------+--------+---------------------+--------------+
| gameId | home | away | homeScore | awayScore | week | season | time      | isFinalScore |
+--------+------+------+-----------+-----------+------+--------+---------------------+--------------+
|    654 | WAS  | TB   |        16 |        10 |    1 |   2004 | 2004-09-12 13:00:00 |            1 |
|    679 | DEN  | KC   |        34 |        24 |    1 |   2004 | 2004-09-12 20:30:00 |            1 |
|    682 | HOU  | SD   |        20 |        27 |    1 |   2004 | 2004-09-12 13:00:00 |            1 |
|    689 | NYJ  | CIN  |        31 |        24 |    1 |   2004 | 2004-09-12 13:00:00 |            1 |
|    704 | BUF  | JAC  |        10 |        13 |    1 |   2004 | 2004-09-12 13:00:00 |            1 |
|    707 | MIA  | TEN  |         7 |        17 |    1 |   2004 | 2004-09-12 13:00:00 |            1 |
|    713 | STL  | ARI  |        17 |        10 |    1 |   2004 | 2004-09-12 13:00:00 |            1 |
|    715 | PHI  | NYG  |        31 |        17 |    1 |   2004 | 2004-09-12 16:15:00 |            1 |
|    729 | SF   | ATL  |        19 |        21 |    1 |   2004 | 2004-09-12 16:15:00 |            1 |
|    546 | ATL  | STL  |        34 |        17 |    2 |   2004 | 2004-09-19 13:00:00 |            1 |
+--------+------+------+-----------+-----------+------+--------+---------------------+--------------+
10 rows in set (0.00 sec)

mysql>
[12 Apr 2005 20:38] o s
changin the version to reflect the server version used rather than connector/J version
[13 Apr 2005 22:57] Jorge del Conde
Thanks for your bug report.

Verified w/4.1.11 from bk
[5 May 2005 12:41] Konstantin Osipov
This bug has been fixed along with the fixes for Bug#9096 and Bug#9777.
Feel free to reopen the bug report if you are able to reproduce the bug.