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:
<database /var/db/test.gdb>
enabled true
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
</database>
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 Index Update Insert
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 Index Update Insert
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&iu=/4140
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
