Bug in JOIN with MySQL-4.1

Hi,

How-to-repeat :

mysql> SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2 as b,
3 as c) as b USING(a));
ERROR 1054: Unknown column '.b.a' in 'on clause'

mysql> SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2 as b,
3 as c) as b USING(d));
ERROR 1054: Unknown column '.a.d' in 'on clause'

It's not subselect specific :

use taist;
CREATE TABLE t1 (a int(1) NOT NULL, b int(1) NOT NULL);
CREATE TABLE t2 (b int(1) NOT NULL, c int(1) NOT NULL);
INSERT INTO t1 VALUES (1,2);
INSERT INTO t2 VALUES (2,3);

mysql> SELECT * FROM t1 as d LEFT JOIN t2 as e USING(a);
ERROR 1054: Unknown column 'taist.e.a' in 'on clause'
mysql> SELECT * FROM t1 as d LEFT JOIN t2 as e USING(c);
ERROR 1054: Unknown column 'taist.d.c' in 'on clause'

BTW, why when we do :

mysql> SELECT * FROM t1 LEFT JOIN t2 USING(b);
+---+---+---+---+
| a | b | b | c |
+---+---+---+---+
| 1 | 2 | 2 | 3 |
+---+---+---+---+
1 row in set (0.00 sec)

Or :

mysql> SELECT * FROM t1,t2 WHERE t1.b=t2.b;
+---+---+---+---+
| a | b | b | c |
+---+---+---+---+
| 1 | 2 | 2 | 3 |
+---+---+---+---+
1 row in set (0.00 sec)

Or :

mysql> SELECT * FROM ((SELECT 1 as a, 2 as b) as a NATURAL JOIN (SELECT 2 as
b, 3 as c) as b);
+---+---+---+---+
| a | b | b | c |
+---+---+---+---+
| 1 | 2 | 2 | 3 |
+---+---+---+---+
1 row in set (0.00 sec)

Column b is displayed 2 times ??

Regards,
Jocelyn


------------------------------------------------------------ ---------
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 bugs-thread13228@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOIN with MySQL-4.1

Jocelyn Fournier writes:
> Hi,
>
> How-to-repeat :
>

[skip]

> CREATE TABLE t1 (a int(1) NOT NULL, b int(1) NOT NULL);
> CREATE TABLE t2 (b int(1) NOT NULL, c int(1) NOT NULL);
> INSERT INTO t1 VALUES (1,2);
> INSERT INTO t2 VALUES (2,3);
>
> mysql> SELECT * FROM t1 as d LEFT JOIN t2 as e USING(a);
> ERROR 1054: Unknown column 'taist.e.a' in 'on clause'
> mysql> SELECT * FROM t1 as d LEFT JOIN t2 as e USING(c);
> ERROR 1054: Unknown column 'taist.d.c' in 'on clause'
>
> BTW, why when we do :
>
> mysql> SELECT * FROM t1 LEFT JOIN t2 USING(b);
> +---+---+---+---+
> | a | b | b | c |
> +---+---+---+---+
> | 1 | 2 | 2 | 3 |
> +---+---+---+---+
> 1 row in set (0.00 sec)
>
> Column b is displayed 2 times ??
>
> Regards,
> Jocelyn

The above is quite logical, as only t1 has a and only t2 has c, while
they both have b.

So, what is a problem ???

--
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/

__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ 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 bugs-thread13230@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOIN with MySQL-4.1

Hi,

Well since this is a join, I assume this is not needed to return 2 times the
same column where the join is done.
And according to a comment here
http://www.mysql.com/doc/en/Differences_from_ANSI.html, SQL 92 specify there
should be no duplicate column.
(indeed if we use this query with a derived table, the resulted derived
table will be a table with two times the same column).

Regards,
Jocelyn

----- Original Message -----
From: "Sinisa Milivojevic"
To:
Cc:
Sent: Friday, December 06, 2002 6:16 PM
Subject: Re: Bug in JOIN with MySQL-4.1


> Jocelyn Fournier writes:
> > Hi,
> >
> > How-to-repeat :
> >
>
> [skip]
>
> > CREATE TABLE t1 (a int(1) NOT NULL, b int(1) NOT NULL);
> > CREATE TABLE t2 (b int(1) NOT NULL, c int(1) NOT NULL);
> > INSERT INTO t1 VALUES (1,2);
> > INSERT INTO t2 VALUES (2,3);
> >
> > mysql> SELECT * FROM t1 as d LEFT JOIN t2 as e USING(a);
> > ERROR 1054: Unknown column 'taist.e.a' in 'on clause'
> > mysql> SELECT * FROM t1 as d LEFT JOIN t2 as e USING(c);
> > ERROR 1054: Unknown column 'taist.d.c' in 'on clause'
> >
> > BTW, why when we do :
> >
> > mysql> SELECT * FROM t1 LEFT JOIN t2 USING(b);
> > +---+---+---+---+
> > | a | b | b | c |
> > +---+---+---+---+
> > | 1 | 2 | 2 | 3 |
> > +---+---+---+---+
> > 1 row in set (0.00 sec)
> >
> > Column b is displayed 2 times ??
> >
> > Regards,
> > Jocelyn
>
> The above is quite logical, as only t1 has a and only t2 has c, while
> they both have b.
>
> So, what is a problem ???
>
> --
> MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
>
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ 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 bugs-thread13231@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOIN with MySQL-4.1

Jocelyn Fournier writes:
> Hi,
>
> Well since this is a join, I assume this is not needed to return 2 times the
> same column where the join is done.
> And according to a comment here
> http://www.mysql.com/doc/en/Differences_from_ANSI.html, SQL 92 specify there
> should be no duplicate column.
> (indeed if we use this query with a derived table, the resulted derived
> table will be a table with two times the same column).
>
> Regards,
> Jocelyn
>


Why ??

SELECT * FROM t1 LEFT JOIN t2 USING(b);

is the same as :

SELECT t1.a,t1.b,t2.b,t2.c FROM t1 LEFT JOIN t2 USING(b);



--
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/

__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ 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 bugs-thread13232@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOIN with MySQL-4.1

BTW, about

> > mysql> SELECT * FROM t1 as d LEFT JOIN t2 as e USING(a);
> > ERROR 1054: Unknown column 'taist.e.a' in 'on clause'
> > mysql> SELECT * FROM t1 as d LEFT JOIN t2 as e USING(c);
> > ERROR 1054: Unknown column 'taist.d.c' in 'on clause'

I made a mistake, of course this is not a bug :)

Regards,
Jocelyn

----- Original Message -----
From: "Sinisa Milivojevic"
To:
Cc:
Sent: Friday, December 06, 2002 6:16 PM
Subject: Re: Bug in JOIN with MySQL-4.1


> Jocelyn Fournier writes:
> > Hi,
> >
> > How-to-repeat :
> >
>
> [skip]
>
> > CREATE TABLE t1 (a int(1) NOT NULL, b int(1) NOT NULL);
> > CREATE TABLE t2 (b int(1) NOT NULL, c int(1) NOT NULL);
> > INSERT INTO t1 VALUES (1,2);
> > INSERT INTO t2 VALUES (2,3);
> >
> > mysql> SELECT * FROM t1 as d LEFT JOIN t2 as e USING(a);
> > ERROR 1054: Unknown column 'taist.e.a' in 'on clause'
> > mysql> SELECT * FROM t1 as d LEFT JOIN t2 as e USING(c);
> > ERROR 1054: Unknown column 'taist.d.c' in 'on clause'
> >
> > BTW, why when we do :
> >
> > mysql> SELECT * FROM t1 LEFT JOIN t2 USING(b);
> > +---+---+---+---+
> > | a | b | b | c |
> > +---+---+---+---+
> > | 1 | 2 | 2 | 3 |
> > +---+---+---+---+
> > 1 row in set (0.00 sec)
> >
> > Column b is displayed 2 times ??
> >
> > Regards,
> > Jocelyn
>
> The above is quite logical, as only t1 has a and only t2 has c, while
> they both have b.
>
> So, what is a problem ???
>
> --
> MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
>
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ 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 bugs-thread13233@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOIN with MySQL-4.1

I agree, but SQL 92 says :

6) The same shall not be specified more than once in

a .

And :

6) If NATURAL is specified or if a simply

containing a is specified, then:

a) If NATURAL is specified, then let common column name be a

that is the of exactly one column

of T1 and the of exactly one column of T2. T1

shall not have any duplicate common column names and T2 shall

not have any duplicate common column names. Let corresponding

join columns refer to all columns of T1 and T2 that have

common column names, if any.



So

mysql> SELECT * FROM t1 NATURAL JOIN t2;


should return

+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
1 row in set (0.00 sec)

instead of

+---+---+---+---+
| a | b | b | c |
+---+---+---+---+
| 1 | 2 | 2 | 3 |
+---+---+---+---+
1 row in set (0.00 sec)

And it's a same thing for a derived table, i.e.

mysql> SELECT * FROM (SELECT 1 as a,1 as a) as b;
+---+---+
| a | a |
+---+---+
| 1 | 1 |
+---+---+
1 row in set (0.01 sec)

should not be possible.



Regards,

Jocelyn

----- Original Message -----
From: "Sinisa Milivojevic"
To:
Cc:
Sent: Friday, December 06, 2002 6:31 PM
Subject: Re: Bug in JOIN with MySQL-4.1


> Jocelyn Fournier writes:
> > Hi,
> >
> > Well since this is a join, I assume this is not needed to return 2 times
the
> > same column where the join is done.
> > And according to a comment here
> > http://www.mysql.com/doc/en/Differences_from_ANSI.html, SQL 92 specify
there
> > should be no duplicate column.
> > (indeed if we use this query with a derived table, the resulted derived
> > table will be a table with two times the same column).
> >
> > Regards,
> > Jocelyn
> >
>
>
> Why ??
>
> SELECT * FROM t1 LEFT JOIN t2 USING(b);
>
> is the same as :
>
> SELECT t1.a,t1.b,t2.b,t2.c FROM t1 LEFT JOIN t2 USING(b);
>
>
>
> --
> MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
>
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ 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 bugs-thread13234@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOIN with MySQL-4.1

Jocelyn Fournier writes:
> BTW, about
>
> I made a mistake, of course this is not a bug :)
>
> Regards,
> Jocelyn
>

Just keep shooting your bug reports, please ... ;o)

--
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/

__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ 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 bugs-thread13235@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOIN with MySQL-4.1

Hi!

>>>>> "Jocelyn" == Jocelyn Fournier writes:

Jocelyn> I agree, but SQL 92 says :
Jocelyn> 6) The same shall not be specified more than once in

Jocelyn> a .

Jocelyn> And :

Jocelyn> 6) If NATURAL is specified or if a simply

Jocelyn> containing a is specified, then:

Jocelyn> a) If NATURAL is specified, then let common column name be a

Jocelyn> that is the of exactly one column

Jocelyn> of T1 and the of exactly one column of T2. T1

Jocelyn> shall not have any duplicate common column names and T2 shall

Jocelyn> not have any duplicate common column names. Let corresponding

Jocelyn> join columns refer to all columns of T1 and T2 that have

Jocelyn> common column names, if any.


mysql> SELECT * FROM t1 NATURAL JOIN t2;

Jocelyn> should return

Jocelyn> +---+---+---+
Jocelyn> | a | b | c |
Jocelyn> +---+---+---+
Jocelyn> | 1 | 2 | 3 |
Jocelyn> +---+---+---+
Jocelyn> 1 row in set (0.00 sec)

Jocelyn> instead of

Jocelyn> +---+---+---+---+
Jocelyn> | a | b | b | c |
Jocelyn> +---+---+---+---+
Jocelyn> | 1 | 2 | 2 | 3 |
Jocelyn> +---+---+---+---+
Jocelyn> 1 row in set (0.00 sec)

I checked the above from "SQL 99 Complete, Really", but didn't find
anything to sustain the above.

On page 588, we have that the following joins are equal:

SELECT * from t1, t2 where t1.key2=t2.key2;

SELECT t1.*, t2.* from t1,t2;

SELECT * from t1 NATURAL JOIN t2 where t1.key2=t2.key2;

On page 589 it says that when refering to a field you MUST use the
table specificion and also that

"To eliminate duplicate columns from the result, specific reference> (rather than '*') must be put in the select list,..."

In other words: NATURAL join affects how the join condition is done
(In other words the automatic WHERE). It does not affect which
columns returned with '*')

I find the above very logical as otherwise you would have a big
problem in unions etc if result columns would change just depending on
how you specify the join.

Regards,
Monty

--
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ 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 bugs-thread13257@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOIN with MySQL-4.1

Hi,

About this email, even if this is far from being a critical bug, nobody
replied to the first part of the mail ?
(This is happening only if no database is selected before)

(> mysql> SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2 as
b,
> 3 as c) as b USING(a));
> ERROR 1054: Unknown column '.b.a' in 'on clause'
>
> mysql> SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2 as
b,
> 3 as c) as b USING(d));
> ERROR 1054: Unknown column '.a.d' in 'on clause')

Regards,
Jocelyn

----- Original Message -----
From: "Jocelyn Fournier"
To:
Sent: Friday, December 06, 2002 1:53 AM
Subject: Bug in JOIN with MySQL-4.1


> Hi,
>
> How-to-repeat :
>
> mysql> SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2 as
b,
> 3 as c) as b USING(a));
> ERROR 1054: Unknown column '.b.a' in 'on clause'
>
> mysql> SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2 as
b,
> 3 as c) as b USING(d));
> ERROR 1054: Unknown column '.a.d' in 'on clause'
>
> It's not subselect specific :
>
> use taist;
> CREATE TABLE t1 (a int(1) NOT NULL, b int(1) NOT NULL);
> CREATE TABLE t2 (b int(1) NOT NULL, c int(1) NOT NULL);
> INSERT INTO t1 VALUES (1,2);
> INSERT INTO t2 VALUES (2,3);
>
> mysql> SELECT * FROM t1 as d LEFT JOIN t2 as e USING(a);
> ERROR 1054: Unknown column 'taist.e.a' in 'on clause'
> mysql> SELECT * FROM t1 as d LEFT JOIN t2 as e USING(c);
> ERROR 1054: Unknown column 'taist.d.c' in 'on clause'
>
> BTW, why when we do :
>
> mysql> SELECT * FROM t1 LEFT JOIN t2 USING(b);
> +---+---+---+---+
> | a | b | b | c |
> +---+---+---+---+
> | 1 | 2 | 2 | 3 |
> +---+---+---+---+
> 1 row in set (0.00 sec)
>
> Or :
>
> mysql> SELECT * FROM t1,t2 WHERE t1.b=t2.b;
> +---+---+---+---+
> | a | b | b | c |
> +---+---+---+---+
> | 1 | 2 | 2 | 3 |
> +---+---+---+---+
> 1 row in set (0.00 sec)
>
> Or :
>
> mysql> SELECT * FROM ((SELECT 1 as a, 2 as b) as a NATURAL JOIN (SELECT 2
as
> b, 3 as c) as b);
> +---+---+---+---+
> | a | b | b | c |
> +---+---+---+---+
> | 1 | 2 | 2 | 3 |
> +---+---+---+---+
> 1 row in set (0.00 sec)
>
> Column b is displayed 2 times ??
>
> Regards,
> Jocelyn
>


------------------------------------------------------------ ---------
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 bugs-thread13299@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOIN with MySQL-4.1

Jocelyn Fournier writes:
> Hi,
>
> About this email, even if this is far from being a critical bug, nobody
> replied to the first part of the mail ?
> (This is happening only if no database is selected before)
>
> (> mysql> SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2 as
> b,
> > 3 as c) as b USING(a));
> > ERROR 1054: Unknown column '.b.a' in 'on clause'
> >
> > mysql> SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2 as
> b,
> > 3 as c) as b USING(d));
> > ERROR 1054: Unknown column '.a.d' in 'on clause')
>
> Regards,
> Jocelyn
>

Hi!

I just tried the above with and without a database selected and this
is what I get:


mysql -e "SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2 as b, 3 as c) as b USING(a))"
ERROR 1239 at line 1: Cardinality error (more/less than 0 columns)
mysql bug -e "SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2 as b, 3 as c) as b USING(a))"
ERROR 1239 at line 1: Cardinality error (more/less than 0 columns)


So, it seems to be fixed with the latest BK pushes.

But the error message should be fixed ! The above should generate a
syntax error.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
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 bugs-thread13307@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOIN with MySQL-4.1

Hi,

That's weird, I'm up to date with the bk tree (latest changeset : 1.1404.1.1
from vva)

mysql -e "SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2 as
b, 3 as c) as b USING(a))" -uroot -p
Enter password:
ERROR 1054 at line 1: Unknown column '.b.a' in 'on clause'

BTW, I don't understand with this should return a syntax error ? Only the
column specified into the USING is wrong. (and that's why it returns a
unknown column error, but it should print 'b.a' instead of '.b.a')
If I use a correct column, the query is working fine :

SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2 as b, 3 as
c) as b USING(b));
+---+---+---+---+
| a | b | b | c |
+---+---+---+---+
| 1 | 2 | 2 | 3 |
+---+---+---+---+
1 row in set (0.00 sec)

Regards,
Jocelyn


----- Original Message -----
From: "Sinisa Milivojevic"
To:
Cc:
Sent: Tuesday, December 17, 2002 1:20 PM
Subject: Re: Bug in JOIN with MySQL-4.1


> Jocelyn Fournier writes:
> > Hi,
> >
> > About this email, even if this is far from being a critical bug, nobody
> > replied to the first part of the mail ?
> > (This is happening only if no database is selected before)
> >
> > (> mysql> SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT
2 as
> > b,
> > > 3 as c) as b USING(a));
> > > ERROR 1054: Unknown column '.b.a' in 'on clause'
> > >
> > > mysql> SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2
as
> > b,
> > > 3 as c) as b USING(d));
> > > ERROR 1054: Unknown column '.a.d' in 'on clause')
> >
> > Regards,
> > Jocelyn
> >
>
> Hi!
>
> I just tried the above with and without a database selected and this
> is what I get:
>
>
> mysql -e "SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2
as b, 3 as c) as b USING(a))"
> ERROR 1239 at line 1: Cardinality error (more/less than 0 columns)
> mysql bug -e "SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN
(SELECT 2 as b, 3 as c) as b USING(a))"
> ERROR 1239 at line 1: Cardinality error (more/less than 0 columns)
>
>
> So, it seems to be fixed with the latest BK pushes.
>
> But the error message should be fixed ! The above should generate a
> syntax error.
>
> --
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ www.mysql.com
>
> Join MySQL Users Conference and Expo:
> http://www.mysql.com/events/uc2003/
>
>
> ------------------------------------------------------------ ---------
> 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 bugs-thread13307@lists.mysql.com
> To unsubscribe, e-mail
>
>
>
>


------------------------------------------------------------ ---------
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 bugs-thread13308@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOIN with MySQL-4.1

Jocelyn Fournier writes:
> Hi,
>
> That's weird, I'm up to date with the bk tree (latest changeset : 1.1404.1.1
> from vva)
>
> mysql -e "SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2 as
> b, 3 as c) as b USING(a))" -uroot -p
> Enter password:
> ERROR 1054 at line 1: Unknown column '.b.a' in 'on clause'
>
> BTW, I don't understand with this should return a syntax error ? Only the
> column specified into the USING is wrong. (and that's why it returns a
> unknown column error, but it should print 'b.a' instead of '.b.a')
> If I use a correct column, the query is working fine :
>
> SELECT * FROM ((SELECT 1 as a, 2 as b) as a LEFT JOIN (SELECT 2 as b, 3 as
> c) as b USING(b));
> +---+---+---+---+
> | a | b | b | c |
> +---+---+---+---+
> | 1 | 2 | 2 | 3 |
> +---+---+---+---+
> 1 row in set (0.00 sec)
>
> Regards,
> Jocelyn

I truly can't help you with error message.

May be it is a difference between our and public repositories.

The above should return syntax error because:

SELECT item_list .........

is missing before first derived table in derived table b ......

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
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 bugs-thread13311@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOIN with MySQL-4.1

Hi,

Humm ok, there were too many braces in the original query.

SELECT * FROM (SELECT 1 as a, 2 as b) as d LEFT JOIN (SELECT 2 as b, 3 as
c) as e USING(a);
ERROR 1054: Unknown column '.e.a' in 'on clause'

should be a more valid syntax for the join :)

Regards,
Jocelyn

----- Original Message -----
From: "Sinisa Milivojevic"
To:
Cc:
Sent: Tuesday, December 17, 2002 5:10 PM
Subject: Re: Bug in JOIN with MySQL-4.1


> Jocelyn Fournier writes:
> > Hi,
> >

>
> I truly can't help you with error message.
>
> May be it is a difference between our and public repositories.
>
> The above should return syntax error because:
>
> SELECT item_list .........
>
> is missing before first derived table in derived table b ......
>
> --
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ www.mysql.com
>
> Join MySQL Users Conference and Expo:
> http://www.mysql.com/events/uc2003/
>
>
>
>


------------------------------------------------------------ ---------
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 bugs-thread13312@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOIN with MySQL-4.1

Jocelyn Fournier writes:
> Hi,
>
> Humm ok, there were too many braces in the original query.
>
> SELECT * FROM (SELECT 1 as a, 2 as b) as d LEFT JOIN (SELECT 2 as b, 3 as
> c) as e USING(a);
> ERROR 1054: Unknown column '.e.a' in 'on clause'
>
> should be a more valid syntax for the join :)
>
> Regards,
> Jocelyn

Hi!

Table e does not have column a, so what is the issue here ??

If you do not specify a database, then database can't be printed.

Well, we could beautify error message, if that is your point.


--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
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 bugs-thread13314@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOIN with MySQL-4.1

Hi,

Yes, that was only about the '.' in the beginning of the error message.
It's why I was saying it wasn't a critical bug at all :)

Regards,
Jocelyn

----- Original Message -----
From: "Sinisa Milivojevic"
To:
Cc:
Sent: Tuesday, December 17, 2002 6:30 PM
Subject: Re: Bug in JOIN with MySQL-4.1


> Jocelyn Fournier writes:
> > Hi,
> >
> > Humm ok, there were too many braces in the original query.
> >
> > SELECT * FROM (SELECT 1 as a, 2 as b) as d LEFT JOIN (SELECT 2 as b, 3
as
> > c) as e USING(a);
> > ERROR 1054: Unknown column '.e.a' in 'on clause'
> >
> > should be a more valid syntax for the join :)
> >
> > Regards,
> > Jocelyn
>
> Hi!
>
> Table e does not have column a, so what is the issue here ??
>
> If you do not specify a database, then database can't be printed.
>
> Well, we could beautify error message, if that is your point.
>
>
> --
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ www.mysql.com
>
> Join MySQL Users Conference and Expo:
> http://www.mysql.com/events/uc2003/
>
>
>
>


------------------------------------------------------------ ---------
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 bugs-thread13315@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOIN with MySQL-4.1

Jocelyn Fournier writes:
> Hi,
>
> Yes, that was only about the '.' in the beginning of the error message.
> It's why I was saying it wasn't a critical bug at all :)
>
> Regards,
> Jocelyn
>

OK.

That is fixed now ......;o)

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
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 bugs-thread13316@lists.mysql.com
To unsubscribe, e-mail