Bugs item #2183288, was opened at 2008-10-21 00:57
Message generated for change (Comment added) made by stmane
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2183288&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL/Core
Group: SQL CVS Head
Status: Closed
Resolution: Fixed
Priority: 5
Private: No
Submitted By: Stefan de Konink (skinkie)
Assigned to: Niels Nes (nielsnes)
Summary: SQL: no error about invalid *qualified* column name
Initial Comment:
I find many errors that I reported here were based on my incompetence to
remember field names, and the use invalid fieldnames. Would it be possible to
warn a user if a comparison is done on a field that does not exist in the
table, nor is a variable?
Example, I type:
SELECT * FROM way_tags WHERE waysid = 100;
In this table the field waysid does not exist, and the query will also return 0
tuples.
If we advance to a query where this wrong field is used in a comparison with
another field that doesn't exist hence:
select * from way_tags as w1, way_tags as w2 where w1.k = 'bridge' and w1.wayid
= w2.wayid;
Unexpected behavior occurs.
Now I don't know if this is a bug, or actually a valid principle according to
SQL. But if two features on their own return 0 tuples simple because the
feature doesn't exist, shouldn't the query at least directly return 0, warn (or
better abort!) if such thing is located. I plea guilty, fully, but there are
users that like my don't have the precision of working with MonetDB and are
rather 'verstrooid'.
----------------------------------------------------------------------
>Comment By: Stefan Manegold (stmane)
Date: 2008-10-21 19:24
Message:
@skinkie:
I did not know what was wrong, I simply located and analyzed the problem
as follow (you may read and learn, if you want to):
You said:
"
SELECT * FROM way_tags WHERE waysid = 100;
In this table the field waysid does not exist, and the query will also
return 0 tuples.
"
but I found out:
"
sql>create table way_tags (way integer);
0 tuples
sql>SELECT * FROM way_tags WHERE waysid = 100;
!SELECT: identifier 'waysid' unknown
0 tuples
"
(I still don't know, whether you got this error message or not --- I
cannot imagine why it should not have occurred in your case, but have no
clue why you did not mention it...)
Then, I just looked at the relevant differences between you ("simple")
first query and your ("complex") second query, noticed that this might be
the qulified vs. non qualified column name in the predicate, tried it, and
found the problem (and a very simple way to reproduce it).
Well, I do not necessarily expect that users do all this analysis (though
it was trivial and straight forward in this case), but they should at least
provide all relevant information in a bug report --- in this case:
mentioning clearly whether there was a (correct and to be expected!) error
message with the first query and what exactly the "Unexpected behavior" of
the second one was --- or not expect any quick or "useful" reply ...
----------------------------------------------------------------------
Comment By: Stefan de Konink (skinkie)
Date: 2008-10-21 18:54
Message:
Niels, thanks for fixing it.
@Stefan:
"The difference is most probably related to giving only an attribute name
vs. giving <table_name>.<attribute_name> ..."
I think you found something out that I was not mentally aware of at the
time of 'seeing' this bug, fundamentally you already knew what was wrong.
With mentioning 'unexpected behavior occurs' I was not aware of it.
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2008-10-21 15:53
Message:
Fixed by Niels in CVS.
Thanks for the quick fix, Niels!
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2008-10-21 14:11
Message:
for got this in my previous posting below:
sql>create table t (a integer);
0 tuples
(ps: see my previous posting below as an example how to file a proper
useful bug report including minimal instructions to fully reproduce the
problem and information about where the problem occurs --- any (successful)
attempt to learn from that would be highly appreciated...)
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2008-10-21 14:06
Message:
While MonetDB/SQL correctly issued an error when trying to use a
non-existing unqualified column name, it seems to fail to do so in case of
non-existing qualified column names:
(with CVS HEAD of Tue Oct 21 09:19:22 CEST 2008, configured with defaults,
compiled with gcc 4.1.2 on 64-bit Fedora 8)
sql>select * from t where b = 0;
!SELECT: identifier 'b' unknown
0 tuples
sql>select * from t where t.b = 0;
0 tuples
sql>select * from t where s.a = 0;
!SELECT: no such column 's.a'
0 tuples
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2008-10-21 13:57
Message:
Ah, here's the actual problem (and I actually consider this a bug):
With
sql>create table way_tags (way integer);
0 tuples
query
sql>SELECT * FROM way_tags WHERE waysid = 100;
^^^^^^
!SELECT: identifier 'waysid' unknown
0 tuples
correctly issues an error,
query
sql>SELECT * FROM way_tags WHERE way_tags.waysid = 100;
^^^^^^^^^^^^^^^
0 tuples
does not.
The difference is most probably related to giving only an attribute name
vs. giving <table_name>.<attribute_name> ...
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2008-10-21 13:48
Message:
works for me (CVS head of Tue Oct 21 09:19:22 CEST 2008):
sql>create table way_tags (way integer);
0 tuples
sql>SELECT * FROM way_tags WHERE waysid = 100;
!SELECT: identifier 'waysid' unknown
0 tuples
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2183288&group_id=56967
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
