mirror of
https://github.com/MariaDB/server.git
synced 2025-01-16 03:52:35 +01:00
ca5c122adc
The problem is that the first execution of the prepared statement makes
a permanent optimization of converting the LEFT JOIN to an INNER JOIN.
This is based on the assumption that all the user parameters (?) are
always constants and that parameters to Item_cond() will not change value
from true and false between different executions.
(The example was using IS NULL, which will change value if parameter
depending on if the parameter is NULL or not).
The fix is to change Item_cond::fix_fields() and
Item_cond::eval_not_null_tables() to not threat user parameters as
constants. This will ensure that we don't do the LEFT_JOIN -> INNER
JOIN conversion that causes problems.
There is also some things that needs to be improved regarding
calculations of not_null_tables_cache as we get a different value for
WHERE 1 or t1.a=1
compared to
WHERE t1.a= or 1
Changes done:
- Mark Item_param with the PARAM flag to be able to quickly check
in Item_cond::eval_not_null_tables() if an item contains a
prepared statement parameter (just like we check for stored procedure
parameters).
- Fixed that Item_cond::not_null_tables_cache is not depending on
order of arguments.
- Don't call item->eval_const_cond() for items that are NOT on the top
level of the WHERE clause. This removed a lot of unnecessary
warnings in the test suite!
- Do not reset not_null_tables_cache for not top level items.
- Simplified Item_cond::fix_fields by calling eval_not_null_tables()
instead of having duplication of all the code in
eval_not_null_tables().
- Return an error if Item_cond::fix_field() generates an error
The old code did generate an error in some cases, but not in all
cases.
- Fixed all handling of the above error in make_cond_for_tables().
The error handling by the callers did not exists before which
could lead to asserts in many different places in the old code).
- All changes in sql_select.cc are just checking the return value of
fix_fields() and make_cond_for_tables() and returning an error
value if fix_fields() returns true or make_cond_for_tables()
returns NULL and is_error() is set.
- Mark Item_cond as const_item if all arguments returns true for
can_eval_in_optimize().
Reviewer: Sergei Petrunia <sergey@mariadb.com>
117 lines
2.8 KiB
Text
117 lines
2.8 KiB
Text
PREPARE stmt1 FROM "
|
|
SELECT table_name FROM information_schema.tables
|
|
WHERE table_name = 't1_first'
|
|
UNION ALL
|
|
SELECT table_name FROM information_schema.tables
|
|
WHERE table_name = 't1_second'";
|
|
execute stmt1;
|
|
table_name
|
|
execute stmt1;
|
|
table_name
|
|
create or replace table t1 (a int primary key, table_name char(40));
|
|
insert into t1 values(1,"t1_first");
|
|
insert into t1 values(2,"t1_second");
|
|
PREPARE stmt2 FROM "
|
|
SELECT table_name FROM t1
|
|
WHERE table_name = 't1_first'
|
|
UNION ALL
|
|
SELECT table_name FROM t1
|
|
WHERE table_name = 't1_second'";
|
|
execute stmt2;
|
|
table_name
|
|
t1_first
|
|
t1_second
|
|
execute stmt2;
|
|
table_name
|
|
t1_first
|
|
t1_second
|
|
flush tables;
|
|
execute stmt2;
|
|
table_name
|
|
t1_first
|
|
t1_second
|
|
alter table t1 add column b int;
|
|
execute stmt2;
|
|
table_name
|
|
t1_first
|
|
t1_second
|
|
execute stmt2;
|
|
table_name
|
|
t1_first
|
|
t1_second
|
|
drop table t1;
|
|
execute stmt2;
|
|
ERROR 42S02: Table 'test.t1' doesn't exist
|
|
create or replace table t1 (a int primary key, table_name char(40));
|
|
insert into t1 values(1,"t1_first");
|
|
execute stmt2;
|
|
table_name
|
|
t1_first
|
|
deallocate prepare stmt1;
|
|
deallocate prepare stmt2;
|
|
drop table t1;
|
|
#
|
|
# MDEV-25808 PREPARE/EXECUTE makes signed integer out of unsigned
|
|
#
|
|
prepare p1 from 'select concat(?)';
|
|
execute p1 using 17864960750176564435;
|
|
concat(?)
|
|
17864960750176564435
|
|
prepare p1 from 'select SQRT(?) is not null';
|
|
execute p1 using 17864960750176564435;
|
|
SQRT(?) is not null
|
|
1
|
|
#
|
|
# End of 10.3 tests
|
|
#
|
|
#
|
|
# MDEV-17869 AddressSanitizer: use-after-poison in Item_change_list::rollback_item_tree_changes
|
|
#
|
|
create table t1 (pk int, v1 varchar(1));
|
|
insert t1 values (1,'v'),(2,'v'),(3,'c');
|
|
create table t2 (pk int, v1 varchar(1));
|
|
insert t2 values (1,'x');
|
|
create table t3 (pk int, i1 int, v1 varchar(1));
|
|
insert t3 values (10,8,9);
|
|
execute immediate 'select straight_join 1 from (t1 join t2 on (t1.v1 = t2.v1))
|
|
where (3, 6) in (select tc.pk, t3.i1 from (t3 join t1 as tc on (tc.v1 = t3.v1)) having tc.pk > 1 );';
|
|
1
|
|
drop table t1, t2, t3;
|
|
#
|
|
# End of 10.4 tests
|
|
#
|
|
#
|
|
# MDEV-9938 Prepared statement return wrong result (missing row)
|
|
#
|
|
CREATE TABLE t1 (a_id INT AUTO_INCREMENT PRIMARY KEY, a_text VARCHAR(20));
|
|
CREATE TABLE t2 (b_id INT AUTO_INCREMENT PRIMARY KEY, b_a_id INT);
|
|
INSERT INTO t1 VALUES (NULL, 'word1');
|
|
INSERT INTO t2 VALUES (NULL, 1), (NULL, NULL);
|
|
PREPARE q FROM 'SELECT * FROM t2
|
|
LEFT JOIN t1 ON (t1.a_id = t2.b_a_id)
|
|
WHERE ((? IS NULL) OR (t1.a_text = ?))';
|
|
SET @var = 'word1';
|
|
expect row count 1
|
|
EXECUTE q USING @var, @var;
|
|
b_id b_a_id a_id a_text
|
|
1 1 1 word1
|
|
expect row count = 2
|
|
EXECUTE q USING @nul, @nul;
|
|
b_id b_a_id a_id a_text
|
|
1 1 1 word1
|
|
2 NULL NULL NULL
|
|
PREPARE q2 FROM 'SELECT * FROM t2
|
|
LEFT JOIN t1 ON (t1.a_id = t2.b_a_id)
|
|
WHERE ((? IS NULL) OR (t1.a_text = ?))';
|
|
expect row count 2
|
|
SET @var = 'word1';
|
|
EXECUTE q2 USING @nul, @nul;
|
|
b_id b_a_id a_id a_text
|
|
1 1 1 word1
|
|
2 NULL NULL NULL
|
|
deallocate prepare q;
|
|
deallocate prepare q2;
|
|
drop table t1,t2;
|
|
#
|
|
# End of 10.6 tests
|
|
#
|