[MDEV-13436] PREPARE doesn't work as expected & throws errors but MySQL is working fine Created: 2017-08-03  Updated: 2017-09-11  Resolved: 2017-09-11

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Prepared Statements, Views
Affects Version/s: 10.2.7, 10.2
Fix Version/s: 10.2.9, 10.3.2

Type: Bug Priority: Major
Reporter: Ralf Niedermayer Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: optimizer
Environment:

Windows 2008R2 64 Bit und Windows 7 64 Bit

Attachments: File Bug_MariaDB_in_PREPARE_Statement_cte_multiple_with_queries.sql     File Bug_MariaDB_in_PREPARE_Statement_subselect_max.sql    
Issue Links:
Relates
relates to MDEV-13753 CTE is not visible during view creation Closed

 Description   

In some cases (see attached files for two simple examples) an SQL query with PREPARE gives me an error message instead of a result set. I think this queries should work and if I execute the exact same queries on MySQL 5.7.18 (subselect example) or MySQL 8.0.2 (subselect and CTE examples) it is working fine and as excepted. So I think there may be an Bug with MariaDB?

This occurs only with PREPARE. If I'm running this directly (without PREPARE) it is working fine too with MariaDB.
If I change the "max(1)" to "ceil(1)" in my subselect example it is working fine altogether (PREPARE and MariaDB).

The error messages are included in the examples.
It would be nice if you can double check my examples and maybe fix it in a future version?

 Comments   
Comment by Elena Stepanova [ 2017-08-04 ]

Thanks for the report and test cases.

With 10.1, neither of the queries is expected to work anyway, so I'm removing it from the affected versions; but with 10.2, both should work, so it appears to be a bug indeed.

Here are the test cases from the attachments (for convenience):

Subquery with MAX

 
USE test;
 
SET @sql_query = "
 
  CREATE OR REPLACE VIEW testview AS
 
    SELECT * FROM (
 
          SELECT CASE WHEN 1 IN (SELECT MAX(1)) THEN TRUE END AS testcase
 
    ) testalias
 
";
 
PREPARE stmt FROM @sql_query;
 
EXECUTE stmt;
 
DEALLOCATE PREPARE stmt;
 
SELECT * FROM testview;


MariaDB [test]> SELECT * FROM testview;
 
ERROR 1064 (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 '<in_optimizer>(1,<exists>(select max(1))) then 1 end AS `testcase`) `testalias`' at line 1

CTE

 
USE test;
 
SET @sql_query = "
 
  CREATE OR REPLACE VIEW cte_test AS
 
   WITH  cte1  AS ( SELECT 1 )
 
    ,    cte2  AS ( SELECT * FROM cte1 )
 
    SELECT * FROM cte2;
 
";
 
PREPARE stmt FROM @sql_query;


MariaDB [test]> PREPARE stmt FROM @sql_query;
 
ERROR 1146 (42S02): Table 'test.cte1' doesn't exist

Comment by Oleksandr Byelkin [ 2017-09-06 ]

First case have nothing to do with MAX, max only prevent to get rid of the subquery, here is other example where it fails:

USE test;
 
create table t1 (a int);
 
insert into t1 values (1),(2);
 
SET @sql_query = "
 
  CREATE OR REPLACE VIEW testview AS
 
    SELECT * FROM (
 
          SELECT CASE WHEN 1 IN (SELECT a from t1 where a < 2) THEN TRUE END AS testcase
 
    ) testalias
 
";
 
PREPARE stmt FROM @sql_query;
 
EXECUTE stmt;
 
DEALLOCATE PREPARE stmt;
 
show create view testview;
 
SELECT * FROM testview;

the real problem incorrect print of subquery probably because of incorrect mode or because of something triggered optimisation.

Comment by Oleksandr Byelkin [ 2017-09-07 ]

Second test suite is a bug in CTE so I put it separate.

Comment by Oleksandr Byelkin [ 2017-09-07 ]

revision-id: 0e931867fd540123679f8af7da0ddf9bd351d546 (mariadb-10.2.8-52-g0e931867fd5)
parent(s): 2b387855df7be57a3017a5c211543574fa4364d4
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2017-09-07 11:13:08 +0200
message:

MDEV-13436 PREPARE doesn't work as expected & throws errors but MySQL is working fine

Now we allow derived in the from clause of subqueries so set
flag which prevent optimisation of subqueries during view
creation before derived processing so subquery will be
correctly printed in the view .frm.

—

Comment by Oleksandr Byelkin [ 2017-09-07 ]

github tree bb-10.2-MDEV-13436

Comment by Igor Babaev [ 2017-09-10 ]

It's ok to push this patch.

Generated at Thu Feb 08 08:05:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.