Hi there,
I have noticed a few things that cause problems when using table aliases
(SELECT ... FROM table1 t1, table2 t2):
1) Fulltext index queries don't work. I tried to complete a following query:
a)
SELECT p.*, c.name AS categoryname FROM products p, categories c WHERE
MATCH(p.name,p.description) AGAINST('keywords');
It will cause an error that there is no FULLTEXT index matching the query.
b)
SELECT products.*, categories.name AS categoryname FROM products,
categories WHERE MATCH(products.name,products.description)
AGAINST('keywords');
works anyway well.
2) Table name locking doesn't work with table aliases - or works, but
eatch alias must be locked separately and the final queries must contain
same aliases. Examples a and b cause both an error that tables are not
locked. Examples c and d work well.
a)
LOCK TABLES products READ, categories READ;
SELECT p.*, c.name catname FROM products p, categories c WHERE (...);
b)
LOCK TABLES products READ, categories READ;
SELECT p.*, c.name catname FROM products p, products p2, categories c
WHERE (...)
c)
LOCK TABLES products p READ, categories c READ;
SELECT p.*, c.name catname FROM products p, categories c WHERE (...);
d)
LOCK TABLES products p1 READ, products p2 READ, categories c READ;
SELECT p.*, c.name catname FROM products p, products p2, categories c
WHERE (...)
Thank you - just to write down my experiences to the history and mail
archives. =)
Ville Mattila
