[MDEV-12440] CTE not found in a complex SQL code - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.4
Fix Version/s: 10.2.6
Component/s: Optimizer - CTE
Labels:
None
Environment:
debian sid package on x64

Description

Dear all,

I am currently working on a tool to translate machine learning models (built with scikit-learn python library) into a SQL code that can produce the model predictions when executed. The tool works by translating the model into a "sequence" of CTEs. It supports the main relational databases available on the market (sqlite, postgresql, MSSql, Oracle). Some demo is available here (https://github.com/antoinecarme/sklearn2sql-demo/) with sample SQL codes.

MySQL used to not support CTEs. I see that MariaDB is adding support for CTEs and I will be glad to use it and help with examples. This is very good news!!!

I am giving it a try. I use the debian sid (x64) 10.2.4 version and already read some the bug reports filed recently about CTEs.

I use a set of automatic tests that run on all supported databases. I will only report when MariaDB is the only failing case.

So far, a lot of the tested models generate SQL that executes OK on MariaDB which is great!! However, I still have some issues with some complex models (>10 CTEs), I can provide a list.

For example, the AdaBoostClassifier models produce some SQL code that works on postgresql/oracle/MSSQL/SQlite but not on MariaDB. I get a message

"Table \'db.AdaBoost_AVG_Scores\' doesn\'t exist"

AdaBoost_AVG_Scores is a CTE defined in the query. It seems to be a lookup issue for CTEs. the SQL code is available here :
https://github.com/antoinecarme/sklearn2sql-demo/blob/master/sample_outputs_round_4/AdaBoostClassifier/iris/mysql/demo1_AdaBoostClassifier_mysql.sql

the SQL code is based on a table INPUT_DATA that can be defined this way :

create table `INPUT_DATA` AS
(
select 1 as `KEY`, 0.0 as `Feature_0`, 1.0 as `Feature_1`, 2.0 as `Feature_2`, 3.0 as `Feature_3`
)

Your help is welcome.

Sorry for the long message. I tried to give a full bug report with the most details.

Thanks in advance

Antoine

Attachments

Issue Links

duplicates

MDEV-13371 Classic CTE query fails in 10.2.2

Closed

relates to

MDEV-14184 recursive CTE not found

Closed

Activity

Ascending order - Click to sort in descending order

CARME Antoine created issue - 2017-04-04 09:07

CARME Antoine made changes - 2017-04-04 09:13

Field	Original Value	New Value
Description	Dear all, I am currently working on a tool to translate machine learning models (built with scikit-learn python library) into a SQL code that can produce the model predictions when executed. The tool works by translating the model into a "sequence" of CTEs. It supports the main relational databases available on the market (sqlite, postgresql, MSSql, Oracle). Some demo is available here (https://github.com/antoinecarme/sklearn2sql-demo/) with sample SQL codes. MySQL used to not support CTEs. I see that MariaDB is adding support for CTEs and I will be glad to use it and help with examples. This is very good news!!! I am giving it a try. I use the debian (x64) sid version and already read some the bug reports filed recently about CTEs. I use a set of automatic tests that run on all supported databases. I will only report when MariaDB is the only failing case. I still have some issues with some models, I can provide a list. For the moment, the AdaBoostClassifier models produce some SQL code that works on postgresql/oracle/MSSQL/SQlite but not on MariaDB. I get a message "Table \'db.AdaBoost_AVG_Scores\' doesn\'t exist" AdaBoost_AVG_Scores is a CTE defined in the query. It seems to be allokup issue for CTEs. the SQL code is available here : https://github.com/antoinecarme/sklearn2sql-demo/blob/master/sample_outputs_round_4/AdaBoostClassifier/iris/mysql/demo1_AdaBoostClassifier_mysql.sql the SQL code is based on a table INPUT_DATA that contain the columns cited here : SELECT `ADS`.`KEY` AS `KEY`, CASE WHEN (`ADS`.`Feature_3` <= 0.800000011920929) THEN 1 ELSE CASE WHEN (`ADS`.`Feature_3` <= 1.75) THEN CASE WHEN (`ADS`.`Feature_2` <= 4.449999809265137) THEN 4 ELSE 5 END ELSE CASE WHEN (`ADS`.`Feature_0` <= 6.350000381469727) THEN 7 ELSE 8 END END END AS node_id_2 FROM `INPUT_DATA` AS `ADS` Your help is welcome. Sorry for the long message. I tried to give a full bug report with the most details. Thanks in advance Antoine	Dear all, I am currently working on a tool to translate machine learning models (built with scikit-learn python library) into a SQL code that can produce the model predictions when executed. The tool works by translating the model into a "sequence" of CTEs. It supports the main relational databases available on the market (sqlite, postgresql, MSSql, Oracle). Some demo is available here (https://github.com/antoinecarme/sklearn2sql-demo/) with sample SQL codes. MySQL used to not support CTEs. I see that MariaDB is adding support for CTEs and I will be glad to use it and help with examples. This is very good news!!! I am giving it a try. I use the debian (x64) sid version and already read some the bug reports filed recently about CTEs. I use a set of automatic tests that run on all supported databases. I will only report when MariaDB is the only failing case. I still have some issues with some models, I can provide a list. For the moment, the AdaBoostClassifier models produce some SQL code that works on postgresql/oracle/MSSQL/SQlite but not on MariaDB. I get a message "Table \'db.AdaBoost_AVG_Scores\' doesn\'t exist" AdaBoost_AVG_Scores is a CTE defined in the query. It seems to be a lookup issue for CTEs. the SQL code is available here : https://github.com/antoinecarme/sklearn2sql-demo/blob/master/sample_outputs_round_4/AdaBoostClassifier/iris/mysql/demo1_AdaBoostClassifier_mysql.sql the SQL code is based on a table INPUT_DATA that contain the columns cited here : SELECT `ADS`.`KEY` AS `KEY`, CASE WHEN (`ADS`.`Feature_3` <= 0.800000011920929) THEN 1 ELSE CASE WHEN (`ADS`.`Feature_3` <= 1.75) THEN CASE WHEN (`ADS`.`Feature_2` <= 4.449999809265137) THEN 4 ELSE 5 END ELSE CASE WHEN (`ADS`.`Feature_0` <= 6.350000381469727) THEN 7 ELSE 8 END END END AS node_id_2 FROM `INPUT_DATA` AS `ADS` Your help is welcome. Sorry for the long message. I tried to give a full bug report with the most details. Thanks in advance Antoine

CARME Antoine made changes - 2017-04-04 09:15

Description

Dear all,

I am currently working on a tool to translate machine learning models (built with scikit-learn python library) into a SQL code that can produce the model predictions when executed. The tool works by translating the model into a "sequence" of CTEs. It supports the main relational databases available on the market (sqlite, postgresql, MSSql, Oracle). Some demo is available here (https://github.com/antoinecarme/sklearn2sql-demo/) with sample SQL codes.

MySQL used to not support CTEs. I see that MariaDB is adding support for CTEs and I will be glad to use it and help with examples. This is very good news!!!

I am giving it a try. I use the debian (x64) sid version and already read some the bug reports filed recently about CTEs.

I use a set of automatic tests that run on all supported databases. I will only report when MariaDB is the only failing case.

I still have some issues with some models, I can provide a list. For the moment, the AdaBoostClassifier models produce some SQL code that works on postgresql/oracle/MSSQL/SQlite but not on MariaDB. I get a message

"Table \'db.AdaBoost_AVG_Scores\' doesn\'t exist"

AdaBoost_AVG_Scores is a CTE defined in the query. It seems to be a lookup issue for CTEs. the SQL code is available here :
https://github.com/antoinecarme/sklearn2sql-demo/blob/master/sample_outputs_round_4/AdaBoostClassifier/iris/mysql/demo1_AdaBoostClassifier_mysql.sql

the SQL code is based on a table INPUT_DATA that contain the columns cited here :

SELECT `ADS`.`KEY` AS `KEY`, CASE WHEN (`ADS`.`Feature_3` <= 0.800000011920929) THEN 1 ELSE CASE WHEN (`ADS`.`Feature_3` <= 1.75) THEN CASE WHEN (`ADS`.`Feature_2` <= 4.449999809265137) THEN 4 ELSE 5 END ELSE CASE WHEN (`ADS`.`Feature_0` <= 6.350000381469727) THEN 7 ELSE 8 END END END AS node_id_2
FROM `INPUT_DATA` AS `ADS`

Your help is welcome.

Sorry for the long message. I tried to give a full bug report with the most details.

Thanks in advance

Antoine

Dear all,

I am currently working on a tool to translate machine learning models (built with scikit-learn python library) into a SQL code that can produce the model predictions when executed. The tool works by translating the model into a "sequence" of CTEs. It supports the main relational databases available on the market (sqlite, postgresql, MSSql, Oracle). Some demo is available here (https://github.com/antoinecarme/sklearn2sql-demo/) with sample SQL codes.

MySQL used to not support CTEs. I see that MariaDB is adding support for CTEs and I will be glad to use it and help with examples. This is very good news!!!

I am giving it a try. I use the debian (x64) sid version and already read some the bug reports filed recently about CTEs.

I use a set of automatic tests that run on all supported databases. I will only report when MariaDB is the only failing case.

I still have some issues with some models, I can provide a list. For the moment, the AdaBoostClassifier models produce some SQL code that works on postgresql/oracle/MSSQL/SQlite but not on MariaDB. I get a message

"Table \'db.AdaBoost_AVG_Scores\' doesn\'t exist"

AdaBoost_AVG_Scores is a CTE defined in the query. It seems to be a lookup issue for CTEs. the SQL code is available here :
https://github.com/antoinecarme/sklearn2sql-demo/blob/master/sample_outputs_round_4/AdaBoostClassifier/iris/mysql/demo1_AdaBoostClassifier_mysql.sql

the SQL code is based on a table INPUT_DATA that can be defined this way :

create table `INPUT_DATA` AS
(
select 1 as `KEY`, 0.0 as `Feature_0`, 1.0 as `Feature_1`, 2.0 as `Feature_2`, 3.0 as Feature_3
)

Your help is welcome.

Sorry for the long message. I tried to give a full bug report with the most details.

Thanks in advance

Antoine

CARME Antoine made changes - 2017-04-04 09:39

Description

Dear all,

I am currently working on a tool to translate machine learning models (built with scikit-learn python library) into a SQL code that can produce the model predictions when executed. The tool works by translating the model into a "sequence" of CTEs. It supports the main relational databases available on the market (sqlite, postgresql, MSSql, Oracle). Some demo is available here (https://github.com/antoinecarme/sklearn2sql-demo/) with sample SQL codes.

MySQL used to not support CTEs. I see that MariaDB is adding support for CTEs and I will be glad to use it and help with examples. This is very good news!!!

I am giving it a try. I use the debian (x64) sid version and already read some the bug reports filed recently about CTEs.

I use a set of automatic tests that run on all supported databases. I will only report when MariaDB is the only failing case.

I still have some issues with some models, I can provide a list. For the moment, the AdaBoostClassifier models produce some SQL code that works on postgresql/oracle/MSSQL/SQlite but not on MariaDB. I get a message

"Table \'db.AdaBoost_AVG_Scores\' doesn\'t exist"

AdaBoost_AVG_Scores is a CTE defined in the query. It seems to be a lookup issue for CTEs. the SQL code is available here :
https://github.com/antoinecarme/sklearn2sql-demo/blob/master/sample_outputs_round_4/AdaBoostClassifier/iris/mysql/demo1_AdaBoostClassifier_mysql.sql

the SQL code is based on a table INPUT_DATA that can be defined this way :

create table `INPUT_DATA` AS
(
select 1 as `KEY`, 0.0 as `Feature_0`, 1.0 as `Feature_1`, 2.0 as `Feature_2`, 3.0 as Feature_3
)

Your help is welcome.

Sorry for the long message. I tried to give a full bug report with the most details.

Thanks in advance

Antoine

Dear all,

I am currently working on a tool to translate machine learning models (built with scikit-learn python library) into a SQL code that can produce the model predictions when executed. The tool works by translating the model into a "sequence" of CTEs. It supports the main relational databases available on the market (sqlite, postgresql, MSSql, Oracle). Some demo is available here (https://github.com/antoinecarme/sklearn2sql-demo/) with sample SQL codes.

MySQL used to not support CTEs. I see that MariaDB is adding support for CTEs and I will be glad to use it and help with examples. This is very good news!!!

I am giving it a try. I use the debian (x64) sid version and already read some the bug reports filed recently about CTEs.

I use a set of automatic tests that run on all supported databases. I will only report when MariaDB is the only failing case.

I still have some issues with some models, I can provide a list. For the moment, the AdaBoostClassifier models produce some SQL code that works on postgresql/oracle/MSSQL/SQlite but not on MariaDB. I get a message

"Table \'db.AdaBoost_AVG_Scores\' doesn\'t exist"

AdaBoost_AVG_Scores is a CTE defined in the query. It seems to be a lookup issue for CTEs. the SQL code is available here :
https://github.com/antoinecarme/sklearn2sql-demo/blob/master/sample_outputs_round_4/AdaBoostClassifier/iris/mysql/demo1_AdaBoostClassifier_mysql.sql

the SQL code is based on a table INPUT_DATA that can be defined this way :

create table `INPUT_DATA` AS
(
select 1 as `KEY`, 0.0 as `Feature_0`, 1.0 as `Feature_1`, 2.0 as `Feature_2`, 3.0 as `Feature_3`
)

Your help is welcome.

Sorry for the long message. I tried to give a full bug report with the most details.

Thanks in advance

Antoine

Sergei Golubchik made changes - 2017-04-04 09:49

Affects Version/s

10.2.4 [ 22116 ]

Sergei Golubchik made changes - 2017-04-04 09:49

Component/s

Optimizer - CTE [ 13513 ]

Sergei Golubchik made changes - 2017-04-04 09:50

Description

Dear all,

I am currently working on a tool to translate machine learning models (built with scikit-learn python library) into a SQL code that can produce the model predictions when executed. The tool works by translating the model into a "sequence" of CTEs. It supports the main relational databases available on the market (sqlite, postgresql, MSSql, Oracle). Some demo is available here (https://github.com/antoinecarme/sklearn2sql-demo/) with sample SQL codes.

MySQL used to not support CTEs. I see that MariaDB is adding support for CTEs and I will be glad to use it and help with examples. This is very good news!!!

I am giving it a try. I use the debian (x64) sid version and already read some the bug reports filed recently about CTEs.

I use a set of automatic tests that run on all supported databases. I will only report when MariaDB is the only failing case.

I still have some issues with some models, I can provide a list. For the moment, the AdaBoostClassifier models produce some SQL code that works on postgresql/oracle/MSSQL/SQlite but not on MariaDB. I get a message

"Table \'db.AdaBoost_AVG_Scores\' doesn\'t exist"

AdaBoost_AVG_Scores is a CTE defined in the query. It seems to be a lookup issue for CTEs. the SQL code is available here :
https://github.com/antoinecarme/sklearn2sql-demo/blob/master/sample_outputs_round_4/AdaBoostClassifier/iris/mysql/demo1_AdaBoostClassifier_mysql.sql

the SQL code is based on a table INPUT_DATA that can be defined this way :

create table `INPUT_DATA` AS
(
select 1 as `KEY`, 0.0 as `Feature_0`, 1.0 as `Feature_1`, 2.0 as `Feature_2`, 3.0 as `Feature_3`
)

Your help is welcome.

Sorry for the long message. I tried to give a full bug report with the most details.

Thanks in advance

Antoine

Dear all,

I am currently working on a tool to translate machine learning models (built with scikit-learn python library) into a SQL code that can produce the model predictions when executed. The tool works by translating the model into a "sequence" of CTEs. It supports the main relational databases available on the market (sqlite, postgresql, MSSql, Oracle). Some demo is available here (https://github.com/antoinecarme/sklearn2sql-demo/) with sample SQL codes.

MySQL used to not support CTEs. I see that MariaDB is adding support for CTEs and I will be glad to use it and help with examples. This is very good news!!!

I am giving it a try. I use the debian (x64) sid version and already read some the bug reports filed recently about CTEs.

I use a set of automatic tests that run on all supported databases. I will only report when MariaDB is the only failing case.

I still have some issues with some models, I can provide a list. For the moment, the AdaBoostClassifier models produce some SQL code that works on postgresql/oracle/MSSQL/SQlite but not on MariaDB. I get a message

"Table \'db.AdaBoost_AVG_Scores\' doesn\'t exist"

AdaBoost_AVG_Scores is a CTE defined in the query. It seems to be a lookup issue for CTEs. the SQL code is available here :
https://github.com/antoinecarme/sklearn2sql-demo/blob/master/sample_outputs_round_4/AdaBoostClassifier/iris/mysql/demo1_AdaBoostClassifier_mysql.sql

the SQL code is based on a table INPUT_DATA that can be defined this way :
{code:sql}
create table `INPUT_DATA` AS
(
select 1 as `KEY`, 0.0 as `Feature_0`, 1.0 as `Feature_1`, 2.0 as `Feature_2`, 3.0 as `Feature_3`
)
{code}
Your help is welcome.

Sorry for the long message. I tried to give a full bug report with the most details.

Thanks in advance

Antoine

CARME Antoine made changes - 2017-04-04 09:50

Description

Dear all,

I am currently working on a tool to translate machine learning models (built with scikit-learn python library) into a SQL code that can produce the model predictions when executed. The tool works by translating the model into a "sequence" of CTEs. It supports the main relational databases available on the market (sqlite, postgresql, MSSql, Oracle). Some demo is available here (https://github.com/antoinecarme/sklearn2sql-demo/) with sample SQL codes.

MySQL used to not support CTEs. I see that MariaDB is adding support for CTEs and I will be glad to use it and help with examples. This is very good news!!!

I am giving it a try. I use the debian (x64) sid version and already read some the bug reports filed recently about CTEs.

I use a set of automatic tests that run on all supported databases. I will only report when MariaDB is the only failing case.

I still have some issues with some models, I can provide a list. For the moment, the AdaBoostClassifier models produce some SQL code that works on postgresql/oracle/MSSQL/SQlite but not on MariaDB. I get a message

"Table \'db.AdaBoost_AVG_Scores\' doesn\'t exist"

AdaBoost_AVG_Scores is a CTE defined in the query. It seems to be a lookup issue for CTEs. the SQL code is available here :
https://github.com/antoinecarme/sklearn2sql-demo/blob/master/sample_outputs_round_4/AdaBoostClassifier/iris/mysql/demo1_AdaBoostClassifier_mysql.sql

the SQL code is based on a table INPUT_DATA that can be defined this way :
{code:sql}
create table `INPUT_DATA` AS
(
select 1 as `KEY`, 0.0 as `Feature_0`, 1.0 as `Feature_1`, 2.0 as `Feature_2`, 3.0 as `Feature_3`
)
{code}
Your help is welcome.

Sorry for the long message. I tried to give a full bug report with the most details.

Thanks in advance

Antoine

Dear all,

I am currently working on a tool to translate machine learning models (built with scikit-learn python library) into a SQL code that can produce the model predictions when executed. The tool works by translating the model into a "sequence" of CTEs. It supports the main relational databases available on the market (sqlite, postgresql, MSSql, Oracle). Some demo is available here (https://github.com/antoinecarme/sklearn2sql-demo/) with sample SQL codes.

MySQL used to not support CTEs. I see that MariaDB is adding support for CTEs and I will be glad to use it and help with examples. This is very good news!!!

I am giving it a try. I use the debian sid (x64) 10.2.4 version and already read some the bug reports filed recently about CTEs.

I use a set of automatic tests that run on all supported databases. I will only report when MariaDB is the only failing case.

I still have some issues with some models, I can provide a list. For the moment, the AdaBoostClassifier models produce some SQL code that works on postgresql/oracle/MSSQL/SQlite but not on MariaDB. I get a message

"Table \'db.AdaBoost_AVG_Scores\' doesn\'t exist"

AdaBoost_AVG_Scores is a CTE defined in the query. It seems to be a lookup issue for CTEs. the SQL code is available here :
https://github.com/antoinecarme/sklearn2sql-demo/blob/master/sample_outputs_round_4/AdaBoostClassifier/iris/mysql/demo1_AdaBoostClassifier_mysql.sql

the SQL code is based on a table INPUT_DATA that can be defined this way :

create table `INPUT_DATA` AS
(
select 1 as `KEY`, 0.0 as `Feature_0`, 1.0 as `Feature_1`, 2.0 as `Feature_2`, 3.0 as `Feature_3`
)

Your help is welcome.

Sorry for the long message. I tried to give a full bug report with the most details.

Thanks in advance

Antoine

CARME Antoine made changes - 2017-04-04 10:07

Description

Dear all,

I am currently working on a tool to translate machine learning models (built with scikit-learn python library) into a SQL code that can produce the model predictions when executed. The tool works by translating the model into a "sequence" of CTEs. It supports the main relational databases available on the market (sqlite, postgresql, MSSql, Oracle). Some demo is available here (https://github.com/antoinecarme/sklearn2sql-demo/) with sample SQL codes.

MySQL used to not support CTEs. I see that MariaDB is adding support for CTEs and I will be glad to use it and help with examples. This is very good news!!!

I am giving it a try. I use the debian sid (x64) 10.2.4 version and already read some the bug reports filed recently about CTEs.

I use a set of automatic tests that run on all supported databases. I will only report when MariaDB is the only failing case.

I still have some issues with some models, I can provide a list. For the moment, the AdaBoostClassifier models produce some SQL code that works on postgresql/oracle/MSSQL/SQlite but not on MariaDB. I get a message

"Table \'db.AdaBoost_AVG_Scores\' doesn\'t exist"

AdaBoost_AVG_Scores is a CTE defined in the query. It seems to be a lookup issue for CTEs. the SQL code is available here :
https://github.com/antoinecarme/sklearn2sql-demo/blob/master/sample_outputs_round_4/AdaBoostClassifier/iris/mysql/demo1_AdaBoostClassifier_mysql.sql

the SQL code is based on a table INPUT_DATA that can be defined this way :

create table `INPUT_DATA` AS
(
select 1 as `KEY`, 0.0 as `Feature_0`, 1.0 as `Feature_1`, 2.0 as `Feature_2`, 3.0 as `Feature_3`
)

Your help is welcome.

Sorry for the long message. I tried to give a full bug report with the most details.

Thanks in advance

Antoine

Dear all,

I am currently working on a tool to translate machine learning models (built with scikit-learn python library) into a SQL code that can produce the model predictions when executed. The tool works by translating the model into a "sequence" of CTEs. It supports the main relational databases available on the market (sqlite, postgresql, MSSql, Oracle). Some demo is available here (https://github.com/antoinecarme/sklearn2sql-demo/) with sample SQL codes.

MySQL used to not support CTEs. I see that MariaDB is adding support for CTEs and I will be glad to use it and help with examples. This is very good news!!!

I am giving it a try. I use the debian sid (x64) 10.2.4 version and already read some the bug reports filed recently about CTEs.

I use a set of automatic tests that run on all supported databases. I will only report when MariaDB is the only failing case.

So far, al lot of models generate SQL that executes OK on MariaDB which is great!! However, I still have some issues with some complex models (>10 CTEs), I can provide a list.

For example, the AdaBoostClassifier models produce some SQL code that works on postgresql/oracle/MSSQL/SQlite but not on MariaDB. I get a message

"Table \'db.AdaBoost_AVG_Scores\' doesn\'t exist"

AdaBoost_AVG_Scores is a CTE defined in the query. It seems to be a lookup issue for CTEs. the SQL code is available here :
https://github.com/antoinecarme/sklearn2sql-demo/blob/master/sample_outputs_round_4/AdaBoostClassifier/iris/mysql/demo1_AdaBoostClassifier_mysql.sql

the SQL code is based on a table INPUT_DATA that can be defined this way :

create table `INPUT_DATA` AS
(
select 1 as `KEY`, 0.0 as `Feature_0`, 1.0 as `Feature_1`, 2.0 as `Feature_2`, 3.0 as `Feature_3`
)

Your help is welcome.

Sorry for the long message. I tried to give a full bug report with the most details.

Thanks in advance

Antoine

CARME Antoine made changes - 2017-04-04 10:12

Description

Dear all,

I am currently working on a tool to translate machine learning models (built with scikit-learn python library) into a SQL code that can produce the model predictions when executed. The tool works by translating the model into a "sequence" of CTEs. It supports the main relational databases available on the market (sqlite, postgresql, MSSql, Oracle). Some demo is available here (https://github.com/antoinecarme/sklearn2sql-demo/) with sample SQL codes.

MySQL used to not support CTEs. I see that MariaDB is adding support for CTEs and I will be glad to use it and help with examples. This is very good news!!!

I am giving it a try. I use the debian sid (x64) 10.2.4 version and already read some the bug reports filed recently about CTEs.

I use a set of automatic tests that run on all supported databases. I will only report when MariaDB is the only failing case.

So far, al lot of models generate SQL that executes OK on MariaDB which is great!! However, I still have some issues with some complex models (>10 CTEs), I can provide a list.

For example, the AdaBoostClassifier models produce some SQL code that works on postgresql/oracle/MSSQL/SQlite but not on MariaDB. I get a message

"Table \'db.AdaBoost_AVG_Scores\' doesn\'t exist"

AdaBoost_AVG_Scores is a CTE defined in the query. It seems to be a lookup issue for CTEs. the SQL code is available here :
https://github.com/antoinecarme/sklearn2sql-demo/blob/master/sample_outputs_round_4/AdaBoostClassifier/iris/mysql/demo1_AdaBoostClassifier_mysql.sql

the SQL code is based on a table INPUT_DATA that can be defined this way :

create table `INPUT_DATA` AS
(
select 1 as `KEY`, 0.0 as `Feature_0`, 1.0 as `Feature_1`, 2.0 as `Feature_2`, 3.0 as `Feature_3`
)

Your help is welcome.

Sorry for the long message. I tried to give a full bug report with the most details.

Thanks in advance

Antoine

Dear all,

I am currently working on a tool to translate machine learning models (built with scikit-learn python library) into a SQL code that can produce the model predictions when executed. The tool works by translating the model into a "sequence" of CTEs. It supports the main relational databases available on the market (sqlite, postgresql, MSSql, Oracle). Some demo is available here (https://github.com/antoinecarme/sklearn2sql-demo/) with sample SQL codes.

MySQL used to not support CTEs. I see that MariaDB is adding support for CTEs and I will be glad to use it and help with examples. This is very good news!!!

I am giving it a try. I use the debian sid (x64) 10.2.4 version and already read some the bug reports filed recently about CTEs.

I use a set of automatic tests that run on all supported databases. I will only report when MariaDB is the only failing case.

So far, a lot of the tested models generate SQL that executes OK on MariaDB which is great!! However, I still have some issues with some complex models (>10 CTEs), I can provide a list.

For example, the AdaBoostClassifier models produce some SQL code that works on postgresql/oracle/MSSQL/SQlite but not on MariaDB. I get a message

"Table \'db.AdaBoost_AVG_Scores\' doesn\'t exist"

AdaBoost_AVG_Scores is a CTE defined in the query. It seems to be a lookup issue for CTEs. the SQL code is available here :
https://github.com/antoinecarme/sklearn2sql-demo/blob/master/sample_outputs_round_4/AdaBoostClassifier/iris/mysql/demo1_AdaBoostClassifier_mysql.sql

the SQL code is based on a table INPUT_DATA that can be defined this way :

create table `INPUT_DATA` AS
(
select 1 as `KEY`, 0.0 as `Feature_0`, 1.0 as `Feature_1`, 2.0 as `Feature_2`, 3.0 as `Feature_3`
)

Your help is welcome.

Sorry for the long message. I tried to give a full bug report with the most details.

Thanks in advance

Antoine

CARME Antoine made changes - 2017-04-04 11:48

Summary

CTE not found in a compelx SQL code

CTE not found in a complex SQL code

Vicențiu Ciorbaru made changes - 2017-04-05 16:59

Assignee

Igor Babaev [ igor ]

Igor Babaev (Inactive) made changes - 2017-04-06 16:43

Status

Open [ 1 ]

In Progress [ 3 ]

Igor Babaev (Inactive) added a comment - 2017-04-06 16:49 - edited

The following simple test case demonstrates this problem:

create table t1 (a int, b  varchar(32));

insert into t1 values

  (4,'aaaa' ), (7,'bb'), (1,'ccc'), (4,'dd');

with

cte1 as (select * from t1 where  b >= 'c'),

cte2 as (select * from cte1 where b < 'b'),

cte3 as (select * from cte2 where a > 1 union select * from cte2 where a > 1)

select * from ct3;

As in the reported query the same CTE in used twice in another CTE and the server returns:

MariaDB [test]> with

    -> cte1 as (select * from t1 where  b >= 'c'),

    -> cte2 as (select * from cte1 where b < 'b'),

    -> cte3 as (select * from cte2 where a > 1 union select * from cte2 where a > 1)

    -> select * from cte3;

ERROR 1146 (42S02): Table 'test.cte1' doesn't exist

Here's another query where the same CTE is used twice: In the main query and in a CTE.

ariaDB [test]> with

    -> cte1 as (select * from t1 where  b >= 'c'),

    -> cte2 as (select * from cte1 where a < 7),

    -> cte3 as (select * from cte2 where a > 1)

    -> select * from cte2, cte3 where cte2.a = cte3.a;

ERROR 1146 (42S02): Table 'test.cte1' doesn't exist

Igor Babaev (Inactive) added a comment - 2017-04-06 16:49 - edited The following simple test case demonstrates this problem: create table t1 (a int, b varchar(32)); insert into t1 values (4,'aaaa' ), (7,'bb'), (1,'ccc'), (4,'dd'); with cte1 as (select * from t1 where b >= 'c'), cte2 as (select * from cte1 where b < 'b'), cte3 as (select * from cte2 where a > 1 union select * from cte2 where a > 1) select * from ct3; As in the reported query the same CTE in used twice in another CTE and the server returns: MariaDB [test]> with -> cte1 as (select * from t1 where b >= 'c'), -> cte2 as (select * from cte1 where b < 'b'), -> cte3 as (select * from cte2 where a > 1 union select * from cte2 where a > 1) -> select * from cte3; ERROR 1146 (42S02): Table 'test.cte1' doesn't exist Here's another query where the same CTE is used twice: In the main query and in a CTE. ariaDB [test]> with -> cte1 as (select * from t1 where b >= 'c'), -> cte2 as (select * from cte1 where a < 7), -> cte3 as (select * from cte2 where a > 1) -> select * from cte2, cte3 where cte2.a = cte3.a; ERROR 1146 (42S02): Table 'test.cte1' doesn't exist

Igor Babaev (Inactive) added a comment - 2017-04-07 23:13

The fix for this bug was pushed into the 10.2 tree.

Igor Babaev (Inactive) added a comment - 2017-04-07 23:13 The fix for this bug was pushed into the 10.2 tree.

Igor Babaev (Inactive) made changes - 2017-04-07 23:13

Fix Version/s		10.2.6 [ 22527 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Igor Babaev (Inactive) added a comment - 2017-04-07 23:17

Hi Antoine,

I executed your query after having applied my fix. It returned some result set, but I could not verify the result, because I did know what to expect.

Igor Babaev (Inactive) added a comment - 2017-04-07 23:17 Hi Antoine, I executed your query after having applied my fix. It returned some result set, but I could not verify the result, because I did know what to expect.

CARME Antoine added a comment - 2017-04-07 23:18

YEEEES!

That's excellent. Thanks a lot Igor Babaev.

Can't wait or testing the 10.2.6 release !!!!

CARME Antoine added a comment - 2017-04-07 23:18 YEEEES! That's excellent. Thanks a lot Igor Babaev. Can't wait or testing the 10.2.6 release !!!!

CARME Antoine added a comment - 2017-04-07 23:21

Igor,

I can do some tests and give you my feedback (in particular, I compare all supported databases result sets, should be the same).

CARME Antoine added a comment - 2017-04-07 23:21 Igor, I can do some tests and give you my feedback (in particular, I compare all supported databases result sets, should be the same).

CARME Antoine added a comment - 2017-06-04 20:09 - edited

Igor,
Just a message to tell you that I made some tests with 10.2.6 release, and this is a full success. Now I get a test report like this

??
TEST_STATUS AdaBoostClassifier_('sqltm', 'sqlite://')_None success None
TEST_STATUS AdaBoostClassifier_('mssql', 'mssql+pymssql://sa:Micro123@SklearnTestDB/db?charset=utf8&timeout=180')_None success None
TEST_STATUS AdaBoostClassifier_('pgsql', 'postgresql://db:db@localhost/db?port=5432')_None success None
TEST_STATUS AdaBoostClassifier_('mysql', 'mysql://db:db@localhost/db')_None success None
TEST_STATUS AdaBoostClassifier_('oracle', 'oracle+cx_oracle://db:dba@xe')_None success None
??

instead of this

??
TEST_STATUS AdaBoostClassifier_('sqltm', 'sqlite://')_None success None
TEST_STATUS AdaBoostClassifier_('mssql', 'mssql+pymssql://sa:Micro123@SklearnTestDB/db?charset=utf8')_None success None
TEST_STATUS AdaBoostClassifier_('pgsql', 'postgresql://db:db@localhost/db?port=5432')_None success None
TEST_STATUS AdaBoostClassifier_('mysql', 'mysql://db:db@localhost/db')_None failure ProgrammingError_mysql_exceptions.ProgrammingError) (1146, "Table 'db.AdaBoost_AVG_Scores' doesn't exist") [SQL: "WITH `DT_node_lookup` AS \n(SELECT `ADS`.`index` AS `index`, CASE WHEN (`ADS`.`XD_10` <= -0.751862764
TEST_STATUS AdaBoostClassifier_('oracle', 'oracle+cx_oracle://db:db@xe')_None success None
??

Good job, guys. You made a happy MariaDB user.

Cheers
Antoine

CARME Antoine added a comment - 2017-06-04 20:09 - edited Igor, Just a message to tell you that I made some tests with 10.2.6 release, and this is a full success. Now I get a test report like this ?? TEST_STATUS AdaBoostClassifier_('sqltm', 'sqlite://')_None success None TEST_STATUS AdaBoostClassifier_('mssql', 'mssql+pymssql://sa:Micro123@SklearnTestDB/db?charset=utf8&timeout=180')_None success None TEST_STATUS AdaBoostClassifier_('pgsql', 'postgresql://db:db@localhost/db?port=5432')_None success None TEST_STATUS AdaBoostClassifier_('mysql', 'mysql://db:db@localhost/db')_None success None TEST_STATUS AdaBoostClassifier_('oracle', 'oracle+cx_oracle://db:dba@xe')_None success None ?? instead of this ?? TEST_STATUS AdaBoostClassifier_('sqltm', 'sqlite://')_None success None TEST_STATUS AdaBoostClassifier_('mssql', 'mssql+pymssql://sa:Micro123@SklearnTestDB/db?charset=utf8')_None success None TEST_STATUS AdaBoostClassifier_('pgsql', 'postgresql://db:db@localhost/db?port=5432')_None success None TEST_STATUS AdaBoostClassifier_('mysql', 'mysql://db:db@localhost/db')_None failure ProgrammingError _mysql_exceptions.ProgrammingError) (1146, "Table 'db.AdaBoost_AVG_Scores' doesn't exist") [SQL: "WITH `DT_node_lookup` AS \n(SELECT `ADS`.`index` AS `index`, CASE WHEN (`ADS`.`XD_10` <= -0.751862764 TEST_STATUS AdaBoostClassifier_('oracle', 'oracle+cx_oracle://db:db@xe')_None success None ?? Good job, guys. You made a happy MariaDB user. Cheers Antoine

Elena Stepanova made changes - 2017-07-23 16:22

Link

This issue duplicates ~~MDEV-13371~~ [ ~~MDEV-13371~~ ]

gregory jarrige made changes - 2017-10-28 19:36

Link

This issue is blocked by ~~MDEV-14184~~ [ ~~MDEV-14184~~ ]

Elena Stepanova made changes - 2017-10-31 18:20

Link

This issue relates to ~~MDEV-14184~~ [ ~~MDEV-14184~~ ]

Elena Stepanova made changes - 2017-10-31 18:20

Link

This issue is blocked by ~~MDEV-14184~~ [ ~~MDEV-14184~~ ]

Sergei Golubchik made changes - 2021-12-06 21:44

Workflow

MariaDB v3 [ 80235 ]

MariaDB v4 [ 151916 ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: CARME Antoine

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2017-04-04 09:07

Updated:: 2017-10-31 18:20

Resolved:: 2017-04-07 23:13

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration