A new fiddle for dba.se

Question

Update Jan 2020:

Please note, the place for reporting db<>fiddle bugs and feature requests is now on this TopAnswers post pending a future move to a GitHub issue tracker!.

---

SQLFiddle has been an essential tool and a great complement to dba.se for a very long time, but it has gone downhill a bit in the last year or two.

I have also wondered for some time what a 'fiddle' designed specifically with dba.se and markdown in mind might look like, and over the last few days I've put together a prototype of how I think it should be.

Please have a play with the site and let me know here if you have suggestions for improvements or design changes.

The current list of supported databases is:

• Db2 Developer-C 11.1 (with lots of thanks to @mustaccio)
• Firebird 3.0
• MariaDB 10.2, 10.3 and 10.4
• MySQL 5.6, 5.7 and 8.0
• Oracle 11.2 and 18
• Postgres 8.4, 9.4, 9.5, 9.6, 10, 11 and 12
• SQL Server 2012, 2014, 2016, 2017, 2017 Linux and 2019 Linux RC1
• SQLite 3.8, 3.16 and 3.27 (but 3.16 is currently offline for security reasons)

Here's an example (the image links to the fiddle):

• 

I've edited some of my answers (e.g. this one) to use db<>fiddle.

As of Apr 2018 the site uses ssl by default — there are some benefits even for a site like db<>fiddle and it's free now so why not?

As of Apr 2019 you can now hide batches selectively.

Answer 1

status-bydesign

I got a problem with MySQL when I wanted to execute more statements in one field.

Please have a look at this working example (Postgres): https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=a0fc52d4e45e4d925b428fabcdee70f5

Now I changed the DBMS into MySQL and tried the same: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a0fc52d4e45e4d925b428fabcdee70f5

That wasn't working. But if I split the statement into one field each, it is working: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b6166ff71140b5957794f1f6c9fb9780

Why does the multi execution work with Postgres but not with MySQL?

Answer 2

status-completed

First, thank you for the site, it's a very useful tool.

Recently MySQL 8.0 introduced JSON_TABLE, however, it's currently not possible to use it in dbfiddle.uk.

See dbfiddle.

Answer 3

status-completed

There are problems in the display of space / empty string / NULL value:

 VALUES
  (1, 'foo')
, (2, '')
, (3, NULL)
, (4, '   ')
, (5, '|   |');

/*
column1 | column2
------: | :------
      1 | foo    
      2 |        
      3 |        
      4 |        
      5 | |   |  
*/

VALUES
  ('foo')
, ('')
, (NULL)
, ('   ')
, ('|   |');

/*
| column1 |
| :------ |
| foo     |
|         |
|         |
|         |
| |   |   |
*/

dbfiddle here

In the online display:

• Note the collapsed row 2 for the empty string in the second SQL field - in the fiddle, not here.

In the markup:

• Some way to distinguish rows 2, 3 and 4 would be useful.

---

I sharpened the demo to include single quotes and <tag>):

VALUES
  ('<tag>')
, (NULL)
, ('')      -- empty string
, ('''''')  -- actual single quotes
, ('   ');  -- spaces

/*
| column1 |
| :------ |
| <tag>   |
|         |
|         |
| ''      |
|         |
*/

dbfiddle here

Maybe rendering with a <pre> block would be an improvement? Would also take care of the comments you mentioned.

| column1 |
| :------ |
| <tag>   |
| null    |
| ''      |
| ''      |
|         |

You'd have to escape <. I used < above ...

Included your italic null, added italic single quotes for empty string. May be hard to distinguish from literal single quotes. But still an improvement, because the distinction between empty string and space is more important because it's a more common problem.

All spaces remain to be solved ... Some corner cases are hard to solve. Most clients have a hard time to get it right.

The only change I am convinced is good is the italic null. The rest is just ideas ...

See this related meta-post:

• Syntax highlighting: <!-​- language: lang-none --> does not seem to work for block quote

Answer 4

status-completed

I have to say it once again: db<>fiddle is simply the best.

---

I have a small request. Could you add traceflag 460 to startup parameters after you patch SQL Server with the newest cumulative update?

Current CU of SQL Server 2017 is CU9.

db<>fiddle demo

SELECT SERVERPROPERTY('ProductUpdateLevel'),@@version;
-- CU9

I would like to be able to use:

Improvement: Optional replacement for "String or binary data would be truncated" message with extended information in SQL Server 2017

Which was previously intended to be SQL Server 2019 feature Truncation error message improved to include table and column names, and truncated value (CTP 2.1)

Answer 5

status-completed

On SO in the PostgreSQL section there are often PostGIS questions. At the moment I don't know any fiddle engine which supports this framework. So as a suggestion: Wouldn't it be great to install the PostGIS frame along with your PostgreSQL instances?

Answer 6

bugstatus-completed

Bug for MySQL-8.0

SELECT
  json_tb.item_id,
  json_tb.model_number,
  json_tb.quantity
FROM JSON_TABLE
(
'[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
, "$[*]"
COLUMNS
(
item_id int PATH "$.item_id",
model_number varchar(100) PATH "$.model_number",
quantity int PATH "$.quantity"
)
) json_tb

Returns:

SELECT command denied to user 'fiddle_SMYHCBMIQRDQGWHKBNDS'@'localhost' for table 'json_table'

json_table is a function more than a table (ref: JSON_TABLE function manual)

Answer 7

bug

Attempting to run the following two SQL Server batches on any available db<>fiddle version:

SET LANGUAGE Italiano;
SELECT CONVERT(datetime, '2019.06.30', 0);

SET LANGUAGE Italiano;
SET DATEFORMAT YMD;
SELECT CONVERT(datetime, '2019.06.30', 0);

Produces an error:

The code runs 'successfully' in SSMS producing the expected message and error for the first statement:

L'impostazione della lingua è stata sostituita con Italiano.
Msg 242, Level 16, State 3, Line 2
La conversione di un tipo di dati varchar in datetime ha generato un valore non compreso nell'intervallo dei valori consentiti.

The second statement does not produce an error, but does produce the informational message:

L'impostazione della lingua è stata sostituita con Italiano.

Answer 8

bugstatus-completed

Would it be possible to show multiple outputs in one SQL field? At the moment if you have two SELECT statements only one will be shown in the results. https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=c17a17e3a448a08adc851b21d0bea5d2

For my Postgres answers I like to do these steps: CREATE TABLE, INSERT data and SELECT * FROM new_table to show how the origin table and its content look like. For multiple tables I have to use two section only for the SELECT part.

It's nothing critical, I know. But it would be nice candy I think!

Answer 9

bugstatus-completed

It seems there is a bug with DB-fiddle regarding detecting or reporting of Firebird errors.

See https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=57729b31e0a5019aea68a136638d9f50

See https://stackoverflow.com/questions/58096187

Last query should generate an internal Firebird error (as Numeric is capped with 18 digits in FB 2/3 and 18-4*4=2 is not enough to contain the calculation result of 1875).

DB fiddle displays neither error nor query result.

Answer 10

Few UI notes.

1. MS IE 11 rendering is garbled (who would have thought :-D ) - the first vertical splitter is too high, and the "run" button is too narrow.

If anything, "MSIE6 compatibility mode" is not fixing it but makes a total mess :-)

2. Feature Request, can you make the vertical splitter (when it is vertical) to be user-draggable left and right? Perhaps it is to be a fiddle-global (page-global) thing, affecting all the queries at once - less flexible, but more eye-candish and simple to control. Perhaps something like this: http://cross-browser.com/x/examples/xsplitter.php

Answer 11

bugstatus-completed

When working with Firebird, you cannot create a VIEW.

Error:

unsuccessful metadata update CREATE VIEW VW_TEST failed There is no privilege for this operation

DB Fiddle

Answer 12

Running into a display bug with SQL Server (any version) related to the precision displayed for DATETIME2 values.

This dbfiddle should be pretty much self-explanatory, but regardless of the precision defined, the output only shows the value up to the second.

Answer 13

feature-request status-deferred

Now tracked here

---

The 19.2 release makes it look like integrating it might be relatively easy, would you be interested in adding CockroachDB as a supported platform?

The cockroach demo command ostensibly provides an ephemeral in-memory enterprise instance; and having seen it in action it appears to do what it says on the tin :)

Answer 14

SQLFormat has an API (and code) to clean up syntax to make it readable.

Answer 15

I am missing the possibility to copy the table results for the SO editor. If I simply copy the table I end up with things like that:

user_id     check_date  day     time_in     time_out    hours   status
53  2018-07-16 00:00:00+01  Monday      08:15:17    09:38:03    25.3794444444444    present
53  2018-07-17 00:00:00+01  Tuesday                 absent
53  2018-07-18 00:00:00+01  Wednesday   06:34:13    08:05:17    1.51777777777778    present

The columns and values are not really aligned together. So I always have to append a manual formatting step (e.g. converting tabs into spaces):

user_id   check_date               day         time_in    time_out   hours              status
53        2018-07-16 00:00:00+01   Monday      08:15:17   09:38:03   25.3794444444444   present
53        2018-07-17 00:00:00+01   Tuesday                                              absent
53        2018-07-18 00:00:00+01   Wednesday   06:34:13   08:05:17   1.51777777777778   present

In SQLFiddle you can chose the output between (table, plaintext, markdown). Even plaintext looks much better and can be taken for copying into SO without any addional work:

| user_id |           check_date |       day |  time_in | time_out |                hours |  status |
|---------|----------------------|-----------|----------|----------|----------------------|---------|
|      53 | 2018-07-16T00:00:00Z | Monday    | 08:15:17 | 09:38:03 |   25.379444444444445 | present |
|      53 | 2018-07-17T00:00:00Z | Tuesday   |   (null) |   (null) |               (null) |  absent |
|      53 | 2018-07-18T00:00:00Z | Wednesday | 06:34:13 | 08:05:17 |   1.5177777777777777 | present |

Edit: dbfiddle markdown syntax

> user_id | check_date             | day       | time_in  | time_out | hours               | status 
> ------: | :--------------------- | :-------- | :------- | :------- | :------------------ | :------
>      53 | 2018-07-16 00:00:00+01 | Monday    | 08:15:17 | 09:38:03 | 25.3794444444444    | present
>      53 | 2018-07-17 00:00:00+01 | Tuesday   | <em>null</em>     | <em>null</em>     | <em>null</em>                | absent 
>      53 | 2018-07-18 00:00:00+01 | Wednesday | 06:34:13 | 08:05:17 | 1.51777777777778    | present

As you can see, the second row is not really aligned. Furthermore you can find the <em> tags and things like that.

---

Added by Jack:

select 1, null;

?column? | ?column?
-------: | :-------
       1 | null    

Answer 16

feature-requeststatus-declined

Could you make the default number of lines to something like 10 instead of 1?

Answer 17

feature-request

Feature request: Firebird Query plans

The topic post features a screenshot with MS SQL query with the query plan rendered. Firebird also has plan output feature, and it was enhanced in Firebird 3 to be more human-readable.

• Example of output: https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-dml-planoutput.html
• Mention of Firebird API to be used: https://forums.devart.com/viewtopic.php?t=36639
• Buffer size needed to get new "explained plan" in Firebird sources and the routine fetching it should be 64KB (MAX_USHORT) for Firebird 2.5+ - see isql.h and static void process_plan() in isql.epp starting with https://github.com/FirebirdSQL/firebird/commit/24b7a7982cbae16b3c01629b58325b1ade37385e#diff-5a884df9c41990ebe603e73e1b9a05ba

I wonder if PHP FB library has or can be modified to have support for fetching query plan as well as resulting data.

Answer 18

feature-requeststatus-declined

Feature Request: display server version. Sometimes tools like db<>fiddle are used to test some edgy query on different server versions, like looking for a bug in either query or server.

Usually database engines have some means to query version, but they may be not exposed to SQL or may require some in-depth knowledge.

It would be nice if after pressing "Run" button the DBMS version, as detailed as reasonable, was displayed for example in left bottom corner of the page.

In case of Firebird it can be obtained:

• brief version by select RDB$GET_CONTEXT('SYSTEM','ENGINE_VERSION') from rdb$database
• detailed version by asking for "version" and "implementation" with PHP function https://www.php.net/manual/en/function.fbird-server-info.php

Answer 19

feature-requeststatus-completed

A couple of nice-to-haves would be:

Ctrl-Enter to run the code rather than having to use the mouse?

and also, after running the code, can we be dropped at the end of the page? Normally, one tries stuff, progresses, then adds more - so the focus of interest is normally at the bottom of the page. It's irritating to have to scroll down each time and it would be deadly if we could be dropped to the bottom of the page.

Informix?

As I said, these are nice to haves and kudos for your amazing achievement so far - I particularly appreciate the fact that you cater for Firebird - a totally underrated and overignored system that (like PostgreSQL) is (vastly) superior to MySQL (<rant> how is it the most successful of the F/LOSS servers despite being the worst of them? </rant>...) - is the code up on GitHub or similar yet - you did mention that at one point.

Finally, could you please make one-off payments possible? I don't like the idea of recurring payments and would just like to make a (small) one-off donation for the excellent service you provide to the community.

EDIT (22/11/2019):

Hi again Jack, a few things too big to put into a comment.

• The Ctrl-Enter thing works but, as I said earlier, erratically. It seems to drop you somewhere in the middle of the page regardless of where you were last editing - which can in fact be confusing! I've noticed this behaviour on Windows, Linux and Mac Airbook.

• EXPLAIN EXTENDED doesn't work on MySQL 8 but does on MySQL 5.7? EXPLAIN does work for both.

• I discovered the above issue when looking for timings for MySQL. They don't come with the EXPLAIN EXTENDED whereas they do with PostgreSQL (gotta love it!). Would there be a way of including timings by default?

• With the Microsoft Edge browser (for the 5 people who use it! :-) ), if you try and copy and paste the results from a db<>fiddle query into the StackExchange text boxes, it looks like this (compare with the answer and the fiddle it was copied in from using Chrome):

Every single column name/field becomes a new line:

tid_1
tid_2
loc_1
loc_2
ldt_1
ldt_2
diff
Mon
Mon
Hom
Hom
2019-10-22 10:27:40
2019-10-22 11:22:40
3300
Mon
Mon
Gas
Gas
2019-10-22 12:43:00
2019-10-22 13:43:00
3600
Mon
Mon
Hom
Hom
2019-10-22 16:43:00
2019-10-22 17:43:00
3600
Mon
Mon
Hom
Hom
2019-10-22 20:43:00
2019-10-22 20:56:00
780

As for Informix, I wrote to them:

Dear Sir or Madam, I wish to use your Informix Innovator-C Edition for a not-for-profit project and am wondering if the licence will permit this? The site/project is one which permits users to test SQL against servers to which they may not have (immediate/current) access (small data volumes). I believe that such usage would be covered by the "free for development, testing, and small production use." section of the licence (from here: https://www.ibm.com/products/informix/editions), but would just like to confirm. Should you require any further information, please don't hesitate to contact me. Best regards,

and they replied:

Thank you for contacting the IBM Service Center. I refer to your e-mail received 05.11.2019

You have reached the IBM UK General Enquiries.

With your inquiry please contact our Support team for Informix products on +44 800 013 0333. Please note this department is only reachable via phone as it hasn’t got a general email address.

Or visit our website on http://www-306.ibm.com/software/data/

Or you can also contact our Software Team on +44 239 228 2340

If we can be of any further assistance please do not hesitate to contact us at [email protected] Any requests sent through to the representative directly will not be actioned immediately.

Kind Regards,

Pavol

I tried ringing but got put on hold and as it's an international call for me, I didn't fancy racking up large bills. If you are UK based (?) maybe you might fancy ringing the 0800 number (free from UK AFAIK)? This isn't a big deal for me personally - I haven't used Informix in years, but it was my first RDBMS (and a good one), so there's a residue of affection but it appears to be slowly withering on the vine in any case. A quick check shows 68 questions all told with the Informix tag, slightly behind Firebird with 77.

Should you require any further information/testing, please don't hesitate to ping me. Again, I would like to thank you for your sterling efforts on behalf of the community to date.

Answer 20

bug

I'm so excited I found dbfiddle! What a wonderful service to have at hand, thank you! I was especially floored by how it's already got CU1 applied to SQL Server 2019, or Firebird 3.0.5, both released mere days ago. However, I'm here with a bug report, for Firebird specifically.

There's something wonky going on with LIST(), Firebird's version of GROUP_CONCAT() in MySQL/SQLite, or STRING_AGG() in Postgresql/MS SQL Server. It's supposed to return a text string, but on dbfiddle I keep getting odd binary values.

Here's a simple example dbfiddle:

select list(t) from (select 'hello' as t from rdb$database union select 'world' from rdb$database);

| LIST               |
| :----------------- |
| 0x0000000100000000 |

db<>fiddle here

On any system I have, this returns the expected 'hello,world' not 0x0000000100000000 which I honestly can't even imagine where that might be coming from. An errant UDF overriding the built-in function?