https://bugs.freedesktop.org/show_bug.cgi?id=38811
--- Comment #21 from Lionel Elie Mamane <[email protected]> 2012-06-12 07:25:17 PDT --- (In reply to comment #20) >> In the discussion in bug 50575, I discovered that SQLite3 has a rather weak >> notion of datatype, and uses dynamic typing instead of static typing which >> underlies the SQL data model. > Interesting of course :-) - I guess we're not talking though about importing > arbitrary sqlite databases, but having a well confined database inside our own > file formats, that we can apply our own constraints to (right?). Good luck explaining that to the users; that's however the least of the problems. > In such a situation surely it is possible to layer constraints / strong typing > on top of the database in our code ? [ wouldn't we do that by accident / > default anyway ]. But I guess it's more work if we have to have extra type > annotation information around the place. We could do that, but that doesn't give us the right operator semantics on these "subtypes". In SQL (and in most programming languages), the operators have a different meaning depending on the datatype of the value, and sqlite3 has too few types; only NULL, integer, real (floating-point) text or BLOB. Missing is e.g. any date/time related stuff. E.g. is "40 > 5" true or false? If "40" and "5" are strings, then it is false; if they are integers, then it is true. sqlite3 properly supports the distinction between integers and strings, so these examples are handled correctly. But sqlite has no notion of date or time values per se. Only as strings or integers or floats. Hence the problems as in bug 50575, where the query "SELECT * FROM table WHERE column='constant_value'" does not find the row because that equality is interpreted as string equality (where 0 <> 0.0 ) instead of timestamp equality (where 0 = 0.0). Also consider "WHERE column < '18:12'". As a timestamp, '18:12' is '2000-01-01 18:12:00': sqlite> SELECT datetime('18:12'); 2000-01-01 18:12:00 But if a row in the column contains '1900-05-09', that test will evaluate to false and the row not returned, while 'it should' evaluate to true. We *could* theoretically, when using sqlite, basically wrap each time, date, etc value into a "julianday()" call to force date/time handling, as when the user asks for this query: SELECT * FROM table WHERE column < '18:12' we see that column is date/time/timestamp and instead issue to SQLite3: SELECT * from table WHERE julianday(column) < julianday('18:12') 1) That's a much deeper analysis of the query than we do now, and heavy special-casing. Beh. 2) If you think it is feasible, what about this query: SELECT col2, MAX(column) as maxcolumn FROM table GROUP BY col2 WHERE strftime("%w", min(column, '18:12')) = '1' This is supposed to select, for each distinct value of col2, the maximal value of column that is a Monday among those that are earlier than '2000-01-01 18:12:00'. You *can* get this correctly using sqlite, with something like: SELECT col2, datetime(MAX(julianday(column))) as maxcolumn FROM table GROUP BY col2 WHERE strftime("%w", min(julianday(column), julianday('18:12'))) = '1' Note the conversion back to datetime string after we are done computing. Still think one can do that? Now consider: SELECT col2, MAX(column) as maxcolumn FROM table GROUP BY col2 WHERE strftime("%Y-%m-%d", min(column, '18:12')) = '00:00' Now, to get this correct: SELECT col2, datetime(MAX(julianday(column))) as maxcolumn FROM table GROUP BY col2 WHERE strftime("%Y-%m-%d", min(julianday(column), julianday('18:12'))) = date('00:00') or SELECT col2, datetime(MAX(julianday(column))) as maxcolumn FROM table GROUP BY col2 WHERE julianday(strftime("%Y-%m-%d", min(julianday(column), julianday('18:12')))) = julianday('00:00') Note that depending on the format given by strftime, we need to decide whether this is a time/date or not. Also consider SELECT col2, datetime(MAX(julianday(column))) as maxcolumn FROM table GROUP BY col2 WHERE strftime('just a constant', min(julianday(column), julianday('18:12'))) = 'just a constant' -- Configure bugmail: https://bugs.freedesktop.org/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the assignee for the bug. _______________________________________________ Libreoffice-bugs mailing list [email protected] http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
