[Firebird-devel] [FB-Tracker] Created: (CORE-5163) prepared statement with subselect with union does not use indices

[Reginald Poyau (JIRA)]

prepared statement with subselect with union does not use indices 
--

 Key: CORE-5163
 URL: http://tracker.firebirdsql.org/browse/CORE-5163
 Project: Firebird Core
  Issue Type: Bug
Reporter: Reginald Poyau


steps to reproduce:
-- create two tables:
--
CREATE TABLE test1 (id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(20));
CREATE TABLE test2 (id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(20));
-- add some indices

create index test1_idx ON test1(name);
create index test2_idx ON test2(name);

-- data
insert into test1(id, name) values(1, 'foo');
insert into test1(id, name) values(3, 'food');
insert into test2(id, name) values(2, 'bar');
insert into test2(id, name) values(4, 'barking');


execute following queries using python fdb
 *firebird trace configuration  trace.conf:

enabledtrue
include_filter %(INSERT|UPDATE|DELETE|SELECT)%
log_statement_finish   true
log_procedure_finish   true
log_trigger_finish true
print_plan true
print_perf true
time_threshold 0

run:
fbtracemgr -se service_mgr -user sysdba -password test -start -name "My trace" 
-config trace.conf

using python
>>> import fdb
>>> password = 'test'
>>> dsn = 'localhost:/tmp/test.fdb'
>>> user='sysdba'
>>> con = fdb.connect(dsn=dsn, user=user, password=password)

>>> cr = con.cursor()
>>>  q1 = """
SELECT t.id, t.name
FROM (
SELECT id, name FROM test1 WHERE name LIKE 'f%'
UNION
SELECT id, name FROM test2 WHERE name LIKE 'b%') AS t
"""
>>> cr.execute(q1).fetchall()
[(1, 'foo'), (2, 'bar'), (3, 'food'), (4, 'barking')]

>>> q2 = """
SELECT t.id, t.name
FROM (
SELECT id, name FROM test1 WHERE name LIKE ?
UNION
SELECT id, name FROM test2 WHERE name LIKE ?) AS t
"""
>>> params = ('f%', 'b%')
>>> cr.execute(q2, params).fetchall()
[(1, 'foo'), (2, 'bar'), (3, 'food'), (4, 'barking')]


Trace output for each queries
q1
--

SELECT t.id, t.name
FROM (
SELECT id, name FROM test1 WHERE name LIKE 'f%'
UNION
SELECT id, name FROM test2 WHERE name LIKE 'b%') AS t

^^^
PLAN (T TEST1 INDEX (TEST1_IDX))
PLAN (T TEST2 INDEX (TEST2_IDX))
4 records fetched
  0 ms, 2 read(s), 12 fetch(es)

Table Natural IndexUpdateInsert
Delete   Backout Purge   Expunge
***
TEST1 2 
   
TEST2 2 
   


q2
---

SELECT t.id, t.name
FROM (
SELECT id, name FROM test1 WHERE name LIKE ?
UNION
SELECT id, name FROM test2 WHERE name LIKE ?) AS t

^^^
PLAN (T TEST1 NATURAL)
PLAN (T TEST2 NATURAL)

param0 = varchar(20), "f%"
param1 = varchar(20), "b%"

4 records fetched
  0 ms, 14 fetch(es)

Table Natural IndexUpdateInsert
Delete   Backout Purge   Expunge
***
TEST1   2   
   
TEST2   2   
 




-- 
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



--
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351=/4140
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Services and encoding

[Dimitry Sibiryakov]

22.03.2016 16:31, Mark Rotteveel wrote:
> Sounds like a hack, why not just introduce a lc_ctype spb item for 
> consistency?

   I would like on contrary declare isc_dpb_lc_ctype deprecated.
   Problem is that Firebird charsets match neither system locales not code 
pages. It 
causes a big problem to convert data between them and unicode.

-- 
   WBR, SD.

--
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351=/4140
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Git and ChangeLog

[Dmitry Yemanov]

22.03.2016 19:47, Dimitry Sibiryakov wrote:
>
> Will ChangeLog be updated someday or it is abandoned?

It will not be maintained anymore in its old form. Its new contents will 
migrate from WhatsNew which will be deleted.


Dmitry


--
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351=/4140
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Firebird-devel] Git and ChangeLog

[Dimitry Sibiryakov]

   Hello, All.

   Will ChangeLog be updated someday or it is abandoned?

-- 
   WBR, SD.

--
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351=/4140
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Services and encoding

[Mark Rotteveel]

Sounds like a hack, why not just introduce a lc_ctype spb item for consistency?
Mark

- Bericht beantwoorden -
Van: "Dimitry Sibiryakov" 
Aan: "For discussion among Firebird Developers" 

Onderwerp: [Firebird-devel] Services and encoding
Datum: di, mrt. 22, 2016 13:33

Hello, All.

Because there is nothing like isc_spb_lc_ctype, must be established a rule for 
determining of encoding of all strings passed into services and back. I suggest 
to use 
following:
If isc_spb_utf8_filename is included in SPB, all strings are supposed to be in 
UTF8.
Otherwise they are in ANSI code page on Windows and current locale on *nix.

Do you agree?

-- 
WBR, SD.

--
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351=/4140
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel--
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351=/4140Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Services and encoding

[Dimitry Sibiryakov]

22.03.2016 14:54, Jim Starkey wrote:
> I don't agree.  It would be better all around if everything passing over
> the wire is passed as utf-8.

   I agree here, but I'm talking about API level, not engine internals, so

> all character code translation be handled on the client
> side as part of the client library.

   is done exactly this way.

-- 
   WBR, SD.

--
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351=/4140
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Services and encoding

[Alex Peshkoff]

On 03/22/2016 04:54 PM, Jim Starkey wrote:
> On 3/22/2016 8:33 AM, Dimitry Sibiryakov wrote:
>>  Hello, All.
>>
>>  Because there is nothing like isc_spb_lc_ctype, must be established a 
>> rule for
>> determining of encoding of all strings passed into services and back. I 
>> suggest to use
>> following:
>>  If isc_spb_utf8_filename is included in SPB, all strings are supposed 
>> to be in UTF8.
>>  Otherwise they are in ANSI code page on Windows and current locale on 
>> *nix.
>>
>>  Do you agree?
>>
> I don't agree.  It would be better all around if everything passing over
> the wire is passed as utf-8.

Certainly - but we must support old clients.

> The server should get out of the business
> of handling local character sets.  The Unicode Foundation supplies
> conversion tables from just about every character set on earth to
> Unicode, and translation of Unicode to utf-8 is simple and mechanical.
>
> I suggest that all character code translation be handled on the client
> side as part of the client library.
>
> --
> Transform Data into Opportunity.
> Accelerate data analysis in your applications with
> Intel Data Analytics Acceleration Library.
> Click to learn more.
> http://pubads.g.doubleclick.net/gampad/clk?id=278785351=/4140
> Firebird-Devel mailing list, web interface at 
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>


--
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351=/4140
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Services and encoding

[Jim Starkey]

On 3/22/2016 8:33 AM, Dimitry Sibiryakov wrote:
> Hello, All.
>
> Because there is nothing like isc_spb_lc_ctype, must be established a 
> rule for
> determining of encoding of all strings passed into services and back. I 
> suggest to use
> following:
> If isc_spb_utf8_filename is included in SPB, all strings are supposed to 
> be in UTF8.
> Otherwise they are in ANSI code page on Windows and current locale on 
> *nix.
>
> Do you agree?
>

I don't agree.  It would be better all around if everything passing over 
the wire is passed as utf-8.  The server should get out of the business 
of handling local character sets.  The Unicode Foundation supplies 
conversion tables from just about every character set on earth to 
Unicode, and translation of Unicode to utf-8 is simple and mechanical.

I suggest that all character code translation be handled on the client 
side as part of the client library.

--
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351=/4140
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Physical replication using NBACKUP

[Vlad Khorsun]

20.04.2013 21:24, Nikolay Samofatov wrote:
> Hello, All!
>
> We use NBACKUP for physical replication of the databases. For that we added 2 
> features:
>
> 1) We only read one sector (with header) of each page when scanning for 
> changes. This is helpful for
> SSD media and might provide 4x-32x speed up.
> This is probably not important for FB3, as it will have SCN pages, but is 
> very useful optimization
> for 2.X ODS.
>
> 2) We added option into NBACKUP utility to create change set based on 
> baseline GUID rather than level.
>
> -B |  [] Create incremental backup
>
> This is the same GUID that is displayed in GSTAT output:
>
> Database backup GUID:   {35F5915A-4A62-4A27-AD91-6FB0EBDCCF11}
>
> To update offline copy of the database over WAN we use the following 
> procedure in a shall script:
> - obtain baseline GUID of the database using GSTAT -h
> - pull changes using SSH+NBACKUP using baseline GUID
> - if pull is successful apply changes to a local copy
> - if incremental pull fails get clean copy of the database using "NBACKUP -B 
> 0", CAT and DD (we have
> implemented download resume in a shall script)
>
> Pull might fail if baseline with given GUID doesn't exist in the database (it 
> was restored from gbak
> backup, etc).
>
> This idea of using nbackup for exact this purpose was in the original design, 
> data formats, etc
> although it was never completed. Changes do not affect engine code.
>
> If there are no objections to this design I will ask Dmitry Starodubov to 
> port this code to FB3 and
> submit a patch for integration.

   The feature (part 2) is committed (with little changes) into master.

   Basic tests show it works :) If anyone have idea how to improve it - you are 
welcome.
For example, we could introduce special switch to query backup GUID of target 
database
to pass in into backup automatically.

Regards,
Vlad


--
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351=/4140
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Services and encoding

[Alex Peshkoff]

On 03/22/2016 03:33 PM, Dimitry Sibiryakov wrote:
> Hello, All.
>
> Because there is nothing like isc_spb_lc_ctype, must be established a 
> rule for
> determining of encoding of all strings passed into services and back. I 
> suggest to use
> following:
> If isc_spb_utf8_filename is included in SPB, all strings are supposed to 
> be in UTF8.
> Otherwise they are in ANSI code page on Windows and current locale on 
> *nix.
>
> Do you agree?
>
yes


--
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351=/4140
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Firebird-devel] Services and encoding

[Dimitry Sibiryakov]

   Hello, All.

   Because there is nothing like isc_spb_lc_ctype, must be established a rule 
for 
determining of encoding of all strings passed into services and back. I suggest 
to use 
following:
   If isc_spb_utf8_filename is included in SPB, all strings are supposed to be 
in UTF8.
   Otherwise they are in ANSI code page on Windows and current locale on *nix.

   Do you agree?

-- 
   WBR, SD.

--
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351=/4140
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Firebird-devel] [FB-Tracker] Created: (CORE-5162) SEC$ tables and tag/attributes

[Carlos H. Cantu (JIRA)]

SEC$ tables and tag/attributes
--

 Key: CORE-5162
 URL: http://tracker.firebirdsql.org/browse/CORE-5162
 Project: Firebird Core
  Issue Type: Bug
  Components: Engine
Affects Versions: 3.0 RC2
Reporter: Carlos H. Cantu


FB 3 allows to have users with the same name, if they are created by different 
plugins. But if you set a tag/attribute to the user A with plugin SRP, this 
tag/attribute stay visible to  user A of LegacyAuth plugin, when you select 
from the sec$ virtual tables.
PS: Initial report by Ann Harrison.

-- 
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



--
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351=/4140
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel