Re: [Firebird-devel] ODP: Firebird 4: Could not find acceptable ICUlibrary
14.06.2019 22:10, Mark Rotteveel wrote: On 2019-06-14 20:27, Adriano dos Santos Fernandes wrote: On 14/06/2019 13:01, Mark Rotteveel wrote: And I repeat what I said earlier: if Firebird uses an offset that is too large then I can't use the standard features in Java for date/time handling and I'll need to mangle things myself to get a - hopefully - meaningful value. What is the Java range? [-18:00, +18:00], see https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/time/ZoneOffset.html : This article shows that Java keep seconds in ZoneOffset (not minutes as Firebird currently). 18 hours limit is probably because two-byte word could keep just 18 full hour's: 65535 / (60*60) = 18 but sign should be kept somewhere else... Should we reconsider current implementation ? Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6094) Outer join tables, on text columns with different collates (perhaps charsets too), may propagate wrong LIKE predicate
Outer join tables, on text columns with different collates (perhaps charsets too), may propagate wrong LIKE predicate - Key: CORE-6094 URL: http://tracker.firebirdsql.org/browse/CORE-6094 Project: Firebird Core Issue Type: Bug Affects Versions: 3.0.3, 2.1.7 Environment: Firebird 2.1.7, Firebird 3.unknown Reporter: Arioch Initial report: https://stackoverflow.com/questions/56858452/why-did-my-where-clause-affect-my-left-join Initial discussion: https://www.sql.ru/forum/1314381-a/fb3-join-vs-like-vs-case-sensitivity The example is based upon mismatched collates but probably implementation behavior is the same with regard to charsets too. DML: ~ create table t_CI ( -- case-INSENSITIVE id VarChar(10) character set UTF8 primary key collate UNICODE_CI, payload integer ); create table t_CS ( -- case-SENSITIVE id VarChar(10) character set UTF8 primary key collate UNICODE, payload integer ); insert into t_CS values ( 'ABCD', -100 ); insert into t_CI values ( 'ABCD', +22 ); ~~ We now have two tables with ID fields having different collates. However the ID value is the same within both tables and is within both collates. When joining those two tables - the results should be either data from both tables or no data at all. Situation, when upon join by ID data is fetched from only one of the two tables but not from another would be abnormal !!! Abnormal query returning only half of the data, most probably due to propagation of `LIKE` predicate with wrong collation: ~~~ select * from T_CI left join T_CS on T_CS.id = T_CI.id where T_CI.id like '%bc%' PLAN JOIN (T_CI NATURAL, T_CS INDEX (RDB$PRIMARY3)) Adapted plan: PLAN JOIN (T_CI NATURAL, T_CS INDEX (INTEG_21)) ~~~ For comparison: Correct queries returning data from BOTH tables: select * from T_CS, T_CI where T_CS.id = T_CI.id select * from T_CS left join T_CI on T_CS.id = T_CI.id select * from T_CI left join T_CS on T_CS.id = T_CI.id select * from T_CI left join T_CS on T_CS.id = T_CI.id where T_CI.id like '%BC%' select * from T_CS left join T_CI on T_CS.id = T_CI.id where T_CS.id like '%BC%' select * from T_CI left join T_CS on T_CS.id = T_CI.id where T_CI.id collate unicode_ci like '%bc%' ~~~ Correct queries returning no data at all: ~~~ select * from T_CS left join T_CI on T_CS.id = T_CI.id where T_CS.id like '%bc%' select * from T_CI left join T_CS on T_CS.id = T_CI.id where T_CI.id collate unicode like '%bc%' ~~~ One more query, somewhat similar, different is engaging master-table index thus probably preventing LIKE-propagation ~~ select * from T_CI left join T_CS on T_CS.id = T_CI.id where T_CI.id starting with 'abc' -- OK: data ~ data Plan: PLAN JOIN (T_CI INDEX (RDB$PRIMARY2), T_CS INDEX (RDB$PRIMARY3)) ~ -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6093) Errors simultaneously accessing security database from two engines
Errors simultaneously accessing security database from two engines -- Key: CORE-6093 URL: http://tracker.firebirdsql.org/browse/CORE-6093 Project: Firebird Core Issue Type: Sub-task Reporter: Alexander Peshkov Assignee: Alexander Peshkov -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Underlying format of high precision numerics
>I don't think that somebody perform time-critical intensive > calculations in SQL. From > my POV it has no sense. You'd be surprised what people do in SQL. Also it's maybe not about the calculation itself, but about the number of calculations. I.e. doing some fairly fine looking calculation over a 1M rows. Suddenly the time adds up. -- Mgr. Jiří Činčura https://www.tabsoverspaces.com/ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-6092) isql -i should stop after first error
isql -i should stop after first error - Key: CORE-6092 URL: http://tracker.firebirdsql.org/browse/CORE-6092 Project: Firebird Core Issue Type: Bug Components: ISQL Reporter: Karol Bieniaszewski currently when we run isql -i and we provide script file name then if inside script is an error isql only report it and ignore and it go to the next script this can be catastrophic in case when next script is dependend on previous one which is near to always true -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel