mirror of
https://github.com/MariaDB/server.git
synced 2025-02-22 13:23:07 +01:00
5a8e6230d7
normalize_cond() translated `WHERE col` into `WHERE col<>0`
But the opetator "not equal to 0" does not necessarily exists
for all data types.
For example, the query:
SELECT * FROM t1 WHERE inet6col;
was translated to:
SELECT * FROM t1 WHERE inet6col<>0;
which further failed with this error:
ERROR : Illegal parameter data types inet6 and bigint for operation '<>'
This patch changes the translation from `col<>0` to `col IS TRUE`.
So now
SELECT * FROM t1 WHERE inet6col;
gets translated to:
SELECT * FROM t1 WHERE inet6col IS TRUE;
Details:
1. Implementing methods:
- Field_longstr::val_bool()
- Field_string::val_bool()
- Item::val_int_from_val_str()
If the input contains bad data,
these methods raise a better error message:
Truncated incorrect BOOLEAN value
Before the change, the error was:
Truncated incorrect DOUBLE value
2. Fixing normalize_cond() to generate Item_func_istrue/Item_func_isfalse
instances instead of Item_func_ne/Item_func_eq
3. Making Item_func_truth sargable, so it uses the range optimizer.
Implementing the following methods:
- get_mm_tree(), get_mm_leaf(), add_key_fields() in Item_func_truth.
- get_func_mm_tree(), for all Item_func_truth descendants.
4. Implementing the method negated_item() for all Item_func_truth
descendants, so the negated item has a chance to be sargable:
For example,
WHERE NOT col IS NOT FALSE -- this notation is not sargable
is now translated to:
WHERE col IS FALSE -- this notation is sargable
80 lines
2.9 KiB
Text
80 lines
2.9 KiB
Text
--source include/have_sequence.inc
|
|
#
|
|
# Test of boolean operations with NULL
|
|
#
|
|
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS t1;
|
|
--enable_warnings
|
|
|
|
SELECT IF(NULL AND 1, 1, 2), IF(1 AND NULL, 1, 2);
|
|
SELECT NULL AND 1, 1 AND NULL, 0 AND NULL, NULL and 0;
|
|
|
|
create table t1 (a int);
|
|
insert into t1 values (0),(1),(NULL);
|
|
SELECT * FROM t1 WHERE IF(a AND 1, 0, 1);
|
|
SELECT * FROM t1 WHERE IF(1 AND a, 0, 1);
|
|
SELECT * FROM t1 where NOT(a AND 1);
|
|
SELECT * FROM t1 where NOT(1 AND a);
|
|
SELECT * FROM t1 where (a AND 1)=0;
|
|
SELECT * FROM t1 where (1 AND a)=0;
|
|
SELECT * FROM t1 where (1 AND a)=1;
|
|
SELECT * FROM t1 where (1 AND a) IS NULL;
|
|
|
|
# WL#638 - Behaviour of NOT does not follow SQL specification
|
|
--disable_service_connection
|
|
set sql_mode='high_not_precedence';
|
|
select * from t1 where not a between 2 and 3;
|
|
set sql_mode=default;
|
|
select * from t1 where not a between 2 and 3;
|
|
--enable_service_connection
|
|
|
|
# SQL boolean tests
|
|
select a, a is false, a is true, a is unknown from t1;
|
|
select a, a is not false, a is not true, a is not unknown from t1;
|
|
|
|
# Verify that NULL optimisation works in AND clause:
|
|
SET @a=0, @b=0;
|
|
SELECT * FROM t1 WHERE NULL AND (@a:=@a+1);
|
|
SELECT * FROM t1 WHERE NOT(a>=0 AND NULL AND (@b:=@b+1));
|
|
SELECT * FROM t1 WHERE a=2 OR (NULL AND (@a:=@a+1));
|
|
SELECT * FROM t1 WHERE NOT(a=2 OR (NULL AND (@b:=@b+1)));
|
|
DROP TABLE t1;
|
|
|
|
|
|
# Test boolean operators in select part
|
|
# NULLs are represented as N for readability
|
|
# Read nA as !A, AB as A && B, AoB as A || B
|
|
# Result table makes ANSI happy
|
|
|
|
create table t1 (a int, b int);
|
|
insert into t1 values(null, null), (0, null), (1, null), (null, 0), (null, 1), (0, 0), (0, 1), (1, 0), (1, 1);
|
|
|
|
# Below test is valid untill we have True/False implemented as 1/0
|
|
# To comply to all rules it must show that: n(AB) = nAonB, n(AoB) = nAnB
|
|
|
|
select ifnull(A, 'N') as A, ifnull(B, 'N') as B, ifnull(not A, 'N') as nA, ifnull(not B, 'N') as nB, ifnull(A and B, 'N') as AB, ifnull(not (A and B), 'N') as `n(AB)`, ifnull((not A or not B), 'N') as nAonB, ifnull(A or B, 'N') as AoB, ifnull(not(A or B), 'N') as `n(AoB)`, ifnull(not A and not B, 'N') as nAnB from t1;
|
|
|
|
# This should work with any internal representation of True/False
|
|
# Result must be same as above
|
|
|
|
select ifnull(A=1, 'N') as A, ifnull(B=1, 'N') as B, ifnull(not (A=1), 'N') as nA, ifnull(not (B=1), 'N') as nB, ifnull((A=1) and (B=1), 'N') as AB, ifnull(not ((A=1) and (B=1)), 'N') as `n(AB)`, ifnull((not (A=1) or not (B=1)), 'N') as nAonB, ifnull((A=1) or (B=1), 'N') as AoB, ifnull(not((A=1) or (B=1)), 'N') as `n(AoB)`, ifnull(not (A=1) and not (B=1), 'N') as nAnB from t1;
|
|
|
|
drop table t1;
|
|
|
|
# End of 4.1 tests
|
|
|
|
|
|
--echo # Start of 10.6 tests
|
|
|
|
--echo #
|
|
--echo # MDEV-34189 Unexpected error on `WHERE inet6col`
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, a INT, KEY(a));
|
|
INSERT INTO t1 VALUES (-1,NULL);
|
|
INSERT INTO t1 SELECT seq,seq FROM seq_0_to_31;
|
|
--source include/boolean_factor.inc
|
|
DROP TABLE t1;
|
|
|
|
--echo # End of 10.6 tests
|