List:General Discussion« Previous MessageNext Message »
From:A. Brandic Date:April 26 1999 1:36pm
Subject:Re: Problem executing query, a bug perhaps?
View as plain text  
On Mon, 26 Apr 1999, Jules Bean wrote:

> "A. Brandic" wrote:
> > 
> > Hi!
> > 
> > I've got a problem with MySql and I'm not sure if it is a bug or my
> > fault.  I'm running MySql 3.22.21
> > on Alpha 800 with Unix 4.0 on it and MySql was installed as Binary version.
> > 
> > I've created three test tables each containing just over 15000 records in
> > 14 columns.
> > If I do a simple query such as :
> > mysql> select Universe.Surname from Universe, TestTable where
> >          -> ID=AdmissionNo;
> > 
> > ID is unique for TestTable and it is integer(9) Primary Key and
> > auto_increment  and
> > AdmissionNo  is unique for Universe and it is integer(9) Primary Key and
> > auto_increment as well.
> > 
> > This executes in 1.05 secs with no probs.
> > 
> > But when I do a similar query:
> > mysql> select Universe.Surname from Universe, TestTable, Streams where
> >          -> ID=AdmissionNo;
> > 
> > Still ID and AdmissionNo are Unique for their tables and do not appear in
> > any other table but this time
> > I've inserted Streams table which is not used at all.
> > Well this time after 2min the following is displayed
> > 
> > mysql: Out of memory (Needed 8160 bytes)
> > ERROR 2008: MySQL client run out of memory
> > 
> > and also it disconnects me from the database that I was usinq and I have
> > to logi in again.
> 
> Well, I find the error message confusing.  But I can explain the
> problem...
> 
> Your query wasn't useful.  Adding a table to a select, and not
> specifying any conditions, will do a cartesian product.  So if your
> original query returns N rows, and your Streams table has M rows, then
> this is going to return MN rows.
> 
> Which could be a very big number..
> 
> Jules



Sorry Jules!

You were right, it is displaying 'MN' number of rows.  I did not think it 
would have but it does.  I've just created a small table with 5 records 
in it and used it in the above query instead of Streams table and it 
displaye over 60000 recods instead of 15000.  My view was that the extra 
table (rows from the extra table) would have been ignored after the 
execution of the query.

Thanks.

Ajdin
Thread
Question: how to count unique rows in multiple tables?Kelly Yancey22 Apr
Re: Question: how to count unique rows in multiple tables?Tomislav Petrovic23 Apr
  • Problem executing query, a bug perhaps?A. Brandic26 Apr
    • Re: Problem executing query, a bug perhaps?Jules Bean26 Apr
      • Re: Problem executing query, a bug perhaps?A. Brandic26 Apr
      • Re: Problem executing query, a bug perhaps?A. Brandic26 Apr
        • Re: Problem executing query, a bug perhaps?Jules Bean26 Apr
    • Re: Problem executing query, a bug perhaps?Sasha Pachev26 Apr