mirror of
https://github.com/MariaDB/server.git
synced 2026-02-18 08:38:39 +01:00
10643cffe9
Oracle mode has no priority between set-operators but the current implementation with disable_index_if_needed() can not work with arbitrary mix of distinct/non-distinct set-operators as the algorithm is tied to the properly wrapped version of expression. Index can be disabled only once and cannot be reenabled afterwards. To adapt Oracle mode for the aforementioned implementation we prioritize each set operator with parens in the order of appearance. So the expression: S1 op1 S2 op2 S3 op3 S4 will be rewritten like this: (((S1 op1 S2) op2 S3) op3 S4). The rewritten expression is valid for both Oracle and default mode and renders the same result. Prioritizing is done with create_priority_nest() but it was tweaked to work with derived to be the first operand. Originally create_priority_nest() could wrap only second operand, like this: S1 op1 S2 op2 S3 -> S1 op1 (S2 op2 S3). Now this behaviour is kept by non-NULL attach_to argument. With NULL attach_to argument it produces this transformation: S1 op1 S2 -> (S1 op1 S2), but to be strict it will look like this: SELECT .. FROM (S1 op1 S2). This expression is treated as the first one in the select list, so nothing to "attach to". On importance of not making fake_select_lex after create_priority_nest() in parsed_select_expr_start(). This will fail nastily in Protocol::valid_handler() check in store_longlong() for EXPLAIN. This happens because select_options are inherited from newly made fake_select_lex but without SELECT_DESCRIBE flag which is meant to be assigned by mysql_explain_union() but union_needs_tmp_table() is false (because now top-level unit has no set-operator, it is just select from derived), so SELECT_DESCRIBE assignment is skipped. Depending on that flag send_result_set_metadata() is conditionally executed in JOIN::exec_inner(). And originally it is done via select_describe() branch for top-level select when there is no set operator (it is select from derived as explained above), not via direct send_result_set_metadata(). On IS_OR_WAS_ORACLE. Sql_mode_save_for_frm_handling removes MODE_ORACLE when view is registered or opened. We cannot return it back as this conflicts with MDEV-12478 (tested by compat/oracle.func_concat). So we are temporarily adding WAS_ORACLE flag instead. WAS_MODE_ORACLE is for testing both WAS_ORACLE and MODE_ORACLE simultaneously.
48 lines
1.4 KiB
Text
48 lines
1.4 KiB
Text
CREATE TABLE tx1 (c1 int, c2 varchar(30));
|
|
CREATE TABLE tx2 (c1 int, c2 varchar(30));
|
|
CREATE TABLE tx3 (c1 int, c2 varchar(30));
|
|
INSERT INTO tx1 VALUES (1, 'jim');
|
|
INSERT INTO tx1 VALUES (2, 'menny');
|
|
INSERT INTO tx1 VALUES (3, 'linda');
|
|
INSERT INTO tx2 VALUES (1, 'jim');
|
|
INSERT INTO tx2 VALUES (2, 'kris');
|
|
INSERT INTO tx2 VALUES (3, 'shory');
|
|
INSERT INTO tx3 VALUES (1, 'jim');
|
|
INSERT INTO tx3 VALUES (2, 'kris');
|
|
INSERT INTO tx3 VALUES (3, 'linda');
|
|
#
|
|
# test when sql_mode is not oracle
|
|
#
|
|
SELECT c2 FROM tx1 EXCEPT SELECT c2 from tx2;
|
|
c2
|
|
menny
|
|
linda
|
|
SELECT c2 FROM tx1 MINUS SELECT c2 from tx2;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT c2 from tx2' at line 1
|
|
create table MINUS (a int);
|
|
drop table MINUS;
|
|
#
|
|
# test when sql_mode is oracle
|
|
#
|
|
SET sql_mode=ORACLE;
|
|
SELECT c2 FROM tx1 MINUS SELECT c2 from tx2;
|
|
c2
|
|
menny
|
|
linda
|
|
SELECT c2 FROM tx1 MINUS SELECT c2 from tx2 MINUS SELECT c2 from tx3;
|
|
c2
|
|
menny
|
|
SELECT c2 FROM tx1 MINUS SELECT c2 from tx2 EXCEPT SELECT c2 from tx3;
|
|
c2
|
|
menny
|
|
SELECT c2 FROM tx1 MINUS SELECT c2 from tx2 UNION SELECT c2 from tx3;
|
|
c2
|
|
menny
|
|
linda
|
|
jim
|
|
kris
|
|
create table MINUS (a int);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'MINUS (a int)' at line 1
|
|
DROP TABLE tx1;
|
|
DROP TABLE tx2;
|
|
DROP TABLE tx3;
|