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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.7, 10.2(EOL)
Fix Version/s: 10.2.9, 10.3.2
Component/s: Data Manipulation - Subquery, Prepared Statements, Views
Labels:
- optimizer
Environment:
Windows 2008R2 64 Bit und Windows 7 64 Bit

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?

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Bug_MariaDB_in_PREPARE_Statement_cte_multiple_with_queries.sql
2017-08-03 13:11
0.4 kB
Ralf Niedermayer
Bug_MariaDB_in_PREPARE_Statement_subselect_max.sql
2017-08-03 13:11
0.6 kB
Ralf Niedermayer

Issue Links

relates to

MDEV-13753 CTE is not visible during view creation

Closed

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2017-08-04 21:28

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

Elena Stepanova added a comment - 2017-08-04 21:28 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

Oleksandr Byelkin added a comment - 2017-09-06 15:35 - edited

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.

Oleksandr Byelkin added a comment - 2017-09-06 15:35 - edited 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.

Oleksandr Byelkin added a comment - 2017-09-07 09:01

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

Oleksandr Byelkin added a comment - 2017-09-07 09:01 Second test suite is a bug in CTE so I put it separate.

Oleksandr Byelkin added a comment - 2017-09-07 09:13

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.

—

Oleksandr Byelkin added a comment - 2017-09-07 09:13 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. —

Oleksandr Byelkin added a comment - 2017-09-07 10:04

github tree bb-10.2-~~MDEV-13436~~

Oleksandr Byelkin added a comment - 2017-09-07 10:04 github tree bb-10.2- MDEV-13436

Igor Babaev (Inactive) added a comment - 2017-09-10 17:17

It's ok to push this patch.

Igor Babaev (Inactive) added a comment - 2017-09-10 17:17 It's ok to push this patch.

MariaDB Server

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

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration