mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
a931da82fa
Search conditions were evaluated using val_int(), which was wrong. Fixing the code to use val_bool() instead. Details: - Adding a new item_base_t::IS_COND flag which marks Items used as <search condition> in WHERE, HAVING, JOIN ON, CASE WHEN clauses. The flag is at the parse time. These expressions must be evaluated using val_bool() rather than val_int(). Note, the optimizer creates more Items which are used as search conditions. Most of these items are not marked with IS_COND yet. This is OK for now, but eventually these Items can also be fixed to have the flag. - Adding a method Item::is_cond() which tests if the Item has the IS_COND flag. - Implementing Item_cache_bool. It evaluates the cached expression using val_bool() rather than val_int(). Overriding Type_handler_bool::Item_get_cache() to create Item_cache_bool. - Implementing Item::save_bool_in_field(). It uses val_bool() rather than val_int() to evaluate the expression. - Implementing Type_handler_bool::Item_save_in_field() using Item::save_bool_in_field(). - Fixing all Item_bool_func descendants to implement a virtual val_bool() rather than a virtual val_int(). - To find places where val_int() should be fixed to val_bool(), a few DBUG_ASSERT(!is_cond()) where added into val_int() implementations of selected (most frequent) classes: Item_field Item_str_func Item_datefunc Item_timefunc Item_datetimefunc Item_cache_bool Item_bool_func Item_func_hybrid_field_type Item_basic_constant descendants - Fixing all places where DBUG_ASSERT() happened during an "mtr" run to use val_bool() instead of val_int().
201 lines
4.4 KiB
SQL
201 lines
4.4 KiB
SQL
--echo #
|
|
--echo # WHERE <search condition>
|
|
--echo #
|
|
|
|
let datatype=`SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_NAME='t0' AND COLUMN_NAME='c'`;
|
|
let use_string_func=0;
|
|
let use_field=1;
|
|
let update_source=c+11;
|
|
|
|
if ($datatype == 'varchar')
|
|
{
|
|
let use_string_func=1;
|
|
}
|
|
|
|
if ($datatype == 'datetime')
|
|
{
|
|
let update_source=c+0.1;
|
|
}
|
|
|
|
if ($datatype == 'inet6')
|
|
{
|
|
let use_field=0;
|
|
let update_source=concat(c,'77');
|
|
}
|
|
|
|
|
|
CREATE TABLE t1 AS SELECT * FROM t0;
|
|
if ($use_field)
|
|
{
|
|
SELECT * FROM t1 WHERE c;
|
|
}
|
|
SELECT * FROM t1 WHERE c IS FALSE;
|
|
SELECT * FROM t1 WHERE c IS TRUE;
|
|
SELECT * FROM t1 WHERE COALESCE(c);
|
|
if ($use_string_func)
|
|
{
|
|
SELECT * FROM t1 WHERE CONCAT(c);
|
|
SELECT * FROM t1 WHERE LEFT(c,100);
|
|
}
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # HAVING <search condition>
|
|
--echo #
|
|
|
|
CREATE TABLE t1 AS SELECT * FROM t0;
|
|
if ($use_field)
|
|
{
|
|
SELECT COALESCE(c,c) AS c2 FROM t1 GROUP BY c2 HAVING c2;
|
|
}
|
|
SELECT COALESCE(c,c) AS c2 FROM t1 GROUP BY c2 HAVING c2 IS FALSE;
|
|
SELECT COALESCE(c,c) AS c2 FROM t1 GROUP BY c2 HAVING c2 IS TRUE;
|
|
SELECT COALESCE(c,c) AS c2 FROM t1 GROUP BY c2 HAVING COALESCE(c2);
|
|
if ($use_string_func)
|
|
{
|
|
SELECT CONCAT(c,'0') AS c2 FROM t1 GROUP BY c2 HAVING LEFT(c2,100);
|
|
}
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # <join condition> := ON <search condition>
|
|
--echo #
|
|
|
|
CREATE TABLE t1 AS SELECT * FROM t0;
|
|
if ($use_field)
|
|
{
|
|
SELECT t1.c FROM t1 JOIN t1 AS t2 ON (t1.c);
|
|
}
|
|
SELECT t1.c FROM t1 JOIN t1 AS t2 ON (t1.c IS FALSE);
|
|
SELECT t1.c FROM t1 JOIN t1 AS t2 ON (t1.c IS TRUE);
|
|
SELECT t1.c FROM t1 JOIN t1 AS t2 ON (COALESCE(t1.c));
|
|
if ($use_string_func)
|
|
{
|
|
SELECT t1.c FROM t1 JOIN t1 AS t2 ON (CONCAT(t1.c));
|
|
}
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # <delete statement: searched>
|
|
--echo # DELETE FROM <target table> [ WHERE <search condition> ]
|
|
--echo #
|
|
|
|
if ($use_field)
|
|
{
|
|
CREATE TABLE t1 AS SELECT * FROM t0;
|
|
DELETE FROM t1 WHERE c;
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
}
|
|
|
|
CREATE TABLE t1 AS SELECT * FROM t0;
|
|
DELETE FROM t1 WHERE c IS FALSE;
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 AS SELECT * FROM t0;
|
|
DELETE FROM t1 WHERE c IS TRUE;
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 AS SELECT * FROM t0;
|
|
DELETE FROM t1 WHERE COALESCE(c);
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
if ($use_string_func)
|
|
{
|
|
CREATE TABLE t1 AS SELECT * FROM t0;
|
|
DELETE FROM t1 WHERE CONCAT(c);
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
}
|
|
|
|
|
|
--echo #
|
|
--echo # <update statement: searched>
|
|
--echo # UPDATE <target table> SET <set clause list> [ WHERE <search condition> ]
|
|
|
|
if ($use_field)
|
|
{
|
|
CREATE TABLE t1 AS SELECT * FROM t0;
|
|
eval UPDATE t1 SET c=$update_source WHERE c;
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
}
|
|
|
|
CREATE TABLE t1 AS SELECT * FROM t0;
|
|
eval UPDATE t1 SET c=$update_source WHERE c IS FALSE;
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 AS SELECT * FROM t0;
|
|
eval UPDATE t1 SET c=$update_source WHERE c IS TRUE;
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 AS SELECT * FROM t0;
|
|
eval UPDATE t1 SET c=$update_source WHERE COALESCE(c);
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
if ($use_string_func)
|
|
{
|
|
CREATE TABLE t1 AS SELECT * FROM t0;
|
|
eval UPDATE t1 SET c=$update_source WHERE COALESCE(c);
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
}
|
|
|
|
--echo #
|
|
--echo # <check constraint definition>
|
|
--echo # CHECK <left paren> <search condition> <right paren>
|
|
|
|
if ($use_field)
|
|
{
|
|
CREATE TABLE t1 LIKE t0;
|
|
ALTER TABLE t1 ADD CONSTRAINT check0 CHECK(c);
|
|
--error ER_CONSTRAINT_FAILED
|
|
INSERT INTO t1 SELECT * FROM t0 WHERE NOT c;
|
|
INSERT INTO t1 SELECT * FROM t0 WHERE c;
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
}
|
|
|
|
CREATE TABLE t1 LIKE t0;
|
|
ALTER TABLE t1 ADD CONSTRAINT check0 CHECK(c IS FALSE);
|
|
INSERT INTO t1 SELECT * FROM t0 WHERE c IS FALSE;
|
|
--error ER_CONSTRAINT_FAILED
|
|
INSERT INTO t1 SELECT * FROM t0 WHERE c IS TRUE;
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 LIKE t0;
|
|
ALTER TABLE t1 ADD CONSTRAINT check0 CHECK(c IS TRUE);
|
|
--error ER_CONSTRAINT_FAILED
|
|
INSERT INTO t1 SELECT * FROM t0 WHERE c IS FALSE;
|
|
INSERT INTO t1 SELECT * FROM t0 WHERE c IS TRUE;
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 LIKE t0;
|
|
ALTER TABLE t1 ADD CONSTRAINT check0 CHECK(COALESCE(c));
|
|
--error ER_CONSTRAINT_FAILED
|
|
INSERT INTO t1 SELECT * FROM t0 WHERE c IS FALSE;
|
|
INSERT INTO t1 SELECT * FROM t0 WHERE c IS TRUE;
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # <case expression>
|
|
--echo # WHEN <search condition> THEN <result>
|
|
|
|
CREATE TABLE t1 AS SELECT * FROM t0;
|
|
SELECT c, CASE WHEN c THEN 'true' ELSE 'false' END AS c2 FROM t1;
|
|
SELECT c, CASE WHEN COALESCE(c) THEN 'true' ELSE 'false' END AS c2 FROM t1;
|
|
DROP TABLE t1;
|