| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.10a | OS: | |
| Assigned to: | Konstantin Osipov | CPU Architecture: | Any |
[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
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