MariaDB using ‘distinct’ keyword changes expected behavior of other column

Why does this work: SET @cnt = 0; SELECT (@cnt:=@cnt+1) AS ‘foo’, title FROM employee; But when I add ‘distinct’ keyword, it does not, while distinct works on its own (not shown). SET @cnt = 0; …

Recursive query in tree MySQL8 / MariaDB10

I have a tree-like structure for categories on my database: create table cat( id int auto_increment primary key, url varchar(50), parent int references cat(id) ) How can i build the …

SQL, operation between paired rows

I have a table t1 a b 1 4 2 2 3 9 2 1 1 6 3 7 I need the output a result 1 4*6 2 2*1 3 9*7 I have been able to do it in a far from efficient way and would like a more …

how to validate date null in mysql?

I’m working with MariaDB. when I try to show some result don’t show me nothing. select if(fecha is null,’hello’,’bye’) dat from table I’m trying to show me some result, but I don’t know how?…

SQL order by a function with literal values

I want to order some SQL result using the result of a calculation. I can select this SELECT sqrt( POWER(TERM_0 – -0.12985125184059143,2)) from faces f limit 3 So the syntax seems correct, but when I …

Mysql IF statement Error on Nested if sentence

I am trying to create a procedure to update the stopTime field on my works table. The query I am using is: update mydb.works set stopTime= (IF((SELECT table_name FROM information_schema.tables …

MariaDB SUM returns wrong value on column type float

version: 5.5.53-MariaDB I added this table to my MariaDB: CREATE TABLE `myTable` ( `key` int(10) unsigned NOT NULL, `val_float` float unsigned NOT NULL, `val_bigint` bigint(20) unsigned NOT …

Syntax error in SQL when inserting data via nodejs

I have a simple nodejs code in pipedream that sends the body email to mySQL Database. i have checked the connection to database and its working. Here is my code const mysql = require(‘mysql2/promise’);…

Major bug in MySQL 8.x? — foreign keys

While retrieving foreign keys information from MySQL (for a code generator) I noticed this strange behavior. It looks like a major bug in MySQL 8.x. When I create a foreign key using REFERENCES the …

mariadb/mysql Nested `WITH` statement causes table-not-found error

I am not sure if this is a documented limit in mariadb or a bug in its query parsing. We are porting some enormous queries from vertica to mariadb, and I have encountered cases where queries with …