MariaDB subqueries to same table and column resulting in several columns

I have a table and want to pick monthly minute data to compare column wize in 10.3.13-MariaDB

Tested and tested for hours and hours different approaches without success, one example is below. Some tests does not complain syntactically but takes forever, and some tests complains about column names not recognized. All subqueries if tested separately return the same number of records, each in one column.

`SELECT RD, OT1, OT2, OT3 FROM

(SELECT rdate from OO where month(rdate) = 7 and year(rdate) = 2006) AS RD,

(SELECT ot from OO where month(rdate)=7 and year(rdate) =2006) AS OT1,

(SELECT ot from OO where month(rdate)=7 and year(rdate) =2007) AS OT2,

(SELECT ot from OO where month(rdate)=7 and year(rdate) =2008) AS OT3;`

The result should be something like:

`RD OT1 OT2 OT3

2006-07-01 00:00:00 1.2345 2.1234 1.543

… … … …

2006-07-31 23:59:00 3.456 3.234 2.234`

And, no I dont want to use UNION because then they will still follow one after the other…

Any thoughts?!

Why does adding an index increase the execution time in SQLite?

I’ll just show you an example. Here candidates is a table of 1000000 candidates from 1000 teams and their individual scores. We want a list of all teams and whether the total score of all candidates within each team is within the top 50. (Yeah this is similar to the example from another question, which I encourage you to look at, but I assure you that it is not a duplicate)

Note that all CREATE TABLE results AS ... statements are identical, and the only difference is the presence of indices. These tables are created (and dropped) to suppress the query results so that they won’t make a lot of noise in the output.

------------ -- Set up -- ------------  .open delete-me.db    -- A persistent database file is required  .print '' .print '[Set up]'  DROP TABLE IF EXISTS candidates;  CREATE TABLE candidates AS WITH RECURSIVE candidates(team, score) AS (     SELECT ABS(RANDOM()) % 1000, 1     UNION     SELECT ABS(RANDOM()) % 1000, score + 1     FROM candidates     LIMIT 1000000 ) SELECT team, score FROM candidates;   ------------------- -- Without Index -- -------------------  .print '' .print '[Without Index]'  DROP TABLE IF EXISTS results;  ANALYZE;  .timer ON .eqp   ON CREATE TABLE results AS WITH top_teams_verbose(top_team, total_score) AS (     SELECT team, SUM(score)     FROM candidates     GROUP BY team     ORDER BY 2 DESC     LIMIT 50 ), top_teams AS (     SELECT top_team     FROM top_teams_verbose ) SELECT team, SUM(team IN top_teams) FROM candidates GROUP BY team; .eqp   OFF .timer OFF   ------------------------------ -- With Single-column Index -- ------------------------------  .print '' .print '[With Single-column Index]'  CREATE INDEX candidates_idx_1 ON candidates(team);  DROP TABLE IF EXISTS results;  ANALYZE;  .timer ON .eqp   ON CREATE TABLE results AS WITH top_teams_verbose(top_team, total_score) AS (     SELECT team, SUM(score)     FROM candidates     GROUP BY team     ORDER BY 2 DESC     LIMIT 50 ), top_teams AS (     SELECT top_team     FROM top_teams_verbose ) SELECT team, SUM(team IN top_teams) FROM candidates GROUP BY team; .eqp   OFF .timer OFF   ----------------------------- -- With Multi-column Index -- -----------------------------  .print '' .print '[With Multi-column Index]'  CREATE INDEX candidates_idx_2 ON candidates(team, score);  DROP TABLE IF EXISTS results;  ANALYZE;  .timer ON .eqp   ON CREATE TABLE results AS WITH top_teams_verbose(top_team, total_score) AS (     SELECT team, SUM(score)     FROM candidates     GROUP BY team     ORDER BY 2 DESC     LIMIT 50 ), top_teams AS (     SELECT top_team     FROM top_teams_verbose ) SELECT team, SUM(team IN top_teams) FROM candidates GROUP BY team; .eqp   OFF .timer OFF 

Here is the output

[Set up]  [Without Index] QUERY PLAN |--SCAN TABLE candidates |--USE TEMP B-TREE FOR GROUP BY `--LIST SUBQUERY 3    |--CO-ROUTINE 1    |  |--SCAN TABLE candidates    |  |--USE TEMP B-TREE FOR GROUP BY    |  `--USE TEMP B-TREE FOR ORDER BY    `--SCAN SUBQUERY 1 Run Time: real 0.958 user 0.923953 sys 0.030911  [With Single-column Index] QUERY PLAN |--SCAN TABLE candidates USING COVERING INDEX candidates_idx_1 `--LIST SUBQUERY 3    |--CO-ROUTINE 1    |  |--SCAN TABLE candidates USING INDEX candidates_idx_1    |  `--USE TEMP B-TREE FOR ORDER BY    `--SCAN SUBQUERY 1 Run Time: real 2.487 user 1.108399 sys 1.375656  [With Multi-column Index] QUERY PLAN |--SCAN TABLE candidates USING COVERING INDEX candidates_idx_1 `--LIST SUBQUERY 3    |--CO-ROUTINE 1    |  |--SCAN TABLE candidates USING COVERING INDEX candidates_idx_2    |  `--USE TEMP B-TREE FOR ORDER BY    `--SCAN SUBQUERY 1 Run Time: real 0.270 user 0.248629 sys 0.014341 

While the covering index candidates_idx_2 does help, it seems that the single-column index candidates_idx_1 makes the query significantly slower, even after I ran ANALYZE;. It’s only 2.5x slower in this case, but I think the factor can be made greater if you fine-tune the number of candidates and teams.

Why is it?

How can a ‘Request’ store a ‘Result’ code and its associated ‘Error’ code and still enforce the relationship between Result and Error?

I’m an application developer creating the database structure to represent a flat file message format. I’d like to ask the collective knowledge the best/correct way to represent the following scenario:

Request table (PK: RequestID) contains requests; a request has a Result property which is indeterminate (null) until the request has completed.

Result table (PK ResultID) is a lookup table containing (currently) two possible results:

  • Success (Result ID = 0)
  • Failure (Result ID = 1)

ErrorCode table (PK ErrorCodeID) is a lookup table containing error details and their parent ResultID:

  • No Error (ErrorCodeID = 0, ResultID = 0)
  • Generic Error (ErrorCodeID = 1, ResultID = 1)
  • Queue Full (ErrorCodeID = 2, ResultID = 1)
  • Unsupported Interface (ErrorCodeID = 3, ResultID = 1)
  • etc…

I’ve created a one to many relationship between Result (one) and ErrorCode (many). A ‘Success’ Result can only have a ‘No Error’ Error Code, while a ‘Failure’ Result can have a single error code of ‘Generic Error’, ‘Queue Full’, ‘Unsupported Interface’, etc.

When the Request has completed, I need to store the result and its associated error code.

I’ve thought of combining the two tables but that strikes me as repeating columns.

I’ve also thought of having the Request table store the ResultID and the ErrorCodeID but this doesn’t enforce the Result to ErrorCode relationship.

I’m a big believer in database that ‘defends itself’ from bad data so I want the relationship to reject a Result/ErrorCode combination that is invalid; a.k.a. a Result of ‘Success’ and an ErrorCode of ‘Generic Error’ or a Result of ‘Failure’ and an ErrorCode of ‘No Error’.

I’m also a big believer in solid initial design so when changes come down the pike at a later date (as they always do) the structure will not need rework.

Thank you in advance for your time.

Regards, John E.

Why is ALTER faster than UPDATE?

Let’s create a garbage table..

CREATE TABLE foo   AS SELECT x::text FROM generate_series(1,20e6) AS t(x);  Time: 14077.654 ms (00:14.078) 

Now, let’s look at boring UPDATE,

UPDATE foo SET x = 'foo'; Time: 22239.050 ms (00:22.239) 

Compared with ALTER,

ALTER TABLE foo   ALTER COLUMN x   SET DATA TYPE text   USING 'foo'; Time: 9480.027 ms (00:09.480) 

It’s approximately twice as fast.

How can I dump the schema of a SQL server database on Linux?

On MySQL:

mysqldump --no-data somedatabasename dump_schema_mysql.out 

On Postgres:

pg_dump -s somedatabasename > dump_schema_postgres.out 

I can’t seem to find an equivalent for SQL server on Linux, though surely such a common task must be easily done?

I have the sqlcmd utility running ok:

ubuntu@ubuntu:~$   sqlcmd -? Microsoft (R) SQL Server Command Line Tool Version 17.6.0001.1 Linux Copyright (C) 2017 Microsoft Corporation. All rights reserved.  usage: sqlcmd            [-U login id]          [-P password]   [-S server or Dsn if -D is provided]   [-H hostname]          [-E trusted connection]   [-N Encrypt Connection][-C Trust Server Certificate]   [-d use database name] [-l login timeout]     [-t query timeout]   [-h headers]           [-s colseparator]      [-w screen width]   [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]   [-c cmdend]   [-q "cmdline query"]   [-Q "cmdline query" and exit]   [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]   [-u unicode output]    [-r[0|1] msgs to stderr]   [-i inputfile]         [-o outputfile]   [-k[1|2] remove[replace] control characters]   [-y variable length type display width]   [-Y fixed length type display width]   [-p[1] print statistics[colon format]]   [-R use client regional setting]   [-K application intent]   [-M multisubnet failover]   [-b On error batch abort]   [-D Dsn flag, indicate -S is Dsn]   [-X[1] disable commands, startup script, environment variables [and exit]]   [-x disable variable substitution]   [-g enable column encryption]   [-G use Azure Active Directory for authentication]   [-? show syntax summary] ubuntu@ubuntu:~$   sqlcmd -? 

Is there a better way of displaying ‘Count’ of records beside other columns in a select query?

I have a table with below structure :

Test1(c_num  number ,c_first_name varchar2(50), c_last_name varchar2(50)) 

1)There is a normal index on c_num column.

2)The table has nearly 5 million records.

I have a procedure as you can see below. I want to display the Count(*) along with other columns.I want to know if there are better ways of doing this so that we can have better performance.

create or replace procedure get_members(o_result out sys_refcursor)    is begin  open o_result for   select c_num,          c_first_name,          c_last_name,          (select count(*) from test1) members_cnt -->Is there a better way instead of doing this?   from test1;  end; 

Thanks in advance

Calling a function that creates records and referencing the created records in a join

I have a function that creates records for different tables that are referenced with each other. The function returns the record from the "parent" table. So I am using that function in the FROM of my query so that I can then perform JOIN on the associated records so that I can piece the tables together. My problem is that the associated records aren’t getting returned and I know for certain that they are getting created.

From what I understand, FROM and JOIN are pretty much executed at the same time so it makes sense that the FROM‘s creation of the records that are used in the JOIN would not exist.

So how can I ensure that the call to the function is executed first before the JOIN is executed? I tried doing this with CTE but no avail.

SELECT   i.invoice_id,   i.invoice_date,   i.invoice_due_date,   i.created_by_id,   i.currency_id,   i.created_at,   si.supplier_info,   bi.billing_info FROM invoices.create_shift_invoice(   CAST(NULLIF($  invoice_date, NULL) AS TIMESTAMP),   CAST(NULLIF($  invoice_due_date, NULL) AS TIMESTAMP),   CAST(NULLIF($  currency_id, '') AS INT8),   CAST(NULLIF($  created_by_id, '') AS INT8),   CAST(NULLIF($  supplier_info, NULL) AS JSONB),   CAST(NULLIF($  billing_info, NULL) AS JSONB),   CAST(NULLIF($  invoice_items_shifts, NULL) AS JSONB) ) i JOIN (   SELECT     _si.invoice_id,     json_build_object(       'supplier_info_id', CAST(_si.supplier_info_id AS VARCHAR),       'invoice_id', CAST(_si.invoice_id AS VARCHAR),       'suppler_name', _si.supplier_name     ) AS supplier_info   FROM invoices.supplier_infos _si ) si USING (invoice_id) JOIN (   SELECT     _bi.invoice_id,     json_build_object(       'billing_info_id', CAST(_bi.billing_info_id AS VARCHAR),       'invoice_id', CAST(_bi.invoice_id AS VARCHAR),       'customer_id', CAST(_bi.customer_id AS VARCHAR)     ) AS billing_info   FROM invoices.billing_infos _bi ) bi USING (invoice_id) 

And here’s my attempt at a CTE:

WITH create_shift_invoice AS (   SELECT *   FROM invoices.create_shift_invoice(     CAST(NULLIF($  invoice_date, NULL) AS TIMESTAMP),     CAST(NULLIF($  invoice_due_date, NULL) AS TIMESTAMP),     CAST(NULLIF($  currency_id, '') AS INT8),     CAST(NULLIF($  created_by_id, '') AS INT8),     CAST(NULLIF($  supplier_info, NULL) AS JSONB),     CAST(NULLIF($  billing_info, NULL) AS JSONB),     CAST(NULLIF($  invoice_items_shifts, NULL) AS JSONB)   ) )  SELECT   i.invoice_id,   i.invoice_date,   i.invoice_due_date,   i.created_by_id,   i.currency_id,   i.created_at,   si.supplier_info,   bi.billing_info FROM create_shift_invoice i LEFT JOIN (   SELECT     _si.invoice_id,     json_build_object(       'supplier_info_id', CAST(_si.supplier_info_id AS VARCHAR),       'invoice_id', CAST(_si.invoice_id AS VARCHAR),       'suppler_name', _si.supplier_name     ) AS supplier_info   FROM invoices.supplier_infos _si ) si USING (invoice_id) LEFT JOIN (   SELECT     _bi.invoice_id,     json_build_object(       'billing_info_id', CAST(_bi.billing_info_id AS VARCHAR),       'invoice_id', CAST(_bi.invoice_id AS VARCHAR),       'customer_id', CAST(_bi.customer_id AS VARCHAR)     ) AS billing_info   FROM invoices.billing_infos _bi ) bi USING (invoice_id) 

UPDATE with variables to renumber column gives syntax error

Searching for a way to renumber a column within mysql, I’ve found multiple articles showing the same approach:

  • Renumbering an Ordering Field in MySQL
  • How to update a MySql column with ascending numbers

among others.

But trying it on my table I get a syntax error.

mysql> SET @rankStart = 10; mysql> SET @rankInc = 10; mysql> UPDATE fileFileTbl SET rank = (@rankStart := @rankStart + @rankInc) ORDER BY `rank` ASC; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank = (@rankStart := @rankStart + @rankInc) ORDER BY `rank` ASC' at line 1 

The command looks identical to the articles posted. What am I missing?

Here’s the table (with some columns omitted):

CREATE TABLE `fileFileTbl` (   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,   `fileEngineId` int(11) DEFAULT NULL,   `rank` int(11) DEFAULT '0',   `fileName` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'newfile',   PRIMARY KEY (`id`),   UNIQUE KEY `fileEngineId` (`fileEngineId`,`rank`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8; 

System is Centos 8, with mysql as:

# mysql --version mysql  Ver 8.0.17 for Linux on x86_64 (Source distribution)