RES: RES: blocking tables in a query
Galbino
I think, I can be wrong, that through WHERE-clause or even of the
attributes list of a SELECT, there would be information so that SGBD
could define in which tables would be the researched information.
See the, for example, the query 1 that used:
Table CD (CD_ID, RECORD_LABEL, CD_TITLE);
Table Artist (ARTIST_ID, ARTIST_NAME);
Table Song (SONG_ID, CD_ID, SONG_NAME);
Table RecordLabel ( RECORD_LABEL_ID, RECORD_LABEL_NAME);
Query 1:
SELECT CD_TITLE
FROM CD, Artist
WHERE CD_TITLE='Creedence Clearwater Revisited - Recollection';
CD_TITLE is attribute of the table CD, it doesn't make part of the table
Artist. It is not also mentioned any attribute of the table Artist, just
she was related of FROM-clause, let us say, for an inattention.
Does that "human fail", could it be corrected by SGBD avoiding, this
way, in the case, what was the table used in the processing of the
query?
Volnei Galbino
--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?unsub=mysql-i...@freebsd.csie.nctu.edu.tw
Peter Zaitsev
>
> Query 1:
> SELECT CD_TITLE
> FROM CD, Artist
> WHERE CD_TITLE='Creedence Clearwater Revisited - Recollection';
>
> CD_TITLE is attribute of the table CD, it doesn't make part of the table
> Artist. It is not also mentioned any attribute of the table Artist, just
> she was related of FROM-clause, let us say, for an inattention.
>
> Does that "human fail", could it be corrected by SGBD avoiding, this
> way, in the case, what was the table used in the processing of the
> query?
>
> Volnei Galbino
Dear Galbino,
As Alexandr wrote this is legal query and it makes some sense, different
from what it will have if you remove "Artist" table. You do not want it
in most cases but still it is legal query and SQL engine is to allow you
to do what you want even if you do something wrong.
If you have automatically generated queries you shall better do checks
on the application side to ensure server do not get wrongly formed
queries.
If you really want server to do it, you're free to extend it to have
special option (ie as we have --safe-updates or sql_select_limit) but
It is just better to avoid this type of error :)
--
Peter Zaitsev, Senior Support Engineer
MySQL AB, www.mysql.com
Want to swim with the dolphins? (April 14-16, 2004)
http://www.mysql.com/uc2004/
Alexander Keremidarski
Hash: SHA1
Hello Galbino,
Galbino wrote:
> Hello Alexander,
>
> I think, I can be wrong, that through WHERE-clause or even of the
> attributes list of a SELECT, there would be information so that SGBD
> could define in which tables would be the researched information.
> See the, for example, the query 1 that used:
>
> Table CD (CD_ID, RECORD_LABEL, CD_TITLE);
> Table Artist (ARTIST_ID, ARTIST_NAME);
> Table Song (SONG_ID, CD_ID, SONG_NAME);
> Table RecordLabel ( RECORD_LABEL_ID, RECORD_LABEL_NAME);
>
> Query 1:
> SELECT CD_TITLE
> FROM CD, Artist
> WHERE CD_TITLE='Creedence Clearwater Revisited - Recollection';
>
> CD_TITLE is attribute of the table CD, it doesn't make part of the table
> Artist. It is not also mentioned any attribute of the table Artist, just
> she was related of FROM-clause, let us say, for an inattention.
>
> Does that "human fail", could it be corrected by SGBD avoiding, this
> way, in the case, what was the table used in the processing of the
> query?
As I wrote before this may be considered a "human fail", but the reason for
considering it a "mistake" instead of "intended" is something impossible for
Server to know and ti be implemented there.
Let me reread your query in terms of theory (simplified):
SELECT CD_TITLE
FROM CD, Artist
WHERE CD_TITLE='Creedence Clearwater Revisited - Recollection';
We have 3 specific things here:
* Join between 2 tables.
* Where clause filtering some rows in final result
* Select part defining what columns to include into output
For Join itself there is no join condition. This means Cartesian product is
required - every row from CD combined with every row from Artist. This is
sometimes called "Cross Join"
This type of join is perfectly legal
Then there is where clause which filters some rows from result of the join. Note
this as it is very important in this case. Where clause is applied to the
result of join, not to specific table.
Finally there are some columns to be selected. You specify columns from table CD
to be included in result. Again this is accepted syntax and logic in RDBMS
So we can read your query as:
"Join tables CD and Artist in a way to end with Cartesian product, then leave
only these rows for which CD.CD_TITLE = "some value" and from rows in *result*
which are matching this condition extract CD_TITLE only"
There is nothing wrong with this request from Server point of view. It s legal
by all means. Whether result is meaningful for you is something server has no
way to be aware of.
It is same as if you send query:
SELECT ... WHERE LENGTH(CD_TITLE) < -10;
Result of this query is predictable and meaningless. CD_TITLE can't be -10 bytes
long. String length can't be negative.
But the query is *valid*, server does not have an idea about your reason to ask
this so it *must* accept and execute it and return you Empty set as result.
In many cases of human error MySQL will simply refuse executing query with an
error, but this is because query becomes illegal. Like for example:
mysql> SELECT col1 FROM tt1, tt2;
ERROR 1052: Column: 'col1' in field list is ambiguous
mysql> SELECT * FROM tt1, tt2 WHERE col1=1;
ERROR 1052: Column: 'col1' in where clause is ambiguous
Here both tables have column called 'col1' so it is not clear which one is
requested.
mysql> SELECT col2 FROM tt1, tt2;
ERROR 1054: Unknown column 'col2' in 'field list'
mysql> SELECT * FROM tt1, tt2 WHERE tt1.col1=tt2.col2;
ERROR 1054: Unknown column 'tt2.col2' in 'where clause'
Here unexisting column is specified.
But there is huge class of queries which make no sense at all, but are valid and
will be executed:
mysql> SELECT * FROM tt1 WHERE col1 != col1;
Empty set (0.00 sec)
mysql> SELECT * FROM tt1 WHERE 1 > 10;
Empty set (0.00 sec)
Here you have WHERE clauses which can *never* be true. This pretty much looks
like human mistake. Server being server only must return result.
Same if you ask for:
SELECT ...
FROM CD, Artist
WHERE CD.RECORD_LABEL = Artist.ARTIST_NAME;
Does this query makes sense for you? I would say it makes no sense at all.
But for server it is just like any other query of form:
SELECT
FROM A, B
WHERE A.X = B.Y;
So the just server says "Yes Sir!!!" and does the job.
I don't want to say your request does not makes no sense.
It does, but this is not Server task.
As Peter Zaitsev also hinted you this could be implemented as some "Query
Analyzer" which checks your queries for "Potential problems" and sends you
alerts like "Warning - join without condition".
Like there are code analysers which can scan your application source and warn
you for potential mistakes.
> Volnei Galbino
>
Best regards
- --
Want to swim with the dolphins? (April 14-16, 2004)
http://www.mysql.com/uc2004/
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski <sa...@mysql.com>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFAEplyeehWBZ4HcYkRAukUAKDGWSETd0kPRkO5DbvP3kdmvbVtJwCgxQgl
v034jjkI2yiw220eqzspNZA=
=1zZB
-----END PGP SIGNATURE-----