[MDEV-35173] Query fails on macOS, while on ubuntu it is fine. - Jira

Details

Type: Bug
Status: Needs Feedback (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 11.6(EOL)
Fix Version/s: 11.6(EOL)
Component/s: None
Labels:
None

Description

What happend?

In ampersand we programatically generate ansi-sql. Recently, I added a feature to be able to generate transitive closures as well. The generation is done from an expression in a domain specific language based on set theory.
During the implementation I found out that a `with recursive` construct cannot be used as subquery everywhere where a `select` subquery can be used (See this issue for details. @Rex Jonston was kind enough to suggest an alternative, that perfectly fits in the way the generation is done.
The suggestion was to wrap the `with recursive` subquery, yealding `select * from <with-recursive-subquery>`.

So I did. The generation is fine, MariaDB vs 11.5 accepts the generated query in ubuntu, but on MacOS it rejects the exact same query. (we run our testcases both in ubuntu 22.04 as well as macos 13.)

The testcases are run using github actions. The log of the failing run can be found here

What did I expect?

I expected MariaDB to behave the same under macOS as under ubuntu.

Attachments

Activity

Ascending order - Click to sort in descending order

Rex Johnston added a comment - 2024-10-16 17:54

The error in the log linked to above appears to be

PHP Fatal error:  Uncaught mysqli_sql_exception: Table 'TempDB_Bug335_Kl0Kl1.r' doesn't exist in engine in /private/var/folders/4b/7k50gk0j4f5bjk3799wdt8nw0000gn/T/tmpPhpQueryOfAmpersand.php:54

Rex Johnston added a comment - 2024-10-16 17:54 The error in the log linked to above appears to be PHP Fatal error: Uncaught mysqli_sql_exception: Table 'TempDB_Bug335_Kl0Kl1.r' doesn't exist in engine in /private/var/folders/4b/7k50gk0j4f5bjk3799wdt8nw0000gn/T/tmpPhpQueryOfAmpersand.php:54

Han Joosten added a comment - 2024-10-16 20:19 - edited

True. The PHP that causes this error is below. However, this very same code is tested on ubuntu as well, and there it gives the correct result. I can assure that the table should exist, but clearly it doesn't on macos.

      PHP Fatal error:  Uncaught mysqli_sql_exception: Table 'TempDB_Bug335_Kl0Kl1.r' doesn't exist in engine in /private/var/folders/4b/7k50gk0j4f5bjk3799wdt8nw0000gn/T/tmpPhpQueryOfAmpersand.php:54

      Stack trace:

      #0 /private/var/folders/4b/7k50gk0j4f5bjk3799wdt8nw0000gn/T/tmpPhpQueryOfAmpersand.php(54): mysqli_query(Object(mysqli), '/* EDcD r[A*A] ...')

      #1 {main}

        thrown in /private/var/folders/4b/7k50gk0j4f5bjk3799wdt8nw0000gn/T/tmpPhpQueryOfAmpersand.php on line 54

  Error: 4-10-16 06:16:28.897369: [error] Could not execute PHP: readCreateProcess: php "/var/folders/4b/7k50gk0j4f5bjk3799wdt8nw0000gn/T/tmpPhpQueryOfAmpersand.php" > "/var/folders/4b/7k50gk0j4f5bjk3799wdt8nw0000gn/T/tmpPhpQueryOfAmpersand.phpResult" (exit 255): failed

  Error: 4-10-16 06:16:28.897644: [error] /*00000*/ <?php

  Error: 4-10-16 06:16:28.897702: [error] /*00001*/ // Try to connect to the MySQL server

  Error: 4-10-16 06:16:28.897747: [error] /*00002*/ global $DB_host,$DB_user,$DB_pass;

  Error: 4-10-16 06:16:28.897820: [error] /*00003*/ $DB_host='127.0.0.1';

  Error: 4-10-16 06:16:28.897866: [error] /*00004*/ $DB_user='root';

  Error: 4-10-16 06:16:28.897906: [error] /*00005*/ $DB_pass='';

  Error: 4-10-16 06:16:28.897937: [error] /*00006*/

  Error: 4-10-16 06:16:28.897998: [error] /*00007*/ $DB_name='TempDB_Bug335_Kl0Kl1';

  Error: 4-10-16 06:16:28.898065: [error] /*00008*/ // Connect to the database

  Error: 4-10-16 06:16:28.898134: [error] /*00009*/ $DB_link = mysqli_connect($DB_host,$DB_user,$DB_pass,$DB_name);

  Error: 4-10-16 06:16:28.898171: [error] /*00010*/ // Check connection

  Error: 4-10-16 06:16:28.898205: [error] /*00011*/ if (mysqli_connect_errno()) {

  Error: 4-10-16 06:16:28.898259: [error] /*00012*/   die('Error : Failed to connect to the database: ' . mysqli_connect_error());

  Error: 4-10-16 06:16:28.898323: [error] /*00013*/   }

  Error: 4-10-16 06:16:28.898375: [error] /*00014*/

  Error: 4-10-16 06:16:28.898442: [error] /*00015*/ $sql="SET SESSION sql_mode = 'ANSI,TRADITIONAL'";

  Error: 4-10-16 06:16:28.898480: [error] /*00016*/ if (!mysqli_query($DB_link,$sql)) {

  Error: 4-10-16 06:16:28.898517: [error] /*00017*/   die('Error setting sql_mode: ' . mysqli_error($DB_link));

  Error: 4-10-16 06:16:28.898583: [error] /*00018*/   }

  Error: 4-10-16 06:16:28.898641: [error] /*00019*/

  Error: 4-10-16 06:16:28.898704: [error] /*00020*/ $sql='/* EDcD r[A*A] */

  Error: 4-10-16 06:16:28.898761: [error] /*00021*/                           /*    Expression: r [A*A] */

  Error: 4-10-16 06:16:28.898823: [error] /*00022*/                           /*    Signature : [A*A] */

  Error: 4-10-16 06:16:28.898860: [error] /*00023*/                           select "SrcA" as src, "TgtA" as tgt

  Error: 4-10-16 06:16:28.898899: [error] /*00024*/                           from "r"

  Error: 4-10-16 06:16:28.898955: [error] /*00025*/                           where ("SrcA" is not null)

  Error: 4-10-16 06:16:28.899234: [error] /*00026*/                                 and ("TgtA" is not null)

  Error: 4-10-16 06:16:28.899302: [error] /*00027*/                           union distinct

  Error: 4-10-16 06:16:28.899338: [error] /*00028*/                           /* EKl1 (EDcD r[A*A]) */

  Error: 4-10-16 06:16:28.899370: [error] /*00029*/                           /*    Expression: r [A*A]+ */

  Error: 4-10-16 06:16:28.899401: [error] /*00030*/                           /*    Signature : [A*A] */

  Error: 4-10-16 06:16:28.899433: [error] /*00031*/                           /* case: EKl1 expr -- (Kleene plus) */

  Error: 4-10-16 06:16:28.899464: [error] /*00032*/                           /*    Expression: r [A*A]+ */

  Error: 4-10-16 06:16:28.899506: [error] /*00033*/                           /*    Signature : [A*A] */

  Error: 4-10-16 06:16:28.899536: [error] /*00034*/                           select distinct src as src, tgt as tgt

  Error: 4-10-16 06:16:28.899565: [error] /*00035*/                           from (with recursive TheExpression as (/* EDcD r[A*A] */

  Error: 4-10-16 06:16:28.899595: [error] /*00036*/                                                                  /*    Expression: r [A*A] */

  Error: 4-10-16 06:16:28.899635: [error] /*00037*/                                                                  /*    Signature : [A*A] */

  Error: 4-10-16 06:16:28.899667: [error] /*00038*/                                                                  select "SrcA" as src, "TgtA" as tgt

  Error: 4-10-16 06:16:28.899695: [error] /*00039*/                                                                  from "r"

  Error: 4-10-16 06:16:28.899726: [error] /*00040*/                                                                  where ("SrcA" is not null)

  Error: 4-10-16 06:16:28.899763: [error] /*00041*/                                                                        and ("TgtA" is not null)),

  Error: 4-10-16 06:16:28.899792: [error] /*00042*/                                      TransitiveClosure as (select src as src, tgt as tgt

  Error: 4-10-16 06:16:28.899820: [error] /*00043*/                                                            from "TheExpression"

  Error: 4-10-16 06:16:28.899850: [error] /*00044*/                                                            union distinct

  Error: 4-10-16 06:16:28.899888: [error] /*00045*/                                                            select "TransitiveClosure".src as src,

  Error: 4-10-16 06:16:28.899915: [error] /*00046*/                                                                   "TheExpression".tgt as tgt

  Error: 4-10-16 06:16:28.899945: [error] /*00047*/                                                            from "TransitiveClosure",

  Error: 4-10-16 06:16:28.899973: [error] /*00048*/                                                                 "TheExpression"

  Error: 4-10-16 06:16:28.900002: [error] /*00049*/                                                            where "TheExpression".src = "TransitiveClosure".tgt)

  Error: 4-10-16 06:16:28.900031: [error] /*00050*/                                 select src as src, tgt as tgt from "TransitiveClosure")

  Error: 4-10-16 06:16:28.900060: [error] /*00051*/                                as enforceAnsisql

  Error: 4-10-16 06:16:28.900090: [error] /*00052*/ ';

  Error: 4-10-16 06:16:28.900120: [error] /*00053*/ $result=mysqli_query($DB_link,$sql);

  Error: 4-10-16 06:16:28.900148: [error] /*00054*/ if(!$result) {

  Error: 4-10-16 06:16:28.900185: [error] /*00055*/   die('Error : Connect to server failed'.($ernr=mysqli_errno($DB_link)).': '.mysqli_error($DB_link).'(Sql: $sql)');

  Error: 4-10-16 06:16:28.900213: [error] /*00056*/ }

Han Joosten added a comment - 2024-10-16 20:19 - edited True. The PHP that causes this error is below. However, this very same code is tested on ubuntu as well, and there it gives the correct result. I can assure that the table should exist, but clearly it doesn't on macos. PHP Fatal error: Uncaught mysqli_sql_exception: Table 'TempDB_Bug335_Kl0Kl1.r' doesn't exist in engine in /private/var/folders/4b/7k50gk0j4f5bjk3799wdt8nw0000gn/T/tmpPhpQueryOfAmpersand.php:54 Stack trace: #0 /private/var/folders/4b/7k50gk0j4f5bjk3799wdt8nw0000gn/T/tmpPhpQueryOfAmpersand.php(54): mysqli_query(Object(mysqli), '/* EDcD r[A*A] ...') #1 {main} thrown in /private/var/folders/4b/7k50gk0j4f5bjk3799wdt8nw0000gn/T/tmpPhpQueryOfAmpersand.php on line 54 Error: 4-10-16 06:16:28.897369: [error] Could not execute PHP: readCreateProcess: php "/var/folders/4b/7k50gk0j4f5bjk3799wdt8nw0000gn/T/tmpPhpQueryOfAmpersand.php" > "/var/folders/4b/7k50gk0j4f5bjk3799wdt8nw0000gn/T/tmpPhpQueryOfAmpersand.phpResult" (exit 255): failed Error: 4-10-16 06:16:28.897644: [error] /*00000*/ <?php Error: 4-10-16 06:16:28.897702: [error] /*00001*/ // Try to connect to the MySQL server Error: 4-10-16 06:16:28.897747: [error] /*00002*/ global $DB_host,$DB_user,$DB_pass; Error: 4-10-16 06:16:28.897820: [error] /*00003*/ $DB_host='127.0.0.1'; Error: 4-10-16 06:16:28.897866: [error] /*00004*/ $DB_user='root'; Error: 4-10-16 06:16:28.897906: [error] /*00005*/ $DB_pass=''; Error: 4-10-16 06:16:28.897937: [error] /*00006*/ Error: 4-10-16 06:16:28.897998: [error] /*00007*/ $DB_name='TempDB_Bug335_Kl0Kl1'; Error: 4-10-16 06:16:28.898065: [error] /*00008*/ // Connect to the database Error: 4-10-16 06:16:28.898134: [error] /*00009*/ $DB_link = mysqli_connect($DB_host,$DB_user,$DB_pass,$DB_name); Error: 4-10-16 06:16:28.898171: [error] /*00010*/ // Check connection Error: 4-10-16 06:16:28.898205: [error] /*00011*/ if (mysqli_connect_errno()) { Error: 4-10-16 06:16:28.898259: [error] /*00012*/ die('Error : Failed to connect to the database: ' . mysqli_connect_error()); Error: 4-10-16 06:16:28.898323: [error] /*00013*/ } Error: 4-10-16 06:16:28.898375: [error] /*00014*/ Error: 4-10-16 06:16:28.898442: [error] /*00015*/ $sql="SET SESSION sql_mode = 'ANSI,TRADITIONAL'"; Error: 4-10-16 06:16:28.898480: [error] /*00016*/ if (!mysqli_query($DB_link,$sql)) { Error: 4-10-16 06:16:28.898517: [error] /*00017*/ die('Error setting sql_mode: ' . mysqli_error($DB_link)); Error: 4-10-16 06:16:28.898583: [error] /*00018*/ } Error: 4-10-16 06:16:28.898641: [error] /*00019*/ Error: 4-10-16 06:16:28.898704: [error] /*00020*/ $sql='/* EDcD r[A*A] */ Error: 4-10-16 06:16:28.898761: [error] /*00021*/ /* Expression: r [A*A] */ Error: 4-10-16 06:16:28.898823: [error] /*00022*/ /* Signature : [A*A] */ Error: 4-10-16 06:16:28.898860: [error] /*00023*/ select "SrcA" as src, "TgtA" as tgt Error: 4-10-16 06:16:28.898899: [error] /*00024*/ from "r" Error: 4-10-16 06:16:28.898955: [error] /*00025*/ where ("SrcA" is not null) Error: 4-10-16 06:16:28.899234: [error] /*00026*/ and ("TgtA" is not null) Error: 4-10-16 06:16:28.899302: [error] /*00027*/ union distinct Error: 4-10-16 06:16:28.899338: [error] /*00028*/ /* EKl1 (EDcD r[A*A]) */ Error: 4-10-16 06:16:28.899370: [error] /*00029*/ /* Expression: r [A*A]+ */ Error: 4-10-16 06:16:28.899401: [error] /*00030*/ /* Signature : [A*A] */ Error: 4-10-16 06:16:28.899433: [error] /*00031*/ /* case: EKl1 expr -- (Kleene plus) */ Error: 4-10-16 06:16:28.899464: [error] /*00032*/ /* Expression: r [A*A]+ */ Error: 4-10-16 06:16:28.899506: [error] /*00033*/ /* Signature : [A*A] */ Error: 4-10-16 06:16:28.899536: [error] /*00034*/ select distinct src as src, tgt as tgt Error: 4-10-16 06:16:28.899565: [error] /*00035*/ from (with recursive TheExpression as (/* EDcD r[A*A] */ Error: 4-10-16 06:16:28.899595: [error] /*00036*/ /* Expression: r [A*A] */ Error: 4-10-16 06:16:28.899635: [error] /*00037*/ /* Signature : [A*A] */ Error: 4-10-16 06:16:28.899667: [error] /*00038*/ select "SrcA" as src, "TgtA" as tgt Error: 4-10-16 06:16:28.899695: [error] /*00039*/ from "r" Error: 4-10-16 06:16:28.899726: [error] /*00040*/ where ("SrcA" is not null) Error: 4-10-16 06:16:28.899763: [error] /*00041*/ and ("TgtA" is not null)), Error: 4-10-16 06:16:28.899792: [error] /*00042*/ TransitiveClosure as (select src as src, tgt as tgt Error: 4-10-16 06:16:28.899820: [error] /*00043*/ from "TheExpression" Error: 4-10-16 06:16:28.899850: [error] /*00044*/ union distinct Error: 4-10-16 06:16:28.899888: [error] /*00045*/ select "TransitiveClosure".src as src, Error: 4-10-16 06:16:28.899915: [error] /*00046*/ "TheExpression".tgt as tgt Error: 4-10-16 06:16:28.899945: [error] /*00047*/ from "TransitiveClosure", Error: 4-10-16 06:16:28.899973: [error] /*00048*/ "TheExpression" Error: 4-10-16 06:16:28.900002: [error] /*00049*/ where "TheExpression".src = "TransitiveClosure".tgt) Error: 4-10-16 06:16:28.900031: [error] /*00050*/ select src as src, tgt as tgt from "TransitiveClosure") Error: 4-10-16 06:16:28.900060: [error] /*00051*/ as enforceAnsisql Error: 4-10-16 06:16:28.900090: [error] /*00052*/ '; Error: 4-10-16 06:16:28.900120: [error] /*00053*/ $result=mysqli_query($DB_link,$sql); Error: 4-10-16 06:16:28.900148: [error] /*00054*/ if(!$result) { Error: 4-10-16 06:16:28.900185: [error] /*00055*/ die('Error : Connect to server failed'.($ernr=mysqli_errno($DB_link)).': '.mysqli_error($DB_link).'(Sql: $sql)'); Error: 4-10-16 06:16:28.900213: [error] /*00056*/ }

Han Joosten added a comment - 2024-10-16 20:42

The specific testcase is shown as Start: ampersand validate --verbose Bug335_Kl0Kl1.adl, and this log of the run in ubuntu shows it succeeded.

Han Joosten added a comment - 2024-10-16 20:42 The specific testcase is shown as Start: ampersand validate --verbose Bug335_Kl0Kl1.adl , and this log of the run in ubuntu shows it succeeded.

Dave Gosselin added a comment - 2025-02-20 13:01

Hi hanjoosten, thank you for filing this ticket. I'm trying to reproduce your environment and query on macOS so that I can attempt to fix the problem there. Unfortunately, the links to the log runs both in the 'Description' field and you recent comment from October are expired and the logs have been deleted. Are you able to provide a sample query that fails on macOS and succeeds elsewhere? If yes, please include table definitions on which the query depends. Thank you!

Dave Gosselin added a comment - 2025-02-20 13:01 Hi hanjoosten , thank you for filing this ticket. I'm trying to reproduce your environment and query on macOS so that I can attempt to fix the problem there. Unfortunately, the links to the log runs both in the 'Description' field and you recent comment from October are expired and the logs have been deleted. Are you able to provide a sample query that fails on macOS and succeeds elsewhere? If yes, please include table definitions on which the query depends. Thank you!

Han Joosten added a comment - 2025-02-20 18:44

Hi @Gosselin, I don't use macOS myself, so I hope I can provide you with the things you need. The logs are logs on github, where we run our code on windows, linux as well as macOS. Only the macOS variant fails, so it is definetly something to do with macOS.

Since the logs have expired, I created a new commit that is the same as the one the logs were from. That triggers github to create a log. [The error shows in the log here](https://github.com/AmpersandTarski/Ampersand/actions/runs/13440080374/job/37552027175#step:5:12175). Please be aware that this log will expire in a couple of months as well.

The query that is shown is generated programatically. Therefor, it might look wierd in some respect. I expect that there is something wrong with the implementation of the `with recursive` part.
If you aren't able to see what is going wrong this way, let me know and I will try to provide a reproducer. That will take some longer, for I don't have a lot of time the comming week. Let me know

Han Joosten added a comment - 2025-02-20 18:44 Hi @Gosselin, I don't use macOS myself, so I hope I can provide you with the things you need. The logs are logs on github, where we run our code on windows, linux as well as macOS. Only the macOS variant fails, so it is definetly something to do with macOS. Since the logs have expired, I created a new commit that is the same as the one the logs were from. That triggers github to create a log. [The error shows in the log here] ( https://github.com/AmpersandTarski/Ampersand/actions/runs/13440080374/job/37552027175#step:5:12175 ). Please be aware that this log will expire in a couple of months as well. The query that is shown is generated programatically. Therefor, it might look wierd in some respect. I expect that there is something wrong with the implementation of the `with recursive` part. If you aren't able to see what is going wrong this way, let me know and I will try to provide a reproducer. That will take some longer, for I don't have a lot of time the comming week. Let me know

Han Joosten added a comment - 6 days ago

This should be a reproducer:

~~~.sql
create table r (
src int primary key,
tgt int
);

insert into r (src, tgt) values
(2, 1),(3, 1),(4, 2),(5, 2),(6, 3),(7, 3),(8, 4),(9, 5);

(select src, tgt
from r
)
union
(with recursive
TransitiveClosure as (select src, tgt
from r
union
(select
TransitiveClosure.src as src,
r.tgt as tgt
from TransitiveClosure,
r
where r.src = TransitiveClosure.tgt)
)
select src, tgt from TransitiveClosure
);
~~~

Han Joosten added a comment - 6 days ago This should be a reproducer: ~~~.sql create table r ( src int primary key, tgt int ); insert into r (src, tgt) values (2, 1),(3, 1),(4, 2),(5, 2),(6, 3),(7, 3),(8, 4),(9, 5); (select src, tgt from r ) union (with recursive TransitiveClosure as (select src, tgt from r union (select TransitiveClosure.src as src, r.tgt as tgt from TransitiveClosure, r where r.src = TransitiveClosure.tgt) ) select src, tgt from TransitiveClosure ); ~~~

People

Assignee:: Dave Gosselin

Reporter:: Han Joosten

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2024-10-16 09:40

Updated:: 1 week ago 14:07

Git Integration

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

MariaDB Server