query problems with MySQL 5.0.x

[punzeroni]

Hi there,

I'm using 4images in version 1.7.1.

I recently updated my mysql server to version 5 (5.0.13-rc). After that I received heaps of SQL-Errors like this:

DB Error: Bad SQL Query:
SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name, u.user_name
FROM 4images_images i, 4images_categories c
LEFT JOIN 4images_users u ON (u.user_id = i.user_id)
WHERE i.image_active = 1 AND i.cat_id NOT IN (0, -1) AND c.cat_id = i.cat_id LIMIT 290, 1
Unknown column 'i.user_id' in 'on clause'

The error messages are all looking the same: "Unknown column 'XY' in 'on clause'"
As a result 4images doesn't show any images 

So i had a search through the mysql bug database and discovered the following:
http://bugs.mysql.com/bug.php?id=12943

To summarize it, among other things they say that the join-SQL syntax used by 4images is not SQL:2003 compliant. Since version 5 mysql claims to be fully SQL:2003 compliant and thus complains about those queries. As it seems the mysql guys are working on some fixes to make mysql 5 work with non SQL:2003 compliant queries.

If you take a look at the example quoted above you can see the problem: the 2 tables (u and i) mentioned in the ON statement of JOIN need to be operands of the JOIN statement. But currently the operands are c and u. 

the correct syntax needs to be something like this:

SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name, u.user_name
FROM 4images_categories c, 4images_images i
LEFT JOIN 4images_users u ON (u.user_id = i.user_id)
WHERE i.image_active = 1 AND i.cat_id NOT IN (0, -1) AND c.cat_id = i.cat_id LIMIT 290, 1

this problem exists in a lot of 4images files

I took the liberty to rewrite some queries in order to make them SQL:2003 compliant.
I put my altered files here:
http://www.milchsemmel.de/~punzeroni/4images-SQL2003fix.tar.bz2

Cheers
punzeroni

[V@no]

* V@no didn't know v5.x was already released!

Since you are so up-to-date, what about the lattest version 5.0.15?  can you see if it works there?

[punzeroni]

what about the lattest version 5.0.15?  can you see if it works there?

5.0.15 came into gentoo portage this night and it just finished compiling 10 minutes ago but the problems remain. if you turn around the JOIN operands - as explained in my example - it works.

See you
punzeroni

[darkman83]

okay guys...before i use this fiy i get 2 of this errors...and after i use this i get only one, i think the only thing wich need to changed is the random image displaying...mhm...i dunno what to change, but i read the topic..my i be noobish? have also tried to disable the random image displying...but i dunno how lol
I use MySQL 5.0.15 xD

So any fixes there?

[punzeroni]

Hi darkman,

did you also overwrite the file functions.php in the include subdir? I reckon the random-function is in there.

Cheers
punzeroni

[darkman83]

yes, i've overwritten all files...also functions.php...i think you have forgotten to fix this ^^

[punzeroni]

@darkman

I'm not sure what your problem is. I think I fixed all the files I could find. Random pictures work for me. Could you please post your error message and - if possible - all of the 4images dist files. Then I'll have a look.

[darkman83]

http://dm.hopto.org/4images/

as i said...before i get 2 of those lines...and after replacing the files only 1!!!

[V@no]

Do you think its a bug in mysql itself? well, I do, because for me it doesnt make sence why would it metter the sequence of the tables in the query...

[EDIT]
I digged out something, its NOT a bug:
http://bugs.mysql.com/bug.php?id=13551

This is a change that was made in 5.0.15 to make MySQL more compliant with the
standard.
According to the SQL:2003

<from clause> ::= FROM <table reference list>
<table reference list> ::=
    <table reference> [ { <comma> <table reference> }... ]
<table reference> ::=
    <table factor>
  | <joined table>
<joined table> ::=
    <cross join>
  | <qualified join>
  | <natural join>
...

Thus when you write

  ... FROM t1 , t2 LEFT JOIN t3 ON (expr)

it is parsed as

(1)    ... FROM t1 , (t2 LEFT JOIN t3 ON (expr))

and not as

(2)    ... FROM (t1 , t2) LEFT JOIN t3 ON (expr)

so, from expr you can only refer to columns of t2 and t3 - operands of the
join.
Workaround - to put parentheses explicitly as in (2). Then you can refer to t1
columns from expr.

Unfortunately, this change is not properly documented in the manual, it will be
fixed.

So, try instead of switching the tables around replace

Code: [Select]

FROM 4images_images i, 4images_categories c with:

Code: [Select]

FROM (4images_images i, 4images_categories c) see if it helps

[darkman83]

okay i've tried....if i do so it shows me no more images up and i get those two error lines back as before xD

[punzeroni]

Hi guys,

sorry I was gone for a while... Did a kernel update which didn't work as it was supposed to

@darkman: I can't find any additional problems in my files. So maybe send me your whole 4images distribution and I'll have a look at it (ok... you might leave the passwords out )

@V@no: I gave Sergei's brackets a try for some examples and it works for me. So which version you finally choose seems to be an aestetical question.

Ciao
punzeroni

[V@no]

@V@no: I gave Sergei's brackets a try for some examples and it works for me. So which version you finally choose seems to be an aestetical question.

I'd say the brackets version
But, I'm still waiting for Jan's responce about "would it be faster to left join the second table instead of joining them as it is now":

SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name, u.user_name
FROM 4images_images i
LEFT JOIN 4images_categories c ON (c.cat_id = i.cat_id)
LEFT JOIN 4images_users u ON (u.user_id = i.user_id)
WHERE i.image_active = 1 AND i.cat_id NOT IN (0, -1) LIMIT 290, 1

In fact since the only value is being read from 4images_categories table is "cat_name", and that value is avalabe by default in $cat_cache array, I'd say 4images_categories table should not be included in the query at all and the final queries with work around would be:

Code: [Select]

    $sql = "SELECT DISTINCT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits".get_user_table_field(", u.", "user_name")."
            FROM ".IMAGES_TABLE." i
            LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)
            WHERE i.image_active = 1 AND i.cat_id NOT IN ($cat_id_sql)
            ORDER BY RAND()";
    $result = $site_db->query($sql);
    while ($row = $site_db->fetch_array($result)) {
      $random_image_cache[$row['cat_id']] = $row;
      $random_image_cache[$row['cat_id']]['cat_name'] = $cat_cache[$row['cat_id']]['cat_name'];
And

Code: [Select]

    $sql = "SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits".get_user_table_field(", u.", "user_name")."
            FROM ".IMAGES_TABLE." i
            LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)
            WHERE i.image_active = 1 AND i.cat_id NOT IN ($cat_id_sql)
            LIMIT $number, 1";
    $random_image_cache[0] = $site_db->query_firstrow($sql);
    $random_image_cache[0]['cat_name'] = $cat_cache[$random_image_cache[0]['cat_id']]['cat_name'];

[darkman83]

Ah okay...thanks...that worked...no more error is showing up ^^
YEAHH BIG THANKS!!!! 

[baconzoo]

Can you spell out the process of fixing this?  For the lame?

[Murphy]

please give us a chance to fix it