Re: AW: CAST and CONVERT string to int

Hi Dennis,

would you mind sharing your final solution on this list? This way, other members of this list could also benefit and learn from your findings. In particular, did my final sub-query "trick" work, or did MonetDB "optimize it away"?

Thanks! Stefan

----- On Jul 20, 2015, at 9:05 AM, Dennis Wiest Dennis.Wiest@avitech.aero wrote:

Hello Martin and Stefan,

Thanks for the great information, so I could work out a good solution to me. Stefan your explanation was amazing and is also logical. You were with your assumption right.

many thanks

Dennis

-----Ursprüngliche Nachricht----- Von: users-list [mailto:users-list-bounces+dennis.wiest=avitech.aero@monetdb.org] Im Auftrag von Stefan Manegold Gesendet: Donnerstag, 16. Juli 2015 18:11 An: Communication channel for MonetDB users Betreff: Re: CAST and CONVERT string to int

Hi Dennis,

am I right that "value" can have non-numerical values for "xpath_id" <> 378, but has only numerical values (represented as strings) for "xpath_id" = 378?

If so, your query inherently assumes that the database system evaluates the "xpath_id" = 378 predicate first, and the predicate on "value" (incl. the type cast from string via float to integer only on the tuples that qualify the first predicate, right?

Well, while this assumption might in general be reasonable --- after all, evaluating the plain integer point predicate can generally be considered cheaper and more selective than evaluating the cast from string via float to integer plus range predicate. However, given commutativity of conjunctive predicates --- which allows for above re-ordering in the first place --- the evaluation order has no semantic meaning and it's up to the DBMS to choose any order.

In this case, i.e., given your data and query, MonetDB seems to choose to evaluate the predicate on "value" first, and the predicate on "xapths_id" second, probably failing to "see" potential performance benefits of the reversed order.

You might want to confirm this by double-checking the "PLAN" or "EXPLAIN" of your query.

Your last query works, because the projection in the select clause definitely only happens for the tuples that qualify the predicate(s) in the where clause.

You might consider MonetDB's inability to create a "better" plan, i.e., to evaluate the "xpath_id" predicate before the "values" predicate a bug and file a bug report via our bugtracker. However, in general there cannot be any "guarantees" about execution order of (commutative) predicates.

You can try to force the systems into a desired evaluation order by using a subquery, e.g.,

select * from (select * from value v2 WHERE v2.xpath_id = 378) as v3 where CAST(v3.value AS integer) > 1000 LIMIT 10;

Please note that I use a direct cast from string to integer, because I see no point in casting to float, first.

Best, Stefan

----- On Jul 16, 2015, at 11:39 AM, Dennis Wiest Dennis.Wiest@avitech.aero wrote:

...

Hello,

I have a question for the Operation Cast and CONVERT .

I have the following table and wants to run this query.

select * from value v2 WHERE v2.xpath_id = 378 AND v2.value BETWEEN 1000 AND 2000 LIMIT 10;

This provides me with the wrong result because v2.value is a textfield .

Now I wanted to with the following query to fix this problem, but I get an error . Is it at all possible to the cast or convert?

select * from value v2 WHERE v2.xpath_id = 378 AND CAST(CAST(v2.value AS float)AS integer) > 1000 LIMIT 10;

conversion of string 'RNAV' to type dbl failed.

But this one Works

select CAST(CAST(v2.value AS float)AS integer) from value v2 WHERE v2.xpath_id = 378 LIMIT 10;

The problem is , “value” must be a text field , “ value” can take all possible values .

CREATE TABLE value (

id int NOT NULL AUTO_INCREMENT,

element_id int NOT NULL,

xpath_id int NOT NULL,

value text,

PRIMARY KEY (id)

);

Thanks 4 Help

Greetings Dennis

Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Geschäftsführer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero

---

users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list