RE: NULL sometimes joins to NULL
I am not convinced that index on nullable field is a design flaw. This is not the behavior in the major databases. To match and join two nulls, IS NULL should be expected to be used instead of a.col1 = b.col2. -Original Message- From: Jan Steinman [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 10, 2002 9:45 AM To: [EMAIL PROTECTED] Subject: Re: NULL sometimes joins to NULL From: [EMAIL PROTECTED] After adding a key to a nullable column, null values will successfully join through to null values in other tables. And this is a problem because? :-) Seriously, it may be a bug, but IMHO it is a design flaw to index a NULL field. Indexed fields should always be NOT NULL, with the possible exception of FULLTEXT indeces. But if one DOES have NULLs in an indexed field, I'd fully expect them to match and join other NULLs. SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL -- : Jan Steinman -- nature photography: http://www.Bytesmiths.com : Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services : Join the forums at http://www.Bytesmiths.com/wiki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Question about solving a query !
Here is a correct one to solve your problem: select b.city, b.name, b.numbeds, count(distinct a.numbeds) c from hospitals a, hospitals b where a.city = b.city and a.numbeds=b.numbeds group by b.name, b.city, b.numbeds having c=1; -Original Message- From: Myoung-Ah KANG [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 09, 2002 8:45 AM To: [EMAIL PROTECTED] Subject: Question about solving a query ! Hello, I have a problem to treat a query with MySQL because MySQL does not permit nested SELECT clauses. The schema of the table is : Hospital num | name | city| numbeds 10 | Clairval| Marseille | 70 20 | Notre-Dame | Marseille | 150 30 | Tonkin | Lyon | 90 40 | Charpennes | Lyon | 300 80 | AAA| Paris |1500 90 | BBB | Paris |1400 100| CCC| Paris | 300 The query is: For each city, what is the name of the hospital having the highest number of beds ? ' . In fact, I can write (if I do not use MySQL): Select name From Hospital Where (city, numbeds) In (Select city, MAX(numbeds) From Hospital Group By city); But it does not work with MySQL. I'm looking for a MySQL version of this query, I will be very grateful if you help me !!! Thank you !! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: transaction isolation level
Thanks for all your help. Then it's more confusing to me. I opened two MySql windows
and set autocommit=0 and then update one column and then issue the command COMMIT in
one window, in the other MySql window, I queried the column and saw the change
immediately.
I also did the same thing with one MySql window and one JDBC client(my testcase), and
the testcase can see the change immediately.
In either case, instead of issuing COMMIT, if I issue ROLLBACK, the change will be
rolled back. (Just to make sure the auto commit is not on).
Here is my testcase:
import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.Driver;
public class TestMySql {
public static void main(String[] args) throws Exception {
Driver.class.getName();
Properties props = new Properties();
props.setProperty(user, root);
props.setProperty(password, geneva);
Connection conn = DriverManager.getConnection(jdbc:mysql://localhost/test,
props);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(select * from testtable);
while(rs.next()) {
System.out.println(rs.getInt(1));
}
System.out.println(Please update the table TESTTABLE...);
System.in.read();
rs = stmt.executeQuery(select * from testtable);
while(rs.next()) {
System.out.println(rs.getInt(1));
}
}
}
-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
Sent: 2002?10?8? 0:22
To: Mark Matthews
Cc: [EMAIL PROTECTED]
Subject: Re: transaction isolation level
Hi!
- Original Message -
From: Mark Matthews [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Tuesday, October 08, 2002 5:07 AM
Subject: Re: transaction isolation level
Jianliang Zhao wrote:
...
I am connecting to MySql 3.23(innodb) with
mysql-connector-java-2.0.14-bin.jar. I set the global transaction
isolation level to READ COMMITTED. However, I still couldn't see the
committed changes through JDBC client. Does anyone know about this
issue?
...
The isolation level of READ_COMMITTED has no effect currently in MySQL,
unless you're using BDB tables...InnoDB runs either as REPEATABLE_READ
or SERIALIZABLE, and converts everthing else to REPEATABLE_READ, because
it can do REPATABLE_READ as fast (or faster) than most other DB's
READ_COMMITTED.
I have to correct that BDB always runs at the SERIALIZABLE isolation level.
The default for InnoDB is REPEATABLE READ, and you can enhance it to
SERIALIZABLE with the
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
SQL command. Since MyISAM uses table locking, we can say that MyISAM always
runs at the SERIALIZABLE level.
The command SHOW VARIABLES currently shows the MySQL default isolation level
as READ-COMMITTED, but it has no effect on any of the table handlers.
Version 4.0.5 will probably feature a new InnoDB isolation level READ
COMMITTED. I will probably at the same time change the MySQL default
isolation level to REPEATABLE READ. You can then lower the InnoDB isolation
level by the command
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL ...
Users porting from Oracle and other databases will find READ COMMITTED a
useful level.
-Mark
Best regards,
Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: transaction isolation level
Thanks Heikki,
My testcase is not valid. After I get the connection, I didn't set auto commit to
false on the connection object, so I was unable to see the committed data. I saw the
REPEATABLE READ behavior now, but it's unfortunate to know READ COMMITTED is not
supported yet...
--Jianliang
-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
Sent: 2002?10?8? 9:56
To: Jianliang Zhao; Mark Matthews
Cc: [EMAIL PROTECTED]
Subject: Re: transaction isolation level
Jianliang,
I tested that with two mysql clients, and it appeared to work ok. Check with
SHOW CREATE TABLE tablename
what is the type of your table. Is it MyISAM?
Also put the line
log
to the [mysqld] section of your my.cnf and restart mysqld. Then you will see
all received queries in the file 'hostname'.log in your datadir. Check from
it what SQL queries your clients actually send to the mysqld server.
Regards,
Heikki
Innobase Oy
Below my test:
CLIENT A:
heikki@hundin:~/mysql/client mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.53-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql set autocmmit = 0;
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresp
onds to your MySQL server version for the right syntax to use near
'autocmmit =
0' at line 1
mysql set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql create table zhao (a int not null, b int, primary key (a)) type =
innodb;
Query OK, 0 rows affected (0.11 sec)
mysql insert into zhao values (10, 20);
Query OK, 1 row affected (0.00 sec)
mysql commit
- ;
Query OK, 0 rows affected (0.00 sec)
mysql update zhao set b = 100;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql
CLIENT B:
heikki@hundin:~/mysql/client mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.53-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql select * from zhao;
++--+
| a | b|
++--+
| 10 | 20 |
++--+
1 row in set (0.00 sec)
mysql select * from zhao;
++--+
| a | b|
++--+
| 10 | 20 |
++--+
1 row in set (0.00 sec)
mysql select * from zhao;
++--+
| a | b|
++--+
| 10 | 20 |
++--+
1 row in set (0.00 sec)
mysql set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql select * from zhao;
++--+
| a | b|
++--+
| 10 | 20 |
++--+
1 row in set (0.00 sec)
mysql commit;
Query OK, 0 rows affected (0.00 sec)
mysql select * from zhao;
++--+
| a | b|
++--+
| 10 | 20 |
++--+
1 row in set (0.00 sec)
mysql
- Original Message -
From: Jianliang Zhao [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]; Mark Matthews
[EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, October 08, 2002 7:40 PM
Subject: RE: transaction isolation level
Thanks for all your help. Then it's more confusing to me. I opened two MySql
windows and set autocommit=0 and then update one column and then issue the
command COMMIT in one window, in the other MySql window, I queried the
column and saw the change immediately.
I also did the same thing with one MySql window and one JDBC client(my
testcase), and the testcase can see the change immediately.
In either case, instead of issuing COMMIT, if I issue ROLLBACK, the change
will be rolled back. (Just to make sure the auto commit is not on).
Here is my testcase:
import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.Driver;
public class TestMySql {
public static void main(String[] args) throws Exception {
Driver.class.getName();
Properties props = new Properties();
props.setProperty(user, root);
props.setProperty(password, geneva);
Connection conn =
DriverManager.getConnection(jdbc:mysql://localhost/test, props);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(select * from testtable);
while(rs.next()) {
System.out.println(rs.getInt(1));
}
System.out.println(Please update the table TESTTABLE...);
System.in.read();
rs = stmt.executeQuery(select * from testtable);
while(rs.next()) {
System.out.println(rs.getInt(1));
}
}
}
-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
Sent: 2002?10?8? 0:22
To: Mark Matthews
Cc: [EMAIL PROTECTED]
Subject: Re: transaction isolation level
Hi!
- Original Message -
From: Mark Matthews [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Tuesday, October 08, 2002 5:07 AM
Subject: Re: transaction isolation level
Jianliang Zhao wrote:
...
I am connecting to MySql 3.23(innodb) with
mysql-connector-java-2.0.14-bin.jar. I set the global transaction
isolation level to READ COMMITTED. However, I still couldn't see the
committed changes through JDBC client. Does
transaction isolation level
Hi, I am connecting to MySql 3.23(innodb) with mysql-connector-java-2.0.14-bin.jar. I set the global transaction isolation level to READ COMMITTED. However, I still couldn't see the committed changes through JDBC client. Does anyone know about this issue? Thanks, Jianliang - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: transaction isolation level
Thanks Jeremy. It turns out the JDBC SQL query tool(ViennaSQL) I am trying is causing the problem. I couldn't reproduce the problem by writing a test case. Thanks, Jianliang -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: 2002?10?7? 17:36 To: Jianliang Zhao Cc: [EMAIL PROTECTED] Subject: Re: transaction isolation level On Mon, Oct 07, 2002 at 05:30:57PM -0700, Jianliang Zhao wrote: Hi, I am connecting to MySql 3.23(innodb) with mysql-connector-java-2.0.14-bin.jar. I set the global transaction isolation level to READ COMMITTED. However, I still couldn't see the committed changes through JDBC client. Does anyone know about this issue? Can you provide a sample test case, maybe? -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 62 days, processed 1,334,315,423 queries (246/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
