CAST and CONVERT string to int
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
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:
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
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:
-- | 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
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:
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
Hi Stefan, I've completely forgotten again attach my solution. I've tried 3 ways that all work for me. Your solution did not work. As you have already stated MonetDB decides itself how to process the query. It is important CAST (CAST (value AS float) As Integer). Otherwise it is not possible for me to, converts the string to an integer. Conversion of string '1559.0551' to type int failed. Solution 1: I'm creating a table for the airspaces because they are always needed. CREATE TABLE Airspace ( id int NOT NULL AUTO_INCREMENT, Element_ID int NOT NULL, Upper int, PRIMARY KEY (id) ); Then I convertiere the airspaces values in integer. This works without error because these are only Integer Value. INSERT INTO airspace (Element_ID, upper) select Element_ID, CAST (CAST (value AS float) as integer) as upper value from where xpath_id = (SELECT ID FROM XPath where XPath = 'Airspace/timeSlice/AirspaceTimeSlice/geometryComponent/AirspaceGeometryComponent/theAirspaceVolume/AirspaceVolume/upperLimit/' And at the end I do my query Select v. * FROM A airspace, value v WHERE a.upper> 1000 AND a.upper <2000 AND a.element_id = v.element_id AND v.xpath_id = 371 Solution 2: Is the same as solution 1 with a VIEW CREATE VIEW AS airspaces select id, Element_ID, CAST (CAST (value AS float) as integer) as upper value from where xpath_id = (SELECT ID FROM XPath where XPath = 'Airspace/timeSlice/AirspaceTimeSlice/geometryComponent/AirspaceGeometryComponent/theAirspaceVolume/AirspaceVolume/upperLimit/'); SELECT * FROM WHERE upper airspaces BETWEEN 1001 AND 1999; Solution 3: Comes from Martin. select * from value v2 WHERE v2.xpath_id = 378 AND length(v2.value) = 4 AND ((substring(v2.value, 1, 1) between '1' AND '1' AND substring(v2.value, 2, 1) between '0' AND '9' AND substring(v2.value, 3, 1) between '0' AND '9' AND substring(v2.value, 4, 1) between '0' AND '9') OR (v2.value = '2000')) LIMIT 10; This avoids casting and conversion errors. Alternatively do: ALTER TABLE value ADD COLUMN value_int int NULL; UPDATE value set value_int = cast(value as int) WHERE value IS NOT NULL AND length(value) = 4 AND substring(value, 1, 1) between '0' AND '9' AND substring(value, 2, 1) between '0' AND '9' AND substring(value, 3, 1) between '0' AND '9' AND substring(value, 4, 1) between '0' AND '9'; add next use query: select * from value v2 WHERE v2.xpath_id = 378 AND v2.value_int BETWEEN 1000 AND 2000 LIMIT 10; If you do not want to change the table with adding a column, use a view: CREATE VIEW value_ints_only AS SELECT id, element_id, xpath_id, value, cast(value as int) as value_int FROM value WHERE value IS NOT NULL AND length(value) = 4 AND substring(value, 1, 1) between '0' AND '9' AND substring(value, 2, 1) between '0' AND '9' AND substring(value, 3, 1) between '0' AND '9' AND substring(value, 4, 1) between '0' AND '9'; select * from value_ints_only v2 WHERE v2.xpath_id = 378 AND v2.value_int BETWEEN 1000 AND 2000 LIMIT 10; All 3 have a fast performance with no indexes. Value table has 12519446 entries. -----Ursprüngliche Nachricht----- Von: users-list [mailto:users-list-bounces+dennis.wiest=avitech.aero@monetdb.org] Im Auftrag von Stefan Manegold Gesendet: Montag, 20. Juli 2015 09:13 An: Communication channel for MonetDB users Betreff: 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:
-- | 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
participants (3)
-
Dennis Wiest -
martin van dinther -
Stefan Manegold