Where clause with cast ignores sub-select #6712
Last updated: 2019-09-02 16:05:28 +0200
Date: 2019-06-11 12:56:38 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.103 Safari/537.36
CREATE TABLE x (
CREATE VIEW executiontimes as select * from x where attribute = 'executiontime';
Which means the where clause has been applied to table "x", not to view "executiontimes"
PS. If possible, I would really appreciate the fix to be back-ported to Aug2018 (also affected)
conversion of string '3.15.0' to type lng failed.
MonetDB 5 server 11.33.3 (Apr2019) (64-bit, 128-bit integers)
Date: 2019-06-11 14:10:59 +0200
For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=5ad88b364ee5
Date: 2019-06-11 14:37:21 +0200
well the query gets merged (as we do not support physical views). Somehow we optimized (pushed the select with numbers) before the match on strings (that could be simply fixed, but is a performance issue not really correctness..)
Date: 2019-06-11 14:38:13 +0200
I think your analysis is flawed. The problem you're facing is not that the subquery is being ignored, it is instead that the conversion and selection on the value column is done before the selection on the attribute column. Just having a view doesn't mean that the view is actually executed first.
Your query is equivalent to
The two conditions are reversed because the SQL engine probably thinks that might be more efficient.
Date: 2019-06-11 14:46:43 +0200
Sjoerd, I don't think the query you wrote is equivalent to mine. You perform both selections on the same relations. I don't.
I think it is wrong to think about it in terms of execution order.
The query I wrote logically defines the content of relation "executiontimes". When I select anything from it, I expect to select from that content, no matter the execution order that the engine decides.
Date: 2019-06-11 15:00:15 +0200
For reference, PostgreSQL gives for example the expected result. Not because it chooses a different execution order, but because this is the only correct one.
Date: 2019-06-11 15:22:59 +0200
If we look at it without a view, the following is equivalent to my first query:
Expression "cast(executiontimes.value as bigint)" depends on relation executiontimes. It cannot be evaluated before executiontimes is evaluated.
Flattening everything to a bunch of selections on the same relation is wrong, in my opinion.
Date: 2019-06-11 19:40:06 +0200
For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=47300227a174
Date: 2019-06-11 19:56:27 +0200
when possible we now push the candidate list into the batcalc.convert
Date: 2019-06-12 16:02:26 +0200
I'm sorry to insist, but I think the fix solves my initial example, not the real issue.
Here another example that fails.
CREATE TABLE x (n INTEGER);
INSERT INTO x VALUES (-2);
This is not correct. The semantics of the query is not respected. I'm explicitly using relation nonzero which is by definition not containing zeros. This query should not attempt that division.
I completely understand that the implementation flattens the two selections on the same base table. I also understand that this approach produces the correct result in most cases. But not in all cases. This is one.
The text was updated successfully, but these errors were encountered: