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 3:44]
o s
[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