bug in simple select, what is going on?
hi, i am running a join query between the german_english table and the user_todo_german_english tables, to figure out which words a user is studying. the key i am using is a combination of wordid + pos + posn (that is part of speech == pos) however, i am not able to correctly do the join because of improper results. please see the following mysql select * from user_todo_german_english where date_col '2006-04-22 07:00:00'; +-++--+--+-+ | mb_id | wordid | posn | pos | date_col| +-++--+--+-+ | curious | 94122 |1 |0 | 2006-04-22 07:04:26 | | curious | 327400 |1 |0 | 2006-04-22 07:04:40 | | curious | 327402 |1 |0 | 2006-04-22 07:40:41 | | curious | 59553 |1 |0 | 2006-04-22 07:40:55 | | curious | 86851 |1 |0 | 2006-04-22 08:14:12 | +-++--+--+-+ 5 rows in set (0.00 sec) mysql select wordid,pos,posn from german_english where wordid in (86851,59553); ++--+--+ | wordid | pos | posn | ++--+--+ | 59553 | m|1 | | 86851 | m|1 | ++--+--+ 2 rows in set (0.00 sec) mysql select * from user_todo_german_english where wordid = '86851' and posn = '1' and pos = 'm' AND mb_id='curious'; +-++--+--+-+ | mb_id | wordid | posn | pos | date_col| +-++--+--+-+ | curious | 86851 |1 |0 | 2006-04-22 08:14:12 | +-++--+--+-+ 1 row in set, 1 warning (0.00 sec) mysql select * from user_todo_german_english where wordid = '86851' and posn = '1' and pos = 'm' AND mb_id='curious'; +-++--+--+-+ | mb_id | wordid | posn | pos | date_col| +-++--+--+-+ | curious | 86851 |1 |0 | 2006-04-22 08:14:12 | +-++--+--+-+ 1 row in set, 1 warning (0.00 sec) mysql clearly, pos is ZERO in the database, and the letter 'm' in the query. or is my brain farting again? -- my site a href=http://www.myowndictionary.com;myowndictionary/a was made to help students of many languages learn them faster. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bug in simple select, what is going on?
2006-04-22 (토), 08:43 +0900, kmh496 쓰시길: hi, i am running a join query between the german_english table and the user_todo_german_english tables, to figure out which words a user is studying. the key i am using is a combination of wordid + pos + posn (that is part of speech == pos) however, i am not able to correctly do the join because of improper results. please see the following mysql select * from user_todo_german_english where date_col '2006-04-22 07:00:00'; +-++--+--+-+ | mb_id | wordid | posn | pos | date_col| +-++--+--+-+ | curious | 94122 |1 |0 | 2006-04-22 07:04:26 | | curious | 327400 |1 |0 | 2006-04-22 07:04:40 | | curious | 327402 |1 |0 | 2006-04-22 07:40:41 | | curious | 59553 |1 |0 | 2006-04-22 07:40:55 | | curious | 86851 |1 |0 | 2006-04-22 08:14:12 | +-++--+--+-+ 5 rows in set (0.00 sec) mysql select wordid,pos,posn from german_english where wordid in (86851,59553); ++--+--+ | wordid | pos | posn | ++--+--+ | 59553 | m|1 | | 86851 | m|1 | ++--+--+ 2 rows in set (0.00 sec) mysql select * from user_todo_german_english where wordid = '86851' and posn = '1' and pos = 'm' AND mb_id='curious'; +-++--+--+-+ | mb_id | wordid | posn | pos | date_col| +-++--+--+-+ | curious | 86851 |1 |0 | 2006-04-22 08:14:12 | +-++--+--+-+ 1 row in set, 1 warning (0.00 sec) mysql select * from user_todo_german_english where wordid = '86851' and posn = '1' and pos = 'm' AND mb_id='curious'; +-++--+--+-+ | mb_id | wordid | posn | pos | date_col| +-++--+--+-+ | curious | 86851 |1 |0 | 2006-04-22 08:14:12 | +-++--+--+-+ 1 row in set, 1 warning (0.00 sec) mysql clearly, pos is ZERO in the database, and the letter 'm' in the query. or is my brain farting again? -- my site a href=http://www.myowndictionary.com;myowndictionary/a was made to help students of many languages learn them faster. further information mysql 2035 0.4 1.6 22548 17088 ?Sl Apr09 78:46 /usr/local/mysql-5.1.7-beta/libexec/mysqld --defaults-file=/etc/my.cnf.dict_explicit --basedir=/usr/local/mysql-5.1.7-beta --datadir=/bup/mysqldata/data --socket=/tmp/mysql.sock --log-output=FILE the same results from the commandline using the old mysql client [EMAIL PROTECTED] current]$ mysql --version mysql Ver 14.7 Distrib 4.1.11, for redhat-linux-gnu (i686) and using php. the databse encoding is utf8; but i get the following from php after calling 'set names utf8' and from the clien without saying so. the table structure is mysql show create table user_todo_german_english; +--+--- + | Table| Create Table | +--+--- + | user_todo_german_english | CREATE TABLE `user_todo_german_english` ( `mb_id` varchar(25) default NULL, `wordid` int(11) default NULL, `posn` tinyint(3) default '1', `pos` tinyint(3) default '0', `date_col` datetime NOT NULL default '2006-03-23 22:50:02' ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +--+--- + 1 row in set (0.00 sec) mysql -- my site a href=http://www.myowndictionary.com;myowndictionary/a was made to help students of many languages learn them faster. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SPAM] Re: bug in simple select, what is going on?
2006-04-22 (토), 00:49 +0100, Philippe Poelvoorde 쓰시길: Hi, mysql select * from user_todo_german_english where wordid = '86851' and posn = '1' and pos = 'm' AND mb_id='curious'; +-++--+--+-+ | mb_id | wordid | posn | pos | date_col| +-++--+--+-+ | curious | 86851 |1 |0 | 2006-04-22 08:14:12 | +-++--+--+-+ 1 row in set, 1 warning (0.00 sec) mysql select * from user_todo_german_english where wordid = '86851' and posn = '1' and pos = 'm' AND mb_id='curious'; +-++--+--+-+ | mb_id | wordid | posn | pos | date_col| +-++--+--+-+ | curious | 86851 |1 |0 | 2006-04-22 08:14:12 | +-++--+--+-+ 1 row in set, 1 warning (0.00 sec) there are clear warnings, what are they ? mysql show warnings; +-+--+---+ | Level | Code | Message | +-+--+---+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'm' | +-+--+---+ 1 row in set (0.00 sec) mysql -- my site a href=http://www.myowndictionary.com;myowndictionary/a was made to help students of many languages learn them faster. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
