Re: Problem with MAX()
Chris Boget [EMAIL PROTECTED] wrote: What's going on with the MAX() function? Why did it return NULL in the first query above. There were no adding/deleting data between the above queries and yet MySQL didn't pull the MAX from the table until I added the AND assets IS NOT NULL to the query. And yet after I ran that query, attempting to run the first query yields the proper results. Is this a bug with MySQL? I wasn't able to repeat it on my test table. Could you provide a test case? Here is what our table looks like: [skip] SELECT MAX(assets) as assets FROM do_deductibles WHERE currency = 'usd' AND assets IS NOT NULL; SELECT MAX(assets) as assets FROM do_deductibles WHERE currency = 'usd'; First SELECT query above returns NULL, the second returns the proper value as does the third. Yup, I was able to repeat it, but this bug is related only to ISAM table. ISAM table is no longer supported. On MyISAM table MAX() works fine. mysql SELECT MAX(assets) as assets FROM do_deductibles WHERE currency = 'usd'; +--+ | assets | +--+ | 1000 | +--+ 1 row in set (0.00 sec) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with MAX()
Chris Boget [EMAIL PROTECTED] wrote: We are running v4.0.12. Consider the following: mysql SELECT MAX(assets) as assets - FROM do_deductibles - WHERE currency = 'usd'; ++ | assets | ++ | NULL | ++ 1 row in set (0.00 sec) mysql SELECT MAX(assets) as assets - FROM do_deductibles - WHERE currency = 'usd' AND - assets IS NOT NULL; +--+ | assets | +--+ | 1000 | +--+ 1 row in set (0.00 sec) mysql SELECT MAX(assets) as assets - FROM do_deductibles - WHERE currency = 'usd'; +--+ | assets | +--+ | 1000 | +--+ 1 row in set (0.00 sec) What's going on with the MAX() function? Why did it return NULL in the first query above. There were no adding/deleting data between the above queries and yet MySQL didn't pull the MAX from the table until I added the AND assets IS NOT NULL to the query. And yet after I ran that query, attempting to run the first query yields the proper results. Is this a bug with MySQL? I wasn't able to repeat it on my test table. Could you provide a test case? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug with MySQL (was Re: Problem with MAX())
At 16:09 -0500 6/12/03, Chris Boget wrote: CREATE TABLE do_deductibles ( currency varchar(10) NOT NULL default '', assets int(20) NOT NULL default '0', deductible int(10) NOT NULL default '0', do_deductibles_id int(5) NOT NULL auto_increment, PRIMARY KEY (do_deductibles_id), UNIQUE KEY do_deductibles_idx (currency,assets) ) TYPE=ISAM PACK_KEYS=1; This is definitely a bug with MySQL. Where do I report these things? http://bugs.mysql.com/ I tracked the problem down to this: UNIQUE KEY do_deductibles_idx (currency,assets) If I remove this line altogether from the create table and run the queries provided in my previous post, everything works as expected. If I added the above line but removed the key word UNIQUE, everything works as expected. But if I leave the above line in the create query in whole, the first MAX query that is run return NULL as does every query there after until I qualify the MAX column to be IS NOT NULL then the query and all subsequent queries thereafter work as expected. Chris -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with MAX()
We are running v4.0.12. Consider the following: mysql SELECT MAX(assets) as assets - FROM do_deductibles - WHERE currency = 'usd'; ++ | assets | ++ | NULL | ++ 1 row in set (0.00 sec) mysql SELECT MAX(assets) as assets - FROM do_deductibles - WHERE currency = 'usd' AND - assets IS NOT NULL; +--+ | assets | +--+ | 1000 | +--+ 1 row in set (0.00 sec) mysql SELECT MAX(assets) as assets - FROM do_deductibles - WHERE currency = 'usd'; +--+ | assets | +--+ | 1000 | +--+ 1 row in set (0.00 sec) What's going on with the MAX() function? Why did it return NULL in the first query above. There were no adding/deleting data between the above queries and yet MySQL didn't pull the MAX from the table until I added the AND assets IS NOT NULL to the query. And yet after I ran that query, attempting to run the first query yields the proper results. Is this a bug with MySQL? thnx, Chris mysql,query,blah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Minor install problem w/max-4.0.2
Concerning mysql-max-4.0.2-alpha-pc-linux-gnu-i686.tar.gz on SuSE 7.3 If you cd /usr/local and unpack the tarball then create a symlink to mysql, cd into mysql and run ./bin/mysqld.safe the daemon can't locate mysql-max-4.0.2-alpha-pc-linux-gnu-i686/share/mysql/english/errmsg.sys. If you remove the symlink and mv the distro name dir to mysql, then it works. Larry Irwin CCA Medical - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem with max
hello in delphi the type for max(date) if tstring. the only problem is there is no CAST in mysql 3.23.49 bye - Original Message - From: Johnny Withers [EMAIL PROTECTED] To: 'Jean Bernard' [EMAIL PROTECTED] Sent: Thursday, April 25, 2002 2:46 PM Subject: RE: problem with max I'm looking at this right now on an InnoDB table.. Only problem is I really have no way to tell the type of what mysql is returning when I do a max(date). Since all the date functions work on strings as long as they are formatted as dates.. How are you telling which type is returned? - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 -Original Message- From: Jean Bernard [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 24, 2002 5:06 PM To: [EMAIL PROTECTED] Subject: problem with max i write a program using MyIsam,and i want uses Innodb. the query: select max(date_comp) as mdate from pieces on MyIsam mdate is a date. on InnoDB mdate is string. win2000 delphi6 zeosdbo it's normal? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
problem with max
i write a program using MyIsam,and i want uses Innodb. the query: select max(date_comp) as mdate from pieces on MyIsam mdate is a date. on InnoDB mdate is string. win2000 delphi6 zeosdbo it's normal? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with max() on select
I have a database of bids on auctions that I keep separate from the auction information. The problem comes when I attempt to generate a list of auctions with highest bid information. I set the database for bids like this: ANum, varchar(20), the auction number Bidders, int(4), number of bidders at time of this bid Bidder, varchar(50), username of current bidder CPrice, decimal(9,2), current bid Typical records: 12345 1 bidder1 20.00 12345 2 bidder2 25.00 12345 3 bidder3 30.00 I tried: select ANum, max(Bidders), Bidder, CPrice from Bids group by ANum and it returns: 12345 3 bidder1 20.00 which gives me the higest bid number, but the information from the first bid in the database. How can I retrieve the records that reflect the highest bids for each auction??? Thanx, Jim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with max() on select
On Friday 17 August 2001 07:13, Jim Lynn wrote: I have a database of bids on auctions that I keep separate from the auction information. The problem comes when I attempt to generate a list of auctions with highest bid information. I set the database for bids like this: ANum, varchar(20), the auction number Bidders, int(4), number of bidders at time of this bid Bidder, varchar(50), username of current bidder CPrice, decimal(9,2), current bid Typical records: 12345 1 bidder1 20.00 12345 2 bidder2 25.00 12345 3 bidder3 30.00 I tried: select ANum, max(Bidders), Bidder, CPrice from Bids group by ANum and it returns: 12345 3 bidder1 20.00 which gives me the higest bid number, but the information from the first bid in the database. How can I retrieve the records that reflect the highest bids for each auction??? AFAIK this can only be done in a single SQL statement with a subselect, eg: SELECT * FROM bids WHERE Bidder IN (SELECT MAX(Bidders) FROM bids GROUP BY ANum) Unfortunately MySQL does not yet support subselect (see: http://www.mysql.com/doc/M/i/Missing_Sub-selects.html ), so the only way round this I can see would be on the application side, e.g. SELECT MAX(Bidders), Bidder FROM bids GROUP BY ANum then step through the result set selecting the row for each auction individually using Bidder as the selection criterium. HTH in some way. (If there is a more elegant solution to this problem in MySQL I'd love to hear it) Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB Problem with MAX() and multipart primary keys
Guenther Pewny writes:
Hi folks,
I have a problem with BDB tables. Please look at this short mysql session:
mysql create table sample (
id1 int(8) not null,
id2 int(8) not null,
primary key (id1,id2))
type=BDB;
Query OK, 0 rows affected (0.67 sec)
mysql insert into sample values (1, 2);
Query OK, 1 row affected (0.07 sec)
mysql select id2 from sample where id1=1;
+-+
| id2 |
+-+
| 2 |
+-+
1 row in set (0.00 sec)
mysql select min(id2) from sample where id1=1;
+--+
| min(id2) |
+--+
|2 |
+--+
1 row in set (0.00 sec)
mysql select max(id2) from sample where id1=1;
+--+
| max(id2) |
+--+
| NULL |
+--+
1 row in set (0.00 sec)
Is this a bug in mySQL?
Why does MIN() return the correct result, while MAX() does not?
Is it a severe bug which possibly can cause data inconsistency
within the table data or does there exist a "good" workaround for
this problem with only modifying the query?
Thanks
Gnther Pewny
Hi!
I have just tested this with 3.23.33 and it executed just fine !!
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB Problem with MAX() and multipart primary keys
It's a miracle.
Which version of the BDB patch do you use?
I'm using 3.2.9a.
(Previously, there has been a version 3.2.3g...)
Thanks...
Gnther Pewny
Sinisa Milivojevic wrote:
Guenther Pewny writes:
Hi folks,
I have a problem with BDB tables. Please look at this short mysql session:
mysql create table sample (
id1 int(8) not null,
id2 int(8) not null,
primary key (id1,id2))
type=BDB;
Query OK, 0 rows affected (0.67 sec)
mysql insert into sample values (1, 2);
Query OK, 1 row affected (0.07 sec)
mysql select id2 from sample where id1=1;
+-+
| id2 |
+-+
| 2 |
+-+
1 row in set (0.00 sec)
mysql select min(id2) from sample where id1=1;
+--+
| min(id2) |
+--+
|2 |
+--+
1 row in set (0.00 sec)
mysql select max(id2) from sample where id1=1;
+--+
| max(id2) |
+--+
| NULL |
+--+
1 row in set (0.00 sec)
Is this a bug in mySQL?
Why does MIN() return the correct result, while MAX() does not?
Is it a severe bug which possibly can cause data inconsistency
within the table data or does there exist a "good" workaround for
this problem with only modifying the query?
Thanks
Gnther Pewny
Hi!
I have just tested this with 3.23.33 and it executed just fine !!
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB Problem with MAX() and multipart primary keys
Guenther Pewny writes:
It's a miracle.
Which version of the BDB patch do you use?
I'm using 3.2.9a.
(Previously, there has been a version 3.2.3g...)
Thanks...
Gnther Pewny
Hi!
I used latest source tarball from our 3.23 page.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BDB Problem with MAX() and multipart primary keys
Hi folks, I have a problem with BDB tables. Please look at this short mysql session: mysql create table sample ( id1 int(8) not null, id2 int(8) not null, primary key (id1,id2)) type=BDB; Query OK, 0 rows affected (0.67 sec) mysql insert into sample values (1, 2); Query OK, 1 row affected (0.07 sec) mysql select id2 from sample where id1=1; +-+ | id2 | +-+ | 2 | +-+ 1 row in set (0.00 sec) mysql select min(id2) from sample where id1=1; +--+ | min(id2) | +--+ |2 | +--+ 1 row in set (0.00 sec) mysql select max(id2) from sample where id1=1; +--+ | max(id2) | +--+ | NULL | +--+ 1 row in set (0.00 sec) Is this a bug in mySQL? Why does MIN() return the correct result, while MAX() does not? Is it a severe bug which possibly can cause data inconsistency within the table data or does there exist a "good" workaround for this problem with only modifying the query? Thanks Gnther Pewny - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
