Migration von Mysql 3 zu Mysql 5 Statment geht nicht mehr

Hallo,

Migration von Mysql 3 zu Mysql 5 LEFT JOIN
was mache ich denn falsch, wieso kennt er denn user.userid nicht
mehr ...

geht auf einer 3.23.53 mit der selben Tabellen struktur nicht mehr auf
der neuen 5.0.33 geht das selbe Statment leider nicht mehr ... weiss
jemand rat. Es soll herausgefunden werden welche frage nicht
beantwortet wurde zu welcher Frage in table fragen es keine
ensprechung in antworten usergibt.

Ich hoffe ihr versteht mich.

mysql> explain SELECT user.userid,fragen.id,
fragen.fragetext,fragen.ranswer FROM user, fragen STRAIGHT_JOIN
antwortenuser ON fragen.id = antwortenuser.frageid AND user.userid =
'1' limit 1;
ERROR 1054 (42S22): Unknown column 'user.userid' in 'on clause'

mysql> explain user
-> ;
+-----------+---------------------+------+-----+---------
+----------------+
| Field | Type | Null | Key | Default |
Extra |
+-----------+---------------------+------+-----+---------
+----------------+
| userid | bigint(20) unsigned | NO | PRI | NULL |
auto_increment |
| login | varchar(25) | NO | UNI |
| |
| vorname | varchar(100) | NO | |
| |
| nachname | varchar(100) | NO | |
| |
| password | varchar(100) | NO | |
| |
| userlevel | tinyint(3) unsigned | NO | | 0
| |
+-----------+---------------------+------+-----+---------
+----------------+
6 rows in set (0.02 sec)


mysql> explain fragen;
+-----------+---------------------+------+-----+---------
+----------------+
| Field | Type | Null | Key | Default |
Extra |
+-----------+---------------------+------+-----+---------
+----------------+
| id | bigint(11) unsigned | NO | PRI | NULL |
auto_increment |
| fragetext | text | NO | |
| |
| fragepic | varchar(255) | YES | | NULL
| |
| ranswer | bigint(11) unsigned | NO | MUL | 0
| |
| catid | bigint(11) unsigned | NO | MUL | 0
| |
+-----------+---------------------+------+-----+---------
+----------------+
5 rows in set (0.02 sec)

mysql> explain antwortenuser;
+-----------+---------------------+------+-----+---------
+----------------+
| Field | Type | Null | Key | Default |
Extra |
+-----------+---------------------+------+-----+---------
+----------------+
| id | bigint(11) unsigned | NO | PRI | NULL |
auto_increment |
| userid | bigint(11) unsigned | NO | MUL | 0
| |
| antwortid | bigint(11) unsigned | NO | MUL | 0
| |
| frageid | bigint(11) unsigned | NO | MUL | 0
| |
+-----------+---------------------+------+-----+---------
+----------------+
4 rows in set (0.02 sec)

Re: Migration von Mysql 3 zu Mysql 5 Statment geht nicht mehr

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: Migration von Mysql 3 zu Mysql 5 Statment geht nicht mehr

> > mysql> explain SELECT user.userid,fragen.id,
> > fragen.fragetext,fragen.ranswer FROM user, fragen STRAIGHT_JOIN
> > antwortenuser ON fragen.id =3D antwortenuser.frageid AND user.userid =
=3D
> > '1' limit 1;
> > ERROR 1054 (42S22): Unknown column 'user.userid' in 'on clause'

ist einfacher :) ich habe einfach rumprobiert....

SELECT user.userid,fragen.id, fragen.fragetext,fragen.ranswer FROM
user STRAIGHT_JOIN fragen LEFT JOIN antwortenuser ON fragen.id =3D
antwortenuser.frageid AND user.userid =3D '1' ; also user,frage in user
STRAIGHT_JOIN fragen geändert dann gehts.

Re: Migration von Mysql 3 zu Mysql 5 Statment geht nicht mehr

frank@sperbi.de wrote:
>
> Migration von Mysql 3 zu Mysql 5 LEFT JOIN


> mysql> explain SELECT user.userid,fragen.id,
> fragen.fragetext,fragen.ranswer FROM user, fragen STRAIGHT_JOIN
> antwortenuser ON fragen.id = antwortenuser.frageid AND user.userid =
> '1' limit 1;
> ERROR 1054 (42S22): Unknown column 'user.userid' in 'on clause'

Lesen bildet!


http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.ht ml

"Incompatible change: Beginning with MySQL 5.0.12, natural joins and
joins with USING, including outer join variants, are processed
according to the SQL:2003 standard. The changes include elimination of
redundant output columns for NATURAL joins and joins specified with a
USING clause and proper ordering of output columns. The precedence of
the comma operator also now is lower compared to JOIN, LEFT JOIN, and
so forth."


http://dev.mysql.com/doc/refman/5.0/en/join.html

"Previously, the comma operator (,) and JOIN both had the same
precedence, so the join expression t1, t2 JOIN t3 was interpreted as
((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression
is interpreted as (t1, (t2 JOIN t3)). This change affects statements
that use an ON clause, because that clause can refer only to columns in
the operands of the join, and the change in precedence changes
interpretation of what those operands are."

....

"To allow the join to be processed, group the first two tables
explicitly with parentheses so that the operands for the ON clause are
(t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

Alternatively, avoid the use of the comma operator and use JOIN instead:

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);"



XL

Re: Migration von Mysql 3 zu Mysql 5 Statment geht nicht mehr

On 22 Feb., 12:06, Axel Schwenke wrote:
> f...@sperbi.de wrote:
>
> > Migration von Mysql 3 zu Mysql 5 LEFT JOIN
>
>
>
> > mysql> explain SELECT user.userid,fragen.id,
> > fragen.fragetext,fragen.ranswer FROM user, fragen STRAIGHT_JOIN
> > antwortenuser ON fragen.id =3D antwortenuser.frageid AND user.userid =
=3D
> > '1' limit 1;
> > ERROR 1054 (42S22): Unknown column 'user.userid' in 'on clause'
>
> Lesen bildet!
>
> http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.ht ml
>
> "Incompatible change: Beginning with MySQL 5.0.12, natural joins and
> joins with USING, including outer join variants, are processed
> according to the SQL:2003 standard. The changes include elimination of
> redundant output columns for NATURAL joins and joins specified with a
> USING clause and proper ordering of output columns. The precedence of
> the comma operator also now is lower compared to JOIN, LEFT JOIN, and
> so forth."
>
> http://dev.mysql.com/doc/refman/5.0/en/join.html
>
> "Previously, the comma operator (,) and JOIN both had the same
> precedence, so the join expression t1, t2 JOIN t3 was interpreted as
> ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression
> is interpreted as (t1, (t2 JOIN t3)). This change affects statements
> that use an ON clause, because that clause can refer only to columns in
> the operands of the join, and the change in precedence changes
> interpretation of what those operands are."
>
> ...
>
> "To allow the join to be processed, group the first two tables
> explicitly with parentheses so that the operands for the ON clause are
> (t1,t2) and t3:
>
> SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 =3D t3.i3);
>
> Alternatively, avoid the use of the comma operator and use JOIN instead:
>
> SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 =3D t3.i3);"
>
> XL

Naja, ich habe noch nie was über so viele Versionen migrieren
müssen :)

Danke an alle