Re: subquery fails when a NOT IN operator tests a subset with NULL valu
In the last episode (Mar 16), Giuseppe Maxia said: Here is a description of what looks like a serious bug. This is related to bugs #7294 and #6247 Tested against mysql 4.1.9 and 4.1.10. Description: operator NOT IN fails when a subquery returns one or more NULL values. How-To-Repeat: simple proof of concept: mysql select 1 in (1,null,3); +-+ | 1 in (1,null,3) | +-+ | 1 | +-+ #OK mysql select 2 not in (1,null,3); +-+ | 2 not in (1,null,3) | +-+ |NULL | +-+ # NOT OK This looks okay to me, according to http://dev.mysql.com/doc/mysql/en/comparison-operators.html#id2940868 : To comply with the SQL standard, from MySQL 4.1 on IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL. 2 doesn't match 1, NULL, or 3, and there's a NULL in the list, so the IN expression must return NULL. NOT(NULL) is still NULL, so the entire expression returns NULL. Subqueries using IN() may not be the same as the IN() expression (I rarely use subqueries so I don't know); they are documented at http://dev.mysql.com/doc/mysql/en/any-in-some-subqueries.html and http://dev.mysql.com/doc/mysql/en/all-subqueries.html . -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subquery fails when a NOT IN operator tests a subset with NULL valu
Dan Nelson wrote: In the last episode (Mar 16), Giuseppe Maxia said: Here is a description of what looks like a serious bug. This is related to bugs #7294 and #6247 Tested against mysql 4.1.9 and 4.1.10. Description: operator NOT IN fails when a subquery returns one or more NULL values. How-To-Repeat: simple proof of concept: mysql select 1 in (1,null,3); +-+ | 1 in (1,null,3) | +-+ | 1 | +-+ #OK mysql select 2 not in (1,null,3); +-+ | 2 not in (1,null,3) | +-+ |NULL | +-+ # NOT OK This looks okay to me, according to http://dev.mysql.com/doc/mysql/en/comparison-operators.html#id2940868 : To comply with the SQL standard, from MySQL 4.1 on IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL. Thanks for your comment. I knew about that page, and probably I was a bit too quick when I used this example as proof of concept. Actually, the real problem happens only with subqueries, as I reported in the rest of my previous message. 2 doesn't match 1, NULL, or 3, and there's a NULL in the list, so the IN expression must return NULL. NOT(NULL) is still NULL, so the entire expression returns NULL. Subqueries using IN() may not be the same as the IN() expression (I rarely use subqueries so I don't know); they are documented at http://dev.mysql.com/doc/mysql/en/any-in-some-subqueries.html and http://dev.mysql.com/doc/mysql/en/all-subqueries.html . The whole point is actually in subqueries, not when using IN or NOT IN in a normal query. The bug occurs when a NOT IN is used in a subquery as a LEFT JOIN replacement. SELECT something from t1 where column1 NOT IN (SELECT nullable_column from t2); BTW, I posted to this list by mistake. I re-posted an amended version of the same bug report to the bugs list. Giuseppe Maxia -- Giuseppe Maxia CTO http://www.StarData.it MySQL Certified Professional __ __ __ ___ / / / /__ _/ / _ (_-/ __/ _ `/ __/ _ / _ `/ __/ _ `/ /___/\__/\_,_/_/ \_,_/\_,_/\__/\_,_/ Database is our business -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
change a column type and innodb foreign key constraints
Hi, I have a column 'id' within a table : CREATE TABLE `reference` ( *`*id*`* smallint(5) unsigned NOT NULL auto_increment, `study_name` text, `author` text NOT NULL, `date` date NOT NULL default '-00-00', `reference` varchar(250) NOT NULL default '', `title` varchar(250) NOT NULL default '', `pmid` int(15) default NULL, `project` varchar(35) default NULL, `abstract` text, `datasource` smallint(5) unsigned default NULL, PRIMARY KEY (`id`), KEY `id` (`id`) ) TYPE=InnoDB that i need to change from smallint to int There are a number of foreign key constraints from other columns in other tables on this column: eg CREATE TABLE `monogenic` ( `id` smallint(5) unsigned NOT NULL default '0', `exp_design` varchar(50) default NULL, `disease` varchar(50) default NULL, `omim` varchar(20) default NULL, `phenotype_ID` smallint(5) unsigned NOT NULL default '0', `pop` varchar(200) default NULL, `num_peds` int(7) unsigned default NULL, `affected` int(7) unsigned default NULL, `unaffected` int(7) unsigned default NULL, `mut_type` varchar(50) default NULL, `mut_loc` varchar(50) default NULL, `gene_ID` mediumint(8) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`gene_ID`), KEY `phenotype_ID` (`phenotype_ID`), KEY `gene_ID` (`gene_ID`), CONSTRAINT `0_178` FOREIGN KEY (`gene_ID`) REFERENCES `gene` (`id`), CONSTRAINT `0_179` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE ) TYPE=InnoDB Upon trying to modify the id columns in the reference table, i'm getting the following mysql alter table reference modify id int; ERROR 1025: Error on rename of './nugenob/#sql-2ed1_e001' to './nugenob/reference' (errno: 150) mysql It looks from googling as though I need to drop all foreign key constraints on this column, perform the change and then reestablish the foreign keys. Could anyone confirm or advise of a better solution? cheers Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To extend InnoDB table size
Many many thanks... I was in confusion. But how can I exten the table size. Regards Naveen - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Naveen C Joshi [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, March 15, 2005 7:52 PM Subject: Re: To extend InnoDB table size Naveen C Joshi wrote: Hi, I have a InnoDB database with MySQL version 4.1.0-alpha installed on my RedHat Linux 9.0. The data files name is like *.frm, *.MYD, *.MYI. These are MyIsam, not InnoDB. Currently the table size is fixed 4GB and I want to extend it 10GB. Could you please give me perfect command to extend my table size. The is my production server so I am restricted to do any exercise. Thanks Naveen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Regrading Heading off and Pause on
Hi, Is there any equivalent of set head off, set pause on in mysql 4.0.21. set head off, set pause on work fine in oracle. Please help me in this. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change a column type and innodb foreign key constraints
It looks from googling as though I need to drop all foreign key constraints on this column, perform the change and then reestablish the foreign keys. Could anyone confirm or advise of a better solution? That is the way ! :) You need to drop the constrains... Alter `reference` and make `id` INT You will need to alter the `monogenic` table as well... making it's `id` INT also... Recreate constrains... Though... I do not know if you need to drop ALL constrains or only the one that ties the tables: thisCONSTRAINT `0_179` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE/this Gabriel PREDA www.amr.ro www.lgassociations.info www.falr.ro dev.falr.ro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problems with NULL
Consider this: v_age, v_name, v_eno are 3 variables. Exec sql Select age into :v_age from temp where name = :v_name and eno = :v_eno; Suppose: v_name =rama v_eno = NULL Table: temp Suppose the values in my table are, Name = rama Eno = NULL Age = 23 The prob is it does not retrieve 23 from the table. The prob is I cannot give eno is NULL as I dont know (in practice) whether the field will be NULL. Digvijoy Chatterjee
mysqlbug
Hi I was installing MySQL on Linux. At that time it was giving some problem when I did ./configure. Your libc libraries are not 100% compatible with MySQL version. Mysqld should work normally with the exception that hostname resolving will not work. This means that you should use IP address instead of host name when specifying Mysql privileges. Please let me know how to install MySQL successfully on Linux. Waiting for your valuable reply. Regards Sheela. ** This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. **
Re[2]: Optimising COUNT()
Suc COUNT() only counts non-null values. Suc More on topic...if you say COUNT(fieldname), you say that you want to Suc count all of the non-null values in that column. COUNT(1) or COUNT(2) or COUNT(any_constant) should count all records (this is how it works for example in oracle) -- Best regards, DebugasRumailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
{Spam?} Cannot start replication - can someone help?
I have two servers, server5.mydomain.com and server8.mydomain.com. I want to replicate one database on server5 to server 8. I did the whole proceedure as recommended in http://dev.mysql.com/doc/mysql/en/replication-howto.html - 6.4. How to Set Up Replication I setup the GRANT statement on server5 for the slave. mysql SHOW GRANTS FOR [EMAIL PROTECTED]; +--- ---+ | Grants for [EMAIL PROTECTED] | +--- ---+ | GRANT RELOAD, SUPER, REPLICATION SLAVE ON *.* TO 'server8'@'MyServer8IP' IDENTIFIED BY PASSWORD 'xxx' | +--- ---+ 1 row in set (0.00 sec) But when I stop and start the slave, I get: 050316 8:56:42 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.04' at position 79, relay log './server8-relay-bin.01' position: 4 050316 8:56:42 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Unknown MySQL server host 'server5.picado.com' (1)' errno: 2005 retry-time: 60 retries: 86400 When I try from the command line, I get it to work without problem: [EMAIL PROTECTED] mysql]# mysql -h server5.domain.com -u server8 -p Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 to server version: 4.1.10-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql Both /etc/my.cnf files are listed below [EMAIL PROTECTED] mysql]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords skip-locking key_buffer = 256M max_allowed_packet = 1M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size= 16M skip-networking sort_buffer=2M log-bin server-id=2 log-slave-updates log-warnings replicate-ignore-db=mysql replicate-do-db=anguillaguide [isamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [EMAIL PROTECTED] mysql]# cat /etc/my.cnf [mysqld] old-passwords max_connections = 500 key_buffer = 16M myisam_sort_buffer_size = 64M join_buffer_size = 2M read_buffer_size = 2M sort_buffer_size = 3M table_cache = 1500 thread_cache_size = 128 wait_timeout = 14400 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 32M query_cache_type = 1 skip-innodb log-bin=mysql-bin server-id=1 [mysqld_safe] open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M Chris Mason Anguilla -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 3/15/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
The code for generating the tables is shown below: create table book ( bookid char(12) not null, dimensions char(15), availability char(30), booktype char(20), publisher char(20), isbn char(20), itemsinstock int(3), price float(5,2), title char(50), toc char(24), picture char(36), primary key (bookid)); create table author ( authorid char(12) not null, name char(24), primary key (authorid)); create table authorbook ( bookid char(12) not null references book, authorid char(12) not null references author, primary key(bookid,authorid)); Basically, I want to get it in the format specified in the message, i.e. if the title is the same, I do not want it to be printed again... Any ideas? From: sol beach [EMAIL PROTECTED] Reply-To: sol beach [EMAIL PROTECTED] To: Xristos Karvouneas [EMAIL PROTECTED] Subject: Re: Query Problem Date: Tue, 15 Mar 2005 12:33:24 -0800 http://www.catb.org/~esr/faqs/smart-questions.html It would help a lot to get answers if you shared the description of all three tables. On Tue, 15 Mar 2005 22:17:52 +0200, Xristos Karvouneas [EMAIL PROTECTED] wrote: Dear All, I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like: Title 1 Author 1 Author 2 Title 2Author 3 Author 4 I have written the following: select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid; hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword). Is there any way I can modify the query so that it does what I want it to do? I look forward to hearing from you soon. Thanks in advance. George _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ FREE pop-up blocking with the new MSN Toolbar - get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
The code for generating the tables is shown below: create table book ( bookid char(12) not null, dimensions char(15), availability char(30), booktype char(20), publisher char(20), isbn char(20), itemsinstock int(3), price float(5,2), title char(50), toc char(24), picture char(36), primary key (bookid)); create table author ( authorid char(12) not null, name char(24), primary key (authorid)); create table authorbook ( bookid char(12) not null references book, authorid char(12) not null references author, primary key(bookid,authorid)); Basically, I want to get it in the format specified in the message, i.e. if the title is the same, I do not want it to be printed again... Any ideas? From: gerald_clark [EMAIL PROTECTED] To: Xristos Karvouneas [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Query Problem Date: Tue, 15 Mar 2005 14:49:45 -0600 Xristos Karvouneas wrote: Dear All, I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like: Title 1 Author 1 Author 2 Title 2Author 3 Author 4 I have written the following: select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid; You want to add: ORDER BY title,name; You probably are getting them all, but not in the order you expect. hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword). Is there any way I can modify the query so that it does what I want it to do? I look forward to hearing from you soon. Thanks in advance. George _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrading problems
I want to upgrade mysql client 3.23. to mysql 4.1.10 But when I type rpm -Uvh mysql-client-4.1...rpm It's says that some file from install mysqlrpm conflict with file from package mysql3.23 I try to uninstall package mysql3.23 with the rpm on the cd, but it's doesn't work I have Red hat Enterprise Linus AS How can I fix it Thank Guillaume Chartrand Technicien en informatique Cégep régional de Lanaudière Centre Administratifs, Repentigny Guillaume Chartrand Technicien en informatique Cégep régional de Lanaudière Centre Administratifs, Repentigny
Re: change a column type and innodb foreign key constraints
Gabriel PREDA [EMAIL PROTECTED] wrote on 03/16/2005 06:12:14 AM: It looks from googling as though I need to drop all foreign key constraints on this column, perform the change and then reestablish the foreign keys. Could anyone confirm or advise of a better solution? That is the way ! :) You need to drop the constrains... Alter `reference` and make `id` INT You will need to alter the `monogenic` table as well... making it's `id` INT also... Recreate constrains... Though... I do not know if you need to drop ALL constrains or only the one that ties the tables: thisCONSTRAINT `0_179` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE/this Gabriel PREDA www.amr.ro www.lgassociations.info www.falr.ro dev.falr.ro Yes, Gabriel is right. You do not have to remove ALL of your foreign constraints, only those that include the column you need to change. Imagine the situation that would occur if you had been able to successfully change the definition of the ID column and you didn't un-define your foreign keys or change any of your other referencing columns. You would have had a relationship that was trying to enforce equality between an int on one side and a smallint on the other. Clearly that would be an illegal FK constraint, right? That's why you were not permitted to change the column as it would have made your existing foreign keys illegal. Follow Gabriel's advice, drop the FKs that reference the column you want to change, modify the fields on BOTH ends of your FKs to have matching datatypes, then re-establish your FKs. It may be a little work but that's just part of the job. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: {Spam?} Cannot start replication - can someone help?
Chris Mason wrote: I have two servers, server5.mydomain.com and server8.mydomain.com. I want to replicate one database on server5 to server 8. I did the whole proceedure as recommended in http://dev.mysql.com/doc/mysql/en/replication-howto.html - 6.4. How to Set Up Replication I setup the GRANT statement on server5 for the slave. mysql SHOW GRANTS FOR [EMAIL PROTECTED]; +--- ---+ | Grants for [EMAIL PROTECTED] | +--- ---+ | GRANT RELOAD, SUPER, REPLICATION SLAVE ON *.* TO 'server8'@'MyServer8IP' IDENTIFIED BY PASSWORD 'xxx' | +--- ---+ 1 row in set (0.00 sec) But when I stop and start the slave, I get: 050316 8:56:42 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.04' at position 79, relay log './server8-relay-bin.01' position: 4 050316 8:56:42 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Unknown MySQL server host 'server5.picado.com' (1)' errno: 2005 retry-time: 60 retries: 86400 When I try from the command line, I get it to work without problem: [EMAIL PROTECTED] mysql]# mysql -h server5.domain.com -u server8 -p Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 to server version: 4.1.10-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql You have master set to server5.picado.com, not server5.domain.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
You are confusing data retrieval with data presentation. Sure, SQL can do many things to format data but some data formats are better achieved through the programming language you are using to present this data for viewing. In my opinion, the type of formatting you want to do is one of those tasks. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Xristos Karvouneas [EMAIL PROTECTED] wrote on 03/16/2005 09:03:25 AM: The code for generating the tables is shown below: create table book ( bookid char(12) not null, dimensions char(15), availability char(30), booktype char(20), publisher char(20), isbn char(20), itemsinstock int(3), price float(5,2), title char(50), toc char(24), picture char(36), primary key (bookid)); create table author ( authorid char(12) not null, name char(24), primary key (authorid)); create table authorbook ( bookid char(12) not null references book, authorid char(12) not null references author, primary key(bookid,authorid)); Basically, I want to get it in the format specified in the message, i.e. if the title is the same, I do not want it to be printed again... Any ideas? From: gerald_clark [EMAIL PROTECTED] To: Xristos Karvouneas [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Query Problem Date: Tue, 15 Mar 2005 14:49:45 -0600 Xristos Karvouneas wrote: Dear All, I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like: Title 1 Author 1 Author 2 Title 2Author 3 Author 4 I have written the following: select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid; You want to add: ORDER BY title,name; You probably are getting them all, but not in the order you expect. hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword). Is there any way I can modify the query so that it does what I want it to do? I look forward to hearing from you soon. Thanks in advance. George _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ROW_SIZE or something alike
oix ppl, sorry for the question, but I looked in the Manual online and didn't find nothing that do this that I'm looking for, so I'm hopping someone can help me. I'm looking for some way to know the size each row of a table uses in my database (phisical - real disk space allocated or logical - datasize ignoring compression and any control data, don't mind, anything is better than nothing). The way I was thinking this could be got was with something like: SELECT ROW_SIZE(),* FROM tablename WHERE somefield=somevalue; or SELECT group__id, SUM(ROW_SIZE()) grpsize FROM tablename GROUP BY group__id; This is what I thought would be great to have, but any other way to get a row size would be good enought. This could be both Data and Index size for each row, but data would be good enought. There is anyway to get this information? Thanks, mpneves -- Marco Paulo Neves MySQL Core Certified Linux Certified Professional http://themage.bliker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
You may be able to get something like what you describe using
GROUP_CONCAT(), if you have mysql 4.1, though it would be easier to get a
comma separated list of authors than separate lines. See the manual for
details http://dev.mysql.com/doc/mysql/en/group-by-functions.html.
In my opinion, however, you are confusing presentation of data with how it
is accessed. Usually, presentation is the job of your code, not SQL. If I
were doing this, I would
SELECT book.title, author.name
FROM book
JOIN authorbook ON book.bookid = authorbook.bookid
JOIN author ON authorbook.authorid = author.authorid
ORDER BY book.title, author.name;
to get the data. (Notice I left out DISTINCT. There's something wrong with
the data in your tables if DISTINCT is needed here.) To get the format you
desire when printing the results, my code would only print the value of
book.title if it is different from the value of book.title I previously
printed. Something like (pseudo code):
last_title = ''
for each result_row
{
get title and author from result_row
if title != last_title
{ # first row of a new book, so print the tile
print title
last_title = title
}
else
{ # another author for the same book
print blank space
}
print author
}
That's pretty easy to translate into real code in every language I know.
Michael
Xristos Karvouneas wrote:
The code for generating the tables is shown below:
create table book
(
bookid char(12) not null,
dimensions char(15),
availability char(30),
booktype char(20),
publisher char(20),
isbn char(20),
itemsinstock int(3),
price float(5,2),
title char(50),
toc char(24),
picture char(36),
primary key (bookid));
create table author
(
authorid char(12) not null,
name char(24),
primary key (authorid));
create table authorbook
(
bookid char(12) not null references book,
authorid char(12) not null references author,
primary key(bookid,authorid));
Basically, I want to get it in the format specified in the message, i.e.
if the title is the same, I do not want it to be printed again...
Any ideas?
From: sol beach [EMAIL PROTECTED]
Reply-To: sol beach [EMAIL PROTECTED]
To: Xristos Karvouneas [EMAIL PROTECTED]
Subject: Re: Query Problem
Date: Tue, 15 Mar 2005 12:33:24 -0800
http://www.catb.org/~esr/faqs/smart-questions.html
It would help a lot to get answers if you shared the description of
all three tables.
On Tue, 15 Mar 2005 22:17:52 +0200, Xristos Karvouneas
[EMAIL PROTECTED] wrote:
Dear All,
I am faced with the following problem: I have got three tables -
book,author
and authorbook - containing information about books and authors
(some books
have multiple authors). I want to do a query that would print
information
like:
Title 1 Author 1
Author 2
Title 2Author 3
Author 4
I have written the following:
select distinct title, name
from authorbook,book,author where
authorbook.authorid=author.authorid and
book.bookid=authorbook.bookid;
hoping that it will do what I want, but I am only getting the first
author
for each book (probably because of the distinct keyword).
Is there any way I can modify the query so that it does what I want
it to
do?
I look forward to hearing from you soon.
Thanks in advance.
George
_
Express yourself instantly with MSN Messenger! Download today it's
FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
_
FREE pop-up blocking with the new MSN Toolbar - get it now!
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bdb-no-sync
What are the implications of setting the --bdb-no-sync flag when using the BDB storage engine. There isn't alot of documentation on this. Does this mean that after every transaction, sync will not be called? When will sync be called then? Will setting this flag lead to ACI semantics, ie no durability? If so, when will transactions become durable? Will setting this flag increase the likelyhood of DB corruption? As happens in Postgresql with fsync off. I know in BDB (non MySQL storage engine), setting DB_TXN_NOSYNC on a BDB transaction leads to ACI semantics. It is then up to the app to call DB-sync when it wants durability. How does this map to the BDB/MSQL storage engine? Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Queries inside UDF
Hi folks!
(I hope this is the right list ... if not please tell me where I can submit
this post)
I would like to create a MySQL UDF (i.e. User Defined Function) that embeds
a query; for instance, suppose the UDF is named foobar:
mysql SELECT foobar();
When foobar function receives the control from the MySQL, it attempts to
create a query. To do so it has to connect to DB (since it seems there's no
way to access to current DB connection from a UDF function -- at least I
did'nt find it any way). So the flow of execution is:
SELECT foobar()
1 -- Call foobar
2 - init MySQL
3 - connect to MySQL
4 - create/execute query
5 - get query result
6 - close MySQL connection
7 - return result
After doing step 3 I get the error: Can't connect to MySQL server on
'localhost' (111)
One may argue that error is due to the host permission ...
However, executing the same code used inside the foobar function definition
outside the UDF function (i.e. as a separate executable) all is OK!
So anyone know if is it possible do a query inside a UDF? ... And if it is,
how?!
For everyone who would make a try I've written a C-file very similar to my
...
After compiling the file remeber to do (from the MySQL console):
CREATE FUNCTION foobar RETURNS INTEGER SONAME 'libfoobar.so';
where 'libfoobar.so' is the shared file created from the C-file.
Thanks in advance to everyone
-- Marco
--- BEGIN foobar.c ---
#ifdef __WIN__
typedef unsigned __int64 ulonglong; /* Microsofts 64 bit types */
typedef __int64 longlong;
#else
typedef unsigned long long ulonglong;
typedef long long longlong;
#endif /*__WIN__*/
#include mysql.h
#include stdio.h
#include string.h
#ifdef __cpluscplus
extern C {
#endif
my_bool foobar_init( UDF_INIT* initid, UDF_ARGS* args, char* message );
void foobar_deinit( UDF_INIT* initid );
longlong foobar( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char*
error );
my_bool foobar_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
{
/* empty */
}
void foobar_deinit( UDF_INIT* initid )
{
/* empty */
}
longlong foobar( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char*
error )
{
MYSQL* mysql = NULL;
MYSQL_RES* qryResult = NULL;
const char* query = SELECT COUNT(*) FROM tblfoobar;
longlong retval = 0;
mysql = mysql_init( mysql );
if ( !mysql ) {
fprintf( stderr, Error on Init: %s\n, mysql_error( mysql );
*error = 1;
return retval;
}
if ( !mysql_real_connect( mysql, host user password, database, 0,
NULL, 0 ) ) {
fprintf( stderr, Error on Connect: %s\n, mysql_error( mysql );
*error = 1;
return retval;
}
if ( mysql_real_query( mysql, query, strlen( query ) ) ) {
fprintf( stderr, Error on Query: %s\n, mysql_error( mysql );
*error = 1;
return retval;
}
if ( ( qryResult = mysql_store_result( mysql ) ) ) {
MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;
num_fields = mysql_num_fields( qryResult );
while ( ( row = mysql_fetch_row( qryResult ) ) ) {
unsigned long *lengths = NULL;
lengths = mysql_fetch_lengths( qryResult );
for( i = 0; i num_fields; i++ ) {
fprintf( stderr, [%.*s] , (int) lengths[i], row[i] ? row[i] :
NULL);
retval = strtoll( row[i] );
}
fprintf( stderr, \n );
}
mysql_free_result( qryResult );
}
else {
if ( !mysql_field_count( mysql ) ) {
fprintf( stderr, Error on Storing Query Result: %s\n, mysql_error(
mysql );
*error = 1;
return retval;
}
}
mysql_close( mysql );
return retval;
}
#ifdef __cpluscplus
}
#endif
--- END foobar.c ---
_
Blocca le pop-up pubblicitarie con MSN Toolbar! http://toolbar.msn.it/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Queries inside UDF
On Wednesday, March 16, 2005 09:30, sguazt sguazt wrote: Hi folks! (I hope this is the right list ... if not please tell me where I can submit this post) I would like to create a MySQL UDF (i.e. User Defined Function) that embeds a query; for instance, suppose the UDF is named foobar: mysql SELECT foobar(); When foobar function receives the control from the MySQL, it attempts to create a query. To do so it has to connect to DB (since it seems there's no way to access to current DB connection from a UDF function -- at least I did'nt find it any way). So the flow of execution is: SELECT foobar() 1 -- Call foobar 2 - init MySQL 3 - connect to MySQL 4 - create/execute query 5 - get query result 6 - close MySQL connection 7 - return result [...snip...] const char* query = SELECT COUNT(*) FROM tblfoobar; Can you explain exactly what you are using this for? What benefit does this provide over just executing the query? You can either execute SELECT foobar() or SELECT COUNT(*) FROM tblfoobar, and you don't have to do anything to make the latter work. Creating the UDF just seems like a lot of extra work. Maybe this is my ignorance, but I don't see much use for executing a query within a UDF esspecially if you are using 4.1 with subqueries. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subquery fails when a NOT IN operator tests a subset with NULL valu
Giuseppe, mysql select 2 not in (1,null,3); +-+ | 2 not in (1,null,3) | +-+ |NULL | +-+ 1 row in set (0.00 sec) # NOT OK Isn't that standard SQL behaviour? NULL is not a value. NOT IN compares the values using '=' and correctly returns NULL if any value is NULL ie missing, eg for Oracle see http://builder.com.com/5100-6388_14-5319615.html PB - Giuseppe Maxia wrote: Hi. Here is a description of what looks like a serious bug. This is related to bugs #7294 and #6247 Tested against mysql 4.1.9 and 4.1.10. Cheers Giuseppe Maxia Description: operator NOT IN fails when a subquery returns one or more NULL values. How-To-Repeat: simple proof of concept: mysql select 1 in (1,null,3); +-+ | 1 in (1,null,3) | +-+ | 1 | +-+ 1 row in set (0.00 sec) #OK mysql select 2 not in (1,null,3); +-+ | 2 not in (1,null,3) | +-+ |NULL | +-+ 1 row in set (0.00 sec) # NOT OK More complete proof: mysql drop table if exists t1; Query OK, 0 rows affected (0.00 sec) mysql drop table if exists t2; Query OK, 0 rows affected (0.06 sec) mysql create table t1 (id int not null auto_increment primary key, c1 int); Query OK, 0 rows affected (0.01 sec) mysql mysql create table t2 (id int not null auto_increment primary key, c2 int); Query OK, 0 rows affected (0.02 sec) mysql insert into t1(c1) values (1),(2); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql insert into t2(c2) values (2),(null),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql select * from t1; ++--+ | id | c1 | ++--+ | 1 |1 | | 2 |2 | ++--+ 2 rows in set (0.01 sec) mysql select * from t2; ++--+ | id | c2 | ++--+ | 1 |2 | | 2 | NULL | | 3 |3 | ++--+ 3 rows in set (0.00 sec) mysql select t1.* from t1 left join t2 on (c1=c2) where t2.id is null; ++--+ | id | c1 | ++--+ | 1 |1 | ++--+ 1 row in set (0.01 sec) # OK. This is the normal way of checking for non-existence of records in a # related table mysql select t1.* from t1 where c1 not in (select distinct c2 from t2); Empty set (0.01 sec) # NOT OK. This query should have returned the same result as the previous one mysql select t1.* from t1 where c1 not in (select distinct c2 from t2 where c2 is not null); ++--+ | id | c1 | ++--+ | 1 |1 | ++--+ 1 row in set (0.01 sec) # ugly workaround with an express filter mysql select t1.* from t1 where c1 not in (select distinct coalesce(c2,0) from t2 ); ++--+ | id | c1 | ++--+ | 1 |1 | ++--+ 1 row in set (0.01 sec) # yet another ugly workaround Fix: as a temporary workaround, filter off the NULLs with a WHERE clause or a COALESCE function. Submitter-Id:submitter ID Originator:Giuseppe Maxia Organization: Stardata s.r.l MySQL support: Certified Consulting Partner Synopsis:subquery fails on test with NOT IN and NULL values Severity:serious Priority:high Category:mysql Class:sw-bug Release:mysql-4.1.10-standard (MySQL Community Edition - Standard (GPL)) Server: /usr/local/mysql/bin/mysqladmin Ver 8.41 Distrib 4.1.10, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version4.1.10-standard-log Protocol version10 ConnectionLocalhost via UNIX socket UNIX socket/tmp/mysql.sock Uptime:41 min 36 sec Threads: 2 Questions: 111 Slow queries: 0 Opens: 32 Flush tables: 1 Open tables: 2 Queries per second avg: 0.044 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: Linux ltstardata 2.6.9-1.667 #1 Tue Nov 2 14:41:25 EST 2004 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc/i386-redhat-linux/3.4.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-java-awt=gtk --host=i386-redhat-linux Thread model: posix gcc version 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Feb 12 14:24 /lib/libc.so.6 - libc-2.3.3.so -rwxr-xr-x 1 root root 1504728 Oct 28 01:00 /lib/libc-2.3.3.so -rw-r--r-- 1 root root 2404716 Oct 28 00:46
RE: Queries inside UDF
Hi!
From: Tom Crimmins [EMAIL PROTECTED]
To: sguazt sguazt [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: RE: Queries inside UDF
Date: Wed, 16 Mar 2005 10:09:16 -0600
...
Can you explain exactly what you are using this for? What benefit does this
provide over just executing the query?
You can either execute SELECT foobar() or SELECT COUNT(*) FROM tblfoobar,
and
you don't have to do anything to make the latter work. Creating the UDF
just
seems like a lot of extra work. Maybe this is my ignorance, but I don't see
much use for executing a query within a UDF esspecially if you are using
4.1
with subqueries.
ok sorry if I submitted a stupid exampe :P
but I wanted to keep the stuff simple.
My real case is a little more complicated ...
First of all, unfornately I've to use MySQL 4.0.21.
The purpose of creating a UDF, in my case, is doing something that in my
opinion cannot be done in a query.
For example, if you have to implement in SQL the following pseudo-code:
start = a unix timestamp;
duration = 100;
stop = null;
increment = 0;
while ( true ) {
start2 = 0;
stop2 = 0;
stop = start + ( duration - increment );
rs = doQuery( SELECT start,stop FROM t1 INNER JOIN t2 ON t1.c1=t2.c1
WHERE t1.start= + start + AND t1.stop= + stop );
if ( rs == null ) {
delta = 0;
start2 = rs[0]['start'];
stop2 = rs[0]['stop'];
if ( start start2 ) {
delta += start2 - start;
}
increment += delta;
start = stop2
}
else {
break;
}
}
return stop;
what do you write?
The main problem is that you can establish a-priori when you stop the main
while ... :(
I thought to use a temporary table but the problem remains ...
Furthermore that code produces an info that is to be used by another (more
complex) query.
So the only solutions are:
1. use a high-level language (C,perl,Java,PHP) and implement the entire
logic (not only the code above) in that language (but this seems to be too
slow ... I did it before exploring the UDF world ;) )
2. try to write a UDF that implement the code above; so you create a UDF,
e.g. named foobar, you can use inside other query: SELECT ...,foobar( start,
duration ) AS stop FROM a very long JOIN ...
Any idea?
bye!!!
-- Marco
--
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
_
Ricerche online più semplici e veloci con MSN Toolbar!
http://toolbar.msn.it/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subquery fails when a NOT IN operator tests a subset with NULL valu
Peter Brawley wrote: Giuseppe, mysql select 2 not in (1,null,3); +-+ | 2 not in (1,null,3) | +-+ |NULL | +-+ 1 row in set (0.00 sec) # NOT OK Isn't that standard SQL behaviour? Yes, it is. As I said before, I was too quick using this example, while the problem arises only during a subquery. Now, others have pointed out that even with subqueries this should be considered standard behaviour, even though severa people in my workplace agree that it does not look intuitive. NULL is not a value. NOT IN compares the values using '=' and correctly returns NULL if any value is NULL ie missing, eg for Oracle see http://builder.com.com/5100-6388_14-5319615.html I see now that this mechanism is intentional. Thanks for your link. Giuseppe -- Giuseppe Maxia CTO http://www.StarData.it MySQL Certified Professional __ __ __ ___ / / / /__ _/ / _ (_-/ __/ _ `/ __/ _ / _ `/ __/ _ `/ /___/\__/\_,_/_/ \_,_/\_,_/\__/\_,_/ Database is our business -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Queries inside UDF
sguazt sguazt [EMAIL PROTECTED] wrote on 03/16/2005 11:54:26 AM:
Hi!
From: Tom Crimmins [EMAIL PROTECTED]
To: sguazt sguazt [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: RE: Queries inside UDF
Date: Wed, 16 Mar 2005 10:09:16 -0600
...
Can you explain exactly what you are using this for? What benefit does
this
provide over just executing the query?
You can either execute SELECT foobar() or SELECT COUNT(*) FROM
tblfoobar,
and
you don't have to do anything to make the latter work. Creating the UDF
just
seems like a lot of extra work. Maybe this is my ignorance, but I don't
see
much use for executing a query within a UDF esspecially if you are
using
4.1
with subqueries.
ok sorry if I submitted a stupid exampe :P
but I wanted to keep the stuff simple.
My real case is a little more complicated ...
First of all, unfornately I've to use MySQL 4.0.21.
The purpose of creating a UDF, in my case, is doing something that in my
opinion cannot be done in a query.
For example, if you have to implement in SQL the following pseudo-code:
start = a unix timestamp;
duration = 100;
stop = null;
increment = 0;
while ( true ) {
start2 = 0;
stop2 = 0;
stop = start + ( duration - increment );
rs = doQuery( SELECT start,stop FROM t1 INNER JOIN t2 ON t1.c1=t2.c1
WHERE t1.start= + start + AND t1.stop= + stop );
if ( rs == null ) {
delta = 0;
start2 = rs[0]['start'];
stop2 = rs[0]['stop'];
if ( start start2 ) {
delta += start2 - start;
}
increment += delta;
start = stop2
}
else {
break;
}
}
return stop;
what do you write?
The main problem is that you can establish a-priori when you stop the
main
while ... :(
I thought to use a temporary table but the problem remains ...
Furthermore that code produces an info that is to be used by another
(more
complex) query.
So the only solutions are:
1. use a high-level language (C,perl,Java,PHP) and implement the entire
logic (not only the code above) in that language (but this seems to be
too
slow ... I did it before exploring the UDF world ;) )
2. try to write a UDF that implement the code above; so you create a
UDF,
e.g. named foobar, you can use inside other query: SELECT ...,foobar(
start,
duration ) AS stop FROM a very long JOIN ...
Any idea?
bye!!!
-- Marco
--
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
--
I can almost follow the logic of your pseudocode. Can you explain what
it is you are trying to compute? It seems as though you are looking for
(or computing) the ending value to some sort of time span but I just can't
quite understand how you are getting there. I can see that you are using
some kind of varying-width (you change your increment value), sliding time
window (you move your start and stop times) but I just don't understand
why you stop when you DO get a result (the else clause of (rs==null)) when
you needed results for the previous loops (I think you meant to stop when
you didn't get a result)... And the significance of the data you finally
found escapes me...
Maybe if I understood the nature of your data better I wouldn't be so
confused. And, if you would be so kind, can you explain why you are
scrolling through the data with a shifting time window to reach some kind
of non-record (I assume)? Is there no other way to reach the same
information? Can you not just use the last record and work from there?
It's hard to get help for a complex data issue like yours unless you give
us enough information so that we understand your both your data and your
needs. Any one of us may see a path to your solution that you haven't
thought of but we can't do that unless we understand both where you are
starting from (your base data structures) and where you need to be (the
data you require). It's going to be very hard to confuse all of us. The
more information you provide, the more complete the help will be.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
help on query/group by
Hi, A friend of mine asked me to have a look at one of his query, and I'm stuck Here was his query: SELECT drugID, protID, COUNT(DISTINCT pmid), MAX(s1.syn) AS o1, MAX(s2.syn) AS o2 FROM matches INNER JOIN synonyms AS s1 ON drugID=s1.nameID AND s1.syn LIKE 'a%' INNER JOIN synonyms AS s2 ON protID=s2.nameID AND s2.syn LIKE '%' INNER JOIN sentence ON sentID=id GROUP BY drugID, protID ORDER BY o1, o2 LIMIT 601 and this is his goal: The idea is quite simple: The table called 'matches' contains triples drugID, protID, sentID indicating a co-occurence of a drug and a protein in a sentence. The user of course searches for either drug name or protein name or both. In the above query, the user wants everything for all drugs starting with 'a'. The MAX() calls more or less arbitrarily choose one of the many names associated with drugID as a representative. With the COUNT() I want to find out how many different medline abstracts (not sentences) have a hit. The matches table is 1,247,508 rows, sentence is 817,255 rows and synonyms is 225,497 rows. First I think using inner join in that case is not helpful, because it is making a whole cartesian product on the tables, whereas a left join would limit the number of rows. The second line INNER JOIN synonyms AS s2 ON protID=s2.nameID AND s2.syn LIKE '%' is useless I think, because it just retrieves the not null values for protID. I also added indexes on the table (i'm not very familiar with indexes, so that is probably my problem) - on matches: index on protID,drugID and sentID - on sentence: index on id (primary key) - on synonyms: index on nameID,syn Here are the tables: mysql desc matches; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | protID | text | YES | MUL | NULL | | | drugID | text| YES | | NULL| | | sentID | int(11) | YES | MUL | NULL| | ++-+--+-+-+---+ 3 rows in set (0.00 sec) mysql desc sentence; +---+--+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(10) unsigned || PRI | NULL| auto_increment | | text | text | YES | | NULL || | pmid | int(11)| YES | | NULL || +---+--+--+-+-++ 3 rows in set (0.00 sec) mysql desc synonyms; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | nameID | text | YES | MUL | NULL| | | syn | text | YES || NULL| | ++--+--+-+-+---+ 2 rows in set (0.00 sec) I wanted to see how where used the indexes: mysql explain select * from matches left join synonyms on drugID=nameID; +--+--+---+--+-++-+---+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+--+---+--+-++-+---+ | matches | ALL | NULL | NULL|NULL | NULL | 1247508 | | | synonyms | ref | c | c | 23| matches.drugID | 4 | | +--+--+---+--+-++-+---+ 2 rows in set (0.00 sec) mysql explain select * from matches left join synonyms on drugID=nameID left join sentence on sentID=id; +--++---+-+-++-+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--++---+-+-++-+---+ | matches | ALL | NULL | NULL |NULL | NULL | 1247508 | | | synonyms | ref | c | c | 23 | matches.drugID | 4 | | | sentence | eq_ref | PRIMARY | PRIMARY | 4 | matches.sentID | 1 | | +--++---+-+-++-+---+ 3 rows in set (0.00 sec) If I do a reverse query of the first one, no index is used ( select * from synonyms left join matches on drugID=nameID;) what I don't understand: I thought syn is indexed, so it will quickly find the ones beginning by 'a%' and then do the join with matches but on a limited number of records only, so it will be much faster. I finally manage to have something quite working: (but not the final result my friend is hoping!!) select * from matches left join synonyms
dataKiosk 0.6 released
Hello, I have released dataKiosk version 0.6. DataKiosk is a JuK-like database interface tool for generic SQL databases. What does that mean? Essentially, DataKiosk provides a series of wizards (anyone familiar with Qt Designer's database wizards will find them familiar) that allow you to build a custom Juk-like interface for any SQL database with a QtSQL driver. New features in this version include the ability to edit the database through a new configurable edit form with custom widgets depending upon the the fields data type. MS Access style combo boxes have been implemented for Relation combo editors. Here is a more complete change list: * Fixed saved searches so that they load after all datatables have been created. * Clear advanced searches properly and disable the searchwidget for reports. * DataTable will have two tabs, one for viewing and one for editing. * Subclassed QDataBrowser to provide an edit record form. The edit form uses a custom editorfactory to provide the right widgets for data relations. The main datatable does this too. You can now edit records, although the layout and look of the form is not very good at the moment. * DataKiosk now has flow form layout management for the editor form thanks to dfaure and Qt4! FormLayout is based on this new example by TT: http://doc.trolltech.com/4.0/layouts-flowlayout.html * Fix an annoying problem with sorting the DataFields. * Add more advanced kcombobox for relation editor widgets and get ready for more advanced editor widgets in general. * Make the formlayout draw every widget with the same width/height. The width is calculated after every selectionChange in the QDataTable. The editor with the largest content wins. * The editors in FormLayout are now drawn correctly and the scroll feature of QDataBrowser works. A green/red border is drawn around the form based on what the user is doing. Update works. * Change focus policy to try and get a handle on how the data entry can be made as fast and efficient as possible allowing to navigate with keyboard only. * Editors remember state so when they change the colorbox changes. If the editors are changed back to match the current database buffer, the colorbox goes green reflecting that nothing has changed requiring updating. * Add shortcuts for tab between the datatables. * Ok, so I've settled on the following for keyboard shortcuts Alt+Up Scrolls the listbox containing the tables... UP skipping all the reports. :):) Alt+DownSame as above, just DOWN. Alt+LeftToggles between the edit view and table view plus it keeps all of the datatables in sync. They are all toggled. Alt+Right Same as above, just RIGHT. HomeSeeks to the first record. End Seeks to the last record. PageUp Seeks to the previous record. PageDown Seeks to the next record. CTRL+s Commits the changes to the database. Everything appears to be working and these are all universal shortcuts for the app, except the Home/End shortcuts don't work when a an editor widget in the edit view has focus. * Enforce readonly and do not allow autoediting. * Make sure that the editForm syncs with the configure table wizard. Various fixes. * Imported and modified libkdepim's kdateedit and ktimeedit into datakiosk. They are some ugly parts, but they do the job and I didn't feel like rewriting these widgets. Created a DateTimeEdit part out of them and made some fixes so they now handle the Date, Time and DateTime variant editors. * Added support for nested foreign keys in the Relation Combo editor. This just about completes the work on the Relation editor. * Add a configurable RelationCombo that can do MS Access style Combobox's with a QTable dropdown widget. DataKiosk also has a new and updated homepage available at: http://extragear.kde.org/apps/datakiosk/ ...complete with screenshots of the new features. Flash demos of dataKiosk in action can be found here: http://web.mit.edu/~treat/Public/datakiosk.html and here: http://web.mit.edu/~treat/Public/datakiosk-editor.html Cheers, Adam Treat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DBI mysql question.
Dear MySQL Developers and Enthusiasts, when I run these lines of code : my $T_NO = 12569; use DBI; my $dbh = DBI-connect(DBI:mysql:database=carr_search;192.168.0.1,user,password); my $q = CREATE TABLE IF NOT EXISTS CS_? ( ID_NO MEDIUMINT, NAME VARCHAR(30), TYPE CHAR(1) ); my $sth = $dbh-prepare($q); $sth-execute($T_NO); from w/in a program I get: DBD:mysql::st execute failed: You have an error in your SQL syntax near '12569 ( ID_NO MEDIUMINT, NAME VARCHAR(30), TYP' at line 2 at ./carr_s.pl line 36. However if I cut and paste the exact same code and make it it's own program then execute it, it works perfectly. Can anyone tell me what's happening and how I can fix it? Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Queries inside UDF
From: [EMAIL PROTECTED]
To: sguazt sguazt [EMAIL PROTECTED]
CC: mysql@lists.mysql.com,[EMAIL PROTECTED]
Subject: RE: Queries inside UDF
Date: Wed, 16 Mar 2005 12:37:59 -0500
I can almost follow the logic of your pseudocode. Can you explain what
it is you are trying to compute? It seems as though you are looking for
(or computing) the ending value to some sort of time span but I just can't
quite understand how you are getting there. I can see that you are using
some kind of varying-width (you change your increment value), sliding time
window (you move your start and stop times) but I just don't understand
why you stop when you DO get a result (the else clause of (rs==null)) when
you needed results for the previous loops (I think you meant to stop when
you didn't get a result)... And the significance of the data you finally
found escapes me...
yes you got it ... I wrote an erroneous stopping condition:
The real stopping condition is (as you told me):
if ( ts != null ) {
}
else {
break; // STOP CONDITION
}
Well I'm trying to explain what I want to do ...
I have a starting date of an event (see variable start) and it's virtual
duration (see variable duration); the word virtual will be clearer later.
For instance you have a table of research projects with the starting date
and their related duration.
In the simple vanilla world, the due date (see variable stop) would be:
start + duration (consider start as a UNIX timestamp and duration expressed
in seconds).
But in real world you've to take care about a calendar. Each project has a
project manager associated.
Each project manager is assigned a calendar because can follow different
holydays (in fact project manager for project1 could be American; project
manager for project2 could be European; and so on ...)
So for each project you've to calculate the real due date according to the
calendar associated to the project manager.
The calendar I have is a event-based calendar (that is, the calendar has
associated a series of calendar event representing the holydays).
So the pseudo-code:
int calculateDueDate( projectManager, wTsStart, duration )
{
wTsStop = 0; // Working Day stop timestamp
increment = 0; // increment of duration
// calculate the Working Day stop timestamp according to the
patching duration
while ( true ) do
nwTsStart = 0; // Non-Working Day start timestamp
nwTsStop = 0; // Non-Working Day stop timestamp
wTsStop = wTsStart + (duration increment);
// retrieve the intersecting interval for the interval
[wTsStart, wTsStop]
rs = doQuery(
SELECT CE.tsstart,CE.tsstop
+ FROM calendar C INNER JOIN calendarevent CE ON
C.id=CE.calendar
+ WHERE C.owner= + projectManager + AND (( +
wTsStart + =CE.tsstart AND + wTsStop + CE.tsstart) OR ( + wTsStart +
CE.tsstart AND + wTsStart + CE.tsstop))
+ ORDER BY CE.tsstart, CE.tsstop
);
if ( rs != null ) then
delta = 0; // offset between the start of intervals
nwTsStart = rs[0]['tsstart'];
nwTsStop = rs[0]['tsstop'];
if ( wTsStart nwTsStart ) then
delta += nwTsStart - wTsStart;
endif
increment += delta;
wTsStart = nwTsStop;
else
break;
endif
endwhile
return wTsStop;
}
the returned value is just the real due date.
The above code have to be repeated for each project manager:
SELECT , calculateDueDate( P.manager, P.startDate, P.duration ) FROM
project P INNER JOIN ...;
bye!!
-- Marco
Maybe if I understood the nature of your data better I wouldn't be so
confused. And, if you would be so kind, can you explain why you are
scrolling through the data with a shifting time window to reach some kind
of non-record (I assume)? Is there no other way to reach the same
information? Can you not just use the last record and work from there?
It's hard to get help for a complex data issue like yours unless you give
us enough information so that we understand your both your data and your
needs. Any one of us may see a path to your solution that you haven't
thought of but we can't do that unless we understand both where you are
starting from (your base data structures) and where you need to be (the
data you require). It's going to be very hard to confuse all of us. The
more information you provide, the more complete the help will be.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
_
Scopri il nuovo MSN Htomail - 10MB di allegati
http://www.msn.it/hotmail/minisite_10
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To
Re: DBI mysql question.
Hey, The placeholders (?) are safely escaped by the DBI library -- your query that gets submitted to the server actually looks like: CREATE TABLE IF NOT EXISTS CS_ 12569 ( ID_NO MEDIUMINT, NAME VARCHAR(30), TYPE CHAR(1) ) I think it puts the space in, but it might actually be quoting it. I would do something like: $dbh-do(sprintf(CREATE TABLE IF NOT EXISTS CS_%d ( ID_NO MEDIUMINT, NAME VARCHAR(30), TYPE CHAR(1) ), $T_NO)); out. On Wed, 16 Mar 2005 10:10:39 -0800 (PST), Richard Reina [EMAIL PROTECTED] wrote: Dear MySQL Developers and Enthusiasts, when I run these lines of code : my $T_NO = 12569; use DBI; my $dbh = DBI-connect(DBI:mysql:database=carr_search;192.168.0.1,user,password); my $q = CREATE TABLE IF NOT EXISTS CS_? ( ID_NO MEDIUMINT, NAME VARCHAR(30), TYPE CHAR(1) ); my $sth = $dbh-prepare($q); $sth-execute($T_NO); from w/in a program I get: DBD:mysql::st execute failed: You have an error in your SQL syntax near '12569 ( ID_NO MEDIUMINT, NAME VARCHAR(30), TYP' at line 2 at ./carr_s.pl line 36. However if I cut and paste the exact same code and make it it's own program then execute it, it works perfectly. Can anyone tell me what's happening and how I can fix it? Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help on query/group by
I have a favorite technique for improving the results of queries , like this, which involve fairly large JOINed tables. It's a form of divide-and-conquer in that you pre-compute what you can then make the JOINS you need to finish up the results. I agree that the AND s2.syn LIKE '%' in the ON clause of the second JOIN is worthless and can be eliminated however I recognize this as a common pattern for a two-term search and it may be harder to eliminate that clause than at first glance. This is how I would speed things up, Your friend really has 3 types of searches possible: a) search by drug name only b) search by protein name only c) search by both drug name and protein name Since the c) is the more complex situation, I will model it. It's almost trivial to clip out the unnecessary parts to make the other two queries. If I typed everything correctly, you should be able to cut and paste the whole thing into the MySQL client and have it execute. ### begin## CREATE TEMPORARY TABLE tmpSynDrug (key nameID) SELECT nameID, max(syn) as drugSyn FROM synonyms WHERE syn LIKE 'a%' GROUP BY nameID; CREATE TEMPORARY TABLE tmpSynProt (key nameID) SELECT nameID, max(syn) as protSyn FROM synonyms WHERE syn LIKE 'a%' GROUP BY nameID; CREATE TEMPORARY TABLE tmpMatch (key sentID) SELECT m.sentID, m.drugID, m.protID, tsd.drugSyn, tsp.protSyn FROM matches m INNER JOIN tmpSynDrugs tsd ON tsd.nameID = m.drugID INNER JOIN tmpSynProt tsp ON tsp.nameID = m.protID; # what we should have now is a nice small table that meets most of the # original query criteria. Now to summarize by publication by # joining through the sentence table SELECT tm.drugID, tm.protID, tm.drugSyn, tm.protSyn, COUNT(DISTINCT s.pmid) as publications FROM tmpMatch tm INNER JOIN sentence s ON s.id = tm.sentID GROUP BY 1,2,3,4 ; # I used a shortcut in the GROUP BY, I referenced the columns # by their positions and not by their names #Now that we have the data we wanted we can cleanup after ourselves: DROP TABLE tmpMatch, tmpSynProt, tmpSynDrug; end # By minimizing the number of records that needs to be JOINed at each stage of the query, we keep things moving along. This technique is very useful for queries whose JOIN products are somewhere in the hundreds of billions or records or more (which yours easily is). If you didn't want the names to be representative, but listed, you would change the first two queries to be like: CREATE TEMPORARY TABLE tmpSynDrug (key nameID) SELECT nameID, syn as drugSyn FROM synonyms WHERE syn LIKE 'a%'; If you didn't need names at all I would just say: CREATE TEMPORARY TABLE tmpSynDrug (key nameID) SELECT DISTINCT nameID FROM synonyms WHERE syn LIKE 'a%' and modify the other queries to not look for the name columns. HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine == Original message ==from mel list_php [EMAIL PROTECTED] 03/16/2005 12:45 PM == Hi, A friend of mine asked me to have a look at one of his query, and I'm stuck Here was his query: SELECT drugID, protID, COUNT(DISTINCT pmid), MAX(s1.syn) AS o1, MAX(s2.syn) AS o2 FROM matches INNER JOIN synonyms AS s1 ON drugID=s1.nameID AND s1.syn LIKE 'a%' INNER JOIN synonyms AS s2 ON protID=s2.nameID AND s2.syn LIKE '%' INNER JOIN sentence ON sentID=id GROUP BY drugID, protID ORDER BY o1, o2 LIMIT 601 and this is his goal: The idea is quite simple: The table called 'matches' contains triples drugID, protID, sentID indicating a co-occurence of a drug and a protein in a sentence. The user of course searches for either drug name or protein name or both. In the above query, the user wants everything for all drugs starting with 'a'. The MAX() calls more or less arbitrarily choose one of the many names associated with drugID as a representative. With the COUNT() I want to find out how many different medline abstracts (not sentences) have a hit. The matches table is 1,247,508 rows, sentence is 817,255 rows and synonyms is 225,497 rows. First I think using inner join in that case is not helpful, because it is making a whole cartesian product on the tables, whereas a left join would limit the number of rows. The second line INNER JOIN synonyms AS s2 ON protID=s2.nameID AND s2.syn LIKE '%' is useless I think, because it just retrieves the not null values for protID. I also added indexes on the table (i'm not very familiar with indexes, so that is probably my problem) - on matches: index on protID,drugID and sentID - on sentence: index on id (primary key) - on synonyms: index on nameID,syn Here are the tables: mysql desc matches; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | protID | text | YES | MUL | NULL | | | drugID | text
Adding fields to db table (primary key and other type)
Hi all, I am using MySQL Command Line and have created a table called dtd_test. It has two varchar fields at the moment. How can I add more fields? I want to add a primary key column which autoincrements, how can I do that? Thanks a lot
Re: Adding fields to db table (primary key and other type)
Check out the alter table syntax in the manual at: http://dev.mysql.com/doc/mysql/en/alter-table.html On Wed, 16 Mar 2005 14:56:59 -0500, Ed [EMAIL PROTECTED] wrote: Hi all, I am using MySQL Command Line and have created a table called dtd_test. It has two varchar fields at the moment. How can I add more fields? I want to add a primary key column which autoincrements, how can I do that? Thanks a lot -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding fields to db table (primary key and other type)
http://dev.mysql.com/doc/mysql/en/alter-table.html ALTER TABLE dtd_test ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id); On Wed, 16 Mar 2005 14:56:59 -0500, Ed [EMAIL PROTECTED] wrote: Hi all, I am using MySQL Command Line and have created a table called dtd_test. It has two varchar fields at the moment. How can I add more fields? I want to add a primary key column which autoincrements, how can I do that? Thanks a lot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select timestamp + 0
Hi, i'm using the 4.1.10 version of mysql. If I do : select max(timestamp + 0 ) as timestamp from news; ++ | timestamp | ++ | 20050314194920 | ++ so i got the full timestamp(14), but if I do : select max(timestamp) + 0 as timestamp from news; +---+ | timestamp | +---+ | 2005 | +---+ Did i miss something in the documentation ? or is it normal ? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Queries inside UDF
Marco,
I think I understand why you might want the end date of your projects to
be dynamically calculated (assuming that's why you want this calculation
to be a UDF?). You would be able to change a starting date, the project's
duration, or add or delete a holiday and your ending dates would
automatically adjust. However, as you have found out, it is not a simple
calculation to perform.
Based on the starting date and some initial duration, any number of
non-working days (which includes both holidays and weekends) may occur
within that time span. So you adjust your ending date which may now also
include more non-working days, so you repeat the process until you
achieve the duration you wanted and have accurately accounted for all
non-working days since the startdate of the project. I think your pseudo
code fits that model of calculating the finishdate rather well.
I agree with your assessment to do the finish date calculation in code,
not SQL. However I disagree with the design choice to make the finish
date a dynamic value (if that's what you did, sorry if you didn't). You
should only need to compute the finish date's value under the following
conditions:
a) initial record creation
b) if you change the start date
c) if you change the duration
d) if a non-workday was added to or removed from the calendar and that day
fell within the computed time span of the project.
It makes better sense to me to detect one of those 4 conditions and UPDATE
a field that holds the ending date by using your algorithm while
performing one of those actions. That way a query like
SELECT name, startdate, enddate
FROM projects
WHERE startdate = '2005-07-01'
AND enddate '2005-07-01';
doesn't need to compute the value for enddate twice (once for the result
and once for the WHERE test) for every project just to detect any
projects that will be active on July 1, 2005. In fact, that query may even
be able to use an index if you stored the value in a field while if you
kept it only as a calculated value, that would not be possible.
So... have we answered your original question or did we just go WAAY off
track?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
sguazt sguazt [EMAIL PROTECTED] wrote on 03/16/2005 01:25:09 PM:
From: [EMAIL PROTECTED]
To: sguazt sguazt [EMAIL PROTECTED]
CC: mysql@lists.mysql.com,[EMAIL PROTECTED]
Subject: RE: Queries inside UDF
Date: Wed, 16 Mar 2005 12:37:59 -0500
I can almost follow the logic of your pseudocode. Can you explain
what
it is you are trying to compute? It seems as though you are looking for
(or computing) the ending value to some sort of time span but I just
can't
quite understand how you are getting there. I can see that you are
using
some kind of varying-width (you change your increment value), sliding
time
window (you move your start and stop times) but I just don't understand
why you stop when you DO get a result (the else clause of (rs==null))
when
you needed results for the previous loops (I think you meant to stop
when
you didn't get a result)... And the significance of the data you
finally
found escapes me...
yes you got it ... I wrote an erroneous stopping condition:
The real stopping condition is (as you told me):
if ( ts != null ) {
}
else {
break; // STOP CONDITION
}
Well I'm trying to explain what I want to do ...
I have a starting date of an event (see variable start) and it's virtual
duration (see variable duration); the word virtual will be clearer
later.
For instance you have a table of research projects with the starting
date
and their related duration.
In the simple vanilla world, the due date (see variable stop) would be:
start + duration (consider start as a UNIX timestamp and duration
expressed
in seconds).
But in real world you've to take care about a calendar. Each project has
a
project manager associated.
Each project manager is assigned a calendar because can follow different
holydays (in fact project manager for project1 could be American;
project
manager for project2 could be European; and so on ...)
So for each project you've to calculate the real due date according to
the
calendar associated to the project manager.
The calendar I have is a event-based calendar (that is, the calendar has
associated a series of calendar event representing the holydays).
So the pseudo-code:
int calculateDueDate( projectManager, wTsStart, duration )
{
wTsStop = 0; // Working Day stop timestamp
increment = 0; // increment of duration
// calculate the Working Day stop timestamp according to the
patching duration
while ( true ) do
nwTsStart = 0; // Non-Working Day start timestamp
nwTsStop = 0; // Non-Working Day stop timestamp
wTsStop = wTsStart + (duration increment);
// retrieve the intersecting interval for the
Trouble performing an update
I am trying to update a link table (table with two primary keys) where I want to update all rows where the first primary key is a set value (for example, change key1 from 10 to 20), but I only want to update these where the resulting primary key does not already exist in the table (otherwise an error is thrown on a duplicate key and the remaining rows are not updated). Using other databases, I am able to perform a subquery in the filter for the update such as the following: UPDATE mytable SET key1 = 20 WHERE key2 NOT IN (SELECT key2 FROM mytable WHERE key1 = 20) Unfortunately, MySQL does not allow you to use a table in a subquery which is being updated. If anyone can offer any assistance with this, I would greatly appreciate it. Thanks, Bob Dankert Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780
Re: select timestamp + 0
Mister Jack wrote: Hi, i'm using the 4.1.10 version of mysql. If I do : select max(timestamp + 0 ) as timestamp from news; ++ | timestamp | ++ | 20050314194920 | ++ so i got the full timestamp(14), but if I do : select max(timestamp) + 0 as timestamp from news; +---+ | timestamp | +---+ | 2005 | +---+ Did i miss something in the documentation ? or is it normal ? thanks What does select max(timestamp) as timestamp from news; return? What would you get if that string was converted to a number? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to add String to existing value of String
I need to modify the value of file names in a table. The strings represent an image file path and are stored in a char column in the table. Here is a sample of the values: \products\1_TH.JPG \products\2_TH.JPG I would like to perform the following UPDATE test.Items SET Desc='New'+Desc I've been able to get the update to work on numeric values based on the MySQL docs but cannot figure out how to perform the update on strings. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select timestamp + 0
It returns : select max(timestamp) as timestamp from news; +-+ | timestamp | +-+ | 2005-03-14 19:49:20 | +-+ and also : select timestamp as timestamp from news limit 1; +-+ | timestamp | +-+ | 2002-03-25 19:45:32 | +-+ so If I do : select timestamp + 0 as timestamp from news limit 1; ++ | timestamp | ++ | 20020325194532 | ++ So i would expect a max(timestamp) + 0 to work the same than without the max. is this a bug ? (the code rely heavily on a result as a timestamp(14), like MMDDHHmmss, so getting this work helps migrating from 4.0 to 4.1) thanks for your help On Wed, 16 Mar 2005 14:36:42 -0600, gerald_clark [EMAIL PROTECTED] wrote: Mister Jack wrote: Hi, i'm using the 4.1.10 version of mysql. If I do : select max(timestamp + 0 ) as timestamp from news; ++ | timestamp | ++ | 20050314194920 | ++ so i got the full timestamp(14), but if I do : select max(timestamp) + 0 as timestamp from news; +---+ | timestamp | +---+ | 2005 | +---+ Did i miss something in the documentation ? or is it normal ? thanks What does select max(timestamp) as timestamp from news; return? What would you get if that string was converted to a number? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Trouble performing an update
You can do left joins in an update. mysql show create table t; +---+--- -- | Table | Create Table +---+--- -- | t | CREATE TABLE `t` ( `key1` int(11) NOT NULL default '0', `key2` int(11) NOT NULL default '0', PRIMARY KEY (`key1`,`key2`) ) TYPE=MyISAM | +---+--- -- 1 row in set (0.01 sec) mysql insert into t values (5,5),(20,25),(10,15),(10,20),(10,30),(15,20),(20,30); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql select * from t; +--+--+ | key1 | key2 | +--+--+ |5 |5 | | 10 | 15 | | 10 | 20 | | 10 | 30 | | 15 | 20 | | 20 | 25 | | 20 | 30 | +--+--+ 7 rows in set (0.00 sec) mysql update t as t1 -left join t as t2 -on (t1.key2 = t2.key2 -and t2.key1 = 20) - sett1.key1 = 20 - where t2.key1 IS NULL -AND t1.key1 = 10; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql select * from t; +--+--+ | key1 | key2 | +--+--+ |5 |5 | | 10 | 30 | | 15 | 20 | | 20 | 15 | | 20 | 20 | | 20 | 25 | | 20 | 30 | +--+--+ -Original Message- From: Bob Dankert [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 16, 2005 2:27 PM To: mysql@lists.mysql.com Subject: Trouble performing an update I am trying to update a link table (table with two primary keys) where I want to update all rows where the first primary key is a set value (for example, change key1 from 10 to 20), but I only want to update these where the resulting primary key does not already exist in the table (otherwise an error is thrown on a duplicate key and the remaining rows are not updated). Using other databases, I am able to perform a subquery in the filter for the update such as the following: UPDATE mytable SET key1 = 20 WHERE key2 NOT IN (SELECT key2 FROM mytable WHERE key1 = 20) Unfortunately, MySQL does not allow you to use a table in a subquery which is being updated. If anyone can offer any assistance with this, I would greatly appreciate it. Thanks, Bob Dankert Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to add String to existing value of String
Look for concat : http://dev.mysql.com/doc/mysql/en/string-functions.html On Wed, 16 Mar 2005 15:51:34 -0500, David DeSana [EMAIL PROTECTED] wrote: I need to modify the value of file names in a table. The strings represent an image file path and are stored in a char column in the table. Here is a sample of the values: \products\1_TH.JPG \products\2_TH.JPG I would like to perform the following UPDATE test.Items SET Desc='New'+Desc I've been able to get the update to work on numeric values based on the MySQL docs but cannot figure out how to perform the update on strings. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.10a packaging for Solaris
I just downloaded the 4.1.10a release for Solaris 8 and 9 (32bit). It seems that it is no longer in a tar archive. Was this change intentional? I cant find any mention of it on the web site. How do I unpack the new format into a given directory? Thanks, Jo
Re: select timestamp + 0
Mister Jack wrote: It returns : select max(timestamp) as timestamp from news; +-+ | timestamp | +-+ | 2005-03-14 19:49:20 | +-+ The string shown above converted into a number is 2005. ( Unless you think ist should be 1988 ) Add 0, and it is still 2005. and also : select timestamp as timestamp from news limit 1; +-+ | timestamp | +-+ | 2002-03-25 19:45:32 | +-+ so If I do : select timestamp + 0 as timestamp from news limit 1; ++ | timestamp | ++ | 20020325194532 | ++ So i would expect a max(timestamp) + 0 to work the same than without the max. is this a bug ? (the code rely heavily on a result as a timestamp(14), like MMDDHHmmss, so getting this work helps migrating from 4.0 to 4.1) thanks for your help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select timestamp + 0
From the manual : MIN() and MAX() may take a string argument; in such cases they return the minimum or maximum string value. so I understand that for a timestamp column, values are converted and then compared as strings, then the function return a string, so adding + 0 convert it to a number (which is different from adding 0 to a timestamp). MIN/MAX operating on timestamp would have been more straightforward, no ? Or is there anything to coerce a string into a timestamp ? (appart a format ) On Wed, 16 Mar 2005 15:40:26 -0600, gerald_clark [EMAIL PROTECTED] wrote: Mister Jack wrote: It returns : select max(timestamp) as timestamp from news; +-+ | timestamp | +-+ | 2005-03-14 19:49:20 | +-+ The string shown above converted into a number is 2005. ( Unless you think ist should be 1988 ) Add 0, and it is still 2005. and also : select timestamp as timestamp from news limit 1; +-+ | timestamp | +-+ | 2002-03-25 19:45:32 | +-+ so If I do : select timestamp + 0 as timestamp from news limit 1; ++ | timestamp | ++ | 20020325194532 | ++ So i would expect a max(timestamp) + 0 to work the same than without the max. is this a bug ? (the code rely heavily on a result as a timestamp(14), like MMDDHHmmss, so getting this work helps migrating from 4.0 to 4.1) thanks for your help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Adding fields to db table (primary key and other type)
Thanks for the replies, works fine, I checked out the alter table syntax and added a new field. How can you add two new fields I tried with ALTER TABLE DTD_Test add template_header varchar(255), template_footer varchar(255); but i get an error. Cheers
Re: Fw: Adding fields to db table (primary key and other type)
See my original post: ALTER TABLE dtd_test ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id); Primary key could've just as easily been another column. You have to include another ADD command after the comma. On Wed, 16 Mar 2005 15:25:14 -0800, Scott Klarenbach [EMAIL PROTECTED] wrote: See my original post: ALTER TABLE dtd_test ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id); Primary key could've just as easily been another column. You have to include another ADD command after the comma. On Wed, 16 Mar 2005 18:13:54 -0500, Ed [EMAIL PROTECTED] wrote: Thanks for the replies, works fine, I checked out the alter table syntax and added a new field. How can you add two new fields I tried with ALTER TABLE DTD_Test add template_header varchar(255), template_footer varchar(255); but i get an error. Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Where do I get the Username?
I know the password for MySQL Command line client so I can login and create and modify dbs etc. I am trying to create a MySQL datasource in coldfusion with the MySQL (3.x) driver. It is asking me for the username, is there any way to find that out from MySQL Command line client or is there a file in the mysql folder on my pc? Thanks
Re: Where do I get the Username?
Thanks Kristen I am root - Original Message - From: Kristen G. Thorson [EMAIL PROTECTED] To: Ed [EMAIL PROTECTED] Sent: Wednesday, March 16, 2005 6:49 PM Subject: Re: Where do I get the Username? If you do not supply a username at the command line, MySQL will assume you are logging in as root. SHOW GRANTS FOR CURRENT_USER; Will tell you username, host, and privileges. kgt Ed wrote: I know the password for MySQL Command line client so I can login and create and modify dbs etc. I am trying to create a MySQL datasource in coldfusion with the MySQL (3.x) driver. It is asking me for the username, is there any way to find that out from MySQL Command line client or is there a file in the mysql folder on my pc? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server won't stop...
Using the OSX MySQL Administrator, when Stop Server is clicked under the Service tab, the server doesn't stop. In fact, the only way I have been able to quit the MySQL process is through the Activity Monitor, and a force quit at that. What's up with that? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server won't stop...
Have you tried mysqladmin -u root -p shutdown in Terminal? Michael Jeff Justice wrote: Using the OSX MySQL Administrator, when Stop Server is clicked under the Service tab, the server doesn't stop. In fact, the only way I have been able to quit the MySQL process is through the Activity Monitor, and a force quit at that. What's up with that? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where do I get the Username?
Hello Ed, I am nost sure what you are trying to say, but is it possible that you login as user root ? I assumed that you probably just installed mysql and are new to mysql. Best regards Nils Valentin I know the password for MySQL Command line client so I can login and create and modify dbs etc. I am trying to create a MySQL datasource in coldfusion with the MySQL (3.x) driver. It is asking me for the username, is there any way to find that out from MySQL Command line client or is there a file in the mysql folder on my pc? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Erroneus column using MAX() and GROUP BY
I have this table: mysql select historyvlan_time, historyvlan_vlan, v.clienthistory_id from pe_historyvlan as v join pe_clienthistory using (clienthistory_id) order by historyvlan_vlan, historyvlan_time desc; +--+--+--+ | historyvlan_time | historyvlan_vlan | clienthistory_id | +--+--+--+ | 0503011446 |4 | 55 | -- | 0503011440 |4 | 54 | | 0502181640 |4 | 29 | | 0502181638 |4 | 26 | | 0502181508 |4 | 24 | | 0503021500 |5 | 73 | -- | 0503011808 |6 | 71 | -- | 0503011452 |6 | 56 | | 0502181626 |6 | 25 | | 0502181640 |7 | 28 | -- | 0503011805 |8 | 70 | -- | 0503011801 |8 | 68 | | 0503011731 |8 | 61 | | 0503011730 |8 | 60 | +--+--+--+ 14 rows in set (0.00 sec) I am trying to find the id and vlan for the most recent time: mysql select max(historyvlan_time), historyvlan_vlan, v.clienthistory_id from pe_historyvlan as v join pe_clienthistory using (clienthistory_id) group by historyvlan_vlan order by historyvlan_vlan, historyvlan_time desc; +---+--+--+ | max(historyvlan_time) | historyvlan_vlan | clienthistory_id | +---+--+--+ | 0503011446|4 | 24 | -- | 0503021500|5 | 73 | | 0503011808|6 | 25 | -- | 0502181640|7 | 28 | | 0503011805|8 | 60 | -- +---+--+--+ 5 rows in set (0.00 sec) Why do I get '24' when I should get '55', '25' when I should get '61', and '60' when I should get '70'. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server won't stop...
It should be in mysql's bin directory, typically /usr/local/mysql/bin. You either need to add that to your PATH, export PATH=$PATH:/usr/local/mysql/bin or use the full path when executing the command /usr/local/mysql/bin/mysqladmin -u root -p shutdown I'd recommend the former, and I'd suggest making it permanent by adding that to your .bash_profile. I haven't used the OSX MySQL Administrator, so I can't be sure, but it *may* be that it isn't working because of the PATH problem. Michael Jeff Justice wrote: Yields command not found. Jeff On Mar 16, 2005, at 7:55 PM, Michael Stassen wrote: Have you tried mysqladmin -u root -p shutdown in Terminal? Michael Jeff Justice wrote: Using the OSX MySQL Administrator, when Stop Server is clicked under the Service tab, the server doesn't stop. In fact, the only way I have been able to quit the MySQL process is through the Activity Monitor, and a force quit at that. What's up with that? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Erroneus column using MAX() and GROUP BY
You've misunderstood how GROUP BY and MAX() work. GROUP BY divides your data into groups, and aggregate functions such as MAX() tell you something about each group, but they *do not* return *rows* from your table. Consider the following example rows in a larger table: cat val1 val2 4 1 1 4 5 3 4 7 2 4 3 4 4 7 1 Now consider the query SELECT cat, MIN(val1), MAX(val1), MIN(val2), MAX(val2) FROM mytable GROUP BY cat; I think it should be easy to see that for the group where cat is 4, I'll get the following result: +-+---+---+---+---+ | cat | MIN(val1) | MAX(val1) | MIN(val2) | MAX(val2) | +-+---+---+---+---+ | 4 | 1 | 7 | 1 | 4 | +-+---+---+---+---+ Which row is that in my table? You see? Even if I only asked for MAX(val1), there are 2 rows with the max value of 7. AS I said before, we get information about each group, but not rows from the table. Other systems wouldn't even allow your query, because clienthistory_id is neither an aggregate function nor a grouped column. MySQL allows this as a convenience, but you are warned not to use columns whose values are not unique per group, as you will get random (first found, I believe) results. See the manual for more http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html. Fortunately, yours is such a frequently asked question, that the manual has a page describing three solutions. See http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html. Michael Daevid Vincent wrote: I have this table: mysql select historyvlan_time, historyvlan_vlan, v.clienthistory_id from pe_historyvlan as v join pe_clienthistory using (clienthistory_id) order by historyvlan_vlan, historyvlan_time desc; +--+--+--+ | historyvlan_time | historyvlan_vlan | clienthistory_id | +--+--+--+ | 0503011446 |4 | 55 | -- | 0503011440 |4 | 54 | | 0502181640 |4 | 29 | | 0502181638 |4 | 26 | | 0502181508 |4 | 24 | | 0503021500 |5 | 73 | -- | 0503011808 |6 | 71 | -- | 0503011452 |6 | 56 | | 0502181626 |6 | 25 | | 0502181640 |7 | 28 | -- | 0503011805 |8 | 70 | -- | 0503011801 |8 | 68 | | 0503011731 |8 | 61 | | 0503011730 |8 | 60 | +--+--+--+ 14 rows in set (0.00 sec) I am trying to find the id and vlan for the most recent time: mysql select max(historyvlan_time), historyvlan_vlan, v.clienthistory_id from pe_historyvlan as v join pe_clienthistory using (clienthistory_id) group by historyvlan_vlan order by historyvlan_vlan, historyvlan_time desc; +---+--+--+ | max(historyvlan_time) | historyvlan_vlan | clienthistory_id | +---+--+--+ | 0503011446|4 | 24 | -- | 0503021500|5 | 73 | | 0503011808|6 | 25 | -- | 0502181640|7 | 28 | | 0503011805|8 | 60 | -- +---+--+--+ 5 rows in set (0.00 sec) Why do I get '24' when I should get '55', '25' when I should get '61', and '60' when I should get '70'. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Queries inside UDF
Thanks for answering! Your solution would be right if I can modify the database (and I have no control on software that populate tables). Unfortunately I can do only queries on that db; so the due date has to be recalculated every time I want to perform the main report (that use the ending date). An alternative way is to created a snapshot of main db with the added fields, but the report may contains not so updated information (e.g. if the snapshot is synch one time at day in theory the report may display info older up to a day). This violates the requirements of my boss who wants a very updated data ... :'-| So the only possible ways are: * do that via high-level language, like C, Perl, Java; I've done this but is very slow ... especially the client running the code is remote (with respect to DB server) ... so in addition to the load generated by the number of queries there's the load of network communication: (Send Queries + Receive Results) * N (where N 1 in general); * do that via SQL+UDF; the advantage of this solution is the load of computation is on DB server ... the load due to the network communication a very minimal: Send Query One Time + Receive Result When I tried UDF I'm said WOW this could be the solution to my problems Unfortunaly, I found out there's no way (at least for me) to access to current DBMS thread or connection; i.e.: SELECT foobar( ... ) FROM ...; I believed from foobar FUNCTION there would have been a way (through parameters passed by MySQL, like UDF_INIT* initid) to access to current DBMS connection object. I this is true ... what a pity! I hope MySQL staff will think to extend UDF to include this feature unless there're hidden trickly I don't see. -- Marco From: [EMAIL PROTECTED] To: sguazt sguazt [EMAIL PROTECTED] CC: mysql@lists.mysql.com,[EMAIL PROTECTED] Subject: RE: Queries inside UDF Date: Wed, 16 Mar 2005 15:20:09 -0500 Marco, I think I understand why you might want the end date of your projects to be dynamically calculated (assuming that's why you want this calculation to be a UDF?). You would be able to change a starting date, the project's duration, or add or delete a holiday and your ending dates would automatically adjust. However, as you have found out, it is not a simple calculation to perform. Based on the starting date and some initial duration, any number of non-working days (which includes both holidays and weekends) may occur within that time span. So you adjust your ending date which may now also include more non-working days, so you repeat the process until you achieve the duration you wanted and have accurately accounted for all non-working days since the startdate of the project. I think your pseudo code fits that model of calculating the finishdate rather well. I agree with your assessment to do the finish date calculation in code, not SQL. However I disagree with the design choice to make the finish date a dynamic value (if that's what you did, sorry if you didn't). You should only need to compute the finish date's value under the following conditions: a) initial record creation b) if you change the start date c) if you change the duration d) if a non-workday was added to or removed from the calendar and that day fell within the computed time span of the project. It makes better sense to me to detect one of those 4 conditions and UPDATE a field that holds the ending date by using your algorithm while performing one of those actions. That way a query like SELECT name, startdate, enddate FROM projects WHERE startdate = '2005-07-01' AND enddate '2005-07-01'; doesn't need to compute the value for enddate twice (once for the result and once for the WHERE test) for every project just to detect any projects that will be active on July 1, 2005. In fact, that query may even be able to use an index if you stored the value in a field while if you kept it only as a calculated value, that would not be possible. So... have we answered your original question or did we just go WAAY off track? Shawn Green Database Administrator Unimin Corporation - Spruce Pine sguazt sguazt [EMAIL PROTECTED] wrote on 03/16/2005 01:25:09 PM: From: [EMAIL PROTECTED] To: sguazt sguazt [EMAIL PROTECTED] CC: mysql@lists.mysql.com,[EMAIL PROTECTED] Subject: RE: Queries inside UDF Date: Wed, 16 Mar 2005 12:37:59 -0500 I can almost follow the logic of your pseudocode. Can you explain what it is you are trying to compute? It seems as though you are looking for (or computing) the ending value to some sort of time span but I just can't quite understand how you are getting there. I can see that you are using some kind of varying-width (you change your increment value), sliding time window (you move your start and stop times) but I just don't understand why you stop when you DO get a result (the else clause of (rs==null)) when you needed results for the previous loops (I think you meant to stop when you didn't get a
Re: subquery fails when a NOT IN operator tests a subset with NULL valu
In article [EMAIL PROTECTED], Giuseppe Maxia [EMAIL PROTECTED] writes: The whole point is actually in subqueries, not when using IN or NOT IN in a normal query. The bug occurs when a NOT IN is used in a subquery as a LEFT JOIN replacement. SELECT something from t1 where column1 NOT IN (SELECT nullable_column from t2); That's not a bug. Let's say that the subquery returns 2, NULL, 3. Thus the NOT IN is a shorthand for column1 != 2 AND column1 != NULL AND column1 != 3 Since the second condition is never true, you get an empty result set. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
