MDEV-22534 [do-not-push] a PoC that works for the example case
Test case:
--8<---------------cut here---------------start------------->8---
set @@optimizer_switch='materialization=on,firstmatch=off';
create table ten(a int primary key);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int, b int, c int);
insert into t1 select a,a,a from ten;
create table t2 select * from t1;
set optimizer_trace="enabled=on";
explain select * from t1 where a in (select a from t2 where t1.b=t2.b);
set optimizer_trace="enabled=off";
select * from information_schema.OPTIMIZER_TRACE;
set optimizer_trace="enabled=on";
explain select * from t1 where (a, b) in (select a, b from t2);
set optimizer_trace="enabled=off";
select * from information_schema.OPTIMIZER_TRACE;
drop table ten, t1, t2;
--8<---------------cut here---------------end--------------->8---
Exactly the same explain output:
--8<---------------cut here---------------start------------->8---
explain select * from t1 where a in (select a from t2 where t1.b=t2.b);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 10
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 10
explain select * from t1 where (a, b) in (select a, b from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 10
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 10
--8<---------------cut here---------------end--------------->8---
3ef1116...
by
Daniel Lenski <email address hidden>
[MDEV-29827] Clear error when --event-scheduler=ON is combined with --skip-grant-tables
When the server is started with `--event-scheduler=ON` and with
`--skip-grant-tables` (or built as embedded server which has no grant
tables at all), the event scheduler *appears* to be enabled (`SELECT
@@global.event_scheduler` returns `'ON'`), but attempting to
manipulate it in any way returns a misleading error message:
"Cannot proceed, because event scheduler is disabled"
Possible solutions:
1. Fast-fail: fail immediately on startup if `EVENT_SCHEDULER` is set to
any value other than `DISABLED` when starting up without grant
tables, then prevent `SET GLOBAL event_scheduler` while running.
Problem: there are existing setup scripts and code which start with
this combination and assume it will not fail.
2. Warn and change value: if `EVENT_SCHEDULER` is set to any value
other than `DISABLED` when starting, print a warning and change it
immediately to `DISABLED`.
Advantage: The value of the `EVENT_SCHEDULER` system variable after
startup will be consistent with its functional unavailability.
3. Display a clear error: if `EVENT_SCHEDULER` is enabled, but grant
tables are not enabled, then ensure error messages clearly explain
the fact that the combination is not supported.
Advantage: The error message encountered by the end user when
attempting to manipulate the event scheduler (such as `CREATE
EVENT`) is clear and explicit.
This commit implements the combination of solutions (2) and (3): it
will set `EVENT_SCHEDULER=DISABLED` on startup (reflecting the
functional reality) and it will print a startup warning, *and* it will
print a *distinct* error message each time that an end user attempts to
manipulate the event scheduler, so that the end user will clearly understand
the problem even if the startup messages are not visible at that point.
It also adds an MTR test `main.events_skip_grant_tables` to verify the
expected behavior, and the unmodified `main.events_restart` test
continues to demonstrate no change in the error message when the event
scheduler is non-functional for *different* reasons.
All new code of the whole pull request, including one or several files
that are either new files or modified ones, are contributed under the BSD-new
license. I am contributing on behalf of my employer Amazon Web Services
7321c71...
by
Rucha Deodhar <email address hidden>
MDEV-31032: UBSAN|downcast of address X which does not point to an
object of type 'Item_string' in sql/json_schema.cc
Analysis: make_string_literal() returns pointer of type
Item_basic_constant which is converted to pointer of type Item_string. Now,
Item_string is base class of Item_basic_constant, so the error about
downcasting.
Fix: using constructor of Item_string type directly instead of
downcasting would be more appropriate.
4b67ff3...
by
Rucha Deodhar <email address hidden>
MDEV-30705: JSON_SCHEMA_VALID: schema with multipleOf for big value
always return 1
Analysis: Implementation used double to store value. longlong is better
choice
Fix: Use longlong for multiple_of and the value to store it and num_flag to
check for decimals.
2c4c7c8...
by
Rucha Deodhar <email address hidden>
MDEV-30704: JSON_SCHEMA_VALID: multipleOf must be greater than zero
Analysis: multipleOf must be strictly greater then 0. However the
implementation only disallowed values strcitly less than 0
Fix: check if value is less than or equal to 0 instead of less than 0 and
return true.
Also fixed the incorrect return value for some other keywords.
dffd167...
by
Rucha Deodhar <email address hidden>
MDEV-30703: JSON_SCHEMA_VALID : Enum array must have at least one value
Analysis: Current implementation does not check the number of elements in
the enum array and whether they are unique or not.
Fix: Add a counter that counts number of elements and before inserting the
element in the enum hash check whether it exists.
d555f38...
by
Rucha Deodhar <email address hidden>
MDEV-30690: Server crashed on function JSON_SCHEMA_VALID with incorrect
input json schema
Analysis: In case of syntax error while scanning json schema, true is
returned inspite of it being wanring and not error.
Fix: return true instead of false.
1c25b5c...
by
Rucha Deodhar <email address hidden>
MDEV-30977: Additional key values are not validating properly when using
unevaluatedProperties with properties declared in subschemas
Analysis:
When a key fails to validate for "properties" when "properties" is being
treated as alternate schema, it needs to fall back on alternate schema
for "properites" itself ("unevaluatedProperties" in context of the bug).
But that doesn't happen and we end up returning false (=validated)
Fix:
When "properties" fails to validate as an alternate schema, fall back on
alternate schema for "properties" itself.
ee41fa3...
by
Rucha Deodhar <email address hidden>
MDEV-30995: JSON_SCHEMA_VALID is not validating case sensitive when using
regex
Analysis:
When initializing Regexp_processor_pcre object, we set the PCRE2_CASELESS
flag which is responsible for case insensitive comparison.
Fix:
Unset the flag after initializing.
8939e21...
by
Rucha Deodhar <email address hidden>
MDEV-30795: JSON_SCHEMA_VALID bugs mentioned in comment
comment 2)
Analysis:
flag to check unique gets reset every time. Hence unique values cannot be
correctly checked
Fix:
do not reset the flag that checks unique for null, true and false
values.
comment 3)
Analysis:
current implementation checks for appropriate value but does not
return true.
Fix:
return true on error
comment 4)
Analysis:
Current implementation did not check for value type for values
inside required array.
Fix:
Check values inside required array
