Common Lisp output file streams SBCL

I've made the important points bold. The problem is actually more tricky then one might think:

Let's look at the form.

First mistake: it's not indented correctly. Let's indent:

(with-open-file (output (open #p"file.txt"
                              :direction :output
                              :if-exists :overwrite))
  (format output "test")))

Now we can see more mistakes. An additional parentheses

(with-open-file (output (open #p"file.txt"
                              :direction :output
                              :if-exists :overwrite))
  (format output "test")))  ; <- additional parenthesis

But more important:

(open #p"file.txt"
      :direction :output
      :if-exists :overwrite)

Above opens a file for writing output and returns a stream.

WITH-OPEN-FILE does also open a file. So you try to open the file TWICE, first for writing..

(with-open-file (output stream)
  (format output "test")))

Above opens a file for reading. You have opened the file twice: first for writing, then for reading.

Now you try to write with FORMAT to an input stream.

The slightly surprising part is this: both open and with-open-file can take a file stream as a file spec. If it gets a file stream as a file spec, then the associated pathname is used for the open operation.

So, as mentioned in another answer, this would be more correct:

(with-open-file (output #p"file.txt"
                        :direction :output
                        :if-exists :supersede)
  (format output "Hello"))

SBCL error message:

#<SB-SYS:FD-STREAM for "file /home/me/file.txt" {1004A90813}>
is not a character output stream.

The point of the error message here is not that the stream is not a character stream. It's not an output stream. The stream actually is a character input stream! Thus calling FORMAT using the stream won't work. Let's write an assert to verify this:

CL-USER 18 > (with-open-file (output (open #p"/tmp/file.txt"
                                           :direction :output
                                           :if-does-not-exist :create
                                           :if-exists :overwrite))
               (assert (output-stream-p output) (output)
                       "The stream ~a is not an output stream!"
                       output)
               (format output "test"))
Error: The stream #<STREAM::LATIN-1-FILE-STREAM /tmp/file.txt>
is not an output stream!

Your extra question: Why is the following form working?

(with-open-file (input (open #p"file.txt")) ...)

It just opens the file TWICE for reading.