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-completed

Thank you for this. I have been using rextester for this, but it doesn't work properly for Oracle.

I don't like the tiny input field to start with. I would prefer it to be presented as a much wider (and higher) text field so that it's immediately clear that you can input more then just a few characters.

It also doesn't auto-expand for subsequent statements (using Firefox on Windows 10). After pressing the + the input field stays at the initial size. You can input larger statements but you can't see them properly.

I know parsing SQL is hard, but having to click the + button for each new statement is a bit cumbersome.

Also, if anyone wants to talk me through installing SQL Server 2016 on Debian without a GUI, I'm all ears

What about: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-docker

Answer 2

feature-requeststatus-completed

Just as a suggestion:

For me syntax coloring would be appreciated.

And some short-key to execute instead of click on RUN button.

And I don't know if it is a vNext feature, but dates fields are shown using this long format:

 Jan  1 2017 12:00:00:000AM

Answer 3

The only thing I need is the Text To DDL function from sqlfiddle:

Of course I can create the DDL in sqlfiddle and copy to dbfiddle. But would be nice having that function included.

Answer 4

status-completed

A few comments after superficial testing:

0.

Thank you for the site. It's generally great!

1.

Are you ok with links posted on other SO sites or anywhere in the web? Example on SO, where I just updated an existing answer with it:

• Best way to check for “empty or null value”

Not sure how you would stop people from doing that, but you can stop me.

2.

I have used the generic text "SQL Fiddle" to link to (mostly) sqlfiddle.com in the past. Do you think it's appropriate to use the same text for links to your site or do you prefer "dbfiddle" like you have it in the markup. Maybe more useful for the general public, to see where the link is going right away?

3.

There might be an option to remove an additional SQL window (except for the first one). Currently we can only add more, but there is no way back. The Plus + might flip to -. Also there might be a clear per SQL window (additionally), not just for the whole page.

4.

I like the spartan, no-nonsense design.

• Result fields might have a tiny bit more spacing. That's what I see in Firefox 51.0.1:

5.

I like that we can have any number of queries with results.

• It might be useful to have an option to (re-)execute only one of them. i.e. a button like run this below each SQL window in addition to the general run at the end. Might require more webdesign smarts to update only the selected section (I am no expert with this.).

  While the next SQL window can depend on results from the previous one, that does not have to be the case (like in my example above).

  Alternatively (additionally), run so far could be useful.

6.

I like that we have Postgres versions 9.4, 9.5 and 9.6 at our disposal. SQL Fiddle didn't keep up.

7.

I like the markdown feature, which produces this (example from link above):

-- empty string equals any string of spaces when cast to char(n)
SELECT ''::char(5) = ''::char(5)     AS eq1
      ,''::char(5) = '  '::char(5)   AS eq2
      ,''::char(5) = '    '::char(5) AS eq3;

/*
|eq1|eq2|eq3|
|:--|:--|:--|
|t  |t  |t  |
*/

• What do you (or others) think about this alternative format for results?

eq1 | eq2 | eq3
----+-----+----
t   | t   | t

Less noise, more spacing. It's the default format in psql, with "quoted code" markdown. (Meta has no syntax highlighting, effects are better viewed on the main site.)

8.

On-site disclosure / statements on site owner, intended use, license, update policy, expected lifetime, public visibility, privacy?

I know, it's still beta. Just mentioning it.

Answer 5

Excellent work, but I don't care much for the logo. I'm not an artist, but here's a stab at something reminiscent of what you have. If you don't like it, that's fine, but please find something else.

Answer 6

I tried the Msft SQL Server vNext option by copying this SQL Statements to create tables and data into the first field and a working solution into the second field. You can see the result in my dbafiddle.uk.

It it unclear to me what i would have to do next, to see a result? Some error message or some indication that the tables are created would be helpfull.

Update after comment that it runs now

The html structure is currently this sequence of html tags <input><textarea /><textarea />. It seems that the first textarea is used for the user to input data and the second is used to output a result for instance a ✓.

• It would be helpful to see the result next to each first textarea as either a statement (tables created) or a preview of the table strcuture (see example below). In my dbfiddle above only that the second group of (<input><texta /><texta />) had an output

• I noticed that the output of my first dbfiddle was a little off (to many white space). So I forked the above dbfiddle to confirm that the cause was the RoomName [nchar](120) inside CREATE TABLE [dbo].[Rooms]... This made me aware that a fork button and a field to comment what was changed in the fork could be useful. Linking the forked fiddles could be useful as well. Another option would be to stripe (remove whitespace) of the output columns (although this might be undesired in some cases).

Example: Add a table definition output for each create table-statement next to the matching textarea field like this.

| column name | type        |
|-------------+-------------|
| id          | PRIMARY KEY |
| start_time  | TIMESTAMP   |
| name        | VARCHAR     |
|-------------+-------------|

Answer 7

status-completed

Thanks for making this tool! I tried to break the site. Hopefully some of this is useful feedback.

1.

Any chance of getting Oracle 12c? 11g is pretty old at this point although I think Oracle recently extended support for it.

2.

It looks like SQL Server 2014 is pretty close to RTM. If possible can you patch it, at least to SP2?

Testing on SQL Server 2016:

3.

Can we get a bit more info in the erorr messages? Here's what I get in SSMS for the following query:

SELECT 1 / 0

Msg 8134, Level 16, State 1, Line 1

Divide by zero error encountered.

Here is what I get on the site:

Line 1: Divide by zero error encountered.

The following queries all return the "22P02ERROR: invalid input syntax for type json DETAIL: The input string ended unexpectedly. CONTEXT: JSON data, line 1:" error message:

5.

Any query that selects a VARBINARY value such as this one:

SELECT CAST('A' AS VARBINARY(1));

6.

This code takes a really long time to execute before failing (I would expect a timeout but if it generates too much data before doing so that's fine):

DECLARE @i INT = 0;
WHILE @i < 100
    SELECT REPLICATE('Z', 8000);

7.

use tempdb;

8.

use msdb;

9.

use master;

10.

DECLARE @str VARCHAR(MAX) = REPLICATE('Z', 8001);

SELECT 
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, 
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str,
@str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str, @str;

11.

Is there an easy way to make this one display more than just seconds? It is precise to more than just seconds (as opposed to GETDATE()):

SELECT SYSDATETIME();

Answer 8

status-completed

Extensions / additional modules for Postgres

Besides plpgsql (installed by default) I currently see these additional modules on your site for PostgreSQL 11:

select * from pg_extension;

btree_gin
btree_gist
pageinspect
pg_trgm
postgis
postgis_topology
tablefunc

I repeatedly found myself wishing for some more. Most importantly:

intarray
hstore
tsm_system_rows
unaccent

All of them included in the contrib module of the main distribution. See: "Appendix F. Additional Supplied Modules"

intarray, hstore and unaccent are essentials, and tsm_system_rows is tiny and convenient.
(dblink is also common and useful, but may be to powerful for your site. So I did not include it in the wishlist.)

Would you think it's possible to install those?

Answer 9

status-completed

Is there a chance we get Oracle 18c version?

Oracle Database 18c XE now available!

Answer 10

status-completed

Oracle - DBMS_OUTPUT.PUT_LINE does not print anything and SET SERVEROUTPUT ON is not working.

It would be really useful to test PL/SQL programs. Is there any other way to see this output, other than creating a function and seeing output from a select?

Answer 11

Small Screens Hide Vital Controls (eg "Run")

The images above are from a Samsung S5 using Chrome, not the largest phone screen, but also not the smallest. In landscape mode one can "just" access the Run button, but this is not possible in portrait orientation at all. While I accept that writing SQL on a phone is tedious in the extreme (well I find it is) many people do read Q/A information on such screens.

"Split" is not an intuitive feature:

It took me a few attempts to understand what "Split" does (divides a block of DDL/DML/SQL into individual parts on screen). This is unique to your user interface as far I can tell and certainly not a normal part of SQL. It also appears to be essential to your UI.

I didn't find this initiative at all. Personally it seems of equal importance - and a prerequisite - to "Run" so why isn't it just embedded into the run logic? (and/or placed to the left of run in the top menu?)

Finding the URL

It took me a little while to realize that I needed to use Markdown to locate a URL which would allow me to return to the fiddle.

Removing the 9 unwanted "boxes" to produce "My Fiddle"

If I open https://dbfiddle.uk by default you display multiple "splits" which are aimed at helping "me-the-dumb-user" to understand how it works. Perhaps I'm wrong, but if I want a markdown output that is exclusively related to my own fiddle, I first need to remove these "help splits". That's 9 or so remove clicks to reach a "clean start".

While help is useful for the first-time user, it's tedious for an experienced user. Why not just provide a Help link in the toolbar which displays "help"? The default screen would in that case be almost blank and ready for a new fiddle to be created with no need to hit remove multiple times.

Off-topic content

While faith may be an important topic to some, does it really warrant promotion from a technical help site?

Answer 12

bugstatus-completed

I created a three-batch script on SQL Server 2017 (Windows):

Batch 1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

CREATE TABLE dbo.T (n integer NULL);

Batch 2

CREATE TRIGGER trg ON dbo.T
AFTER INSERT AS
BEGIN
    SELECT * INTO #i FROM Inserted
    CREATE INDEX i ON #i (n);
END;

Batch 3

SET IMPLICIT_TRANSACTIONS ON;

INSERT dbo.T (n) VALUES (1);

SET IMPLICIT_TRANSACTIONS OFF;

This produces no output at all when run is clicked (I expected an error about snapshot isolation not being enabled). There is no indication that db<>fiddle has tried to execute the script at all.

feature-requeststatus-completed

Please enable snapshot isolation for db<>fiddle:

ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;

Answer 13

bugstatus-completed

Not sure if it is correct place to put bug report. But let's give a try:

When I run(SQL Server 2012-2017) I can't see any result set.

SELECT i, COUNT(*) cnt
FROM tab
WHERE status = 1
GROUP BY ALL i;  -- ALL is deprecated feature

Output:

Warning: Null value is eliminated by an aggregate or other SET operation.

Temporary workaround:

-- workaround materializng to table
SELECT i, COUNT(*) cnt
INTO tab2
FROM tab
WHERE status = 1
GROUP BY ALL i;

SELECT * FROM tab2;

DBFiddle Demo

Answer 14

Any chance you could do some user agent sniffing and completely ditch the fancy code boxes in favour of just plain old text boxes if the user is using a cellphone?

Or even simpler to implement and probably better; just a toggling link/button switcher that flips between "use fancy editor/use basic text box editor" so I can flip the mode myself?

I can't get copy or paste to work at all on an iPhone (to be fair, I haven't tested Android) with the fancy text boxes on dB<>fiddle, which kinda makes the entire site unusable for all but the simplest queries as I'd have to literally type some OP's entire query in manually off of SO etc

Many thanks!

Answer 15

Awesome work!

Looks like it would be handy for some of the answers I've been posting to https://codegolf.stackexchange.com/, and what would be super helpful would be to have a byte counter somewhere underneath each code box, since most of the contests are scored by size.

Answer 16

Bug: If you [remove] all sections, then you get just the separator line, and no way to get an empty section. Clicking [+] just gives more separators.

Answer 17

bugstatus-completed

When I try to run:

SELECT COUNT(*)
FROM dual
GROUP BY ()

It works on Oracle 11g but fails on Oracle 18c:

Answer 18

status-bydesign

This is just question/minor bug? When do we get rows count?

For example DBFiddle Demo - PostgreSQL and DBFiddle Demo - SQL Server

The only difference is that I commented WHERE clause.

Answer 19

status-declined

Oracle collection and COLLECT is not showing any result.

Sample scenario:

CREATE TABLE tab(NR NVARCHAR2(100), GRD NVARCHAR2(100), DT DATE);

INSERT INTO tab(NR, GRD, DT)
SELECT '00000000000000000001' AS NR,N'06' AS GRD,DATE '2013-01-01' AS DT 
FROM dual UNION ALL
SELECT '00000000000000000001',N'06',DATE '2013-01-01'  FROM dual UNION ALL
SELECT '00000000000000000001',N'21',DATE '2013-01-01' FROM dual UNION ALL
SELECT '00000000000000000002',N'06',DATE '2013-01-01' FROM dual UNION ALL
SELECT '00000000000000000002',N'21',DATE '2013-01-01' FROM dual UNION ALL
SELECT '00000000000000000004',N'01',DATE '2013-03-31' FROM dual;

CREATE TYPE tbl_array AS TABLE OF NVARCHAR2(4000);

And query:

select nr, CAST(collect(grd) AS tbl_array) grds
from tab
group by nr;

Should return:

┌──────────────────────┬────────────────────────────────┬
│          NR          │              GRDS              │ 
├──────────────────────┼────────────────────────────────┤
│ 00000000000000000001 │ HR.TBL_ARRAY('06', '21', '06') │ 
│ 00000000000000000002 │ HR.TBL_ARRAY('06', '21')       │ 
│ 00000000000000000004 │ HR.TBL_ARRAY('01')             │ 
└──────────────────────┴────────────────────────────────┴

But there is no result at all.

DBFiddle Demo

Note: Similiar scenario with PostgreSQL is working fine.

DBFiddle Demo - PostgreSQL

Answer 20

status-completed

No CREATE TYPE privilege under Oracle. It would have been greatly helpful if included, especially to test/ demonstrate queries and PL/SQL code containing collections of records and TABLE functions.

Answer 21

status-bydesign

I'll put this in as an "answer" and maybe consolidate my "bugreports" here.

I was answering this question, so I used your tool to construct the fiddle. It's a question about SQLite.

Here is the fiddle that answered the first part of the question - an UPDATE.

To answer part two, I tried this. As you can see, it fails. I'm quite persistent, so I tried db-fiddle (your arch-rival? :-) ) here and it works nicely.

Any thoughts?

Answer 22

bugstatus-completed

Here is an advanced attempt, trying to create and use a function:

http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=0c843d449e3fb7b22975c57b3594a79b

42883ERROR:  function f_tmp_groups() does not exist
LINE 1: SELECT * FROM  f_tmp_groups();
                       ^

Not sure why this happens. A simple SQL function works:

http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=af6b4ee3972343aaeeed4a197b5407e7

And a basic set-returning plpgsql function works, too:

http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=28aae0139d521a01c26e748234fe178a

After separating CREATE TABLE and INSERT statements, the first example works:

http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=a64f32b548c342189762e746c16a0eaa

I am confused at this point.

Aside: format of error msg ...

Answer 23

bugstatus-completed

this morning I tried to use dbfiddle.uk with PostgreSQL 11.

Extremely simple sample SQL:

CREATE TABLE test (bill INTEGER, fred TEXT);

and then:

INSERT INTO test VALUES (1, 'sdfs'), (2, 'vcxf'), (3, 'tyiro');

followed by:

SELECT * FROM test;

Result in PostgreSQL 10 (as expected):

bill    fred
   1    sdfs
   2    vcxf
   3    tyiro

But PostgreSQL 11 fails to do anything - not even the ticks for correctly creating the table and inserting the data.

The fiddle is here. Just as an aside, it works fine for all versions of MySQL.

Answer 24

status-completed

Comments/Bugs link on dbfiddle.uk directs me to here, so I suppose "answering" is the way to submit bugs:

Select MySQL 5.7 as database and try this extra simple DML command:

create table errorTest (id integer, issue text);
insert into errorTest (id, issue) values (1,'issue #1');

It is expected to work but comes up with an error message saying:

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 'insert into errorTest (id, issue) values (1,'issue #1')' at line 2

Answer 25

bugstatus-completed

Bug report: cannot create a function in MariaDB link:

You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

though for MySQL I can.

Answer 26

bugstatus-completed

Thanks for your site.

I have two requests:

1. Improvement: current Firebird is 3.0.1, the current latest is 3.0.4. Would it be possible to upgrade?

2. Bug: The result set uses the 'original column name' instead of the alias. The 'original column name' isn't always present. Depending on how the query is constructed it is 'lost' in some versions of Firebird, or never existed in the first place (eg with literals in the select clause), and for example function calls will use the function as the original column name.

   For an example see this fiddle. The first select should have column heading SHIFT_LOG_DET_ID, ENTRY_TIME, DURATION, but has (empty), (empty), DATEDIFF. The second select should have SHIFT_LOG_DET_ID, ENTRY_TIME, DURATION, but has SHIFT_LOG_DET_ID, ENTRY_TIME, DATEDIFF.

   

   In the low-level Firebird API this is the difference between using isc_info_sql_field (original column name) or isc_info_sql_alias (alias).

Answer 27

feature-requeststatus-completed

For Postgres please add the ltree extension :) That would be great. Sometimes there are SO questions referencing it.

Answer 28

bugstatus-completed

The height of the scroll container is too low. I strips of some pixels from the lowest line, making commas and other delimiters quite unreadable:

The height seems to be computed explicitly somewhere and set as an inline style. Using em here works better for me, or just add some margin please.

Answer 29

I really love the db<>fiddle. But sadly I am never able to edit my fiddles with my smartphone (Android, Chrome). There is a very strange behavior with text typing. It sometime overrides the original text when trying to insert something or puts the new text on wrong positions.

The cursor behavior is very strange as well or better: it is hard to handle (finding the right position, move something, ...)

It would be great to use your fiddle on mobile devices as well. Maybe you could check this because at the moment it is really not usable for me.

Answer 30

status-bydesign

I really like to know: When I execute multiple statements in one field (e.g. https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=a0fc52d4e45e4d925b428fabcdee70f5), are they treated as one transaction or are they committed separately?