summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/include/ps_conv.inc8
-rw-r--r--mysql-test/include/ps_query.inc5
-rw-r--r--mysql-test/r/ps_2myisam.result98
-rw-r--r--mysql-test/r/ps_3innodb.result98
-rw-r--r--mysql-test/r/ps_4heap.result98
-rw-r--r--mysql-test/r/ps_5merge.result196
-rw-r--r--mysql-test/r/ps_6bdb.result98
-rw-r--r--mysql-test/r/ps_7ndb.result98
8 files changed, 72 insertions, 627 deletions
diff --git a/mysql-test/include/ps_conv.inc b/mysql-test/include/ps_conv.inc
index f61fb7db1c9..03d93b6190d 100644
--- a/mysql-test/include/ps_conv.inc
+++ b/mysql-test/include/ps_conv.inc
@@ -1190,13 +1190,13 @@ execute stmt1 using @arg00 ;
######## SELECT .. WHERE column(year)=value(DOUBLE(m,n)/DOUBLE) ########
set @arg00= 1.991e+3 ;
select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3 ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;
select 'true' as found from t9
-where c1= 20 and c17= @arg00 ;
+where c1= 20 and abs(c17 - @arg00) < 0.01 ;
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3" ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;
execute stmt1 ;
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= ?" ;
+where c1= 20 and abs(c17 - ?) < 0.01" ;
execute stmt1 using @arg00 ;
diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc
index 3ebe3c7b5a1..e02d0d5bf96 100644
--- a/mysql-test/include/ps_query.inc
+++ b/mysql-test/include/ps_query.inc
@@ -540,6 +540,10 @@ FROM t9,
(select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
--enable_metadata
prepare stmt1 from @stmt ;
+#
+# Result log was disabled upon test case failure in the optimized build.
+#
+--disable_result_log
execute stmt1 ;
--disable_metadata
execute stmt1 ;
@@ -588,6 +592,7 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
--disable_metadata
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
+--enable_result_log
drop table t2 ;
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index ee861b257ea..68e0a53fdba 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -908,53 +908,11 @@ FROM t9,
(select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
prepare stmt1 from @stmt ;
execute stmt1 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def scalar_s 5 21 7 Y 32768 4 8
-def exists_s 8 1 1 N 32769 0 8
-def in_s 8 21 1 Y 32768 0 8
-def in_row_s 8 21 1 Y 32768 0 8
-scalar_s exists_s in_s in_row_s
-2.0000 0 1 0
-18.0000 1 0 1
-2.0000 0 1 0
-18.0000 1 0 1
-execute stmt1 ;
-scalar_s exists_s in_s in_row_s
-2.0000 0 1 0
-18.0000 1 0 1
-2.0000 0 1 0
-18.0000 1 0 1
+execute stmt1 ;
set @stmt= concat('explain ',@stmt);
prepare stmt1 from @stmt ;
execute stmt1 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def id 8 3 1 N 32801 0 8
-def select_type 253 19 18 N 1 31 8
-def table 253 64 10 N 1 31 8
-def type 253 10 3 N 1 31 8
-def possible_keys 253 4096 0 Y 0 31 8
-def key 253 64 0 Y 0 31 8
-def key_len 8 3 0 Y 32800 0 8
-def ref 253 1024 0 Y 0 31 8
-def rows 8 10 1 N 32801 0 8
-def Extra 253 255 44 N 1 31 8
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
execute stmt1 ;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
set @stmt= ' SELECT
(SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2
GROUP BY t9.c15 LIMIT 1) as scalar_s,
@@ -977,56 +935,14 @@ set @arg09= 40e-1 ;
prepare stmt1 from @stmt ;
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def scalar_s 5 23 2 Y 32768 31 8
-def exists_s 8 1 1 N 32769 0 8
-def in_s 8 21 1 Y 32768 0 8
-def in_row_s 8 21 1 Y 32768 0 8
-scalar_s exists_s in_s in_row_s
-2 0 1 0
-18 1 0 1
-2 0 1 0
-18 1 0 1
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-scalar_s exists_s in_s in_row_s
-2 0 1 0
-18 1 0 1
-2 0 1 0
-18 1 0 1
set @stmt= concat('explain ',@stmt);
prepare stmt1 from @stmt ;
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def id 8 3 1 N 32801 0 8
-def select_type 253 19 18 N 1 31 8
-def table 253 64 10 N 1 31 8
-def type 253 10 3 N 1 31 8
-def possible_keys 253 4096 0 Y 0 31 8
-def key 253 64 0 Y 0 31 8
-def key_len 8 3 0 Y 32800 0 8
-def ref 253 1024 0 Y 0 31 8
-def rows 8 10 1 N 32801 0 8
-def Extra 253 255 44 N 1 31 8
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
drop table t2 ;
select 1 < (select a from t1) ;
ERROR 21000: Subquery returns more than 1 row
@@ -3192,17 +3108,21 @@ found
true
set @arg00= 1.991e+3 ;
select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3 ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;
found
+true
select 'true' as found from t9
-where c1= 20 and c17= @arg00 ;
+where c1= 20 and abs(c17 - @arg00) < 0.01 ;
found
+true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3" ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;
execute stmt1 ;
found
+true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= ?" ;
+where c1= 20 and abs(c17 - ?) < 0.01" ;
execute stmt1 using @arg00 ;
found
+true
drop table t1, t9;
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index 33b793d91e6..90cd5e39f9c 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -908,53 +908,11 @@ FROM t9,
(select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
prepare stmt1 from @stmt ;
execute stmt1 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def scalar_s 5 21 7 Y 32768 4 8
-def exists_s 8 1 1 N 32769 0 8
-def in_s 8 21 1 Y 32768 0 8
-def in_row_s 8 21 1 Y 32768 0 8
-scalar_s exists_s in_s in_row_s
-2.0000 0 1 0
-18.0000 1 0 1
-2.0000 0 1 0
-18.0000 1 0 1
-execute stmt1 ;
-scalar_s exists_s in_s in_row_s
-2.0000 0 1 0
-18.0000 1 0 1
-2.0000 0 1 0
-18.0000 1 0 1
+execute stmt1 ;
set @stmt= concat('explain ',@stmt);
prepare stmt1 from @stmt ;
execute stmt1 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def id 8 3 1 N 32801 0 8
-def select_type 253 19 18 N 1 31 8
-def table 253 64 10 N 1 31 8
-def type 253 10 3 N 1 31 8
-def possible_keys 253 4096 0 Y 0 31 8
-def key 253 64 0 Y 0 31 8
-def key_len 8 3 0 Y 32800 0 8
-def ref 253 1024 0 Y 0 31 8
-def rows 8 10 1 N 32801 0 8
-def Extra 253 255 44 N 1 31 8
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
execute stmt1 ;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
set @stmt= ' SELECT
(SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2
GROUP BY t9.c15 LIMIT 1) as scalar_s,
@@ -977,56 +935,14 @@ set @arg09= 40e-1 ;
prepare stmt1 from @stmt ;
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def scalar_s 5 23 2 Y 32768 31 8
-def exists_s 8 1 1 N 32769 0 8
-def in_s 8 21 1 Y 32768 0 8
-def in_row_s 8 21 1 Y 32768 0 8
-scalar_s exists_s in_s in_row_s
-2 0 1 0
-18 1 0 1
-2 0 1 0
-18 1 0 1
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-scalar_s exists_s in_s in_row_s
-2 0 1 0
-18 1 0 1
-2 0 1 0
-18 1 0 1
set @stmt= concat('explain ',@stmt);
prepare stmt1 from @stmt ;
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def id 8 3 1 N 32801 0 8
-def select_type 253 19 18 N 1 31 8
-def table 253 64 10 N 1 31 8
-def type 253 10 3 N 1 31 8
-def possible_keys 253 4096 0 Y 0 31 8
-def key 253 64 0 Y 0 31 8
-def key_len 8 3 0 Y 32800 0 8
-def ref 253 1024 0 Y 0 31 8
-def rows 8 10 1 N 32801 0 8
-def Extra 253 255 44 N 1 31 8
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
drop table t2 ;
select 1 < (select a from t1) ;
ERROR 21000: Subquery returns more than 1 row
@@ -3175,17 +3091,21 @@ found
true
set @arg00= 1.991e+3 ;
select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3 ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;
found
+true
select 'true' as found from t9
-where c1= 20 and c17= @arg00 ;
+where c1= 20 and abs(c17 - @arg00) < 0.01 ;
found
+true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3" ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;
execute stmt1 ;
found
+true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= ?" ;
+where c1= 20 and abs(c17 - ?) < 0.01" ;
execute stmt1 using @arg00 ;
found
+true
drop table t1, t9;
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index b2d01af019f..feef6bcf83e 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -909,53 +909,11 @@ FROM t9,
(select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
prepare stmt1 from @stmt ;
execute stmt1 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def scalar_s 5 21 7 Y 32768 4 8
-def exists_s 8 1 1 N 32769 0 8
-def in_s 8 21 1 Y 32768 0 8
-def in_row_s 8 21 1 Y 32768 0 8
-scalar_s exists_s in_s in_row_s
-2.0000 0 1 0
-18.0000 1 0 1
-2.0000 0 1 0
-18.0000 1 0 1
-execute stmt1 ;
-scalar_s exists_s in_s in_row_s
-2.0000 0 1 0
-18.0000 1 0 1
-2.0000 0 1 0
-18.0000 1 0 1
+execute stmt1 ;
set @stmt= concat('explain ',@stmt);
prepare stmt1 from @stmt ;
execute stmt1 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def id 8 3 1 N 32801 0 8
-def select_type 253 19 18 N 1 31 8
-def table 253 64 10 N 1 31 8
-def type 253 10 3 N 1 31 8
-def possible_keys 253 4096 0 Y 0 31 8
-def key 253 64 0 Y 0 31 8
-def key_len 8 3 0 Y 32800 0 8
-def ref 253 1024 0 Y 0 31 8
-def rows 8 10 1 N 32801 0 8
-def Extra 253 255 44 N 1 31 8
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
execute stmt1 ;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
set @stmt= ' SELECT
(SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2
GROUP BY t9.c15 LIMIT 1) as scalar_s,
@@ -978,56 +936,14 @@ set @arg09= 40e-1 ;
prepare stmt1 from @stmt ;
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def scalar_s 5 23 2 Y 32768 31 8
-def exists_s 8 1 1 N 32769 0 8
-def in_s 8 21 1 Y 32768 0 8
-def in_row_s 8 21 1 Y 32768 0 8
-scalar_s exists_s in_s in_row_s
-2 0 1 0
-18 1 0 1
-2 0 1 0
-18 1 0 1
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-scalar_s exists_s in_s in_row_s
-2 0 1 0
-18 1 0 1
-2 0 1 0
-18 1 0 1
set @stmt= concat('explain ',@stmt);
prepare stmt1 from @stmt ;
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def id 8 3 1 N 32801 0 8
-def select_type 253 19 18 N 1 31 8
-def table 253 64 10 N 1 31 8
-def type 253 10 3 N 1 31 8
-def possible_keys 253 4096 0 Y 0 31 8
-def key 253 64 0 Y 0 31 8
-def key_len 8 3 0 Y 32800 0 8
-def ref 253 1024 0 Y 0 31 8
-def rows 8 10 1 N 32801 0 8
-def Extra 253 255 44 N 1 31 8
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
drop table t2 ;
select 1 < (select a from t1) ;
ERROR 21000: Subquery returns more than 1 row
@@ -3176,17 +3092,21 @@ found
true
set @arg00= 1.991e+3 ;
select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3 ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;
found
+true
select 'true' as found from t9
-where c1= 20 and c17= @arg00 ;
+where c1= 20 and abs(c17 - @arg00) < 0.01 ;
found
+true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3" ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;
execute stmt1 ;
found
+true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= ?" ;
+where c1= 20 and abs(c17 - ?) < 0.01" ;
execute stmt1 using @arg00 ;
found
+true
drop table t1, t9;
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index 125fa84e671..8f87343c894 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -951,53 +951,11 @@ FROM t9,
(select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
prepare stmt1 from @stmt ;
execute stmt1 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def scalar_s 5 21 7 Y 32768 4 8
-def exists_s 8 1 1 N 32769 0 8
-def in_s 8 21 1 Y 32768 0 8
-def in_row_s 8 21 1 Y 32768 0 8
-scalar_s exists_s in_s in_row_s
-2.0000 0 1 0
-18.0000 1 0 1
-2.0000 0 1 0
-18.0000 1 0 1
-execute stmt1 ;
-scalar_s exists_s in_s in_row_s
-2.0000 0 1 0
-18.0000 1 0 1
-2.0000 0 1 0
-18.0000 1 0 1
+execute stmt1 ;
set @stmt= concat('explain ',@stmt);
prepare stmt1 from @stmt ;
execute stmt1 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def id 8 3 1 N 32801 0 8
-def select_type 253 19 18 N 1 31 8
-def table 253 64 10 N 1 31 8
-def type 253 10 3 N 1 31 8
-def possible_keys 253 4096 0 Y 0 31 8
-def key 253 64 0 Y 0 31 8
-def key_len 8 3 0 Y 32800 0 8
-def ref 253 1024 0 Y 0 31 8
-def rows 8 10 1 N 32801 0 8
-def Extra 253 255 44 N 1 31 8
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
execute stmt1 ;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
set @stmt= ' SELECT
(SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2
GROUP BY t9.c15 LIMIT 1) as scalar_s,
@@ -1020,56 +978,14 @@ set @arg09= 40e-1 ;
prepare stmt1 from @stmt ;
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def scalar_s 5 23 2 Y 32768 31 8
-def exists_s 8 1 1 N 32769 0 8
-def in_s 8 21 1 Y 32768 0 8
-def in_row_s 8 21 1 Y 32768 0 8
-scalar_s exists_s in_s in_row_s
-2 0 1 0
-18 1 0 1
-2 0 1 0
-18 1 0 1
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-scalar_s exists_s in_s in_row_s
-2 0 1 0
-18 1 0 1
-2 0 1 0
-18 1 0 1
set @stmt= concat('explain ',@stmt);
prepare stmt1 from @stmt ;
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def id 8 3 1 N 32801 0 8
-def select_type 253 19 18 N 1 31 8
-def table 253 64 10 N 1 31 8
-def type 253 10 3 N 1 31 8
-def possible_keys 253 4096 0 Y 0 31 8
-def key 253 64 0 Y 0 31 8
-def key_len 8 3 0 Y 32800 0 8
-def ref 253 1024 0 Y 0 31 8
-def rows 8 10 1 N 32801 0 8
-def Extra 253 255 44 N 1 31 8
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
drop table t2 ;
select 1 < (select a from t1) ;
ERROR 21000: Subquery returns more than 1 row
@@ -3115,19 +3031,23 @@ found
true
set @arg00= 1.991e+3 ;
select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3 ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;
found
+true
select 'true' as found from t9
-where c1= 20 and c17= @arg00 ;
+where c1= 20 and abs(c17 - @arg00) < 0.01 ;
found
+true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3" ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;
execute stmt1 ;
found
+true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= ?" ;
+where c1= 20 and abs(c17 - ?) < 0.01" ;
execute stmt1 using @arg00 ;
found
+true
drop table t1, t9 ;
create table t1
(
@@ -4039,53 +3959,11 @@ FROM t9,
(select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
prepare stmt1 from @stmt ;
execute stmt1 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def scalar_s 5 21 7 Y 32768 4 8
-def exists_s 8 1 1 N 32769 0 8
-def in_s 8 21 1 Y 32768 0 8
-def in_row_s 8 21 1 Y 32768 0 8
-scalar_s exists_s in_s in_row_s
-2.0000 0 1 0
-18.0000 1 0 1
-2.0000 0 1 0
-18.0000 1 0 1
-execute stmt1 ;
-scalar_s exists_s in_s in_row_s
-2.0000 0 1 0
-18.0000 1 0 1
-2.0000 0 1 0
-18.0000 1 0 1
+execute stmt1 ;
set @stmt= concat('explain ',@stmt);
prepare stmt1 from @stmt ;
execute stmt1 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def id 8 3 1 N 32801 0 8
-def select_type 253 19 18 N 1 31 8
-def table 253 64 10 N 1 31 8
-def type 253 10 3 N 1 31 8
-def possible_keys 253 4096 0 Y 0 31 8
-def key 253 64 0 Y 0 31 8
-def key_len 8 3 0 Y 32800 0 8
-def ref 253 1024 0 Y 0 31 8
-def rows 8 10 1 N 32801 0 8
-def Extra 253 255 44 N 1 31 8
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
execute stmt1 ;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
set @stmt= ' SELECT
(SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2
GROUP BY t9.c15 LIMIT 1) as scalar_s,
@@ -4108,56 +3986,14 @@ set @arg09= 40e-1 ;
prepare stmt1 from @stmt ;
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def scalar_s 5 23 2 Y 32768 31 8
-def exists_s 8 1 1 N 32769 0 8
-def in_s 8 21 1 Y 32768 0 8
-def in_row_s 8 21 1 Y 32768 0 8
-scalar_s exists_s in_s in_row_s
-2 0 1 0
-18 1 0 1
-2 0 1 0
-18 1 0 1
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-scalar_s exists_s in_s in_row_s
-2 0 1 0
-18 1 0 1
-2 0 1 0
-18 1 0 1
set @stmt= concat('explain ',@stmt);
prepare stmt1 from @stmt ;
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def id 8 3 1 N 32801 0 8
-def select_type 253 19 18 N 1 31 8
-def table 253 64 10 N 1 31 8
-def type 253 10 3 N 1 31 8
-def possible_keys 253 4096 0 Y 0 31 8
-def key 253 64 0 Y 0 31 8
-def key_len 8 3 0 Y 32800 0 8
-def ref 253 1024 0 Y 0 31 8
-def rows 8 10 1 N 32801 0 8
-def Extra 253 255 44 N 1 31 8
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
drop table t2 ;
select 1 < (select a from t1) ;
ERROR 21000: Subquery returns more than 1 row
@@ -6203,18 +6039,22 @@ found
true
set @arg00= 1.991e+3 ;
select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3 ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;
found
+true
select 'true' as found from t9
-where c1= 20 and c17= @arg00 ;
+where c1= 20 and abs(c17 - @arg00) < 0.01 ;
found
+true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3" ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;
execute stmt1 ;
found
+true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= ?" ;
+where c1= 20 and abs(c17 - ?) < 0.01" ;
execute stmt1 using @arg00 ;
found
+true
drop table t1, t1_1, t1_2,
t9_1, t9_2, t9;
diff --git a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result
index 88e4d701acf..52009dcf82a 100644
--- a/mysql-test/r/ps_6bdb.result
+++ b/mysql-test/r/ps_6bdb.result
@@ -908,53 +908,11 @@ FROM t9,
(select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
prepare stmt1 from @stmt ;
execute stmt1 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def scalar_s 5 21 7 Y 32768 4 8
-def exists_s 8 1 1 N 32769 0 8
-def in_s 8 21 1 Y 32768 0 8
-def in_row_s 8 21 1 Y 32768 0 8
-scalar_s exists_s in_s in_row_s
-2.0000 0 1 0
-2.0000 0 1 0
-18.0000 1 0 1
-18.0000 1 0 1
-execute stmt1 ;
-scalar_s exists_s in_s in_row_s
-2.0000 0 1 0
-2.0000 0 1 0
-18.0000 1 0 1
-18.0000 1 0 1
+execute stmt1 ;
set @stmt= concat('explain ',@stmt);
prepare stmt1 from @stmt ;
execute stmt1 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def id 8 3 1 N 32801 0 8
-def select_type 253 19 18 N 1 31 8
-def table 253 64 10 N 1 31 8
-def type 253 10 3 N 1 31 8
-def possible_keys 253 4096 0 Y 0 31 8
-def key 253 64 0 Y 0 31 8
-def key_len 8 3 0 Y 32800 0 8
-def ref 253 1024 0 Y 0 31 8
-def rows 8 10 1 N 32801 0 8
-def Extra 253 255 44 N 1 31 8
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2
-1 PRIMARY t9 ALL NULL NULL NULL NULL 3 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
execute stmt1 ;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2
-1 PRIMARY t9 ALL NULL NULL NULL NULL 3 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
set @stmt= ' SELECT
(SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2
GROUP BY t9.c15 LIMIT 1) as scalar_s,
@@ -977,56 +935,14 @@ set @arg09= 40e-1 ;
prepare stmt1 from @stmt ;
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def scalar_s 5 23 2 Y 32768 31 8
-def exists_s 8 1 1 N 32769 0 8
-def in_s 8 21 1 Y 32768 0 8
-def in_row_s 8 21 1 Y 32768 0 8
-scalar_s exists_s in_s in_row_s
-2 0 1 0
-2 0 1 0
-18 1 0 1
-18 1 0 1
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-scalar_s exists_s in_s in_row_s
-2 0 1 0
-2 0 1 0
-18 1 0 1
-18 1 0 1
set @stmt= concat('explain ',@stmt);
prepare stmt1 from @stmt ;
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def id 8 3 1 N 32801 0 8
-def select_type 253 19 18 N 1 31 8
-def table 253 64 10 N 1 31 8
-def type 253 10 3 N 1 31 8
-def possible_keys 253 4096 0 Y 0 31 8
-def key 253 64 0 Y 0 31 8
-def key_len 8 3 0 Y 32800 0 8
-def ref 253 1024 0 Y 0 31 8
-def rows 8 10 1 N 32801 0 8
-def Extra 253 255 44 N 1 31 8
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2
-1 PRIMARY t9 ALL NULL NULL NULL NULL 3 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2
-1 PRIMARY t9 ALL NULL NULL NULL NULL 3 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
drop table t2 ;
select 1 < (select a from t1) ;
ERROR 21000: Subquery returns more than 1 row
@@ -3175,17 +3091,21 @@ found
true
set @arg00= 1.991e+3 ;
select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3 ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;
found
+true
select 'true' as found from t9
-where c1= 20 and c17= @arg00 ;
+where c1= 20 and abs(c17 - @arg00) < 0.01 ;
found
+true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3" ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;
execute stmt1 ;
found
+true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= ?" ;
+where c1= 20 and abs(c17 - ?) < 0.01" ;
execute stmt1 using @arg00 ;
found
+true
drop table t1, t9;
diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result
index eaadafdf339..decfc08b555 100644
--- a/mysql-test/r/ps_7ndb.result
+++ b/mysql-test/r/ps_7ndb.result
@@ -909,53 +909,11 @@ FROM t9,
(select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
prepare stmt1 from @stmt ;
execute stmt1 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def scalar_s 5 21 7 Y 32768 4 8
-def exists_s 8 1 1 N 32769 0 8
-def in_s 8 21 1 Y 32768 0 8
-def in_row_s 8 21 1 Y 32768 0 8
-scalar_s exists_s in_s in_row_s
-18.0000 1 0 1
-2.0000 0 1 0
-18.0000 1 0 1
-2.0000 0 1 0
-execute stmt1 ;
-scalar_s exists_s in_s in_row_s
-18.0000 1 0 1
-2.0000 0 1 0
-18.0000 1 0 1
-2.0000 0 1 0
+execute stmt1 ;
set @stmt= concat('explain ',@stmt);
prepare stmt1 from @stmt ;
execute stmt1 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def id 8 3 1 N 32801 0 8
-def select_type 253 19 18 N 1 31 8
-def table 253 64 10 N 1 31 8
-def type 253 10 3 N 1 31 8
-def possible_keys 253 4096 0 Y 0 31 8
-def key 253 64 0 Y 0 31 8
-def key_len 8 3 0 Y 32800 0 8
-def ref 253 1024 0 Y 0 31 8
-def rows 8 10 1 N 32801 0 8
-def Extra 253 255 44 N 1 31 8
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
execute stmt1 ;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
set @stmt= ' SELECT
(SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2
GROUP BY t9.c15 LIMIT 1) as scalar_s,
@@ -978,56 +936,14 @@ set @arg09= 40e-1 ;
prepare stmt1 from @stmt ;
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def scalar_s 5 23 2 Y 32768 31 8
-def exists_s 8 1 1 N 32769 0 8
-def in_s 8 21 1 Y 32768 0 8
-def in_row_s 8 21 1 Y 32768 0 8
-scalar_s exists_s in_s in_row_s
-18 1 0 1
-2 0 1 0
-18 1 0 1
-2 0 1 0
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-scalar_s exists_s in_s in_row_s
-18 1 0 1
-2 0 1 0
-18 1 0 1
-2 0 1 0
set @stmt= concat('explain ',@stmt);
prepare stmt1 from @stmt ;
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-Catalog Database Table Table_alias Column Column_alias Name Type Length Max length Is_null Flags Decimals Charsetnr
-def id 8 3 1 N 32801 0 8
-def select_type 253 19 18 N 1 31 8
-def table 253 64 10 N 1 31 8
-def type 253 10 3 N 1 31 8
-def possible_keys 253 4096 0 Y 0 31 8
-def key 253 64 0 Y 0 31 8
-def key_len 8 3 0 Y 32800 0 8
-def ref 253 1024 0 Y 0 31 8
-def rows 8 10 1 N 32801 0 8
-def Extra 253 255 44 N 1 31 8
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
@arg07, @arg08, @arg09 ;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t9 ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived6> ALL NULL NULL NULL NULL 2 Using where
-6 DERIVED t2 ALL NULL NULL NULL NULL 2
-5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
drop table t2 ;
select 1 < (select a from t1) ;
ERROR 21000: Subquery returns more than 1 row
@@ -3154,17 +3070,21 @@ found
true
set @arg00= 1.991e+3 ;
select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3 ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;
found
+true
select 'true' as found from t9
-where c1= 20 and c17= @arg00 ;
+where c1= 20 and abs(c17 - @arg00) < 0.01 ;
found
+true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= 1.991e+3" ;
+where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;
execute stmt1 ;
found
+true
prepare stmt1 from "select 'true' as found from t9
-where c1= 20 and c17= ?" ;
+where c1= 20 and abs(c17 - ?) < 0.01" ;
execute stmt1 using @arg00 ;
found
+true
drop table t1, t9;
View Lorry Controller Status